V tomto příspěvku si ukážeme, jakým způsobem můžeme za určitých okolností vytvořit chybějící klíče, které jsou potřebné pro definici relace mezi dvěma tabulkami nahranými v Power BI modelu.
K tomuto tématu je k dispozici také video:
V příkladu budeme pracovat se stejným modelem jako v předcházejícím příspěvku, kde jsme si ukazovali, jak chybějící relaci mezi dvěma tabulkami nahradit virtuální relací vytvořenou v měřítku.
Všechny tabulky zobrazené na obrázku výše pochází z databáze Adwenture Works DW 2020. Tento model je vytvořen podle pravidel Hvězdicového schématu a jako takový se jedná o ideální model pro práci v Power BI.
V modelu je navíc nahraná "konfigurační" tabulka 'Prodejní akce', která nepochází z databáze Adwenture Works DW 2020, a také nemá relaci s ostatními tabulkami v modelu.
Tabulka 'Prodejní akce' vypadá následovně.
Tabulka 'Prodejní akce' obsahuje čtyři sloupce a deset řádků. V prvním sloupci jsou názvy prodejních akcí, dále podkategorie produktů, které se v akci prodávaly, a datum začátku a konce prodejní akce.
Tabulka 'Prodejní akce' ale neobsahuje jedinečný identifikátor řádků, a ani v žádné jiné tabulce nahrané v modelu nenajdeme potřebné klíče pro vytvoření relace s touto tabulkou.
Pokud se ale na tabulku 'Prodejní akce' podíváme detailněji, tak zjistíme, že každá akce je definována produkty, které se v rámci akce prodávaly, a časovým obdobím, ve kterém se akce probíhaly. Díky tomu, že hodnoty ve sloupci 'Prodejní akce'[Produkty v akci] najdeme v tabulce 'Product' ve sloupci 'Product'[Subcategory], a dny ve kterých dané akce probíhali budou odpovídat hodnotám ve sloupci 'Sales'[Order Date] ve faktové tabulce 'Sales', můžeme si v tabulce 'Prodejní akce' vytvořit umělý identifikátor každého řádku, a tuto hodnotu následně přiřadit do tabulky 'Sales'.
To, že dokážeme identifikovat každý řádek v tabulce 'Prodejní akce' na základě jedné nebo více hodnot, které jsou již dostupné v modelu a které jsou buď přímo součástí faktové tabulky 'Sales', nebo jsou v navázaných tabulkách, je základní předpoklad pro vytvoření náhradních klíčů a hlavně k jejich přiřazení k záznamům ve faktové tabulce 'Sales'.
Než se ale pustíme do vytvoření samotných klíčů, zamysleme se ještě na chvíli nad samotnou strukturou tabulky 'Prodejní akce'.
Strukturu tabulky 'Prodejní akce' bychom mohli nazvat jako kompaktní. Důvodem je definice rozmezí, ve kterých akce probíhaly. U každé akce máme uveden první a poslední den, kdy akce probíhala, nicméně v tabulce nejsou uvedeny dny mezi těmito dvěma datumy.
Pokud bychom pracovali s tabulkou v této struktuře, museli bychom v každém DAX výpočtu, ve kterém bychom pracovali s akcemi a hlavně s obdobími, ve kterých akce probíhaly, vytvořit virtuální tabulku, ve které bychom generovali dny v rozmezí hodnot uvedených ve sloupcích 'Prodejní akce'[Začátek] až 'Prodejní akce'[Konec]. Tato transformace by nás stála výpočetní čas, ať už bychom pracovali s obdobím v měřítku, nebo v počítaném sloupci, kde je tento výpočetní čas spotřebován v rámci aktualizace dat v modelu.
Alternativou k současné struktuře tabulky 'Prodejní akce' je pak tabulka, ve které budou v jednom sloupci názvy akcí, ve druhém sloupci produkty, které se v akci prodávaly, a následně ve třetím sloupci dny, ve kterých akce probíhaly. Jinými slovy vygenerujeme do nového sloupce pro každou akci všechny dny, ve kterých daná akce probíhala.
Tento typ transformace je vždy lepší udělat před nahráním dat do modelu, nicméně na blogu věnovanému Power BI a jazyku DAX použijeme pro tento účel počítanou tabulku s následující definicí.
Počítaná tabulka:
Na následujícím obrázku můžeme vidět část nově vytvořené tabulky 'Prodejní akce (2)'.
Nová tabulka má jeden řádek pro každý den, ve který akce probíhala. Původní tabulka 'Prodejní akce' obsahovala pouze deset řádků, nová tabulka 'Prodejní akce (2)', kde jsou v samostatném sloupci všechny dny, ve kterých prodejní akce probíhaly, obsahuje 260 řádků. Tím se dostáváme k důležité otázce, a to, zda je lepší pracovat v datovém modelu s původní, kompaktnější verzí tabulky, nebo s novou verzí tabulky, která obsahuje násobně více řádků.
Na tuto otázku se ale nedá univerzálně odpovědět, protože vždy bude záležet na více faktorech. V našem příkladu budeme preferovat tabulku 'Prodejní akce (2)', která sice obsahuje daleko více řádků než původní verze tabulky, a bude tedy v modelu zabírat více místa, nicméně stále se jedná o zanedbatelný objem dat, v porovnání s ostatními tabulkami v modelu. Na druhou stranu, s novou verzí tabulky ušetříme výpočetní výkon spotřebovávaný při výpočtech.
V reálném modelu, kdy bychom pracovali s větším objemem dat, bychom pak při výběru jedné nebo druhé verze přemýšleli o dvou faktorech. S kompaktní verzí tabulky ušetříme místo v modelu, ale na úkor výpočetního výkonu, který je nutný pro transformaci rozsahu dnů na všechny konkrétní dny v daném období. Druhá verze tabulky pak bude zabírat více místa v modelu, ale měli bychom ušetřit výpočetní výkon.
Při rozhodování o jedné nebo druhé verzi proto budeme zvažovat zejména velikost modelu versus rychlost a složitost výpočtů.
Podle mé vlastní zkušenosti je většinou lepší variantou pracovat v Tabulárním modelu s tabulkou, která odpovídá struktuře tabulky 'Prodejní akce (2)', a to zejména z důvodu jednoduchosti výpočtů a rychlosti jejich vyhodnocení. Na druhou stranu, toto tvrzení je pouze relativní, protože v případě příliš velkého objemu dat můžeme narazit na nedostatek paměti nebo i na zpomalení samotných DAX výpočtů. Vždy je tedy nutné provést měření výpočtů u jedné a druhé varianty, a také kvalifikovaný odhad budoucích přírůstků dat.
Po krátké odbočce zpět k našemu příkladu a k tabulce 'Prodejní akce (2)', se kterou budeme dále pracovat.
V prvním kroku si vytvoříme v této tabulce jedinečný identifikátor každého řádku. K tomuto účelu můžeme použít DAX funkci ROWNUMER(). Funkce ROWNUMBER() funguje podobně jako funkce RANK(), to znamená že vytvoří pořadí podle zadaných argumentů. My pro určení pořadí, respektive pro účel vytvoření jedinečného identifikátoru každého řádku, použijeme všechny hodnoty z každého sloupce aktuálního řádku.
Počítaný sloupec v tabulce 'Prodejní akce (2)':
Ve funkci MATCHBY() jsou uvedeny všechny sloupce z tabulky 'Prodejní akce (2)'. Tím říkáme, že chceme určit aktuální řádek na základě hodnot ze všech sloupců tabulky zadané v prvním argumentu, kromě aktuálně tvořeného sloupce, čímž se vyhneme cyklické závislosti, která je relativně běžná při práci s WINDOW funkcemi v počítaných sloupcích, viz poslední odstavec v článku věnovaném jiné WINDOW funkci RANK().
Poznámka: Funkce ROWNUMBER() nevytvoří sama o sobě jedinečný identifikátor řádků. Název této funkce je matoucí, protože funkce ROWNUMBER() vytvoří pořadí na základě zadaných parametrů. Pokud bude zdrojová tabulka obsahovat duplicitní řádky, tak také funkce ROWNUMBER() může vracet duplicitní hodnoty, protože pořadí těchto duplicitních řádků bude stejné.
Výsledná čísla ve sloupci 'Prodejní akce (2)'[AkceKey] nemají žádný analytický význam, ale slouží pouze k identifikování aktuálního řádku. Ze stejného důvodu je v tomto konkrétním příkladu jedno, jestli použijeme řazení vzestupné (ASC), což je výchozí řazení, nebo sestupné (DESC), které bychom mohli zadat do funkce ORDERBY() jako další argument funkce ROWNUMBER().
Abychom mohli vytvořit relaci mezi tabulkou 'Prodejní akce (2)' a tabulkou 'Sales', vytvoříme si v tabulce 'Sales' nový počítaný sloupec, do kterého vložíme hodnoty ze sloupce 'Prodejní akce (2)'[AkceKey].
Protože je každá akce definována na základě produktů (podkategorií), které se v dané akci prodávaly, a dnů, ve kterých akce probíhala, musíme pro přiřazení správného klíče z tabulky 'Prodejní akce (2)' do tabulky 'Sales' použít dvě podmínky. To, zda se aktuálně prodaný produkt prodal v den, ve který probíhala akce, zjistíme porovnáním hodnot ve sloupcích 'Sales'[Order Date] a 'Prodejní akce (2)'[Datum]. Ve druhé podmínce budeme načítat aktuální podkategorii produktů pomocí funkce RELATED(), a tu porovnávat s hodnotami ve sloupci 'Prodejní akce (2)'[Produkty v akci]. Následně načteme hodnotu ze sloupce 'Prodejní akce (2)'[AkceKey], která odpovídá oběma podmínkám, a to pomocí funkce LOOKUPVALUE().
Počítaný sloupec v tabulce 'Sales':
Výsledkem je nový počítaný sloupec v tabulce 'Sales', který obsahuje u produktů prodaných v akci jedinečný identifikátor pro odpovídající řádek z tabulky 'Prodejní akce (2)'.
Produkty, které se neprodávaly v žádné akci, mají v novém sloupci prázdnou hodnotu BLANK. Nyní můžeme oba nové sloupce, tedy sloupec 'Prodejní akce (2)'[AkceKey] a sloupec 'Sales'[AkceKey], použít pro vytvoření relace.
Jakmile máme tabulku 'Prodejní akce (2)' připojenou k modelu, můžeme s touto tabulkou pracovat jako s kteroukoliv jinou tabulkou.
Můžeme si tak například vytvořit vizuál, kde použijeme v řádcích roky z tabulky 'Date', názvy akcí z tabulky 'Prodejní akce (2)', kategorie produktů z tabulky 'Product' a v hodnotách měřítko [Prodeje].
Jak můžeme vidět na obrázku výše, vše nyní funguje správně až na jeden detail, který způsobují produkty, které se neprodávaly v akcích a jejichž prodeje se zobrazují pod prázdným BLANK řádkem. Tyto produkty nemají v tabulce 'Prodejní akce (2)' žádnou kategorii. Tuto kategorii si ale můžeme uměle vytvořit, a to přidáním jednoho řádku do tabulky 'Prodejní akce (2)', pod kterým se následně budou shlukovat všechny produkty prodané v rámci běžného prodeje.
Začneme úpravou tabulky 'Prodejní akce (2)', do které přidáme jeden řádek pomocí funkce UNION().
Počítaná tabulka:
Dále budeme potřebovat změnit definice počítaných sloupců, které obsahují klíče pro vytvoření relace mezi tabulkou 'Prodejní akce (2)' a tabulkou 'Sales'.
Tabulka 'Prodejní akce (2)' nyní vypadá následovně.
Problém, který nyní potřebujeme vyřešit, je přiřadit k uměle přidanému řádku do sloupce 'Prodejní akce (2)'[AkceKey] hodnotu, která se nikdy nebude měnit, a současně takovou, kterou nikdy nebude vracet funkce ROWNUMBER(), kterou jsme použili pro vytvoření jedinečného identifikátoru každého řádku.
Funkce ROWNUMBER() nikdy nebude vracet zápornou hodnotu, a proto pro označení uměle přidaného řádku můžeme použít číslo -1.
Počítaný sloupec v tabulce 'Prodejní akce (2)':
Nyní, když víme že pro uměle přidaný řádek v tabulce 'Prodejní akce (2)' máme přiřazený identifikátor -1, nahradíme číslem -1 také všechny BLANK hodnoty ve sloupci 'Sales'[AkceKey].
To můžeme udělat jednoduše tak že si ověříme, jestli funkce LOOKUPVALUE() vrací prázdnou hodnotu BLANK. Pokud ano, přiřadíme číslo -1, v opačném případě načteme hodnoty ze sloupce 'Prodejní akce (2)'[AkceKey].
Počítaný sloupec v tabulce 'Sales':
Všechny záznamy v tabulce 'Sales', které obsahují informace o produktech prodaných mimo prodejní akce, mají místo prázdné hodnoty BLANK přiřazenou konstantu -1.
A těmto řádkům, běžným prodejům mimo akci, odpovídá jeden řádek v tabulce 'Prodejní akce (2)'.
Pokud nyní přejdeme zpět do reportu, problém s prázdným řádkem ve vizuálu Matice bude vyřešen.
Jak můžeme vidět na obrázku výše, všechny produkty, které se neprodávaly v akci, mají nyní samostatný řádek nazvaný "Běžný prodej".
Shrnutí
V tomto příspěvku jsme si ukázali, jak vytvořit chybějící klíče pro vytvoření relace mezi tabulkami. Prvním krokem je vytvoření jedinečného identifikátoru v tabulce, kterou potřebujeme připojit k modelu. Následně je třeba přenést tyto identifikátory do faktové tabulky, k čemuž se běžně používá funkce LOOKUPVALUE(). Pokud máme všechno správně tak by již neměl být problém vytvořit na základě nových počítaných sloupců požadovanou relaci.
Komentáře
Okomentovat