Czy podczas tworzenia raportów napotykasz bariery wydajnościowe? A może czasami masz wrażenie, że wizualizacje podczas tworzenia raportów są zbyt wolne?
Bardzo możliwe, że napotykasz wąskie gardła, które powodują poważne problemy silnika Power BI z przetwarzaniem miar i danych.
Poniżej przedstawiono podstawowe wąskie gardła, które powodują spowolnienie działania modelu danych, a w rezultacie negatywne opinie ze strony firmy. Biznes jest wrażliwy na czas, a jeśli wizualizacja ładuje się długo, klienci nie są zadowoleni.
1. Zbyt wiele elementów wizualnych na jednej stronie raportu
Wąskie gardła:
- Pulpity nawigacyjne zawierające 20–30 elementów wizualnych, z których każdy uruchamia własne zapytanie
- Używanie zbyt wielu fragmentatorów, zwłaszcza gdy filtrują się one nawzajem
- Tabele lub macierze zawierające tysiące wierszy renderowanych dynamicznie
Rozwiązanie:
- Zmniejsz liczbę elementów wizualnych
- Grupuj wizualizacje, na przykład grupuj pary kolumn w jedną kolumnę, a następnie filtruj według niej.
- Użyj zakładek, aby przełączać się między elementami wizualnymi zamiast wyświetlać wszystkie
- Używaj niestandardowych elementów wizualnych z umiarem — często działają one wolniej niż elementy natywne
Na przykład poniższa strona raportu Power BI zawiera zbyt wiele fragmentatorów, przez co działa wolno:
W takim przypadku najlepiej jest zmniejszyć liczbę fragmentatorów, aby znacznie przyspieszyć generowanie raportu.
2. Nadmierne miary i kolumny obliczeniowe
Wąskie gardła:
- Setki nieużywanych lub zbędnych miar
- Zbyt wiele kolumn obliczeniowych, które można utworzyć po stronie zaplecza, na przykład w SQL Server lub nawet w PowerQuery.
- Miary zależne od złożonych funkcji DAX (miara wewnątrz miary wewnątrz miary…)
Rozwiązania:
- Sprawdź i usuń nieużywane miary.
- W przypadku setek lub tysięcy miar warto skorzystać z zewnętrznych narzędzi, takich jak MeasureKiller.
- Oszczędnie korzystaj z odwołań do miar; unikaj zależności cyklicznych
- Rozważ rozgałęzienie miar (measure branching). Jest to technika polegająca na podzieleniu miar na mniejsze części i połączeniu ich w jedną miarę, która jest wyświetlana. Na przykład, jeśli miara oblicza udział sprzedaży w całkowitej sprzedaży, zamiast zapisywać miarę w zaawansowany sposób, podziel ją na mniejsze części, odwołaj się do nich w miarze, a gwarantuję, że silnik Power BI zoptymalizuje pamięć i znacznie przyspieszy wykonanie tej miary.
Na przykład poniższa miara DAX jest bardzo złożona, a jej debugowanie jest uciążliwe:
Measure =
IF(
COUNTROWS(DIM_RESTAURANT[Country_Code]) > 1,
CALCULATE(
SUM(FCT_SALES[Amount_EUR]),
FILTER(
ALL(DIM_CALENDAR),
DIM_CALENDAR[YearMonth] >= "2024-01" && DIM_CALENDAR[YearMonth] <= "2024-09"
)
),
CALCULATE(
SUM(FCT_SALES[Amount_LCY]),
FILTER(
ALL(DIM_CALENDAR),
DIM_CALENDAR[YearMonth] >= "2024-01" && DIM_CALENDAR[YearMonth] <= "2024-09"
)
)
)
MeasureUsingMeasureBranching =
VAR rangestart = "2024-01" -- for example it's from slicer
VAR rengeend = "2024-09" -- for example it's from slicer
IF(
[CountCountries] > 1, -- countrows above is transffered into resuable measure
CALCULATE(
[SalesEUR],
FILTER(
ALL(DIM_CALENDAR),
DIM_CALENDAR[YearMonth] >= rangestart && DIM_CALENDAR[YearMonth] <= rengeend
)
),
CALCULATE(
[SalesLCY],
FILTER(
ALL(DIM_CALENDAR),
DIM_CALENDAR[YearMonth] >= rangestart && DIM_CALENDAR[YearMonth] <= rengeend
)
)
)
3. Niewłaściwy projekt modelu danych
Wąskie gardła:
- Użycie schematu typu „śnieżynka” (tabele wymiarów powiązane z innymi tabelami wymiarów)
- Wielokrotne nieaktywne relacje obsługiwane za pomocą USERELATIONSHIP w miarach
- Dwukierunkowe filtry między tabelami powodujące zależności cykliczne lub złożone propagowanie filtrów
Rozwiązania:
- Użyj schematu gwiaździstego: tabela faktów w centrum, wymiary wokół
- Unikaj filtrów dwukierunkowych, chyba że jest to absolutnie konieczne
- Użyj kluczy zastępczych i połączeń całkowitoliczbowych, aby uzyskać lepszą wydajność
4. Nieprawidłowe typy danych w tabelach faktów
Wąskie gardła:
- Jeśli kolumna jest używana w relacjach w Power BI i jest wielokrotnie powielana, ponieważ znajduje się w tabeli faktów, musi mieć odpowiedni typ danych.
Rozwiązania:
- Unikaj łączenia kolumn tekstowych z liczbami lub kolumn tekstowych z tekstem. Zamiast tego spróbuj uzyskać liczbę porządkową. Jeśli nie masz dostępu do hurtowni danych, możesz nawet utworzyć liczbę porządkową w Power Query, dodając indeks, co znacznie przyspieszy działanie modelu danych.
Przykładowo:
Ten ekran pokazuje Store_ID, który jest kluczem do relacji. Jest to typ tekstowy, więc działa dość wolno. Aby uniknąć spowolnienia działania raportu, zmień typ formatu na liczbę całkowitą.
Możesz również dodać kolumnę indeksową w PowerQuery, aby przyspieszyć działanie raportu:
5. Kolumny o wysokiej kardynalności i relacje
Wąskie gardła:
- Używanie kolumn takich jak adres e-mail, identyfikator użytkownika lub pełny znacznik czasu w modelu
- Kolumny zawierające setki tysięcy unikalnych wartości
- Relacje wiele do wielu w polach o wysokiej kardynalności
Rozwiązania:
- Usuń kolumny, które nie są używane w raportowaniu
- W razie potrzeby podziel kolumny daty i godziny na datę i godzinę
- Zastąp kolumny o wysokiej kardynalności mapowaniami kategoryzowanymi (np. mapowanie kodów pocztowych do regionów)
Relacje wiele do wielu to przykłady najczęstszych wąskich gardeł, są bardzo wolne i bardzo nietypowe. Wiele blogów, szkoleń oraz nawet bootcampów w takich sytuacjach zaleca materializację tabeli pomostowej miedzy tabelami, ale wciąż to jest relacja wiele do wielu z tabelą pomostową i wciąż jest wolna i nieoptymalna. Poniższe rozwiązanie to wiedza ekspercka, bo jeśli podłączamy się do modelu na żywo, to NIE JESTEŚMY w stanie stworzyć pomostu fizycznego między tabelami. Wówczas mamy do dyspozycji jedynie miary.
Załóżmy, że mamy poniższy model danych.
Tabela fct_sales ma granularność dzienną per produkt, natomiast tabela fct_budget ma granularność miesięczną per produkt. W takiej sytuacji tworzy się miedzy tabelami relacja wiele do wielu:
Tworząc raport nie popieramy stosowania takich relacji wiele-do-wielu, stąd też naszym rozwiązaniem tego problemu jest poniższy workaround:
NIE TWORZYMY TABELI POMOSTOWEJ, NIE TWORZYMY RELACJI WIELE DO WIELU
6. Niezoptymalizowane kroki Power Query
Wąskie gardła:
- Używanie Table.AddColumn lub Table.SelectRows w operacjach opartych na wierszach
- Wykonywanie scalania lub łączenia po załadowaniu, a nie u źródła
- Niekorzystanie z zapytań przejściowych (zapytania odwołujące się do innych zapytań)
Rozwiązania:
- Przenieś transformacje do SQL lub systemu źródłowego (zaznacz opcję „Wyświetl zapytanie natywne”)
- Przenieś logikę w górę, jeśli źródłem jest baza danych (np. pozwól SQL wykonać grupowanie/łączenie)
- Używaj funkcji buforowania (Table.Buffer) tylko wtedy, gdy jest to absolutnie konieczne
7. Zbyt duża ilość danych (wąskie gardło związane z objętością)
Wąskie gardła:
- Ładowanie surowych danych zawierających miliony wierszy (np. dzienniki, transakcje)
- Pobieranie większej liczby kolumn niż to konieczne
- Brak redukcji danych przed załadowaniem (np. lata historii, z których nikt nie korzysta)
Rozwiązania:
- Użyj widoków lub procedur przechowywanych, aby wstępnie agregować lub filtrować dane u źródła
- Importuj tylko kolumny używane w wizualizacjach, filtrach lub miarach
- Używaj tabel zagregowanych wraz z tabelami szczegółowymi (modele złożone)
MIARY DAX
I co najważniejsze, co jest odrębną kwestią poza tymi 7 najważniejszymi punktami. Miara DAX, którą piszesz, może być zapisana na 10 różnych sposobów, ale nie każdy sposób jest optymalny i dobry.
Wąskie gardła:
- Wielokrotne używanie RELATEDTABLE w miarach
- Iteratory takie jak SUMX, AVERAGEX, RANKX w dużych tabelach
- Zagnieżdżona logika IF, SWITCH lub VAR bez uproszczenia
Rozwiązania:
- Przenieś logikę na stronę zaplecza, jeśli to możliwe
- Używaj relacji i filtrów domyślnych zamiast FILTER(ALL(…))
- Utwórz kolumny w źródle, które wyeliminują potrzebę iteracji miar (te z literą X na końcu nazwy).
Przykładowo:
Utwórz kolumny w źródle w postaci, na przykład, sprzedaży netto.
Jeśli Twoja metryka wygląda tak, czas na zmianę:
SlowMeasure =
SUMX(
FCT_SALES,
FCT_SALES[Sales] - FCT_SALES[TaxAmount] - FCT_SALES[ExcludedSales]
)
FastMeasure
SalesActual =
SUM(FCT_SALES[SalesActual] -- transfer all steps from SUMX to source for example to sql server
To dużo. Czy naprawdę muszę pamiętać wszystko za każdym razem i czy naprawdę nie ma żadnych narzędzi, które mogłyby mi w tym pomóc? Czas to pieniądz.
Oczywiście, że są. Istnieją nawet świetne, bezpłatne i bezpieczne narzędzia, które automatycznie sprawdzają model danych, szukają najlepszych praktyk i zaznaczają nieużywane kolumny oraz złe praktyki, które spowalniają model danych.
Jedną z naszych ulubionych aplikacji jest MeasureKiller. Jest to bezpłatne narzędzie, które łączymy z raportem i wyświetlamy w Power BI:
Link do MeasureKiller: Measure Killer | Brunner BI
Po kliknięciu MeasureKiller i otwarciu raportu pojawi się następujące pole:
Kliknij „Uruchom” i magia się zacznie:
Co widać na załączonym zrzucie ekranu?
- Mamy aż 24% nieużywanych kolumn. Można powiedzieć, że to tylko 4,3 MB, ale pomyśl o tym: jeśli masz ogromny raport, a MeasureKiller pokazuje, że nawet 10% z niego jest nieużywane, to po prostu usunięcie go poprawi wydajność Twojego modelu.
- Wszystkie kolumny zaznaczone na czerwono oznaczają, że nie są używane.
Jednak najważniejszą funkcją jest prawdopodobnie analizator najlepszych praktyk. Za pomocą jednego kliknięcia mogę sprawdzić, że przykładowy model danych jest fatalny. Wspomniany powyżej nieprawidłowy typ danych jest oznaczony jako bardzo poważny błąd. Niektóre błędy są mniej istotne, ale sam fakt ich wystąpienia jest niepokojący.
Podsumowanie
Celem tego artykułu jest przedstawienie najważniejszych wąskich gardeł powodujących problemy z wydajnością raportów. Zaproponowano również rozwiązania tych problemów, aby czytelnicy mogli zoptymalizować swoje raporty i znacznie skrócić czas potrzebny do ich obliczenia.
Przedstawiono również dodatkowe narzędzie zewnętrzne, które niczym wisienka na torcie pokazuje najważniejsze wąskie gardła i skłania do refleksji nad tym, czy nasz model danych jest optymalny, czy też powinniśmy usiąść i go ulepszyć.