Kurs Power BI: Optymalizacja wydajności modelu (VertiPaq)

W skrócieOptymalizacja wydajności modelu Power BI zaczyna się od pomiaru: Analizator wydajności pokazuje czas wizualizacji, a VertiPaq Analyzer rozmiar kolumn. Na modelu Nordvelli znajdujemy, gdzie uciekają czas i pamięć, i odchudzamy model tak, że tabela faktów zajmuje prawie o połowę mniej, a raport pokazuje te same liczby.

Raport Nordvelli ma już wszystko: model gwiazdy, kilkadziesiąt miar DAX, wykresy trendu, mapę sklepów, inteligencję czasową i grupę obliczeniową. Z każdą lekcją model rósł, a nie wszystko, co do niego trafiło, jest jeszcze potrzebne. W tym rozdziale pokażemy Ci, jak zmierzyć wydajność liczbowo, gdzie faktycznie uciekają czas i pamięć, i jak odchudzić model Nordvelli bez utraty żadnej liczby w raporcie. Na końcu lekcji tabela faktów zajmuje w pamięci prawie o połowę mniej niż na jej początku, a strony raportu pokazują te same wyniki.

Analizator wydajności: mierz, zanim zaczniesz zgadywać

Co chcemy osiągnąć. Raport Nordvelli zwalnia, gdy przybywa danych i wizualizacji. Chcemy zmierzyć, który wizual zabiera najwięcej czasu i na co, zanim zaczniemy cokolwiek przyspieszać.

Pierwsza zasada optymalizacji brzmi: nie zgaduj. Zanim zaczniesz coś przyspieszać, zmierz, co jest wolne. Power BI Desktop ma do tego wbudowane narzędzie o nazwie Analizator wydajności (ang. Performance Analyzer). Włączasz je na karcie Optymalizowanie przyciskiem Analizator wydajności (ten sam przycisk jest na karcie Widok, w grupie Pokaż okienka). Po prawej stronie ekranu pojawi się panel z przyciskiem Rozpocznij nagrywanie.

Zrób tak. Otwórz stronę Przegląd (dwie karty, fragmentatory Miasto i Kategoria, przyciski, tabela sklepów i wykres miast). Kliknij Rozpocznij nagrywanie, a potem Odśwież wizualizacje (przycisk pojawia się w tym samym panelu). Analizator przeliczy każdy wizual od nowa i wypisze go na liście z czasem w milisekundach. Lista idzie w kolejności wizualizacji na stronie, a nie po czasie, więc najwolniejszy wizual szukasz po liczbach w kolumnie Czas trwania.

Kliknij plus przy nazwie wizualu, żeby rozwinąć rozbicie na trzy pozycje. Rozwiń pierwszą kartę i tabelę.

Jak czytać trzy liczby przy każdym wizualu

Każdy wizual ma trzy składowe czasu, a rozróżnienie między nimi decyduje o tym, gdzie w ogóle szukać poprawy:

Praktyczny próg: pojedynczy wizual, którego zapytanie DAX przekracza 120 ms, wart jest przyjrzenia się, a powyżej 500 ms zwykle psuje odczucie płynności, bo strona z kilkoma takimi wizualami zaczyna liczyć się grubo ponad sekundę. Na stronie Przegląd żaden wizual nie zbliża się do tych progów: najdłuższe zapytanie DAX trwa 9 ms, a o czasie decyduje pozycja Inne. Najwyższe czasy zapytania DAX dostają zwykle macierze, które krzyżują kilka wymiarów naraz (sklep, kategoria, miesiąc) i wywołują po kilka miar.

Zanim ruszysz dalej, zapisz sobie wartości bazowe. Odśwież wizualizacje jeszcze raz, spisz czasy trzech najwolniejszych wizuali, a potem wróć do tego samego pomiaru po każdej zmianie z kolejnych sekcji. Bez punktu odniesienia nie stwierdzisz, czy naprawa cokolwiek dała.

Redukcja rozmiaru: typy danych, kolumny i kardynalność

Co chcemy osiągnąć. Model Nordvelli rośnie z każdym miesiącem sprzedaży, a od jego rozmiaru zależą szybkość raportu i czas odświeżania. Chcemy zmniejszyć model, usuwając zbędne kolumny i ograniczając liczbę różnych wartości, bez utraty żadnej liczby w raporcie.

Power BI trzyma dane w silniku kolumnowym VertiPaq, który kompresuje każdą kolumnę osobno. Klucz do rozmiaru i szybkości to kardynalność, czyli liczba różnych wartości w kolumnie. Kolumna o niskiej kardynalności (na przykład KanalSprzedazy z kilkoma wartościami, albo Kategoria z czterema) kompresuje się świetnie. Kolumna o wysokiej kardynalności (na przykład WartoscNetto z tysiącami niepowtarzalnych kwot do drugiego miejsca po przecinku) zabiera dużo miejsca i spowalnia odczyt.

Ile waży każda tabela i kolumna, pokazuje VertiPaq Analyzer wbudowany w bezpłatne narzędzie DAX Studio. W naszym Power BI ikona DAX Studio jest na karcie Narzędzia zewnętrzne, obok Tabular Editor z lekcji 17. Kliknij ją, a DAX Studio połączy się od razu z otwartym plikiem. Przejdź na kartę Advanced, kliknij View Metrics i okno Metric Options zatwierdź przyciskiem Ok. Na dole okna pojawi się karta VertiPaq Analyzer z widokiem Tables.

Przełącz widok na Columns. Lista kolumn ze wszystkich tabel, posortowana po rozmiarze, od razu wskazuje, która kolumna waży najwięcej.

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

Krok 1: usuń kolumny, których nie używasz

To najprostsza i często najskuteczniejsza zmiana. Kolumnę źródłową, której nie potrzebujesz w ogóle, usuwasz w Power Query (prawy przycisk na nagłówku, Usuń), żeby silnik nawet nie próbował jej kompresować. W Nordvelli nie usuwamy jednak żadnej kolumny źródłowej: z WartoscBrutto liczy strona Python i R z lekcji 13, a z MarzaNetto miara Łączna marża z lekcji 07 i wszystko, co z niej korzysta. Menu z zrzutu niżej tylko oglądamy i zamykamy klawiszem Esc.

// Power Query (język M): wzór kroku usuwającego kolumny źródłowe.
// Dopisujesz go na końcu zapytania. PoprzedniKrok to nazwa ostatniego kroku,
// KolumnaA i KolumnaB to kolumny, których nie używa żadna miara ani wizualizacja.
BezZbednych = Table.RemoveColumns(PoprzedniKrok, {"KolumnaA", "KolumnaB"})

W Nordvelli zbędna jest kolumna, której w Power Query w ogóle nie ma: kolumna obliczeniowa Wartosc Brutto Sprawdzenie, dopisana w lekcji 07, żeby pokazać kontekst wiersza. Żaden wizual jej nie używa, a według VertiPaq Analyzer zajmuje 42 procent modelu. Kolumna obliczeniowa powstaje dopiero w modelu, więc usuwasz ją w Power BI, nie w Power Query.

Krok po kroku. Kliknij drugą ikonę na lewym pasku (Widok tabeli). W polu wyszukiwania panelu Dane wpisz Sprawdzenie i kliknij znalezioną kolumnę. Power BI pokaże tabelę FaktSprzedaz z zaznaczoną kolumną, jej formułę w pasku formuły i liczbę odrębnych wartości na pasku stanu.

Kliknij prawym przyciskiem nagłówek kolumny i wybierz Usuń.

Power BI zapyta o potwierdzenie. Kliknij Tak.

Wróć do DAX Studio i jeszcze raz kliknij View Metrics.

Gdyby w Twoim modelu nie było kolumny WartoscBrutto, wartość brutto odtworzysz miarą, która liczy się dopiero na potrzeby wizualu, a nie zajmuje miejsca w każdym z 108 675 wierszy. W Nordvelli zakładamy ją obok kolumny, żeby porównać wyniki. Miara trafia do tabeli FaktSprzedaz:

Sprzedaż brutto =
SUMX(
    FaktSprzedaz,
    FaktSprzedaz[WartoscNetto] * ( 1 + RELATED( DimProdukty[StawkaVAT] ) )
)

Miara działa, bo StawkaVAT w DimProdukty jest liczbą (0,23 albo 0,08; wartości 0.23 w kolumnie StawkaVAT widać w podglądzie okna relacji w dalszej części lekcji). W arkuszu Excela kolumna ma tekst „23%”, ale zapytanie DimProdukty w Power Query zamienia go na typ procentowy. Gdyby została tekstem, miara musiałaby w każdym z 108 675 wierszy wycinać znak procentu i zamieniać tekst na liczbę (VALUE, SUBSTITUTE), a to jest dokładnie ten rodzaj kosztu, który Analizator wydajności pokazuje jako wolne zapytanie DAX. Typ zmieniasz raz, przy wczytywaniu. Wynik zobaczysz na stronie Wydajność miar z końca sekcji o miarach: 566 064 986 zł, niecałe 2 zł więcej niż suma kolumny WartoscBrutto (566 064 984,87 zł). Różnica wynika z zaokrągleń groszy w źródle.

Krok 2: obetnij precyzję znaczników czasu i liczb

Jeśli którakolwiek kolumna niesie znacznik czasu z godziną, minutą i sekundą, jej kardynalność wybucha, bo prawie każdy wiersz ma inną wartość. W Nordvelli takiej kolumny nie ma: daty w tabelach faktów to klucze liczbowe w postaci RRRRMMDD (DataID, DataZwrotu, DataZakupu), połączone z DimKalendarz. Gdy w swoim modelu trafisz na kolumnę typu data i godzina, a raportujesz w skali dnia, obetnij czas do samej daty. To jedna z najsilniejszych pojedynczych redukcji, bo liczba różnych wartości spada do liczby dni.

// Power Query (język M): wzór kroku obcinającego datę i godzinę do samej daty.
// PoprzedniKrok i DataGodzina zastąp nazwami ze swojego zapytania.
Table.TransformColumns(
    PoprzedniKrok,
    {{"DataGodzina", each DateTime.Date(_), type date}}
)

To samo dotyczy liczb. Kwota zapisana do sześciu miejsc po przecinku, gdy raportujesz do grosza, ma więcej wartości unikalnych, niż potrzeba. Zaokrąglenie do dwóch miejsc (karta Przekształć, Zaokrąglanie) obniża kardynalność i poprawia kompresję. Kwoty Nordvelli mają już po dwa miejsca po przecinku, a KosztWlasny jest liczbą całkowitą, więc tu nie ma czego obcinać.

Krok 3: wyłącz automatyczną datę i godzinę

Power BI domyślnie tworzy ukrytą tabelę dat dla każdej kolumny typu data w modelu. Przy kilku kolumnach dat i osobnej, poprawnej tabeli DimKalendarz te ukryte tabele to zbędny ciężar. Wejdź w Plik, Opcje i ustawienia, Opcje, sekcja Bieżący plik, Ładowanie danych i w grupie Analiza czasowa odznacz Automatyczna data/godzina. Datę obsługuje wtedy Twoja DimKalendarz, którą i tak masz oznaczoną jako tabelę dat.

Efekt: co daje redukcja na Nordvelli

Tak wygląda bilans redukcji na Nordvelli. Kardynalności pochodzą z widoku Columns w VertiPaq Analyzer.

Star schema kontra jedna tabela płaska

Co chcemy osiągnąć. Model Nordvelli ma być mały i szybki, bo od tego zależy czas otwierania każdej strony raportu. Chcemy zrozumieć, dlaczego schemat gwiazdy spełnia ten warunek lepiej niż jedna szeroka tabela.

Kusi, żeby przed wczytaniem połączyć wszystko w Power Query w jedną szeroką tabelę: dokleić do faktu nazwę produktu, kategorię, markę, miasto sklepu, imię pracownika. Taka tabela płaska (ang. flat table) wygląda wygodnie, bo wszystko jest w jednym miejscu. Dla silnika VertiPaq to zła decyzja. Star schema (schemat gwiazdy z tabelą faktów pośrodku i tabelami wymiarów wokół) jest lżejsza i szybsza, i to z konkretnego powodu.

Gdy dokleisz nazwę produktu do każdego z 108 675 wierszy sprzedaży, powielasz ten sam tekst dziesiątki tysięcy razy. Owszem, VertiPaq to kompresuje, ale kolumna tekstowa o dużej długości i tak zajmuje więcej niż jeden krótki klucz liczbowy plus mały wymiar z 30 wierszami, w którym nazwa występuje raz. Do tego każde grupowanie po kategorii przelicza się teraz na wielkiej tabeli, a nie na 4 wierszach kategorii połączonych relacją. Efekt: większy model i wolniejsze miary.

// ANTYWZORZEC: doklejanie opisów wymiaru do tabeli faktów w Power Query.
// Nazwa i Kategoria zostają powielone w każdym z 108 675 wierszy sprzedaży.
let
    Zlaczenie = Table.NestedJoin(FaktSprzedaz, {"ProduktID"}, DimProdukty, {"ProduktID"}, "prod", JoinKind.LeftOuter),
    Rozwiniete = Table.ExpandTableColumn(Zlaczenie, "prod", {"Nazwa", "Kategoria", "Marka"})
in
    Rozwiniete
// Nie rób tego dla wydajności. Zostaw ProduktID w fakcie i relacje do DimProdukty.

Poniższa tabela zestawia dwie drogi na tych samych danych Nordvelli. Kolumny opisowe (Nazwa, Kategoria, Marka, Miasto) w schemacie gwiazdy żyją raz na wymiar, a w tabeli płaskiej są powielone tyle razy, ile jest wierszy sprzedaży.

Zasada praktyczna: fakty trzymają liczby (ilości, kwoty) i klucze do wymiarów, a wszystkie opisy (nazwy, kategorie, miasta, imiona) mieszkają w wymiarach. W raporcie Nordvelli model jest już gwiazdą, więc Twoje zadanie sprowadza się do pilnowania, by nie psuć go doklejaniem opisów do faktów. O budowie samych relacji piszemy szerzej w rozdziale o modelu danych, tutaj patrzymy na nie wyłącznie przez pryzmat wydajności.

Kosztowne miary DAX i jak je poznać

Co chcemy osiągnąć. Raport Nordvelli ma wiele miar i nie każdą opłaca się mierzyć po kolei. Chcemy umieć wskazać miary, które spowalniają raport, już po samym ich zapisie.

Kiedy Analizator wydajności pokaże wysoki czas zapytania DAX, a nie wyświetlania, wina leży po stronie miary albo relacji. Są wzorce, które prawie zawsze kosztują za dużo, i można je poznać po samym zapisie, jeszcze przed pomiarem. Poniżej trzy najczęstsze pułapki na danych Nordvelli i sposób, w jaki je przepisujemy.

Pułapka 1: iterator tam, gdzie wystarczy agregacja

Miara SUMX chodzi wiersz po wierszu po tabeli faktów. Gdy w środku jest tylko jedna kolumna, silnik rozpoznaje ten wzorzec i liczy go tak samo jak zwykłe SUM, więc obie wersje niżej dają ten sam wynik w podobnym czasie. Iterator robi się drogi dopiero wtedy, gdy w środku pojawi się wyrażenie, którego silnik magazynu nie policzy sam: IF, zamiana tekstu na liczbę, wywołanie innej złożonej miary. Wtedy każdy wiersz trafia do silnika formuł. Dlatego do zwykłej sumy kolumny używaj SUM, a SUMX zostaw na mnożenie kolumn i na logikę, która naprawdę musi iść wiersz po wierszu. Miarę Łączna sprzedaż masz od lekcji 07. Załóż w FaktSprzedaz tylko wersję z dopiskiem (wolno), żeby porównać je w tabeli na końcu sekcji:

// Iterator tam, gdzie wystarczy suma (zakładasz tę miarę)
Łączna sprzedaż (wolno) = SUMX(FaktSprzedaz, FaktSprzedaz[WartoscNetto])

// Prosta agregacja (miara z lekcji 07, już jest w modelu)
Łączna sprzedaż = SUM(FaktSprzedaz[WartoscNetto])

Pułapka 2: FILTER na całej tabeli w CALCULATE

Kolejny częsty koszt to owijanie warunku w FILTER po całej tabeli faktów wewnątrz CALCULATE. To zmusza silnik do przejścia wszystkich 108 675 wierszy. Gdy warunek dotyczy jednej kolumny, przekaż go jako prosty warunek, a silnik zamieni go na tani filtr:

// Za drogo: FILTER na całej tabeli faktów
Sprzedaż karta (wolno) =
CALCULATE(
    SUM(FaktSprzedaz[WartoscNetto]),
    FILTER(FaktSprzedaz, FaktSprzedaz[FormaPlatnosci] = "Karta")
)

// Dobrze: prosty warunek na kolumnie, silnik zamienia go na tani filtr
Sprzedaż karta =
CALCULATE(
    SUM(FaktSprzedaz[WartoscNetto]),
    FaktSprzedaz[FormaPlatnosci] = "Karta"
)

Załóż obie miary w FaktSprzedaz. Jedno zastrzeżenie, bo te dwie miary nie różnią się wyłącznie kosztem. Dają ten sam wynik dopóki kolumna FormaPlatnosci nie jest już filtrowana w wizualizacji. Połóż ją na wierszach tabeli, a wersja z FILTER pokaże wartość tylko w wierszu Karta, w pozostałych zostawi pustkę - bo FILTER zawęża to, co w kontekście już jest, a prosty warunek ten filtr podmienia. W praktyce najczęściej filtrujesz po czymś innym (kategoria, region, miesiąc) i wtedy obie wersje zwracają to samo, więc zostaje sam argument o wydajności. Warto jednak wiedzieć, że przy filtrze na tej samej kolumnie wynik też się rozjedzie.

Pułapka 3: powtarzanie tego samego liczenia w jednej mierze

Gdy miara liczy udział marży, kusi, żeby tę samą sumę policzyć kilka razy: raz w warunku, raz w mianowniku. Wtedy silnik liczy to samo kilka razy. Wyciągnij powtarzalny wynik do zmiennej VAR, policz raz i użyj wielokrotnie. Pierwszej wersji nie zakładaj, to tylko przykład, drugą załóż w FaktSprzedaz pod nazwą Marża % (VAR) z formatem procentowym:

// Za drogo: ta sama suma sprzedaży liczona dwa razy, plus osobne sprawdzenie zera
Marża % (powtórzenia) =
IF(
    SUM(FaktSprzedaz[WartoscNetto]) = 0,
    BLANK(),
    SUM(FaktSprzedaz[MarzaNetto]) / SUM(FaktSprzedaz[WartoscNetto])
)

// Dobrze: każda suma liczona raz, DIVIDE pilnuje dzielenia przez zero
Marża % (VAR) =
VAR SumaMarzy = SUM(FaktSprzedaz[MarzaNetto])
VAR SumaSprzedazy = SUM(FaktSprzedaz[WartoscNetto])
RETURN
    DIVIDE(SumaMarzy, SumaSprzedazy)

Zwróć uwagę na DIVIDE zamiast operatora ukośnika. DIVIDE bezpiecznie zwraca pustą wartość przy dzieleniu przez zero, więc nie musisz dodawać osobnego sprawdzenia, które też kosztuje. Miara Marża % z lekcji 07, DIVIDE([Łączna marża], [Łączna sprzedaż], 0), jest zbudowana w tym samym duchu: każda suma liczona raz, przez odwołanie do miary. Dlatego jej nie zmieniamy, a Marża % (VAR) zakładamy obok, żeby porównać wyniki.

Porównanie na stronie Wydajność miar

Zbierz miary z trzech pułapek w jednej tabeli. Dodaj stronę i nazwij ją Wydajność miar. Wstaw wizualizację Tabela i przez pole wyszukiwania w panelu Dane zaznacz kolejno: FormaPlatnosci, Sprzedaż karta, Sprzedaż karta (wolno), Łączna sprzedaż, Łączna sprzedaż (wolno), Sprzedaż brutto, Marża % i Marża % (VAR). W Formatuj element wizualny ustaw rozmiar 1240 na 260, pozycję 20 i 20 oraz czcionkę siatki 14, a dolną krawędź tabeli pociągnij myszą w dół, aż zmieszczą się wszystkie wiersze.

Teraz zmierz stronę. W Analizatorze wydajności kliknij Wyczyść, potem Odśwież wizualizacje i rozwiń wiersz Tabela.

Na 108 675 wierszach Nordvelli całe zapytanie DAX trwa 34 ms, więc różnice między wersjami miar giną w pomiarze. Pułapki z tej sekcji zaczynają kosztować na tabelach liczonych w milionach wierszy i w wizualizacjach, które wywołują miarę tysiące razy, na przykład w macierzy sklep, produkt i dzień. Tańszy zapis opłaca się więc, zanim model urośnie.

Dobre praktyki modelu pod silnik VertiPaq

Na koniec zbierzmy praktyki, które stosujesz w każdym modelu Power BI, nie tylko w Nordvelli. To lista, do której wracasz przed publikacją raportu. Każdy punkt działa w tę samą stronę: mniej danych do skanowania, prostsze relacje, tańsze miary.

CHECKLISTA WYDAJNOŚCI MODELU (VertiPaq)
[1] Usuń kolumny, których nikt nie używa (u nas Wartosc Brutto Sprawdzenie)
[2] Obetnij znaczniki czasu do daty i liczby do sensownej precyzji
[3] Wyłącz Automatyczną datę i godzinę w opcjach pliku
[4] Trzymaj opisy w wymiarach, klucze liczbowe w fakcie (star schema)
[5] Relacje jeden do wielu, kierunek pojedynczy, na kluczu liczbowym
[6] SUM zamiast SUMX, gdy nie mnożysz kolumn
[7] Prosty warunek w CALCULATE zamiast FILTER na całej tabeli
[8] VAR na powtarzalne składniki, DIVIDE zamiast ukośnika
[9] Ukryj kolumny techniczne i klucze przed użytkownikiem
[10] Zmierz przed i po w Analizatorze wydajności

Relacje: kardynalność i kierunek filtra

Relacja w modelu Nordvelli między FaktSprzedaz a DimProdukty jest typu wiele do jednego (wiele wierszy sprzedaży do jednego produktu) z kierunkiem filtra pojedynczym. To jest ustawienie, którego pilnujesz. Kierunek dwukierunkowy (ang. both) bywa potrzebny w wąskich przypadkach, ale kosztuje wydajność i potrafi wprowadzić niejednoznaczność w modelu, więc włączaj go tylko, gdy naprawdę musisz, i tylko na konkretnej relacji.

Ukrywaj to, czego użytkownik nie liczy

Klucze techniczne (DataID, ProduktID, SklepID, PracownikID) i kolumny pomocnicze ukryj w widoku Model prawym przyciskiem, Ukryj w widoku raportu. To nie zmienia rozmiaru, ale porządkuje listę pól, zmniejsza ryzyko, że ktoś przeciągnie klucz na wykres i wywoła kosztowne grupowanie po kolumnie o średniej kardynalności zamiast po atrybucie opisowym.

Po tej lekcji model Nordvelli zajmuje w pamięci 5,30 MiB (DAX Studio, VertiPaq Analyzer, widok Summary), plik .pbix waży 7,1 MB zamiast 8,4 MB, a wszystkie strony raportu pokazują te same liczby co przed zmianą. To ważne nie tylko dla komfortu, bo mniejszy model odświeża się szybciej i lżej obciąża pojemność w usłudze Power BI, o czym piszemy w rozdziałach o publikacji i odświeżaniu.

✕