PowerBI_BLOG#3

Dobre praktyki modelowania danych w Power BI

Budując raporty poza zapewnieniem prawidłowo przeliczonych liczb, ułożonych odpowiednio wizualizacji, które pozwalają wyciągać wnioski płynące z analizy powinniśmy także zwrócić uwagę na techniczne wykonanie raportu. Ma to istotny wpływ na prędkość ładowania wizualizacji, ogólne działanie raportu oraz wielkość zestawu danych, co może okazać się ograniczeniem wolumenu ładowanych danych. Dla przykładu w licencji Power BI Pro wielkość zestawu danych nie może przekroczyć 1GB, a nieskompresowana wielkość tabeli 10GB oraz czas odświeżania zestawu danych powinien mieścić się w dwóch godzinach. Co jeśli musimy załadować historię danych, która liczy dziesiątki lub setki milionów wierszy?

Dlaczego modelowanie danych jest takie ważne?

Modelowanie danych możemy uznać za pracę u podstaw. Im lepiej wykonana, tym później łatwiej nam zarządzać zmianą, a także ułatwia pracę na dalszych etapach. Dobrze przemyślany model danych pozwoli nam prosto i szybko zbudować zestaw danych, a także pozwoli innym użytkownikom szybko wdrożyć w przypadku potrzeby przejęcia raportu. Poza samą pracą koncepcyjną istnieje również kilka ważnych kroków, które powinniśmy wykonać tworząc nasz model danych.

Ograniczenie ilości kolumn w tabelach.

Najważniejsze i najprostsze pytanie powinniśmy postawić na początku. Czy na pewno będziemy używać wszystkich kolumn wymienionych w źródle danych? Jeśli okażą się kiedyś potrzebne będziemy mogli je zawsze dodać. Wiem, że wiele osób często chce mieć kolumny w danych na wszelki wypadek gdyby okazały się potrzebne, jednak przy pracy z dużymi zbiorami danych, gdy nie jesteśmy pewni czy model danych nie będzie zbyt obciążony warto zrezygnować z nich na pierwszym miejscu zgodnie z zasadą – kolumny nieużywane nie powinny znajdować się w modelu.

Odpowiednie zdefiniowanie typu danych w kolumnach.

Porządkując dane pamiętaj, aby poprawnie definiować typy danych. Jeśli masz możliwość wykorzystania wartości liczbowych i identyfikatorów staraj się to robić. Innym przykładem będzie przechowywanie kolumny daty sprzedaży w formacie daty zamiast tekstowej. Zastanów się także, czy potrzebujesz pełnego rozwinięcia dziesiętnego do 10 miejsc po przecinku jeśli mierzysz sprzedaż w PLN.

Oddzielenie tabel wymiarów od tabel faktów.

Oddzielając od siebie wymiary i fakty wykonujemy pracę mającą na celu zbudowanie tzw. słowników i wdrożenia modelu gwiazdy wykorzystywanego w Power BI. Polega on na wykorzystaniu relacji pomiędzy tabelą faktów i tabelą wymiarów. Przyjmijmy za przykład tabelę sprzedaży jako tabelę faktów i tabelę produktów jako wymiar. W tabeli faktów potrzebny nam będzie tylko identyfikator produktu, który w tabeli produktów będzie miał swoje odzwierciedlenie. Tabela produktów będzie zawierała kolumny opisujące produkt, takie jak kategoria, podkategoria, opis, wielkość, paczka itp. Są to informacje unikatowe dla produktu i niepotrzebne jest ich powielanie w tabeli faktów, ponieważ nadmiernie zwiększa rozmiar zestawu danych. Łatwiej jest przechowywać 10 tysięcy unikatowych produktów z opisem w jednej tabeli niż ich opis w tabeli sprzedaży zawierającej np. 20 mln rekordów. Poza korzystaniem z gotowych tabel wymiarów staraj się także tworzyć własne, jeśli nie jesteś w stanie zidentyfikować ich w bazie. Weźmy za przykład kanał sprzedaży – wysyłkowa lub w punkcie. Zamiast 20mln razy definiować łańcuch 'wysyłkowa’ lepszym rozwiązaniem będzie wstawienie warunku, którego wynikiem będzie 1 jeśli sprzedaż jest wysyłkowa i 0 jeśli fizyczna (w punkcie). Podobnie do produktów, może wyglądać wykorzystanie tabeli klientów. W tabeli faktów nie powinniśmy przechowywać danych teleadresowych.

Sortowanie danych wejściowych.

Choć wydaje się to dziwne, i nie ma jednej określonej drogi, warto przesortować dane w momencie ładowania do Power BI. Wielokrotnie złapałem się na tym, że porównując kilka możliwości sortowania kolumn udało mi się istotnie zmniejszyć wielkość zestawu danych. Warto na wczesnym etapie dobrać mniejszą próbkę do testowania i sprawdzić jej wpływ na wielkość pliku pbix.

Rozdzielanie kolumn wartości na dwie kolumny całkowite.

Power BI za najmniejszy typ danych uznaje liczby całkowite. Stąd najlepiej nimi posługiwać się, w celu zmniejszania modelu danych. Co ciekawe rozbicie kolumny wartości dziesiętnych na dwie kolumny całkowite, np. sprzedaż w złotówkach i sprzedaż w groszach. W późniejszym budowaniu raportu nie będzie to problematyczne, ponieważ zamiast agregacji za pomocy funkcji SUM użyjemy:
SUMX(

FactSprzedaz,

[Sprzedaz_zl] + ([Sprzedaz_gr]/100)

).

Ostatecznie przełoży się to dodatkowo na zmniejszenie wagi naszego zestawu danych, a nie wpłynie na prędkość działania.

Przeniesienie źródeł danych do przepływów danych usługi Power BI.

Jeśli zaplanujemy nasze tabele wcześniej i zamiast importować je do modelu bezpośrednio ze źródła (np. bazy danych) przeniesiemy je do przepływów danych w usłudze Power BI możemy również zmniejszyć dodatkowo wagę naszego zestawu danych. Musimy wtedy pamiętać, że odświeżenie danych musi się odbyć najpierw na etapie przepływów, a następnie na etapie zestawu, zamiast tylko i wyłącznie na etapie zestawu.

Wyłączenie automatycznej daty/godziny w programie Power BI.

Power BI desktop na etapie ładowania danych automatycznie tworzy w tle tabele dat i godzin dla wszystkich tabel zawierających kolumnę daty. Dzięki temu nasza automatycznie możemy korzystać z hierarchii dat. Dobrą praktyką jednak jest wyłączenie tej funkcji gdyż znacząco potrafi to wpłynąć na wielkość zestawu danych (czasem nawet 20%) i utworzenie tabeli kalendarzowej, która zostanie związana relacją z tabelą faktów.

Unikaj tworzenia kolumn kalkulowanych w DAX.

Kolumny kalkulowane w DAX potrafią zabierać dużo pamięci, a do tego zawsze są aplikowane do pełnego zbioru. Zawsze staraj się je utworzyć czy to po stronie źródła danych, w ten sposób przeniesiesz obciążenie z aplikacji na serwer lub na etapie ładowania danych w Power Query.

Podsumowanie

Wykorzystując powyższe techniki możliwe jest zmniejszenie zestawu danych nawet o 90%, a załadowanie tabeli faktów z ilością 200mln rekordów nie waży więcej jak 400MB. Polecam stosowanie tych metod, ponieważ wpływają pozytywnie nie tylko na wielkość zestawu, ale także jego prędkość obliczania oraz zarządzanie zmianą.

Interesujący artykuł? Podaj dalej!