Doba načtení Power BI reportu se může výrazně lišit v závislosti na mnoha faktorech. Pomalé načítání reportů může být způsobeno například velkým množstvím vizuálů na jedné stránce, modelem, jehož struktura není vhodná pro Power BI (obecně pro SSAS Tabular model), obrovským množstvím dat v tabulkách spolu s velkým množstvím jedinečných hodnot ve sloupcích anebo velmi komplexními výpočty, které musejí být vyhodnoceny dynamicky v době načítání reportů.
K tomuto tématu je k dispozici také video:
V tomto příspěvku ale budeme pracovat velmi malým modelem (přibližně 100 000 řádků ve faktové tabulce), s modelem učebnicově vytvořeným podle pravidel hvězdicového schématu (Star schema), s reportem, který obsahuje relativně malé množství vizuálů a s jednoduchými měřítky. Přesto všechno je načítání reportu v použitém Power BI souboru neadekvátně pomalé. Problémem jsou totiž samotná měřítka, která sice neobsahují žádnou komplexní logiku, ale jsou špatně napsaná.
Cílem tohoto příspěvku je pak popsat proces, jak v pomalém reportu s několika vizuály a měřítky zjistit, které z mnoha výpočtů zpomalují načítání reportu a jak debugovat měřítka při jejich optimalizaci. Ačkoliv si v příkladu ukážeme také optimalizaci jednoho konkrétního měřítka, tato optimalizace bude pouze vedlejším efektem celého příspěvku.
Proces optimalizace pomalého reportu se skládá z následujících kroků.
2. Identifikace nejpomalejšího měřítka v DAX dotazu.
3. Vytvoření nové verze měřítka, které vrací stejné výsledky a je vyhodnoceno rychleji než jeho původní verze.
Protože v každém reportu může být více problematických měřítek, celý postup můžeme opakovat do té doby, dokud nebude doba pro načtení všech vizuálů v reportu splňovat naše očekávání.
Poznámka: Výše uvedený postup není jediný možný. Stejně tak není nutné při optimalizaci používat pouze DAX Studio, protože jsou k dispozici i jiné nástroje, některé stejně jako DAX Studio zdarma, pomocí kterých můžeme měřit rychlost vyhodnocení DAX dotazů.
V příkladech v tomto příspěvku budeme pracovat s klasickým Power BI souborem Adventure Works DW 2020.pbix. V tomto souboru, který je k dispozici ke stažení níže pod tímto příspěvkem, je vytvořený velmi jednoduchý report s několika vizuály a měřítky.
Poznámka: Některá měřítka v Power BI souboru, který je k dispozici ke stažení níže pod tímto příspěvkem, jsou pro účely tohoto příkladu záměrně napsána velmi špatným způsobem.
Použité vizuály ani hodnoty jednotlivých měřítek nejsou pro účely tohoto příspěvku podstatné. Podstatné ale je že načtení vizuálů v reportu zachyceném na obrázku výše trvá příliš dlouho. Jedním z faktorů, které mohou ovlivňovat rychlost načítání reportů jsou měřítka použitá ve vizuálech. V následující části si proto popíšeme, jak postupovat při hledání nejpomalejšího měřítka které brzdí načtení reportu, ukážeme si, jak měřit pomocí DAX Studia rychlost vyhodnocení měřítka v rámci DAX dotazu a jak v DAX Studiu debugovat novou verzi měřítka.
Jak už bylo několikrát zmíněno v úvodu tohoto příspěvku, kromě Power BI budeme pracovat také s DAX Studiem. DAX Studio je Open Source nástroj, který je zdarma dostupný ke stažení na webu daxstudio.org.
1. Identifikace nejpomalejšího DAX dotazu v reportu
Každý vizuál, který zobrazuje jakákoliv data pocházející z modelu, generuje DAX dotaz. Tento DAX dotaz je následně vyhodnocen DAX Enginem a vrátí výsledky ve formě jedné nebo více tabulek. Tato tabulka nebo tabulky jsou použity pro vykreslení použitého vizuálu. Pokud do vizuálu vložíme kromě sloupců z modelu také měřítka, jsou tato měřítka součástí vygenerovaného DAX dotazu a mají tak přímý vliv na to, jak rychle bude DAX dotaz vyhodnocen. V prvním kroku je proto důležité zjistit, který z vizuálů generuje nejpomalejší DAX dotaz.
K tomuto účelu můžeme v Power BI použít Analyzátor výkonu (anglicky Performance analyzer). Analyzátor výkonu najdeme v Power BI v levém postranním panelu. Pokud v levém postranním panelu není k dispozici ikona pro spuštění Analyzátoru výkonu, můžeme si ji přidat pomocí tlačítka plus pod poslední ikonou v tomto panelu.
Následně spustíme záznam pomocí tlačítka "Spustit záznam", čímž spustíme analyzátor výkonu. Po spuštění záznamu se aktivuje tlačítko "Aktualizovat vizuály". Pokud na toto tlačítko klikneme, výsledek bude vypadat následovně.
Po kliknutí na tlačítko "Aktualizovat vizuály" došlo k znovunačtení každého vizuálu na aktuální stránce v reportu. Současně vznikl ke každému vizuálu záznam v Analyzátoru výkonu. Zde je důležité poznamenat, že před aktualizací vizuálů pomocí Analyzátoru výkonu dojde k odstranění dat z mezipaměti (Cache) reportu a z mezipaměti Storage Enginu (bude vysvětleno později). Díky tomu můžeme získat obrázek o tom, jak bude vypadat rychlost načtení vizuálů v nejhorším možném scénáři.
Poznámka: Časy načtení jednotlivých vizuálu se po opakovaném spouštění mohou lišit, v závislosti na aktuálně aktivních procesech a s tím souvisejícím aktuálně dostupným výpočetním výkonem.
V našem příkladu je na první pohled zřejmé, že vizuál, který generoval nejpomalejší DAX dotaz je vizuál Matice. Obecně ale není dobré spoléhat se na celkovou dobu trvání načtení vizuálu, protože tato doba se skládá s více částí.
Pokud se podíváme na obrázek výše tak můžeme vidět, že u rozbaleného vizuálu Karta jsou uvedeny další tři řádky. V řádku "Další" vidíme čas v ms, který uplynul při čekání aktuálního vizuálu na načtení ostatních vizuálů v reportu. Tento čas můžeme ovlivnit pouze snížením počtu vizuálů v reportu. V řádku "Zobrazení vizuálu" pak vidíme čas nutný pro grafické vykreslení vizuálu. V řádku "Dotaz DAX" je pak čas v ms potřebný pro vyhodnocení DAX dotazu který vygeneroval daný vizuál.
Při hledání vizuálu s nejpomalejším DAX dotazem je tak dobré seřadit si jednotlivé řádky sestupně podle doby trvání Dotazu DAX.
Po seřazení máme na prvním místě vizuál s nejpomalejším DAX dotazem, který si nyní můžeme zkopírovat pomocí tlačítka "Kopírovat dotaz".
DAX dotaz který generuje vizuál Matice vypadá následovně.
DAX dotaz:
DAX dotaz si můžeme zjednodušit tak, že odebereme funkci TOPN() a klauzuli ORDER BY.
DAX dotaz:
Funkce SUMMARIZECOLUMNS() je "hlavní" funkcí téměř každého dotazu (ne všech), které generují Power BI vizuály. Tato funkce použitá v dotazu uvedeném výše načte všechny dny z datumové tabulky, a pro každý den vyhodnotí každé měřítko uvedené v dalších argumentech této funkce. Pokud si výše uvedený dotaz spustíme v DAX Studiu, výsledek bude vypadat následovně.
Jakmile máme k dispozici DAX dotaz, který vrací všechny hodnoty, které jsou použity pro vykreslení vizuálu, můžeme se pustit do další fáze, kterou je vyhledání nejpomalejšího měřítka.
2. Identifikace nejpomalejšího měřítka
Najít které měřítko je ze všech měřítek použitých ve vizuálu načteno nejpomaleji není složitá úloha. V první fázi zakomentujeme všechna měřítka. Dále budeme odkomentovávat postupně každé měřítko, a současně spouštět DAX dotaz pouze s jedním aktuálně odkomentovaným měřítkem. Při měření budeme čistit mezipaměť Storage Enginu a měřit čas vyhodnocení dotazu pomocí Server Timings.
Na obrázku výše můžeme vidět že v DAX dotazu jsou zakomentována všechna měřítka kromě měřítka [Prodeje], se kterým můžeme začít (pozor na odmazání čárky na konci každého řádku s měřítkem, který je aktuálně odkomentovaný). Výsledný čas pro vyhodnocení daného měřítka uvidíme na kartě "Server Timings".
Celkový čas vyhodnocení DAX dotazu můžeme vidět v poli "Total". Další důležitou informací kterou můžeme vidět na kartě Server Timings je rozdělení času mezi Formula Engine (FE) a Storage Engine (SE).
Formula Engine transformuje DAX dotaz do podoby logického a fyzického plánu. Tyto plány se skládají z po sobě jdoucích operátorů, jejichž výsledkem je finální tabulka. Formula Engine ale nemá přímý přístup k datům nahraným v modelu. Pokud Formula Engine potřebuje načíst data z modelu, předá instrukce Storage Enginu.
Storage Engine pak odešle dotaz do úložiště dat a vrátí načtená data Formula Enginu, který je může dále zpracovat. Tabulky, které vrací Storage Engine, mohou být pro jejich případné opětovné použití v dalších dotazech uloženy v mezipaměti (Storage Engine Cache). Proto je důležité při měření výkonu odstraňovat před spuštěním DAX dotazu data z mezipaměti, abychom pracovali vždy s nejhorším možným scénářem, se kterým se může uživatel reportů setkat.
Stejným způsobem jako v případě měřítka [Prodeje] si můžeme spustit DAX dotaz s dalšími měřítky. Pokud si do řádku s každým měřítkem zapíšeme čas dotazu v ms, zjistíme že suveréně nejpomalejším měřítkem je měřítko [Počet objednávek (1 ks)].
Dále se proto budeme zabývat pouze měřítkem [Počet objednávek (1 ks)], protože doba vyhodnocení ostatních měřítek je v porovnání s tímto měřítkem zanedbatelná. Ostatní měřítka tedy můžeme z dotazu odstranit.
Měřítko [Počet objednávek (1 ks)] je záměrně napsáno velmi špatným způsobem. Hlavním problémem tohoto měřítka je že generuje neadekvátně velké množství SE dotazů, které vrací nekomprimovaná data v tabulkách a které musí být následně zpracovány FE. Dotazy, které generuje SE, můžeme vidět uprostřed na kartě Server Timings.
Po kliknutí na konkrétní dotaz se tento dotaz zobrazí na pravé straně v kartě Server Timings. Jazyk, kterým je popsán dotaz generovaný SE, se jmenuje xmSQL. Tento jazyk bude relativně snadno čitelný pro každého kdo zná jakoukoliv variantu jazyka SQL.
My se ale vrátíme zpět k jazyku DAX a k měřítku [Počet objednávek (1 ks)]. V další části příspěvku si ukážeme, kde je v tomto měřítku problém a vytvoříme si lepší variantu výpočtu stejného výpočtu.
3. Vytvoření nové verze měřítka
Novou verzi měřítka [Počet objednávek (1 ks)] si v první fázi můžeme vytvořit přímo v DAX Studiu. Výhodou tohoto přístupu je fakt, že měřítka definovaná v rámci DAX dotazu mají při vyhodnocení DAX dotazu přednost před měřítky definovanými v modelu. Díky tomu si můžeme do DAX Studia vložit původní verzi výpočtu, upravovat ji a porovnávat výsledky s původní verzí, a až v případě úspěšné optimalizace zkopírovat novou verzi měřítka a vložit tuto novou verzi výpočtu do původního měřítka v Power BI.
Definici měřítka [Počet objednávek (1 ks)] si můžeme vložit do DAX dotazu následujícím způsobem.
Po kliknutí na možnost "Define Measure" se definice měřítka vloží na místo kde je umístěný kurzor v DAX editoru. Z tohoto důvodu je důležité umístit kurzor před kliknutím na možnost "Define Measure" na první řádek v DAX dotazu.
Problémem výpočtu v měřítku zobrazeném na obrázku výše je definice filtru ve funkci CALCULATE(). V tomto filtru je použita celá tabulka 'Sales Order', která je navíc filtrována na základě výpočtu ve funkci CALCULATE(). Funkce CALCULATE() tak musí pro každý řádek 'Sales Order' provést změnu kontextu řádku na kontext filtru, což je relativně náročná operace. Následně je zafiltrovaná tabulka 'Sales Order' použita jako filtr před vyhodnocením funkce DISTINCTCOUNT() se sloupcem který obsahuje čísla objednávek. Filtrování funkce DISTINCTCOUNT() pomocí celé tabulky je další problém. Tato kombinace filtru tabulky a funkce DISTINCTCOUNT() je hlavním důvodem, proč SE generuje velké množství dotazů.
Nová, výrazně lepší verze stejného výpočtu může vypadat například následovně.
Typ výpočtu:
Výše uvedený výpočet následně použijeme pro definici měřítka v DAX dotazu, spustíme dotaz a podíváme se na informace na kartě Server Timings.
DAX dotaz s novou verzí výpočtu je nyní vyhodnocen za 36 ms, což je v porovnání s 13 263 ms obrovský rozdíl. Původní verze měřítka byla ale záměrně napsána velmi špatným způsobem pro zdůraznění rozdílu mezi těmito měřítky. Nyní již bude stačit zkopírovat si novou verzi měřítka a vložit ji do původního měřítka v Power BI.
Pokud nyní znovu aktualizujeme vizuály v Analyzátoru výkonu, výsledek bude vypadat následovně.
Jak je možné vidět na obrázku výše, vizuál Matice je vyhodnocen výrazně rychleji než stejný vizuál s původní verzí měřítka [Počet objednávek (1 ks)].
V Power BI souboru přiloženém pod odkazem níže jsou další dvě měřítka, která také nejsou napsána úplně ideálním způsobem a která by si zasloužila optimalizovat. Nalezení a optimalizace těchto dvou měřítek může být dobrým cvičením pro čtenáře tohoto příspěvku. Zmíněná dvě měřítka však nepředstavují žádný problém s ohledem na rychlost načítání reportu, protože použitý soubor obsahuje relativně malý model s malým množstvím záznamů v tabulkách. Nalezení a optimalizace těchto dvou měřítka tak může zabrat výrazně více času než v případě měřítka [Počet objednávek (1 ks)], které bylo ve své původní pomalé verzi vytvořeno záměrně velmi špatným způsobem pro účely tohoto příspěvku.
Komentáře
Okomentovat