V Tabulárním modelu mohou mezi tabulkami existovat vztahy, které nelze jednoduše zachytit pomocí klasických relací. Zaměřme se například na nejčastěji používanou relaci ONE-TO-MANY. Pokud v dimenzní tabulce produktů, která obsahuje jedinečné záznamy o produktech, zafiltrujeme jeden konkrétní produkt, tak v navázané tabulce prodejů budeme hledat všechny řádky, které obsahují konkrétní hodnotu (klíč), odpovídající hodnotě ze sloupce, na základě kterého je vytvořena vazba mezi těmito tabulkami. V Tabulárním modelu však můžeme mít i jiné typy dimenzích tabulek. Uvažujme například kategorie produktů, kdy do jedné konkrétní kategorie patří ten produkt, který má prodejní cenu v určitém rozmezí od minimální částky po maximální částku stanovenou pro danou kategorii. Minimální cena pro určité rozmezí bude v pro každou kategorii uvedena v jednom sloupci, maximální cena ve druhém sloupci. Aby daný produkt patřil do konkrétní kategorie, musí jeho cena být právě v rozsahu, který je dán minimální a maximální cenou. V tomto příspěvku jsou popsány dva z několika možných přístupů, jak můžeme v Tabulárním modelu pomocí DAX kódu zachytit vztahy definované na základě rozsahu.
Vztahy mezi tabulkami na základě rozsahu a jazyk DAX
Celý příklad je vytvořen ve cvičném Power BI souboru Contoso Sales Sample for Power BI Desktop.pbix, který je volně dostupný ke stažení na internetu. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem. V použitém modelu je mimo jiné k dispozici dimenzní tabulka s názvem 'Stores', která obsahuje obchody rozdělené do čtyř kategorií. Přes tyto obchody se pak prodávají produkty fiktivní společnosti "Contoso". Záznamy o prodaných produktech jsou v tabulce s názvem 'Sales'.
V tabulce 'Stores' jsou čtyři kategorie obchodů – Catalog, Online, Reseller a Store. V příkladech uvedených v tomto příspěvku budeme chtít vyhodnotit, zda obchody v dané kategorii dosahovali určité výkonnosti v konkrétním roce. Výkonnost bude vyhodnocena na základě objemu prodejů uskutečněných přes každý obchod. Objem prodejů přes konkrétní obchod ještě přepočítáme na počet zaměstnanců v daném obchodě. Dále budeme chtít při zařazení obchodu do výkonnostní kategorie rozlišovat, o jaký typ obchodu se jedná, protože nemá smysl porovnávat výkonnost přepočtenou na zaměstnance například Online obchodů s kamennými prodejnami. Obchody budeme řadit do čtyř kategorií podle výkonnosti (Velmi špatná, Špatná, Dobrá, Výborná). Tabulka, kterou budeme používat pro hodnocení výkonnosti obchodů, bude obsahovat čtyři řádky pro každý typ obchodu a sloupce s ID řádku, typem obchodu, kategorií výkonnosti, minimální hranicí pro zařazení do dané kategorie a maximální hranici.
Tabulku s hodnocením si můžeme v Power BI vytvořit jednoduše kliknutím na tlačítko "Nová tabulka", které najdeme na kartě "Modelování". Do okna editoru vzorců pak stačí vložit následující DAX výraz, který vygeneruje výše uvedenou tabulku.
Počítaná tabulka:
Hodnocení =
DATATABLE
(
"ID", INTEGER,
"TypObchodu", STRING,
"Výkonnost na zaměstnance", STRING,
"MIN Prodeje na zaměstnance", CURRENCY,
"MAX Prodeje na zaměstnance", CURRENCY,
{
{1, "Store", "Velmi špatná", 0, 50000},
{2, "Store", "Špatná", 50000, 100000},
{3, "Store", "Dobrá", 100000, 200000},
{4, "Store", "Výborná", 200000, 999999999},
{5, "Online", "Velmi špatná", 0, 7000000},
{6, "Online", "Špatná", 7000000, 14000000},
{7, "Online", "Dobrá", 14000000, 24000000},
{8, "Online", "Výborná", 24000000, 999999999},
{9, "Reseller", "Velmi špatná", 0, 5000000},
{10, "Reseller", "Špatná", 5000000, 7000000},
{11, "Reseller", "Dobrá", 7000000, 9000000},
{12, "Reseller", "Výborná", 9000000, 999999999},
{13, "Catalog", "Velmi špatná", 0, 800000},
{14, "Catalog", "Špatná", 800000, 1600000},
{15, "Catalog", "Dobrá", 1600000, 2200000},
{16, "Catalog", "Výborná", 2200000, 999999999}
}
)
Nová tabulka se po vytvoření stane součástí modelu, nicméně mezi tabulkou 'Hodnocení' a tabulkou 'Stores' nyní nemůžeme vytvořit relaci. V tabulce 'Stores' neexistuje klíč, který bychom mohli použít pro vytvoření relace s tabulkou 'Hodnocení'. Abychom mohli přiřadit hodnocení ke konkrétnímu obchodu, musíme nejdříve:
- Vypočítat prodeje přes daný obchod v konkrétním roce.
- Přepočítat prodeje přes daný obchod na jednoho zaměstnance.
- Zjistit typ daného obchodu (Catalog, Online, Store, Reseller).
- Na základě prodejů na zaměstnance a typu obchodu přiřadit výkonnost obchodu z tabulky 'Hodnocení'.
Jakmile dokážeme přiřadit hodnocení ke každému obchodu v tabulce 'Stores', čeká nás ještě jedno rozhodnutí. V první variantě můžeme přiřadit hodnocení obchodu přímo do tabulky 'Stores' pomocí počítaného sloupce (denormalizace). Druhou možností je vytvořit v tabulce 'Stores' počítaný sloupec s ID hodnotou odpovídajícího řádku z tabulky 'Hodnocení', na základě kterého vytvoříme mezi tabulkami 'Stores' a 'Hodnocení' relaci (normalizace). Logika obou výpočtů bude podobná, nicméně v případě, kdy budeme chtít vytvořit mezi tabulkami relaci, musíme pro výpočet počítaného sloupce použít jiné DAX funkce, než které použijeme v první variantě výpočtu.
Vytvoření nového sloupce na základě vyhodnocení více podmínek
Nový počítaný sloupec si vytvoříme v jednom kroku, kdy si jednotlivé mezivýpočty uložíme do proměnných. Nejdříve určíme rok, ve kterém chceme prodeje vyhodnotit. Jako rozhodující rok pro vyhodnocení výkonnosti můžeme použít rok, který bude předcházet poslednímu roku, ve kterém došlo k alespoň nějakým prodejům, abychom měli jistotu, že budeme pracovat s uzavřeným obdobím. Následně tento rok přidáme do filtru při vytvoření součtu prodejů pro jednotlivé obchody. Počet zaměstnanců v daném obchodě načteme do proměnné jednoduše ze sloupce 'Stores'[EmployeeCount]. Následně vydělíme sumu prodejů ve vybraném roce počtem zaměstnanců v daném obchodu. Poslední hodnotu, kterou potřebujeme zjistit, je typ obchodu, který je uložený ve sloupci 'Stores'[StoreType]. Jakmile máme v proměnných k dispozici všechny hodnoty potřebné pro sestavení filtrovacích podmínek v tabulce 'Hodnocení', můžeme načíst tuto tabulku zafiltrovanou pro aktuální obchod. Tato tabulka by měla v každém řádku tabulky 'Stores' obsahovat pouze jeden řádek, což si před vyhodnocením výpočtu ještě ověříme jednoduchým logickým výrazem. Celý výpočet může vypadat následovně.
Počítaný sloupec v tabulce 'Stores':
Výkonnost =
VAR PredchoziRok = YEAR(MAX(Sales[DateKey])) - 1
VAR ProdejePredchoziRok =
CALCULATE
(
SUM(Sales[SalesAmount]),
'Calendar'[Year] = PredchoziRok
)
VAR PocetZamestnancu = Stores[EmployeeCount]
VAR ProdejeNaZamestnance = DIVIDE(ProdejePredchoziRok, PocetZamestnancu)
VAR TypObchodu = Stores[StoreType]
VAR TabulkaDoFitru =
FILTER
(
'Hodnocení',
'Hodnocení'[TypObchodu] = TypObchodu &&
'Hodnocení'[MIN Prodeje na zaměstnance] <= ProdejeNaZamestnance &&
'Hodnocení'[MAX Prodeje na zaměstnance] > ProdejeNaZamestnance
)
VAR FiltrObsahujeJedenRadek =
COUNTROWS(TabulkaDoFitru) = 1
VAR Hodnoceni =
CALCULATE
(
VALUES('Hodnocení'[Výkonnost na zaměstnance]),
TabulkaDoFitru
)
VAR Vysledek =
IF
(
FiltrObsahujeJedenRadek,
Hodnoceni,
"Nezjištěno"
)
RETURN
Vysledek
Výsledkem je nový počítaný sloupec v tabulce 'Stores'. Tento sloupec pak můžeme použít v Power BI vizuálech a podívat se tak například, kolik obchodů patří do každé kategorie.
Měřítko:
Počet obchodů = COUNTROWS(Stores)
Na obrázku níže můžeme vidět, že nejvíce obchodů má hodnocení výkonnosti "Dobrá", naopak hodnocení výkonnosti "Špatná" má pouze 17 obchodů z celkového množství 306.
Stejně tak se můžeme podívat v každé výkonnostní kategorii na konkrétní obchody.
Pokud bychom chtěli propojit tabulku 'Hodnocení' s tabulkou 'Stores' pomocí relace, musíme pro vytvoření počítaného sloupce v tabulce 'Stores' s klíčem určeným pro relaci použít trochu jiný postup.
Vytvoření relace na základě rozsahu
Pokud bychom chtěli vytvořit relaci mezi tabulkou 'Hodnocení' a tabulkou 'Stores', musíme namísto slovního hodnocení ve sloupci 'Hodnocení'[Výkonnost na zaměstnance] načítat do nového počítaného sloupce jedinečné hodnoty ze sloupce 'Hodnocení'[ID]. Dále pak musíme v kódu nového počítaného sloupce určeného pro vytvoření relace nahradit ty funkce, které pracují s hodnotami z tabulky 'Hodnocení' a mohly by potencionálně vracet prázdné hodnoty BLANK. Takovou funkcí je například funkce VALUES(), kterou v kódu nahradíme funkcí DISTINCT(), která prázdné hodnoty BLANK ignoruje. Logika výpočtu pak zůstává stejná, změny oproti původnímu kódu jsou popsány také v komentářích.
Počítaný sloupec v tabulce 'Stores':
ID Hodnocení =
VAR PredchoziRok = YEAR(MAX(Sales[DateKey])) - 1
VAR ProdejePredchoziRok =
CALCULATE
(
SUM(Sales[SalesAmount]),
'Calendar'[Year] = PredchoziRok
)
VAR PocetZamestnancu = Stores[EmployeeCount]
VAR ProdejeNaZamestnance = DIVIDE(ProdejePredchoziRok, PocetZamestnancu)
VAR TypObchodu = Stores[StoreType]
VAR TabulkaDoFitru =
FILTER
(
'Hodnocení',
'Hodnocení'[TypObchodu] = TypObchodu &&
'Hodnocení'[Prodeje na zaměstnance MIN] <= ProdejeNaZamestnance &&
'Hodnocení'[Prodeje na zaměstnance MAX] > ProdejeNaZamestnance
)
VAR FiltrObsahujeJedenRadek =
COUNTROWS(TabulkaDoFitru) = 1
VAR Hodnoceni =
CALCULATE
(
DISTINCT('Hodnocení'[ID]),
//sloupec 'Hodnocení'[ID]
//namísto sloupce 'Hodnocení'[Výkonnost na zaměstnance]
//funkce DISTINCT místo původní VALUES
TabulkaDoFitru
)
VAR Vysledek =
IF
(
FiltrObsahujeJedenRadek,
Hodnoceni
//odebrán text s alternativním výsledkem
//v počítaném sloupci s datovým typem Whole number
//nemůže být text
)
RETURN
Vysledek
Na základě nového počítaného sloupce již můžeme vytvořit relaci mezi tabulkami 'Hodnocení' a 'Stores'.
V takto vytvořeném modelu můžeme s tabulkou 'Hodnocení' pracovat jako s kteroukoliv jinou dimenzní tabulkou.
Shrnutí
V Tabulárním modelu se můžeme setkat se situací, kdy mezi dvěma tabulkami existuje vztah, který je vyjádřen určitým rozsahem. V takovém případě můžeme hodnoty ze zdrojové tabulky denormalizovat, to znamená pomocí počítaných sloupců je přidat do cílové tabulky. Druhou možností je vytvořit relaci mezi tabulkami. Pokud chceme tabulky se vztahem na základě rozsahu propojit pomocí relace, nesmíme v počítaném sloupci, který je určený pro vytvoření relace mezi tabulkami, používat funkce, které mohou vracet hodnoty BLANK z tabulky, se kterou chceme relaci vytvořit.
Stáhnou soubor s řešením.
č. 44
Komentáře
Okomentovat