Při tvorbě Tabulárního modelu se můžeme relativně často setkat se situací, kdy mezi dvěma nebo více tabulkami existuje vztah, který by se dal obecně popsat jako vztah M:N. V tomto příspěvku si ukážeme alternativní přístup k vytvoření relace M:N, což se může hodit zejména pokud nemáme potřebné klíče pro vytvoření relací, nebo pokud by byla úprava modelu příliš komplikovaná.
K tomuto tématu je k dispozici také video:
Uvažujme například model v Power BI souboru Adwenture Works DW 2020.pbix.
Tento model na první pohled splňuje požadavky podle pravidel Star schématu, a jako takový je to ideální model analýzu dat v Power BI. Tabulka Sales obsahuje detailní informace o prodejích produktů, a všechny ostatní tabulky jsou s touto tabulkou propojeny pomocí relací 1:M. Tabulky na straně 1 obsahují popisné informace o událostech zaznamenaných ve faktové tabulce Sales.
Nyní si do modelu přidáme novou tabulku, která má následující obsah.
Tabulka 'Prodejní akce' obsahuje informace o produktech, které byly v určitém období prodávané v akci. Jak můžeme vidět na obrázku výše, tabulka obsahuje čtyři sloupce, název akce, podkategorii produktů a začátek a konec prodejní akce.
První informace, která je pro nás důležitá je, že každá podkategorie se může v tabulce vyskytovat vícekrát, a také v rámci jedné akce může být více podkategorií produktů.
Ještě důležitější je pro nás ale fakt, že tabulka 'Prodejní akce' nemá ideální strukturu pro připojení do našeho modelu. Důvodem je způsob definování začátku a konce prodejních akcí pro jednotlivé podkategorie produktů. Tyto dva datumy jsou každý v samostatném sloupci. V Tabulárním modelu ale nemůžeme vytvořit relaci na základě rozsahu daném hodnotami ve dvou sloupcích. Ideální by bylo, kdyby tabulka 'Prodejní akce' měla v jednom sloupci u každé podkategorie den, ve který prodejní akce probíhala.
Takto vytvořenou tabulku bychom teoreticky mohli propojit přímo s tabulku "Sales' pomocí relace 1:M, tedy za předpokladu, že bychom měli k dispozici klíče pro vytvoření relace, nebo že bychom si je vytvořili. My se ale v tomto příspěvku budeme držet původního formátu tabulky 'Prodejní akce', tak jak byla představena v úvodu.
Před tím, než se pustíme do vytvoření virtuální relace v měřítku, začněme přípravou reportu. Našim cílem pak bude vytvořit vizuál, kde uvidíme prodeje v jednotlivých prodejních akcích, dále rozpadlé podle let a kategorií produktů.
Roky a kategorie produktů pocházejí z tabulky 'Date', respektive z tabulky 'Product', a jako takové jsou připojené k tabulce 'Sales' pomocí relací a vše funguje tak jak má.
Pokud ale do řádků vizuálu vložíme hodnoty ze sloupce 'Prodejní akce'[Akce], a do hodnot opět měřítko [Prodeje], výsledek bude vypadat následovně.
Pro každou akci vidíme prodeje za všechny produkty ve všech letech, bez ohledu na to, kdy akce probíhala. Tabulka 'Prodejní akce' je totiž odpojená od modelu, a jako taková nefiltruje tabulku 'Sales', ze které sčítáme hodnoty za prodeje produktů v měřítku [Prodeje].
Pokud si tedy vytvoříme vizuál v naší požadované struktuře s roky, kategoriemi produktů a prodejními akcemi, výsledek nebude takový, jaký bychom chtěli.
Při pohledu na obrázek výše již bude stávající problém méně zřejmý, ale je pořád stejný. Roky a kategorie produktů filtrují tabulku 'Sales', prodejní akce tabulku 'Sales' nefiltrují.
Problém, který potřebujeme vyřešit spočívá v přesunutí filtrů z tabulky 'Prodejní akce' do tabulky 'Sales'. V tabulce 'Sales' ale nikde nemáme informace o tom, jestli vůbec a případně v jaké akci byly produkty prodány. V naší konfigurační tabulce 'Prodejní akce' je ale každá akce jasně definovaná podkategorií produktů a dny, ve kterých akce probíhala.
Proto bude postup takový, že pro každou akci si nejdříve načteme podkategorie produktů, které do dané akce spadají, a následně dny, které časově definují rozsah akce. Následně tyto načtené hodnoty použijeme pro zafiltrování tabulek, které obsahují dané atributy a současně filtrují tabulku 'Sales'.
Filtr s podkategoriemi produktů aplikujeme na sloupec 'Product'[Subcategory], a filtr se dny od do aplikujeme na sloupec 'Date'[Date].
Celý takto popsaný výpočet může vypadat například následovně.
Měřítko:
Klíčem k porozumění celého výpočtu je funkce SUMX() a tabulka 'Prodejní akce' v prvním argumentu této funkce. Protože máme v řádcích vizuálu sloupec 'Prodejní akce'[Akce], tak tento sloupec bude filtrovat tabulku 'Prodejní akce' vždy pouze na ty řádky, které odpovídají aktuální akci.
Následně si v každém řádku takto zafiltrované tabulky 'Prodejní akce' načteme aktuální kategorii, začátek a konec akce. Jakmile máme všechny hodnoty v proměnných, můžeme pro každý řádek tabulky 'Prodejní akce' vytvořit ve funkci CALCULATE() filtr, který bude filtrovat tabulky připojené k modelu.
Pro vygenerování dnů v rozsahu do začátku po konec akce můžeme použít Time Intelligence funkci DATESBETWEEN(), která bude v našem případě vracet přímo hodnoty ze sloupce 'Date'[Date]. Pro zafiltrování tabulky 'Product' pomocí podkategorií v dané prodejní akci pak můžeme použít funkci TREATAS(), kde si jen musíme dát pozor na to, že prvním argumentem této funkce musí být tabulka. My iterujeme řádek po řádku tabulku 'Prodejní akce', a proto musíme aktuální kategorii v aktuálním řádku iterované tabulky převést ze skalární hodnoty na tabulku. K tomu jsme použili konstruktor tabulky.
Oba dva filtry jsou pak vloženy do funkce KEEPFILTERS(), abychom nepřepisovali případné vnější filtry aplikované na sloupce 'Product'[Subcategory] a 'Date'[Date].
Pokud nové měřítko vložíme do vizuálu, výsledek bude vypadat následovně.
Jak můžeme vidět na obrázku výše, tak sloupec 'Prodejní akce'[Akce] nyní ovlivňuje výsledek měřítka [Prodeje v akci], a to způsobem, jako kdyby byla tabulka 'Prodejní akce' připojena k modelu.
Shrnutí
V Power BI velmi často pracujeme se složitými modely, které obsahují celou řadu tabulek. Pokud po dokončení vývoje vyvstane požadavek, který si vyžaduje přidání dodatečné tabulky do modelu, můžeme narazit na spoustu problémů, které mohou souviset s nejednoznačností modelu nebo s přílišnou složitostí celého řešení. V těchto a dalších situacích pak můžeme zvažovat, zda pro jeden konkrétní účel nevytvořit raději virtuální relaci v měřítku, namísto složité integrace dalších tabulek do modelu. Pokud se ale rozhodneme pro virtuální relaci, musíme mít vždy na paměti, že při práci s velkými objemy dat bude tento způsob přenesení filtrů pravděpodobně pomalejší než klasické relace.
Komentáře
Okomentovat