Kurs Power BI: modele złożone, tryb Dual i agregacje

W skrócieModel złożony w Power BI łączy tabele w trybach Import, DirectQuery i Dual, a tabela agregatów odpowiada na część zapytań bez sięgania do pełnej tabeli faktów. Na danych Nordvelli z SQL Server ustawiamy tryby tabel, relacje między źródłami i agregacje, a w Analizatorze wydajności sprawdzamy, czy silnik po nie sięgnął.

Do tej pory model Nordvelli w całości mieszkał w pamięci: kliknąłeś Pobierz dane, Power BI wczytał 108 675 wierszy sprzedaży i wszystkie wymiary, a potem liczył na tym, co ma u siebie. To najszybszy wariant i przy takim rozmiarze danych w zupełności wystarcza. Problem zaczyna się wtedy, gdy tabela faktów rośnie do dziesiątek albo setek milionów wierszy, albo gdy dział finansowy wymaga, żeby raport pokazywał stan sprzed minuty, a nie sprzed nocnego odświeżania.

Ten rozdział jest o trzech mechanizmach, które rozwiązują dokładnie te dwa problemy: o trybach przechowywania (Import, DirectQuery, Dual), o modelu złożonym, czyli jednym modelu łączącym tabele o różnych trybach i z różnych źródeł, oraz o agregacjach, czyli małej tabeli podsumowań, po którą silnik sięga zamiast po wielką tabelę faktów. Wszystko pokażemy na Nordvelli, na bazie MS SQL Server odtworzonej z pliku Nordvella.bak.gz. To te same dane, które w poprzednich lekcjach czytaliśmy z arkusza Excel.

Import, DirectQuery i tryb Dual

Co chcemy osiągnąć. Część danych Nordvelli musi być świeża co do minuty, a część zmienia się raz na tydzień. Chcemy rozumieć trzy tryby przechowywania tabel, żeby każdej tabeli dobrać tryb, który daje jednocześnie szybkość i aktualność.

Każda tabela w modelu Power BI ma jeden z trzech trybów przechowywania i to on decyduje, gdzie fizycznie leżą dane i kto wykonuje obliczenie. Różnica jest fundamentalna, bo wpływa na szybkość, na świeżość danych i na to, które funkcje DAX w ogóle zadziałają.

Najważniejsze zdanie tego rozdziału brzmi tak: tryb Dual istnieje po to, żeby wymiar nie zmuszał silnika do kosztownego łączenia danych między trybami. Gdy tabela faktów jest w DirectQuery, a wymiar w Imporcie, Power BI musi najpierw wyciągnąć z pamięci listę wartości wymiaru, wysłać ją do bazy jako warunek zapytania, a potem połączyć wyniki. To jest właśnie ta sytuacja, w której raport zaczyna zauważalnie zwalniać. Ten sam wymiar ustawiony na Dual pozwala bazie zrobić złączenie u siebie, jednym zapytaniem.

Ustawienie trybu przechowywania tabeli

Co chcemy osiągnąć. Chcemy przełączyć wymiary modelu Nordvelli na tryb Podwójne tak, żeby raport nie łączył danych między trybami przy każdym zapytaniu, i rozumieć ostrzeżenia, które Power BI przy tym pokazuje.

Tryb ustawia się per tabela, w widoku modelu. Klikasz tabelę, otwierasz okienko Właściwości i rozwijasz listę Tryb przechowywania. Zrobisz to na DimSklepy w modelu Nordvelli zaraz po wczytaniu tabel w następnej sekcji (krok po kroku niżej): z Tryb DirectQuery przełącz na Podwójne (w anglojęzycznym interfejsie Dual). Zwróć uwagę, że lista nie zawsze ma wszystkie trzy pozycje. Dopóki w modelu nie ma ani jednej tabeli w DirectQuery, tryb Podwójne nie ma sensu i Power BI go nie oferuje.

Każde przełączenie trybu Power BI potwierdza osobnym oknem i warto je przeczytać, bo mówią różne rzeczy. Przy przejściu na Podwójne ostrzeżenie dotyczy odświeżenia tabeli, które przy dużym wymiarze potrafi potrwać. Groźniejsze jest przejście na Import: Power BI pisze wprost, że to operacja nieodwracalna i nie będzie się dało wrócić do DirectQuery. Tabelę trzeba wtedy usunąć z modelu i wczytać od nowa, razem z relacjami i miarami, które się na niej opierały.

Tak samo przestaw DimProdukty i DimKalendarz. Przy DimProdukty okno jest dłuższe: Power BI ostrzega, że zmiana wprowadzi relacje ograniczone, i proponuje ustawić tryb Podwójne także dla powiązanej tabeli DimKategorie. Zostaw zaznaczone Ustaw tryb podwójny dla odnośnych tabel i kliknij OK. Po tym kroku wszystkie cztery wymiary są w trybie Podwójne, a FaktSprzedaz zostaje w DirectQuery.

Co się zmienia w widoku modelu

Po zmianie trybu nagłówki tabel dostają paski w różnych kolorach, a pod nazwą pojawia się etykieta trybu. To jedyne miejsce, w którym widać całą prawdę o modelu złożonym na jednym ekranie, więc warto się do niego przyzwyczaić, zanim model urośnie.

Model złożony: dwa źródła w jednym modelu

Co chcemy osiągnąć. Dział handlowy Nordvelli przygotowuje plan sprzedaży w arkuszu, a sprzedaż jest w bazie SQL Server. Chcemy połączyć oba źródła w jednym modelu, bez przenoszenia planu do bazy.

Model złożony (po angielsku composite model) to taki, w którym tabele pochodzą z różnych źródeł albo mają różne tryby przechowywania. Do niedawna Power BI na to nie pozwalał: cały model musiał być albo importem, albo DirectQuery. Dziś możesz mieć w jednym pliku tabelę faktów odpytywaną na żywo w SQL Server i obok niej tabelę wczytaną z pliku CSV, którego w bazie nie ma i nie będzie.

W Nordvelli robimy to tak: FaktSprzedaz zostaje w DirectQuery na MS SQL Server, wymiary DimSklepy, DimProdukty, DimKalendarz i DimKategorie przechodzą na Podwójne, a tabela agregatu wchodzi Importem. Drugie źródło, na przykład plan sprzedaży z arkusza, dokładasz zwykłym Pobierz dane i łączysz relacją z wymiarem. W pliku tej lekcji drugiego źródła nie dokładamy, żeby pomiary dotyczyły jednej bazy, ale mechanizm jest ten sam: Power BI nie pyta o zgodę na mieszanie trybów i po cichu robi z tego model złożony.

Zobacz też: Szkolenie Power BI - Desktop, DAX i Online + Copilot

Krok po kroku

1. Utwórz nowy plik (Plik, Nowy). Na karcie Narzędzia główne kliknij SQL Server, wpisz serwer (u nas 192.168.1.206) i bazę Nordvella, a w sekcji Tryb łączności danych zaznacz DirectQuery zamiast Importuj. 2. W Nawigatorze zaznacz pięć tabel: DimKalendarz, DimKategorie, DimProdukty, DimSklepy i FaktSprzedaz, a potem kliknij Załaduj. Relacje między nimi Power BI odczyta z kluczy obcych bazy. 3. W widoku modelu przestaw wymiary na Podwójne (sekcja wyżej).

Relacje między źródłami i ich ograniczenia

Co chcemy osiągnąć. Relacja między planem z arkusza a danymi z bazy Nordvelli działa inaczej niż zwykła relacja. Chcemy znać jej ograniczenia, zanim zbudujemy na niej raport dla zarządu.

Relacja między tabelą z jednego źródła a tabelą z drugiego nie jest zwykłą relacją. Power BI nazywa ją relacją ograniczoną (limited relationship) i oznacza w widoku modelu nawiasami na końcach linii. Ograniczona, bo silnik nie może jej rozwiązać wewnątrz jednego zapytania: musi pobrać listę kluczy z jednej strony i przekazać ją do drugiej.

Z relacji ograniczonej wynikają trzy rzeczy, które trzeba znać, zanim zbudujesz na niej raport dla zarządu:

• wiersze z pustym kluczem po stronie wiele nie trafiają do wyniku, więc suma po wymiarze może być mniejsza niż suma bez wymiaru,
• nie zadziała na niej pełne filtrowanie dwukierunkowe, a funkcje z rodziny RELATED zachowują się inaczej niż przy relacji zwykłej,
• jest wyraźnie wolniejsza, bo lista kluczy wędruje między źródłami przy każdym zapytaniu.

Dlatego relację między źródłami zakłada się na kolumnie o możliwie małej liczbie unikalnych wartości: na kluczu sklepu (piętnaście wartości) albo kategorii (cztery), a nie na identyfikatorze transakcji. Piętnaście kluczy przewędruje między źródłami niezauważalnie, sto osiem tysięcy zabiłoby raport.

Relację ograniczoną widzieliśmy już w tej lekcji, w oknie przy przestawianiu DimProdukty na Podwójne. Tam Power BI sam zaproponował, jak jej uniknąć: ustawić tryb Podwójne także dla DimKategorie. Właśnie dzięki wymiarom w trybie Podwójne żadna relacja w modelu Nordvelli nie jest ograniczona, także relacje agregatu z Importu do DimSklepy i DimKategorie: wymiar istnieje po obu stronach, więc silnik rozwiązuje relację bez przesyłania listy kluczy.

Budowa tabeli agregatów

Co chcemy osiągnąć. Większość wizualizacji w raporcie zarządczym Nordvelli pyta o sumy miesięczne według sklepu i kategorii, a nie o pojedyncze transakcje. Chcemy przygotować tabelę podsumowaną do tego poziomu, z której raport odpowie szybciej niż z pełnej tabeli sprzedaży.

Agregat to zaimportowana tabela, która zawiera to samo co tabela faktów, tylko podsumowane do wyższego poziomu. Idea jest prosta: większość wizuali w raporcie zarządczym i tak pyta o sumy miesięczne w rozbiciu na sklep i kategorię, a nie o pojedyncze paragony. Jeśli takie podsumowanie policzysz raz i wczytasz do pamięci, silnik odpowie z pamięci zamiast wysyłać zapytanie do bazy.

Policzmy, ile to daje na danych Nordvelli. Tabela FaktSprzedaz ma 108 675 wierszy. Ta sama sprzedaż zsumowana do poziomu rok, miesiąc, sklep i kategoria to 3 180 wierszy, czyli trzydzieści cztery razy mniej. Agregat zbudujesz w edytorze Power Query, grupując dane:

let
    Źródło = Sql.Database("192.168.1.206", "Nordvella"),
    Fakty = Źródło{[Schema="dbo", Item="FaktSprzedaz"]}[Data],
    Kalendarz = Źródło{[Schema="dbo", Item="DimKalendarz"]}[Data],
    Produkty = Źródło{[Schema="dbo", Item="DimProdukty"]}[Data],

    ZKalendarzem = Table.NestedJoin(Fakty, {"DataID"}, Kalendarz, {"DataID"}, "kal", JoinKind.Inner),
    RozwinDate = Table.ExpandTableColumn(ZKalendarzem, "kal", {"Rok", "Miesiac"}),
    ZProduktem = Table.NestedJoin(RozwinDate, {"ProduktID"}, Produkty, {"ProduktID"}, "prod", JoinKind.Inner),
    RozwinKategorie = Table.ExpandTableColumn(ZProduktem, "prod", {"KategoriaID"}),

    Pogrupowane = Table.Group(
        RozwinKategorie,
        {"Rok", "Miesiac", "SklepID", "KategoriaID"},
        {
            {"WartoscNetto", each List.Sum([WartoscNetto]), type number},
            {"KosztWlasny",  each List.Sum([KosztWlasny]),  Int64.Type},
            {"Ilosc",        each List.Sum([Ilosc]),        Int64.Type},
            {"LiczbaTransakcji", each Table.RowCount(_),    Int64.Type}
        }
    )
in
    Pogrupowane

W edytorze Power Query kliknij Nowe źródło, Puste zapytanie, a potem Edytor zaawansowany. Zaznacz cały szablon, wklej kod z ramki wyżej i kliknij Gotowe.

Nazwij zapytanie AgregatSprzedazy, kliknij Zamknij i zastosuj, a w oknie trybu przechowywania wybierz Importuj. Zwróć uwagę na ostatnią kolumnę: LiczbaTransakcji liczy wiersze w grupie i będzie potrzebna, żeby agregat obsłużył także miary typu COUNTROWS. Bez niej silnik przy pytaniu o liczbę transakcji zejdzie do tabeli szczegółowej, mimo że wszystko inne miał pod ręką.

Podpięcie agregatu przez Zarządzaj agregacjami

Sama tabela niczego jeszcze nie przyspiesza. Najpierw połącz ją z wymiarami: w widoku modelu przeciągnij SklepID z AgregatSprzedazy na SklepID w DimSklepy, a KategoriaID na KategoriaID w DimKategorie. Potem powiedz Power BI, czym jest ta tabela: kliknij ją prawym przyciskiem, wybierz Zarządzaj agregacjami i zmapuj kolumny podsumowań na kolumny tabeli szczegółowej:

Dlaczego klucze bez mapowania GroupBy? Bo Power BI nie pozwala mieszać obu mechanizmów w jednym agregacie. Gdy agregat ma relacje do wymiarów, a w oknie dodasz GroupBy, na przykład KategoriaID albo Rok i Miesiac z DimKalendarz, okno pokaże błąd i nie da się zastosować zmian. Rok i Miesiac zostają więc w agregacie bez mapowania i nie pomagają w zapytaniach po czasie. Agregat miesięczny wymagałby relacji z wymiarem, w którym klucz ma ziarno miesiąca.

Ziarno agregatu wyznaczają tu relacje: agregat odpowiada na pytania po sklepie i kategorii oraz po wszystkim, co z nich wynika (miasto, region, nazwa kategorii). Po zatwierdzeniu Power BI ukrywa tabelę agregatu przed użytkownikiem raportu. Raport dalej buduje się na FaktSprzedaz, a podmiana źródła dzieje się pod spodem.

Sprawdzenie, czy agregat został użyty

Co chcemy osiągnąć. Agregat działa po cichu: gdy zadziała, raport jest szybszy, a gdy nie, nic tego nie sygnalizuje. Chcemy sprawdzić, czy zapytania raportu Nordvelli naprawdę z niego korzystają.

Agregacje mają jedną nieprzyjemną cechę: działają cicho. Jeśli zadziałają, nie zobaczysz nic poza szybszym raportem, a jeśli nie zadziałają, też nie zobaczysz nic. Dlatego po skonfigurowaniu agregatu zawsze trzeba to sprawdzić, a nie zakładać.

Najprostsze narzędzie masz w Desktopie: wstążka Optymalizowanie, przycisk Analizator wydajności, potem Rozpocznij nagrywanie i Odśwież wizualizacje. Dla każdego wizualu zobaczysz rozbicie czasu na zapytanie DAX, wyświetlanie i inne. Kluczowy jest wiersz Zapytanie bezpośrednie: pojawia się tylko wtedy, gdy Power BI musiał wysłać zapytanie do źródła. Wizual, który trafił w agregat, tego wiersza nie ma, bo cała odpowiedź przyszła z pamięci.

Zrób test na trzech wizualizacjach na Stronie 1. Pierwsza: wykres kolumnowy, DimSklepy[Miasto] na osi X i WartoscNetto z FaktSprzedaz na osi Y. Druga: tabela z DimKategorie[Kategoria] i sumą Ilosc. Trzecia: tabela z FaktSprzedaz[SprzedazID] (w menu pola wybierz Nie sumuj) i sumą WartoscNetto. Pierwsze dwie mieszczą się w ziarnie agregatu, trzecia schodzi do pojedynczych transakcji. Agregat obsługuje swoje ziarno i wszystko powyżej, ale nigdy nic poniżej.

Wykres po mieście i tabela po kategorii trafiły w agregat, bo oba wymiary są z nim połączone relacją i oba są w trybie Podwójne. Agregat jest tabelą zaimportowaną, więc wymiar użyty w wizualizacji też musi być dostępny w pamięci. Gdyby DimKategorie została w DirectQuery, relacja agregatu do niej byłaby ograniczona, a każde pytanie po kategorii jechałoby do bazy, choćby agregat miał dokładnie te liczby u siebie. Tabela transakcji pyta o SprzedazID, którego w agregacie nie ma, więc musi zejść do FaktSprzedaz.

Stąd praktyczna zasada, która kosztuje jedno kliknięcie, a decyduje o całej opłacalności agregatu: wymiary, po których filtrujesz raport, przestaw na Podwójne. Zostawienie ich w DirectQuery sprawia, że agregat zbudujesz, odświeżysz i utrzymasz, a raport i tak będzie chodził do bazy przy każdym kliknięciu.

Porównanie czasów przed i po

Co chcemy osiągnąć. Chcemy zmierzyć, o ile szybciej działa wizual Nordvelli z agregatem, porównując ten sam pomiar przed podpięciem agregatu i po nim.

Analizator wydajności podaje czasy w milisekundach, więc porównanie jest proste: mierzysz ten sam wizual przed podpięciem agregatu i po nim. Żeby pomiar był uczciwy, oba stany mierz w tych samych warunkach: przed każdą serią kliknij w Analizatorze Wyczyść, żeby na liście został tylko bieżący pomiar, i porównuj odświeżenia o tym samym numerze, drugie z drugim. Power BI trzyma wyniki ostatnich zapytań w pamięci podręcznej, więc porównanie pierwszego odświeżenia z drugim zawsze wyjdzie sztucznie korzystnie dla drugiego.

Na modelu Nordvelli różnica jest tym większa, im dalej od agregatu jest zapytanie. Wizual na ziarnie agregatu (sklep, kategoria) czyta 3 180 wierszy z pamięci zamiast skanować 108 675 wierszy w bazie i przesyłać wynik po sieci. Przy tabeli faktów rzędu dziesiątek milionów wierszy ta sama zmiana potrafi skrócić czas odpowiedzi z kilkunastu sekund do ułamka sekundy, i to jest realny powód, dla którego agregacje w ogóle istnieją.

Kiedy agregaty nie pomogą

Co chcemy osiągnąć. Agregaty zajmują pamięć i wydłużają odświeżanie, więc nie każdy model na nich zyska. Chcemy wiedzieć, w których sytuacjach ich nie budować, w tym dlaczego model Nordvelli w trybie Import ich nie potrzebuje.

Agregacje nie są darmowe: zajmują pamięć, wydłużają odświeżanie i dokładają miejsce, w którym model może się rozjechać z rzeczywistością. Zanim je zbudujesz, sprawdź, czy Twój raport w ogóle na nich skorzysta. Cztery sytuacje, w których nie skorzysta:

Ostatni wiersz dotyczy wprost naszego kursu i warto to powiedzieć wprost: model Nordvelli w trybie Import nie potrzebuje agregacji. Pokazujemy je tutaj dlatego, że w pracy trafisz na modele, w których tabela faktów ma dziesiątki milionów wierszy i musi zostać w DirectQuery, a wtedy agregacje przestają być ciekawostką i stają się jedynym sposobem, żeby raport w ogóle nadawał się do używania.

Podsumowanie: co daje model złożony

Model złożony przestaje być techniczną ciekawostką w momencie, w którym dane przestają mieścić się w jednym trybie. Tabela faktów, która musi być świeża co do minuty, zostaje w DirectQuery. Wymiary, które zmieniają się raz na tydzień, dostają tryb Dual, żeby nie zmuszać silnika do przenoszenia kluczy między światami. Dane, których w bazie nie ma, wchodzą Importem z pliku. A na wierzchu leży agregat, który przechwytuje te zapytania, których jest najwięcej.

Trzy rzeczy do zapamiętania z tego rozdziału. Po pierwsze, Dual jest dla wymiarów, nigdy dla faktów. Po drugie, relacja między źródłami jest ograniczona i trzeba ją zakładać na kolumnie o małej liczbie unikalnych wartości. Po trzecie, agregat obsługuje swoje ziarno i wszystko powyżej, więc projektujesz go pod te wizualizacje, które odbiorcy otwierają najczęściej, a nie pod te, które są najbardziej szczegółowe. W kolejnym rozdziale wracamy do bezpieczeństwa: pokażemy, jak ograniczyć dane widziane przez konkretnego użytkownika regułami RLS.

✕