Uživatelem definované agregace

Agregace v Power BI můžou zlepšit výkon dotazů u velkých sémantických modelů DirectQuery. Pomocí agregací ukládáte data do mezipaměti na agregované úrovni v paměti. Agregace v Power BI je možné ručně nakonfigurovat v datovém modelu, jak je popsáno v tomto článku. U předplatných Premium automaticky povolte funkci Automatické agregace v nastavení modelu.

Vytváření agregačních tabulek

V závislosti na typu zdroje dat lze tabulku agregací vytvořit ve zdroji dat jako tabulku, zobrazení nebo pomocí nativního dotazu. Pokud chcete dosáhnout nejvyššího výkonu, vytvořte tabulku agregací jako tabulku importu vytvořenou v Power Query. Potom použijete dialogové okno Spravovat agregace v Power BI Desktopu k definování agregací pro sloupce agregace se souhrnem, tabulkou podrobností a vlastnostmi sloupce podrobností.

Dimenzionální zdroje dat, jako jsou datové sklady a datové markety, mohou využívat agregace založené na relacích. Velké zdroje dat založené na Hadoopu často zakládají agregace na sloupcích GroupBy. Tento článek popisuje typické rozdíly modelování dat Power BI pro každý typ zdroje dat.

Správa agregací

V podokně Data libovolného zobrazení Power BI Desktopu klikněte pravým tlačítkem na tabulku agregací a pak vyberte Spravovat agregace.

Dialogové okno Spravovat agregace zobrazuje řádek pro každý sloupec v tabulce, kde můžete zadat chování agregace. V následujícím příkladu jsou dotazy na tabulku podrobností Sales interně přesměrovány na tabulku agregace Sales Agg.

V tomto příkladu agregace založené na relacích jsou položky GroupBy volitelné. S výjimkou funkce DISTINCTCOUNT nemají vliv na chování agregace a primárně se jedná o čitelnost. Bez záznamů GroupBy by agregace stále probíhaly na základě relací. To se liší od příkladu velkých objemů dat dále v tomto článku, kde jsou požadovány položky GroupBy.

Ověření

Dialogové okno Správa agregací provádí kontrolu:

• Sloupec podrobností musí mít stejný datový typ jako sloupec agregace , s výjimkou funkcí souhrnu Počet a Počet řádků tabulky . Řádky tabulky Count a Count jsou k dispozici pouze pro celočíselné sloupce agregace a nevyžadují odpovídající datový typ.
• Zřetězených agregací zahrnujících tři nebo více tabulek není povoleno. Například agregace tabulky A nemůžou odkazovat na tabulku B, která obsahuje agregace odkazující na tabulku C.
• Duplicitní agregace, kde dvě položky používají stejnou funkci souhrnu a odkazují na stejnou tabulku podrobností a sloupec podrobností, nejsou povoleny.
• Tabulka podrobností musí používat režim úložiště DirectQuery, nikoli import.
• Seskupení podle sloupce cizího klíče používaného neaktivní relací a spoléhání se na funkci USERELATIONSHIP pro agregační přístupy se nepodporuje.
• Agregace založené na sloupcích GroupBy můžou používat relace mezi tabulkami agregace, ale vytváření relací mezi tabulkami agregace není v Power BI Desktopu podporované. V případě potřeby můžete vytvořit relace mezi tabulkami agregace pomocí nástroje třetí strany nebo skriptovacího řešení prostřednictvím koncových bodů XML for Analysis (XMLA).

Většina ověření se vynucuje zakázáním hodnot rozevíracího seznamu a zobrazením vysvětlujícího textu v popisu.

Tabulky agregace jsou skryté.

Uživatelé s přístupem jen pro čtení k modelu nemůžou dotazovat tabulky agregace. Přístup pouze pro čtení se vyhýbá bezpečnostním problémům při použití s zabezpečením na úrovni řádků (RLS). Uživatelé a dotazy odkazují na tabulku podrobností, ne na tabulku agregace a nemusí o tabulce agregace vědět.

Z tohoto důvodu jsou tabulky agregace skryté v zobrazení zprávy . Pokud tabulka ještě není skrytá, dialogové okno Spravovat agregace ji nastaví jako skrytou, když vyberete Použít všechny.

Režimy úložiště

Funkce agregace komunikuje s režimy úložiště na úrovni tabulky. Tabulky Power BI můžou používat režimy úložiště DirectQuery , Import , nebo duální režimy úložiště . DirectQuery dotazuje back-end přímo, zatímco Import ukládá data do mezipaměti v paměti a odesílá dotazy do dat uložených v mezipaměti. Všechny zdroje dat DirectQuery importu a nedimenzionálních dat Power BI můžou pracovat s agregacemi.

Pokud chcete nastavit režim úložiště agregované tabulky na Import, aby se urychlily dotazy, vyberte agregovanou tabulku v zobrazení modelu Power BI Desktop . V podokně Vlastnosti rozbalte Upřesnit, rozbalte výběr v části Režim úložiště a vyberte Import . Změna importu je nevratná.

Další informace o režimech úložiště tabulek najdete v tématu Správa režimu úložiště v Power BI Desktopu.

RLS pro agregace

Aby výrazy RLS fungovaly správně pro agregace, měly by filtrovat tabulku agregace a tabulku podrobností.

V následujícím příkladu výraz RLS v tabulce Geography funguje pro agregace, protože Geography je na straně filtrování relací k tabulce Sales a tabulce Sales Agg. Dotazy, které se zaměřily na tabulku agregace, a dotazy, na které nebylo úspěšně aplikováno zabezpečení na úrovni řádků (RLS).

Výraz RLS v tabulce Product filtruje pouze tabulku podrobností Sales, nikoli agregovanou tabulku Sales Agg. Vzhledem k tomu, že tabulka agregace je další reprezentací dat v tabulce podrobností, bylo by nezabezpečené odpovídat na dotazy z tabulky agregace, pokud se filtr RLS nedá použít. Filtrování pouze detailní tabulky se nedoporučuje, protože dotazy uživatelů z této role nemají prospěch ze zásahů agregace.

RLS výraz, který filtruje pouze tabulku agregace Sales Agg a nikoliv tabulku podrobností Sales, není povolen.

Pro agregace založené na sloupcích GroupBylze k filtrování tabulky agregace použít výraz RLS použitý u tabulky podrobností, protože všechny sloupce GroupBy v tabulce agregace jsou pokryty tabulkou podrobností. Na druhou stranu filtr RLS v tabulce agregace nejde použít u tabulky podrobností, takže je nepovolen.

Agregace založená na relacích

Dimenzionální modely obvykle používají agregace založené na relacích. Modely Power BI z datových skladů a datových mart se podobají schématům hvězdy a sněhové vločky s relacemi mezi tabulkami dimenzí a tabulkami faktů.

V následujícím příkladu model získává data z jednoho zdroje dat. Tabulky používají režim úložiště DirectQuery. Tabulka faktů Sales obsahuje miliardy řádků. Nastavení režimu úložiště Sales na Import pro ukládání do mezipaměti by znamenalo značnou zátěž na paměť a prostředky.

Vytvořte místo toho agregační tabulku Sales Agg. V tabulce Sales Agg je počet řádků součtem SalesAmount seskupených podle CustomerKey, DateKeya ProductSubcategoryKey. Tabulka Sales Agg má vyšší členitost než Sales, takže místo miliard může obsahovat miliony řádků, které se snadněji spravují.

Pokud se pro dotazy s vysokou obchodní hodnotou nejčastěji používají následující tabulky dimenzí, můžou filtrovat sales Aggpomocí 1:N nebo relací typu M:1.

• Zeměpis
• Zákazník
• Datum
• Podkategorie produktu
• Kategorie produktu

Následující obrázek znázorňuje tento model.

Následující tabulka ukazuje agregace pro sales Agg table.

Poznámka:

Tabulka Sales Agg (podobně jako každá tabulka) má flexibilitu načítání různými způsoby. Agregaci lze provést ve zdrojové databázi pomocí procesů ETL/ELT nebo výrazem M pro tabulku. Agregovaná tabulka může používat importní režim úložiště s přírůstkovou aktualizací pro sémantické modelynebo může využít DirectQuery a být optimalizovaná pro rychlé dotazy pomocí sloupcových indexů. Tato flexibilita umožňuje vyvážené architektury, které můžou rozložit zatížení dotazů, aby nedocházelo k kritickým bodům.

Změna režimu úložiště agregované tabulky Sales Agg na Import otevře dialogové okno s informací, že související tabulky dimenzí lze nastavit na režim úložiště Duální.

Dialogové okno režim úložiště

Nastavení souvisejících tabulek dimenzí na duální umožňuje v závislosti na poddotazu fungovat jako Import nebo DirectQuery. V příkladu:

• Dotazy, které agregují metriky z tabulky Sales Agg v režimu Import a seskupují podle atributů z příbuzných duálních tabulek, mohou být vráceny z paměťové mezipaměti.
• Dotazy, které agregují metriky z tabulky DirectQuery Sales a seskupují podle atributů ze souvisejících duálních tabulek, se dají vrátit v režimu DirectQuery. Logika dotazu, včetně operace GroupBy, se předává do zdrojové databáze.

Další informace o režimu duálního úložiště najdete v tématu Správa režimu úložiště v Power BI Desktopu.

Normální vs. omezené relace

Přístupy agregace založené na relacích vyžadují pravidelné relace.

Mezi běžné relace patří následující kombinace režimu úložiště, kde obě tabulky pocházejí z jednoho zdroje:

Tabulka s mnoha stranami	Tabulka na straně 1
Dvojitý	Dvojitý
Dovoz	Import nebo duální
DirectQuery	DirectQuery nebo Duální

Jediný případ, kdy je relace mezi zdroji považována za běžnou, je v případě, že jsou obě tabulky nastaveny na import. Vztahy typu M:N jsou vždy považovány za omezené.

agregace mezi zdroji, které nezávisí na relacích, najdete v tématu Agregace založené na sloupcích GroupBy.

Příklady agregačních dotazů založených na relacích

Následující dotaz využívá agregaci, protože sloupce v tabulce Date jsou v granularitě, která umožňuje využití agregace. Sloupec SalesAmount používá agregaci Sum.

Následující dotaz nenarazí na agregaci. Navzdory požadavku na součet SalesAmountdotaz provádí operaci GroupBy na sloupci v tabulce Product, která není na úrovni podrobnosti umožňující provést agregaci. Pokud se podíváte na vztahy v modelu, může podkategorie produktu obsahovat více řádků produktu . Dotaz by nemohl určit, na který produkt se má agregovat. V tomto případě se dotaz vrátí k DirectQuery a odešle dotaz SQL do zdroje dat.

Agregace nejsou jen pro jednoduché výpočty, které provádějí jednoduchý součet. Komplexní výpočty můžou být také přínosné. Koncepčně je komplexní výpočet rozdělen na poddotazy pro každý SUM, MIN, MAX a COUNT. Každý poddotaz se vyhodnocuje, aby se zjistilo, zda může splnit podmínky pro agregaci. Tato logika neplatí ve všech případech kvůli optimalizaci plánu dotazů, ale obecně by to mělo platit. Následující příklad použije agregaci:

Funkce COUNTROWS může těžit z agregací. Následující dotaz spustí agregaci, protože je definována agregace Počet řádků tabulky pro tabulku Sales.

Funkce PRŮMĚR může těžit z agregací. Následující dotaz zasáhne agregaci, protože funkce PRŮMĚR se interně převede na SUMA dělená počtem. Vzhledem k tomu, že sloupec UnitPrice má definované agregace pro součet i počet, k agregaci dojde.

V některých případech může funkce DISTINCTCOUNT těžit z agregací. Následující dotaz dosáhne na agregaci, protože existuje položka GroupBy pro CustomerKey, která udržuje jedinečnost CustomerKey v tabulce agregace. Tato technika může přesto dosáhnout prahové hodnoty výkonu, kdy výkon dotazů může ovlivnit více než dva až pět milionů jedinečných hodnot. Může to ale být užitečné ve scénářích, ve kterých jsou v tabulce podrobností miliardy řádků, ale dva až pět milionů jedinečných hodnot ve sloupci. V tomto případě může funkce DISTINCTCOUNT běžet rychleji než prohledávání tabulky s miliardami řádků, i když jsou uloženy do paměti.

Časově inteligentní funkce DAX (Data Analysis Expressions) automaticky rozpoznávají a zpracovávají agregace. Dotaz zasáhne agregaci tím, že funkce DATESYTD vygeneruje tabulku hodnot CalendarDay, a tabulka agregace má úroveň podrobnosti pokrývající sloupce pro seskupení v tabulce Date. Toto je příklad filtru s hodnotou tabulky na funkci CALCULATE, která může pracovat s agregacemi.

Agregace založená na sloupcích GroupBy

Modely velkých objemů dat založené na Hadoopu mají jiné charakteristiky než dimenzionální modely. Aby se zabránilo spojení mezi velkými tabulkami, modely velkých objemů dat často nepoužívají relace, ale denormalizují atributy dimenzí tabulkám faktů. Takové modely velkých objemů dat můžete pro interaktivní analýzu odemknout pomocí agregací založených na sloupcích GroupBy.

Následující tabulka obsahuje číselný sloupec pro pohyby , který se má agregovat. Všechny ostatní sloupce jsou atributy, podle kterých se mají seskupit. Tabulka obsahuje data IoT a velký počet řádků. Režim úložiště je DirectQuery. Dotazy na zdroj dat, které se agregují napříč celým modelem, jsou pomalé kvůli obrovskému objemu.

tabulky IoT

Pokud chcete u tohoto modelu povolit interaktivní analýzu, můžete přidat tabulku agregace, která seskupuje podle většiny atributů, ale vylučuje atributy s vysokou kardinalitou, jako je zeměpisná délka a zeměpisná šířka. Toto výrazně snižuje počet řádků a je dostatečně malé, aby se pohodlně vešlo do cache v paměti.

Tabulka

Mapování agregací pro agregaci Driver Activity Agg (Agregace aktivity řidiče) definujete v dialogovém okně Spravovat agregace.

V agregacích založených na sloupcích GroupBy nejsou položky GroupBy volitelné. Bez nich nejsou agregace zasaženy. To se liší od použití agregací založených na relacích, kde jsou položky GroupBy volitelné.

Následující tabulka ukazuje agregace pro tabulku Driver Activity Agg, tedy Agregace aktivity řidiče.

Můžete nastavit režim úložiště agregované tabulky Driver Activity Agg na Import.

Příklad agregačního dotazu GroupBy

Následující dotaz zasahuje agregaci, protože sloupec Datum aktivity je zahrnutý v tabulce agregace. Funkce COUNTROWS používá agregaci počítané řádky tabulky .

Zvláště u modelů, které obsahují atributy filtru v tabulkách faktů, je vhodné použít Počet řádků tabulky agregace. Power BI může odesílat dotazy do modelu pomocí funkce COUNTROWS v případech, kdy to uživatel explicitně nevyžaduje. Například dialogové okno filtru zobrazuje počet řádků pro každou hodnotu.

Dialogové okno

Kombinované techniky agregace

Můžete kombinovat techniky relací a sloupců GroupBy pro agregace. Agregace založené na relacích mohou vyžadovat rozdělení tabulek denormalizovaných dimenzí do více tabulek. Pokud je to nákladné nebo nepraktické pro určité tabulky dimenzí, můžete replikovat potřebné atributy v tabulce agregace pro tyto dimenze a používat relace pro ostatní.

Následující model například replikuje Měsíc, Čtvrtletí, Semestra rok v tabulce Sales Agg. Mezi Sales Agg a tabulkou Date neexistuje žádná relace, ale existují relace k Customer a Product Subcategory. Režim úložiště Sales Agg je Import.

Následující tabulka ukazuje položky nastavené v dialogovém okně Spravovat agregace pro tabulku Sales Agg. Položky GroupBy, kde Date je tabulka podrobností povinná, aby byly nalezeny agregace pro dotazy, které seskupují podle atributů Date. Stejně jako v předchozím příkladu položky GroupBy pro CustomerKey a ProductSubcategoryKey nemají vliv na agregační přístupy, s výjimkou DISTINCTCOUNT kvůli přítomnosti relací.

Příklady kombinovaných agregačních dotazů

Následující dotaz zasahuje agregaci, protože agregační tabulka pokrývá CalendarMontha CategoryName je přístupná prostřednictvím relací 1:N. SalesAmount používá agregaci SUM.

Následující dotaz nespadá do agregace, protože tabulka agregace nepokrývá KalendářníDen.

Následující dotaz časového měřítka nenarazí na agregaci, protože funkce DATESYTD generuje tabulku hodnot CalendarDay a tabulka agregace nepokrývá calendarDay.

Priorita agregace

Priorita agregace umožňuje, aby jednotlivý poddotaz zvážil více tabulek agregace.

Následující příklad je složený model obsahující více zdrojů:

• Tabulka Driver Activity DirectQuery obsahuje více než bilión řádků dat IoT zdrojových z rozsáhlého datového systému. Poskytuje průchozí dotazy k zobrazení jednotlivých IoT dat v kontrolovaných kontextech filtru.
• Tabulka Driver Activity Agg je zprostředkující agregační tabulka v režimu DirectQuery. Obsahuje více než miliardu řádků ve službě Azure Synapse Analytics (dříve SQL Data Warehouse) a je optimalizovaná ve zdroji pomocí indexů columnstore.
• Tabulka Driver Activity Agg2 Import má vysokou granularitu, protože atributy seskupování jsou v malém množství a mají nízkou kardinalitu. Počet řádků může být tak nízký jako tisíce, takže se snadno vejde do mezipaměti v paměti. Tyto atributy používá vysokoprofilový řídicí panel vedení, takže dotazy na ně by měly být co nejrychlejší.

Poznámka:

Agregační tabulky DirectQuery, které používají jiný zdroj dat než tabulka podrobností, se podporují jenom v případě, že je tabulka agregace ze zdroje SQL Serveru, Azure SQL nebo Azure Synapse Analytics (dříve SQL Data Warehouse).

Paměťové nároky tohoto modelu jsou relativně malé, avšak umožňují využití obrovského modelu. Představuje vyváženou architekturu, protože rozšiřuje zatížení dotazů mezi komponenty architektury a využívá je na základě jejich silných stránek.

Dialogové okno Spravované agregace pro Driver Activity Agg2 nastaví pole Priorita na 10, což je vyšší než u Driver Activity Agg. Nastavení vyšší priority znamená, že dotazy, které používají agregace, nejprve zvažují Driver Activity Agg2. Pokud poddotazy nejsou v členitosti, kterou může zodpovědět Driver Activity Agg2, mohou zvážit použití Driver Activity Agg. Podrobné dotazy, na které nemohou odpovědět žádné z tabulek agregace, mohou být směrovány na Aktivity řidiče.

Tabulka specifikovaná ve sloupci Tabulka podrobností je Aktivity řidiče, nikoli Agregace aktivity řidiče, protože zřetězené agregace nejsou povolené.

Následující tabulka ukazuje agregace pro tabulku Driver Activity Agg2.

Tabulka agregací aktivity řidiče Agg2Driver Activity Agg2 aggregations table

Zjištění, jestli dotazy narazily na agregace nebo neúspěšné agregace

SQL Profiler dokáže zjistit, zda se dotazy vracejí z modulu úložiště mezipaměti v paměti, nebo jsou předány do zdroje dat pomocí DirectQuery. Stejný proces můžete použít ke zjištění, jestli se agregace narazí. Další informace najdete v tématu Dotazy, které hitují nebo míjejí mezipaměť.

SQL Profiler také poskytuje rozšířenou událost Query Processing\Aggregate Table Rewrite Query.

Následující fragment kódu JSON ukazuje příklad výstupu události při použití agregace.

• odpovídající ukazuje, že poddotaz použil agregaci.
• dataRequest zobrazuje sloupce GroupBy a agregované sloupce, které poddotaz použil.
• mapování ukazuje sloupce v tabulce agregace, kam byly mapovány.

Udržujte mezipaměti synchronizované

Agregace, které kombinují režimy úložiště DirectQuery, Import a/nebo Duální, můžou vracet různá data, pokud se mezipaměť v paměti nesynchronizuje se zdrojovými daty. Například vykonávání dotazu se nepokouší maskovat problémy s daty filtrováním výsledků DirectQuery tak, aby odpovídaly hodnotám uloženým v mezipaměti. V případě potřeby existují zavedené techniky pro řešení těchto problémů ve zdroji. Optimalizace výkonu by se měly používat jenom způsoby, které neohrožují vaši schopnost splnit obchodní požadavky. Je vaší zodpovědností znát toky dat a odpovídajícím způsobem navrhovat.

Úvahy a omezení

• Agregace nepodporují dynamické parametry dotazu M.

• Od srpna 2022 power BI kvůli změnám funkcí ignoruje tabulky agregace režimu importu s povolenými zdroji dat jednotného přihlašování kvůli potenciálním rizikům zabezpečení. Pokud chcete zajistit optimální výkon dotazů s agregacemi, doporučujeme pro tyto zdroje dat zakázat jednotné přihlašování.

Komunita

Power BI má živou komunitu, ve které MVP, profesionálové a kolegové sdílejí odborné znalosti v diskuzích, videích, blogech a dalších. Při získávání agregací se nezapomeňte podívat na tyto další zdroje informací:

• Komunita Power BI
• Vyhledejte "agregace Power BI" na Bingu

Související obsah

• Automatické agregace
• Složené modely