Agregacje zdefiniowane przez użytkownika

Agregacje w usłudze Power BI mogą poprawić wydajność zapytań w dużych modelach semantycznych trybu DirectQuery. Za pomocą agregacji dane są buforowane na zagregowanym poziomie w pamięci. Agregacje w usłudze Power BI można skonfigurować ręcznie w modelu danych zgodnie z opisem w tym artykule. W przypadku subskrypcji Premium można włączyć funkcję Agregacje automatyczne w obszarze Ustawienia modelu, aby utworzyć je automatycznie.

Tworzenie tabel agregacji

W zależności od typu źródła danych można utworzyć tabelę agregacji w źródle danych jako tabelę lub widok, zapytanie natywne. Aby uzyskać największą wydajność, utwórz tabelę agregacji jako tabelę importu utworzoną w dodatku Power Query. Okno dialogowe Zarządzanie agregacjami w programie Power BI Desktop umożliwia definiowanie agregacji dla kolumn agregacji z właściwościami podsumowania, tabeli szczegółów i kolumn szczegółów.

Wielowymiarowe źródła danych, takie jak magazyny danych i składnice danych, mogą używać agregacji opartych na relacjach. Duże źródła danych oparte na usłudze Hadoop często opierają się na agregacjach w kolumnach GroupBy. W tym artykule opisano typowe różnice modelowania danych usługi Power BI dla każdego typu źródła danych.

Zarządzanie agregacjami

W okienku Dane dowolnego widoku programu Power BI Desktop kliknij prawym przyciskiem myszy tabelę agregacji, a następnie wybierz pozycję Zarządzaj agregacjami.

W oknie dialogowym Zarządzanie agregacjami jest wyświetlany wiersz dla każdej kolumny w tabeli, w którym można określić zachowanie agregacji. W poniższym przykładzie zapytania do tabeli Szczegółów sprzedaży są wewnętrznie przekierowywane do tabeli agregacji Sales Agg .

W tym przykładzie agregacji opartej na relacjach wpisy Grupuj według są opcjonalne. Z wyjątkiem funkcji DISTINCTCOUNT nie wpływają one na zachowanie agregacji i są przeznaczone przede wszystkim do czytelności. Bez wpisów grupowania, agregacje są nadal wykonywane na podstawie relacji. To zachowanie różni się od przykładu dużych zbiorów danych w dalszej części tego artykułu, w którym wymagane są wpisy Grupuj według.

Sprawdzanie poprawności

Okno dialogowe Zarządzanie agregacjami wymusza walidację:

• Kolumna szczegółów musi mieć taki sam typ danych jak kolumna agregacji, z wyjątkiem funkcji Podsumowania liczby i liczby wierszy tabeli. Wiersze tabeli Count i Count są dostępne tylko dla kolumn agregacji liczb całkowitych i nie wymagają pasującego typu danych.
• Agregacje łańcuchowe obejmujące co najmniej trzy tabele nie są dozwolone. Na przykład agregacje w tabeli A nie mogą odwoływać się do tabeli B , która zawiera agregacje odwołujące się do tabeli C.
• Zduplikowane agregacje, w których dwa wpisy używają tej samej funkcji Podsumowania i odwołują się do tej samej tabeli szczegółów i kolumny szczegółów, nie są dozwolone.
• Tabela szczegółów musi używać trybu przechowywania DirectQuery, a nie importu.
• Grupowanie według kolumny klucza obcego używanej przez nieaktywną relację i poleganie na funkcji USERELATIONSHIP dla trafień agregacji nie jest obsługiwane. Alternatywnie można użyć funkcji TREATAS zamiast USERELATIONSHIP. W przypadku korzystania z programu TREATAS upewnij się, że nie ma aktywnych relacji między tabelami. Agregaty mogą być nadal uzyskiwane przy użyciu funkcji TREATAS z tą konfiguracją.
• Agregacje oparte na kolumnach GroupBy mogą używać relacji między tabelami agregacji, ale tworzenie relacji między tabelami agregacji nie jest obsługiwane w programie Power BI Desktop. W razie potrzeby można tworzyć relacje między tabelami agregacji przy użyciu narzędzia innej firmy lub rozwiązania do obsługi skryptów za pomocą kodu XML dla punktów końcowych analizy (XMLA).

Większość walidacji jest wymuszana przez wyłączenie wartości list rozwijanych i wyświetlenie tekstu objaśniającego w etykietce narzędzia.

Tabele agregacji są ukryte

Użytkownicy z dostępem tylko do odczytu do modelu nie mogą wykonywać zapytań dotyczących tabel agregacji. Dostęp tylko do odczytu pozwala uniknąć problemów z zabezpieczeniami podczas zastosowania zabezpieczeń na poziomie wiersza (RLS). Konsumenci i zapytania odnoszą się do tabeli szczegółów, a nie do tabeli agregacji, i nie muszą znać szczegółów dotyczących tabeli agregacji.

Z tego powodu tabele agregacji są ukryte w widoku raportu . Jeśli tabela nie jest jeszcze ukryta, okno dialogowe Zarządzanie agregacjami ustawia je na ukryte po wybraniu pozycji Zastosuj wszystko.

Tryby przechowywania

Funkcja agregacji działa z trybami przechowywania na poziomie tabeli. Tabele Power BI mogą używać trybów przechowywania DirectQuery, Import lub Dual. Zapytanie bezpośrednie wysyła zapytania bezpośrednio do zaplecza, podczas gdy import buforuje dane w pamięci i wysyła zapytania do buforowanych danych. Wszystkie źródła danych importowanych w Power BI oraz źródła danych DirectQuery inne niż wielowymiarowe działają z agregacjami.

Aby ustawić tryb przechowywania zagregowanej tabeli na Import w celu przyspieszenia zapytań, wybierz zagregowaną tabelę w widoku modelu programu Power BI Desktop. W okienku Właściwości rozwiń sekcję Zaawansowane, rozwiń listę rozwijaną pod Tryb przechowywania, a następnie wybierz Importuj. Po ustawieniu trybu przechowywania na Import nie można go ponownie zmienić.

Aby uzyskać więcej informacji na temat trybów przechowywania tabel, zobacz Zarządzanie trybem przechowywania w programie Power BI Desktop.

Zastosowanie RLS dla agregacji

Aby prawidłowo działać w przypadku agregacji, wyrażenia zabezpieczeń na poziomie wiersza powinny filtrować zarówno tabelę agregacji, jak i tabelę szczegółów.

W poniższym przykładzie wyrażenie RLS w tabeli Geography działa w przypadku agregacji, ponieważ tabela Geography znajduje się po stronie filtrowania relacji z tabelą Sales oraz tabelą Sales Agg. Zapytania korzystające z tabeli agregacji i te, które z niej nie korzystają, mają pomyślnie zastosowane zabezpieczenia na poziomie wiersza.

Wyrażenie zabezpieczeń na poziomie wiersza w tabeli Product filtruje tylko szczegółową tabelę Sales, a nie zagregowaną tabelę Sales Agg. Ponieważ tabela agregacji jest inną reprezentacją danych w tabeli szczegółowej, odpowiadanie na zapytania z tabeli agregacji byłoby niebezpieczne, jeśli nie można zastosować filtru RLS. Filtrowanie wyłącznie tabeli szczegółów nie jest zalecane, ponieważ zapytania użytkowników w ramach tej roli nie korzystają z wyników agregacji.

Wyrażenie zabezpieczeń na poziomie wiersza, które filtruje tylko tabelę agregacji Sales Agg, a nie tabelę szczegółów Sales, nie jest dozwolone.

W przypadku agregacji opartych na kolumnach GroupBy, wyrażenie zabezpieczeń na poziomie wiersza zastosowane do tabeli szczegółów może filtrować tabelę agregacji, ponieważ wszystkie kolumny GroupBy w tabeli agregacji są zawarte w tabeli szczegółów. Z drugiej strony filtr RLS w tabeli agregacyjnej nie może filtrować tabeli szczegółów, dlatego jest on niedozwolony.

Agregacja oparta na relacjach

Modele wymiarowe zwykle używają agregacji na podstawie relacji. Modele usługi Power BI z magazynów danych i składnic danych przypominają schematy gwiazdy i płatka śniegu z relacjami między tabelami wymiarów i tabelami faktów.

W poniższym przykładzie model pobiera dane z jednego źródła danych. Tabele używają trybu przechowywania DirectQuery. Tabela faktów Sales zawiera miliardy wierszy. Ustawienie trybu przechowywania Sales na Import na potrzeby buforowania spowoduje zużycie znacznej ilości pamięci i obciążenia zasobów.

Zamiast tego utwórz tabelę agregacji Sales Agg . W tabeli Sales Agg liczba wierszy jest równa sumie wartości SalesAmount pogrupowanej według wartości CustomerKey, DateKey i ProductSubcategoryKey. Tabela Sales Agg jest bardziej szczegółowa niż Sales, więc zamiast miliardów może zawierać miliony wierszy, które są łatwiejsze do zarządzania.

Jeśli poniższe tabele wymiarów są najczęściej używane w przypadku zapytań o wysokiej wartości dla biznesu, mogą filtrować tabelę Sales Agg, korzystając z relacji jeden do wielu lub wiele do jednego.

• Geografia
• Klient
• Data kalendarzowa
• Podkategoria produktu
• Kategoria produktu

Na poniższej ilustracji przedstawiono ten model.

W poniższej tabeli przedstawiono agregacje dla tabeli Sales Agg .

Zmiana trybu przechowywania zagregowanej tabeli Sales Agg na Import powoduje otwarcie okna dialogowego z informacją, że powiązane tabele wymiarów można ustawić na tryb przechowywania Podwójny.

Ustawienie powiązanych tabel wymiarów na Podwójne umożliwia im działanie w trybie Import lub DirectQuery w zależności od podzapytania. W przykładzie:

• Zapytania agregujące metryki z tabeli Sales Agg działającej w trybie importu i grupujące według atrybutów z powiązanych Tabel Dual, zwracają wyniki z pamięci podręcznej w pamięci.
• Zapytania agregujące metryki z tabeli DirectQuery Sales i grupują według atrybutów z powiązanych tabel podwójnych, zwracają wyniki w trybie DirectQuery. Logika zapytania, w tym operacja GroupBy, jest przekazywana do źródłowej bazy danych.

Aby uzyskać więcej informacji na temat trybu przechowywania podwójnego, zobacz Zarządzanie trybem przechowywania w programie Power BI Desktop.

Zwykłe a ograniczone relacje

Wyniki agregacji bazujące na relacjach wymagają regularnych relacji.

Relacje regularne obejmują następujące kombinacje trybu przechowywania, w których obie tabele pochodzą z jednego źródła:

Jedynym przypadkiem, w którym relacja między źródłami jest regularna, jest to, że obie tabele są ustawione na Import. Relacje wiele-do-wielu są zawsze ograniczone.

W przypadku trafień agregacji między źródłami , które nie zależą od relacji, zobacz Agregacje oparte na kolumnach Grupuj według.

Przykłady zapytań agregacji opartych na relacjach

Poniższe zapytanie używa agregacji, ponieważ kolumny w tabeli Date znajdują się w stopniach szczegółowości, które mogą używać agregacji. Kolumna SalesAmount używa agregacji Suma .

Następujące zapytanie nie używa agregacji. Pomimo żądania sumy SalesAmount, zapytanie wykonuje operację GroupBy w kolumnie w tabeli Product, która nie jest na poziomie szczegółowości umożliwiającym użycie agregacji. Jeśli obserwujesz relacje w modelu, podkategoria produktu może zawierać wiele wierszy produktu . Zapytanie nie może określić, do którego produktu należy zagregować. W takim przypadku zapytanie powraca do zapytania bezpośredniego i przesyła zapytanie SQL do źródła danych.

Agregacje nie są przeznaczone tylko dla prostych obliczeń, które wykonują prostą sumę. Złożone obliczenia mogą również przynieść korzyści. Koncepcyjnie złożone obliczenie jest podzielone na podzapytania dla każdej wartości SUM, MIN, MAX i COUNT. Każde podzapytywanie jest oceniane w celu określenia, czy może używać agregacji. Ta logika nie zawsze jest prawdziwa we wszystkich przypadkach, ponieważ optymalizacja planów zapytań może na nią wpływać, ale ogólnie powinna mieć zastosowanie. W poniższym przykładzie użyto agregacji:

Funkcja COUNTROWS może korzystać z agregacji. Poniższe zapytanie używa agregacji, ponieważ istnieje agregacja Liczba wierszy tabeli zdefiniowana dla tabeli Sales .

Funkcja AVERAGE może korzystać z agregacji. Poniższe zapytanie używa agregacji, ponieważ AVERAGE jest wewnętrznie przekształcany do sumy podzielonej przez COUNT. Ponieważ kolumna UnitPrice ma agregacje zdefiniowane zarówno dla SUMY, jak i LICZBY, te agregacje są używane.

W niektórych przypadkach funkcja DISTINCTCOUNT może korzystać z agregacji. Poniższe zapytanie używa agregacji, ponieważ istnieje wpis GroupBy dla elementu CustomerKey, który zachowuje odrębność elementu CustomerKey w tabeli agregacji. Ta technika może nadal osiągać próg wydajności, w którym ponad 2 do 5 milionów odrębnych wartości może mieć wpływ na wydajność zapytań. Jednak może to być przydatne w scenariuszach, w których w tabeli szczegółów znajdują się miliardy wierszy, ale od 2 do 5 milionów odrębnych wartości w kolumnie. W takim przypadku funkcja DISTINCTCOUNT może działać szybciej niż skanowanie tabeli z miliardami wierszy, nawet jeśli zostały zapisane w pamięci podręcznej.

Funkcje analizy czasowej języka DAX (Data Analysis Expressions) są świadome agregacji. Poniższe zapytanie korzysta z agregacji, ponieważ funkcja DATESYTD generuje tabelę wartości CalendarDay, a tabela agregacji ma stopień szczegółowości objęty przez kolumny grupowania w tabeli Date. Jest to przykład filtru wartości tabeli dla funkcji CALCULATE, który może pracować z agregacjami.

Agregacja oparta na kolumnach Grupuj według

Modele danych big data oparte na usłudze Hadoop mają różne cechy niż modele wymiarowe. Aby uniknąć łączeń między dużymi tabelami, modele big data często nie używają relacji, lecz denormalizują atrybuty wymiarów do tabel faktów. Takie modele danych big data można odblokować na potrzeby interaktywnej analizy przy użyciu agregacji opartych na kolumnach Grupuj według.

Poniższa tabela zawiera kolumnę liczbową Ruchu do agregowania. Wszystkie pozostałe kolumny są atrybutami do grupowania. Tabela zawiera dane IoT i ogromną liczbę wierszy. Tryb przechowywania to DirectQuery. Zapytania do źródła danych, które agregują dane z całego modelu, są powolne z powodu ogromnej ilości danych.

Aby włączyć interaktywną analizę tego modelu, dodaj tabelę agregacji, która grupuje według większości atrybutów, ale wyklucza atrybuty o wysokiej kardynalności, takie jak długość geograficzna i szerokość geograficzna. Takie podejście znacznie zmniejsza liczbę wierszy i jest wystarczająco małe, aby wygodnie zmieścić się w pamięci podręcznej.

Zdefiniuj mapowania agregacji dla tabeli Driver Activity Agg w oknie dialogowym Zarządzanie agregacjami .

W agregacjach opartych na kolumnach GroupBy wpisy nie są opcjonalne. Bez nich agregacje nie są osiągane. To zachowanie różni się od używania agregacji opartych na relacjach, gdzie wpisy GroupBy są opcjonalne.

W poniższej tabeli przedstawiono agregacje dla tabeli Driver Activity Agg .

Ustaw tryb przechowywania zagregowanej tabeli Driver Activity Agg na Import.

Przykład zapytania agregacji Grupuj według

Poniższe zapytanie używa agregacji, ponieważ kolumna Activity Date (Data działania ) jest objęta tabelą agregacji. Funkcja COUNTROWS używa agregacji wierszy tabeli, które zostały zliczone.

Szczególnie dla modeli zawierających atrybuty filtrów w tabelach faktów, warto użyć agregacji liczby wierszy tabeli. Usługa Power BI może przesyłać zapytania do modelu przy użyciu funkcji COUNTROWS w przypadkach, gdy nie jest jawnie żądana przez użytkownika. Na przykład w oknie dialogowym filtrowania jest wyświetlana liczba wierszy dla każdej wartości.

Połączone techniki agregacji

Możesz połączyć relacje i techniki kolumn GroupBy dla agregacji. Agregacje oparte na relacjach mogą wymagać podziału zdenormalizowanych tabel wymiarów na wiele tabel. Jeśli to wymaganie jest kosztowne lub niepraktyczne dla niektórych tabel wymiarów, możesz replikować niezbędne atrybuty w tabeli agregacji dla tych wymiarów i używać relacji dla innych.

Na przykład poniższy model replikuje Miesiąc, Kwartal, Semestr i Rok w tabeli Sales Agg. Nie ma relacji między tabelą Sales Agg i Date, ale istnieją relacje z podkategorią Customer (Klient) i Product Subcategory (Podkategoria produktów). Tryb przechowywania usługi Sales Agg to Import.

W poniższej tabeli przedstawiono wpisy ustawione w oknie dialogowym Zarządzanie agregacjami dla tabeli Sales Agg . Wpisy Grupuj według, gdzie Date jest tabelą szczegółów, są obowiązkowe, aby używać agregacji w zapytaniach grupujących według atrybutów Date. Podobnie jak w poprzednim przykładzie, elementy GroupBy dla CustomerKey i ProductSubcategoryKey nie wpływają na użycie agregacji, z wyjątkiem DISTINCTCOUNT, ze względu na obecność relacji.

Przykłady połączonych zapytań agregacji

Poniższe zapytanie używa agregacji, ponieważ tabela agregacji obejmuje CalendarMonth i można uzyskać dostęp do CategoryName za pośrednictwem relacji jeden do wielu. Zapytanie używa agregacji SUM dla kolumny SalesAmount.

Poniższe zapytanie nie używa agregacji, ponieważ tabela agregacji nie obejmuje kolumny CalendarDay.

Następujące zapytanie analizy czasowej nie używa agregacji, ponieważ funkcja DATESYTD generuje tabelę wartości CalendarDay , a tabela agregacji nie obejmuje kolumny CalendarDay.

Pierwszeństwo agregacji

Pierwszeństwo agregacji umożliwia pojedynczemu podzapytaniu rozważyć wiele tabel agregacji.

Poniższy przykład to model złożony zawierający wiele źródeł:

• Tabela Driver Activity DirectQuery zawiera ponad bilion wierszy danych IoT pochodzących z systemu dużych danych. Służy ona do obsługi zapytań drillthrough, aby wyświetlać poszczególne odczyty IoT w kontrolowanych kontekstach filtrowania.
• Tabela Driver Activity Agg jest pośrednią tabelą agregacji w trybie DirectQuery. Zawiera ponad miliard wierszy w usłudze Azure Synapse Analytics (dawniej SQL Data Warehouse) i jest zoptymalizowany u źródła przy użyciu indeksów typu columnstore.
• Tabela Driver Activity Agg2 Import jest bardzo szczegółowa, ponieważ atrybuty grupowania są nieliczne i mają niską kardynalność. Liczba wierszy może być tak mała, jak tysiące, więc można ją łatwo zmieścić w pamięci podręcznej. Te atrybuty są używane przez pulpit nawigacyjny kierownictwa o wysokim profilu, więc zapytania odwołujące się do nich powinny być tak szybkie, jak to możliwe.

Zużycie pamięci przez ten model jest stosunkowo małe, ale odblokowuje on ogromny model. Reprezentuje on zrównoważoną architekturę, ponieważ rozkłada obciążenie zapytań między składniki architektury, wykorzystując je na podstawie ich mocnych stron.

Okno dialogowe Zarządzane agregacje dla driver Activity Agg2 ustawia pole Pierwszeństwo na 10, co jest wyższe niż w przypadku driver Activity Agg. Wyższe ustawienie pierwszeństwa oznacza, że zapytania korzystające z agregacji uwzględniają najpierw driver Activity Agg2 . Podzapytania, które nie mają stopnia szczegółowości, na które może odpowiedzieć Driver Activity Agg2, mogą zamiast tego rozważyć Driver Activity Agg. Zapytania szczegółowe, na które nie można odpowiedzieć za pomocą jednej z tabel agregacji, mogą kierować do tabeli Driver Activity.

Tabela określona w kolumnie Tabela szczegółów to Driver Activity, a nie Driver Activity Agg, ponieważ agregacje łańcuchowe nie są dozwolone.

W poniższej tabeli przedstawiono agregacje dla tabeli Driver Activity Agg2 .

Wykrywanie, czy zapytania trafią lub pominą agregacje

SQL Profiler może wykryć, czy zapytania pochodzą z aparatu pamięci podręcznej, czy DirectQuery wysyła je do źródła danych. Możesz użyć tego samego procesu, aby wykryć, czy agregacje są używane. Aby uzyskać więcej informacji, zobacz Zapytania, które trafiają lub omijają pamięć podręczną.

Program SQL Profiler także udostępnia Query Processing\Aggregate Table Rewrite Query zdarzenie rozszerzone.

Poniższy fragment kodu JSON przedstawia przykład danych wyjściowych zdarzenia, gdy jest używana agregacja.

• matchingResult pokazuje, że podzapytanie używa agregacji.
• dataRequest pokazuje kolumny GroupBy i zagregowane kolumny używane przez podzapytanie.
• Mapowanie pokazuje kolumny w tabeli agregacji, do których dokonywane jest mapowanie.

Utrzymuj synchronizację pamięci podręcznych

Agregacje, które łączą tryby DirectQuery, Import i Dual, mogą zwracać różne dane, chyba że pamięć podręczna w pamięci pozostaje zsynchronizowana z danymi źródłowymi. Na przykład wykonanie zapytania nie próbuje maskować problemów z danymi przez filtrowanie wyników zapytania bezpośredniego w celu dopasowania do buforowanych wartości. Może być konieczne rozwiązanie tych problemów u źródła. Optymalizacje wydajności nigdy nie powinny naruszać możliwości spełnienia wymagań biznesowych. Musisz odpowiednio zrozumieć przepływy danych i zaprojektować je.

Uwagi i ograniczenia

• Agregacje nie obsługują dynamicznych parametrów zapytania języka M.

• Od sierpnia 2022 r. ze względu na zmiany w funkcjonalności usługa Power BI ignoruje tabele agregacji trybu importu korzystające z źródeł danych z włączonym logowaniem jednokrotnym ze względu na potencjalne zagrożenia bezpieczeństwa. Aby zapewnić optymalną wydajność zapytań z użyciem agregacji, wyłącz SSO dla tych źródeł danych.

Community

Usługa Power BI ma żywą społeczność, w której specjaliści MVP, specjaliści bi i rówieśnicy dzielą się wiedzą w grupach dyskusyjnych, filmach wideo, blogach i nie tylko. Podczas poznawania agregacji należy zapoznać się z następującymi zasobami:

• Społeczność usługi Power BI
• Wyszukaj frazę "Agregacje usługi Power BI" w usłudze Bing

Treści powiązane

• Agregacje automatyczne
• Modele złożone