V tomto příspěvku si vysvětlíme podstatu uměle přidaného prázdného řádku v tabulkách nahraných v modelu. Tento uměle přidaný prázdný řádek není vidět v náhledu dat v Power BI. Jeho existence se ale projevuje v Power BI vizuálech a v DAX výpočtech, protože některé DAX funkce tento prázdný řádek zahrnují do svých výsledků.
K tomuto tématu je k dispozici také video:
Uměle přidaný prázdný řádek má svůj význam a je velmi důležitý. Tento prázdný řádek vzniká pouze v tabulkách, které mají vytvořenou standardní relaci (originálně Regular relationship) a jsou ve vztahu s ostatními tabulkami na straně 1. Prázdný řádek je pak do tabulky přidaný v případě porušení referenční integrity. K porušení referenční integrity dochází, pokud ve druhé tabulce, která je propojená s tabulkou na straně 1 pomocí relace, existují záznamy, které nemají navázaný záznam v tabulce na straně 1.
Ve výše uvedeném popisu může být celá řada pojmů, které si zaslouží podrobnější vysvětlení. Začneme ale jednoduchým příkladem na kterém si vysvětlíme podstatu uměle přidaného prázdného řádku.
Proč je někdy do tabulky uměle přidán prázdný BLANK řádek
V příkladech v tomto příspěvku budeme pracovat s jednoduchým Power BI modelem, který obsahuje pouze čtyři tabulky a vypadá následovně.
Tabulky 'Reseller', 'Date' a 'Product' jsou dimenzní tabulky a ve vztahu s faktovou tabulkou 'Reseller Sales' jsou na straně 1. Tabulka 'Date' je kalendářní tabulka, která obsahuje jeden záznam pro každý den, kdy rozsah dnů je dán roky, ke kterým máme záznamy o prodejích ve faktové tabulce 'Reseller Sales'. V tabulce 'Product' jsou informace o prodávaných produktech. Jeden řádek obsahuje popis jednoho produktu. Nás bude nejvíce zajímat tabulka 'Reseller', která obsahuje informace o obchodech, ve kterých dochází k prodejům produktů.
Mezi tabulkou 'Reseller' a tabulkou 'Reseller Sales' je vytvořena relace 1:M, a to na základě sloupců 'Reseller'[ResellerKey] a 'Reseller Sales'[ResellerKey]. V tabulce 'Reseller' jsou ve výchozím stavu ve sloupci 'Reseller'[ResellerKey] všechny hodnoty, které jsou také ve sloupci 'Reseller Sales'[ResellerKey]. Jinými slovy v tabulce 'Reseller' jsou všechny obchody, ve kterých došlo k prodejům produktů.
Nyní si můžeme vytvořit jednoduchý Power BI report s atributy z tabulky 'Reseller' a s měřítkem [Prodeje], které jednoduše sčítá částky za prodeje produktů z faktové tabulky 'Reseller Sales'.
Na levé straně je průřez se státy, ve kterých se obchody nacházejí, a ve vizuálu matice jsou názvy obchodů a částky za prodeje produktů v těchto obchodech.
Na tomto jednoduchém reportu není nic zvláštního. Situace se ale změní, pokud z tabulky 'Reseller' odstraníme některé záznamy. Odstranit můžeme například obchody, které jsou na prvních třech místech ve vizuálu matice na obrázku výše (A Bike Store, A Great Bicycle Company, A Typical Bike Shop).
Odstranit záznamy těchto tří obchodů můžeme jednoduše v Power Query. Tímto krokem si nasimulujeme porušení referenční integrity, protože tabulka 'Reseller' již nebude obsahovat všechny obchody, pro které jsou záznamy v navázané tabulce 'Reseller Sales' a některé záznamy v tabulce 'Reseller Sales' tak budou "osamoceny".
Stejný report bude po odstranění tří obchodů vypadat následovně.
Ve vizuálu matice je nyní na prvním místě prázdný řádek, u kterého vrací měřítko [Prodeje] hodnotu 177 689,45. Tato částka odpovídá prodejům přes tři obchody, které jsme odstranili z tabulky 'Reseller'. Všechny záznamy ve faktové tabulce 'Reseller Sales', které nemají odpovídající záznam v tabulce 'Reseller', jsou nyní přiřazeny právě k tomuto prázdnému řádku.
Uměle přidaný řádek do tabulky 'Reseller' obsahuje prázdnou hodnotu BLANK v každém sloupci. Proto vidíme prázdnou hodnotu také v průřezu, kde jsou státy, ve kterých se jednotlivé obchody nacházejí.
Tento uměle přidaný prázdný řádek ale nevidíme v náhledu dat tabulky 'Reseller'.
To může způsobovat rozčarování, protože ve vizuálech v reportu vidíme prázdné řádky, které nejsou v tabulce nahrané v modelu.
O existenci tohoto prázdného řádku se ale můžeme přesvědčit použitím vhodné DAX funkce. Některé funkce totiž uměle vytvořený prázdný řádek ignorují, a jiné ho zase do svých výsledků zahrnují. Jednou z funkcí, které vrací také uměle přidaný prázdný řádek, je funkce VALUES().
Výsledkem výše uvedeného DAX dotazu je celá tabulka 'Reseller', včetně uměle přidaného prázdného řádku, protože funkce VALUES() tento řádek zahrnuje do svých výsledků.
Pokud si tedy nejsme jistí tím, zda prázdný řádek, který se objevuje ve vizuálech, je výsledkem porušení referenční integrity, stačí použít funkci VALUES() podobně jako v předchozím DAX dotazu.
Kdy dochází k ověřování referenční integrity
K ověřování referenční integrity dochází pouze u tabulek, které jsou ve vztahu s jinými tabulkami na straně 1 a současně relace vytvořená s touto tabulkou je relace standardní (Regular relationship).
Opakem standardní relace je relace omezená (Limited relationship), u které nikdy nedochází k ověření referenční integrity a nedochází tak ani k umělému přidání prázdného řádku.
Omezená relace je například relace M:N, nebo relace, která je vytvořená mezi tabulkami z jiného typu úložiště (například jedna tabulka Direct Query a druhá tabulka Import).
Použití omezené relace v kombinaci s porušenou referenční integritou si můžeme znázornit také v našem cvičném souboru. Stačí změnit typ relace mezi tabulkou 'Reseller' a tabulkou 'Reseller Sales' na M:N.
U omezené relace je v náhledu modelu možné vidět přerušení u každé tabulky. Díky tomu můžeme na první pohled vidět, že u tabulek propojených pomocí omezené relace nedochází k ověřování referenční integrity.
Protože nedošlo k ověření referenční integrity, do tabulky 'Reseller' nebyl přidaný prázdný řádek, i přesto že v této tabulce stále nejsou tři obchody, které jsme odstranili v jednom v předešlých kroků.
Na první pohled se tak může zdát, že problém s prázdným řádkem může vyřešit omezená relace. Opak je ale pravdou. Pokud bychom nyní sečetli všechny hodnoty měřítka [Prodeje] u jednotlivých obchodů, nedostali bychom částku, kterou vidíme v řádku souhrnů. Rozdíl mezi součtem dílčích hodnot a hodnotou v řádku souhrnů bude 177 689,45, což je opět částka za prodeje ve třech smazaných obchodech. Částku 177 689,45 v řádcích vizuálu ale nyní nenajdeme.
Dalším problémem při práci s omezenou relací a současně s porušenou referenční integritou může být rozdíl mezi "filtrem na všechno" a žádným filtrem. Pokud v průřezu vybereme všechny státy, uvidíme v řádku souhrnů jinou hodnotu, než pokud není vybraný žádný stát. Tento rozdíl můžeme vidět, pokud si porovnáme hodnotu v řádku celkem na předchozím a následujícím obrázku.
V řádků souhrnů nyní vidíme částku 80 272 907,50. To je o 177 689,45 méně, než byla hodnota v řádku souhrnů, pokud nebyl vybrán žádný stát. Tato chybějící částka opět patří ke třem odebraným obchodům, které nyní v tabulce 'Reseller' chybějí a které by byly v případě práce se standardní relací přiřazeny k uměle přidanému prázdnému BLANK řádku.
Tyto nekonzistentní výsledky jsou dány právě porušením referenční integrity, která je u standardní relace 1:M nebo 1:1 řešena právě přidáním prázdného BLANK řádku do tabulky na straně 1.
Pokud ale pracujeme s relací omezenou, referenční integritu musíme ověřovat sami, před nahráním dat do modelu.
Uměle přidaný prázdný řádek má tedy svůj význam, i když je jeho přítomnost často problémem pro uživatele nebo i autory reportů, protože často neznají jeho význam.
Stále ale platí, že porušení referenční integrity je stav, kterému bychom se při tvorbě modelu měli vyhnout. Uměle přidaný prázdný řádek může být indikátor pro autory modelů k opravě tohoto stavu. Pokud ale pracujeme s modelem, ve kterém má porušení referenční integrity význam nebo pokud nemáme možnost, jak model opravit, je důležité vědět, že některé DAX funkce uměle přidaný prázdný ignorují a jiné funkce ho do svých výsledků zahrnují.
DAX funkce a uměle přidaný prázdný BLANK řádek
V této části příspěvku budeme opět pracovat s modelem, ve kterém je mezi tabulkou 'Reseller' a tabulkou 'Reseller Sales' standardní relace 1:M a v tabulce 'Reseller' nejsou tři obchody (A Bike Store, A Great Bicycle Company, A Typical Bike Shop), ve kterých ale došlo k prodejům a pro které existují záznamy ve faktové tabulce.
Poznámka: V následující části příspěvku budeme používat funkce VALUES(), DISTINCT(), ALL() a ALLNOBLANKROW() s argumentem ve formě tabulky. Níže popsané vlastnosti těchto funkcí ve vztahu k uměle přidanému prázdnému řádku jsou platné také při jejich použití s argumenty ve formě odkazů na konkrétní sloupce.
Pokud tedy v takto vytvořeném modelu vložíme do řádků vizuálu matice hodnoty ze sloupce 'Reseller'[Country-Region] a do hodnot měřítko [Prodeje], výsledek bude vypadat následovně.
Důvod prázdného řádku jsme si už vysvětlili dříve a jeho přítomnost by již neměla být překvapující. Představme si nyní situaci, kdy bychom chtěli spočítat procentuální podíl obchodů v jednotlivých státech vůči všem obchodům. Takto popsané měřítko může vypadat například následovně.
Měřítko:
V tabulce 'Reseller' odpovídá jeden řádek jednomu obchodu. Pokud tedy vydělíme počet řádků z tabulky 'Reseller' které jsou dostupné v aktuálním kontextu, počtem všech řádků v této tabulce, měli bychom dostat požadovaný výsledek.
I u takto jednoduchého výpočtu je zřejmé, že práce s modelem, ve kterém je mezi tabulkami porušená referenční integrita, nebude jednoduchá. Na první pohled můžeme vidět, že v řádku souhrnů máme špatnou hodnotu, protože v tomto řádku bychom měli dostat 100 %. Abychom zjistili, kde je ve výpočtu problém, vytvoříme si pomocná měřítka, kdy jedno měřítko bude vracet hodnotu dělence a druhé hodnotu dělitele.
V dělenci jednoduše sčítáme počet řádků z tabulky 'Reseller', které jsou dostupné v aktuálním kontextu vyhodnocení.
Měřítko:
V děliteli zjišťujeme počet všech obchodů následujícím způsobem.
Měřítko:
Obě nová měřítka si můžeme vložit do vizuálu matice a zobrazit si výsledky.
Problémem měřítka [% Počet obchodů] je, že v dělenci nepočítáme s uměle přidaným prázdným řádkem, zatímco v děliteli s tímto řádkem počítáme, protože funkce ALL() tento řádek do svých výsledků zahrnuje.
Pokud chceme načíst hodnoty v aktuálním kontextu vyhodnocení, máme, kromě odkazu na celou tabulku, dvě možnosti, které funkce použít. Funkce VALUES() zahrnuje do svých výsledků uměle přidaný BLANK řádek, zatímco funkce DISTINCT() tento řádek odfiltruje.
To si opět ukážeme a vytvoříme si další dvě pomocná měřítka. Začít můžeme funkcí VALUES().
Měřítko:
Výpočet s použitím funkce DISTINCT() pak bude vypadat následovně.
Měřítko:
Pokud obě nová měřítka vložíme do vizuálu matice, uvidíme, že funkce VALUES() zahrnuje uměle přidaný prázdný řádek do svých výsledků, a funkce DISTINCT() tento řádek ignoruje.
Pokud tedy chceme zahrnout prázdný řádek do výsledku, použijeme funkci VALUES(). V případě použití funkce DISTINCT() a nebo v případě jednoduchého odkazu na celou tabulku budeme tento uměle přidaný prázdný řádek ignorovat.
Podobně můžeme postupovat také v případě dělitele, kde máme také možnosti, jak uměle přidaný řádek zahrnout nebo nezahrnout do výsledku.
Pokud bychom chtěli zahrnout uměle přidaný prázdný řádek do výsledku, použijeme funkci ALL().
Měřítko:
Všechny obchody bez uměle přidaného prázdného řádku můžeme načíst pomocí funkce ALLNOBLANKROW().
Měřítko:
Výsledky obou dvou nových měřítek si opět můžeme porovnat ve vizuálu matice.
Pro výpočet procentuálního počtu obchodů máme jak pro dělence, tak pro dělitele možnosti, jak zahrnout nebo nezahrnout do výsledku uměle přidaný prázdný řádek. Ať ale použijeme jakoukoli variantu výpočtu pro dělence i dělitele, nedosáhneme v tomto konkrétním příkladu nikdy správného výsledku.
Problémem totiž je že tři chybějící obchody v tabulce 'Reseller' nyní zastupuje jeden prázdný řádek. Každá varianta výpočtu proto bude nepřesná. V jiných typech výpočtů nebo v jiném modelu ale může hrát zahrnutí nebo nezahrnutí uměle přidaného prázdného řádku do výsledku svou roli.
Shrnutí
Pokud DAX Engine detekuje porušení referenční integrity u standardních relací 1:M nebo 1:1, vytvoří se automaticky v tabulce na straně 1 prázdný řádek, ke kterému budou následně přiřazeny všechny "osamocené" řádky z navázané tabulky. Tento prázdný řádek může způsobovat problémy při tvorbě reportů nebo v DAX výpočtech. Nejlepším řešením, jak se tohoto uměle vytvořeného prázdného řádku zbavit, je oprava referenční integrity tím, že v tabulce na straně 1 budou ve sloupci použitém pro vytvoření relace k dispozici všechny hodnoty, které jsou také v tabulce navázané. Pokud oprava není možná, je nutné dbát zvýšené opatrnosti při tvorbě DAX výpočtů, protože některé DAX funkce uměle přidaný prázdný řádek do svých výsledků zahrnují, zatímco jiné ne.
Komentáře
Okomentovat