Modele danych i szeroko rozumiany BI jest coraz powszechniej wykorzystywany przez użytkowników Excela. Przy większych raportach staje się on standardem pracy, ponieważ tradycyjne sposoby łączenia i przekształcania danych stają się nieefektywne. Pierwszym wyznawaniem z jakim spotykamy się przy budowaniu modelu danych jest ich poprawne załadowanie. Dziś przedstawiam moich 10 zasad jak poprawnie i efektywnie ładować dane do modelu danych.

Zasada 1: Zawsze dodawaj dane do modelu danych przez PowerQuery

Budując model danych w PowerPivot możemy zasilić do niego dane na dwa sposoby. Jeden to bezpośrednio załadować dane z tabeli/zakresu, drugi to załadować dane do PowerQuery a następnie dodać je do modelu danych. Różnica między tymi sposobami jest ogromna. Pierwsza metoda odrobinę szybsza, jeśli chodzi o jej przeprowadzenie, za to skrajnie nieelastyczna. Załadowanie danych najpierw do PowerQuery daje nam ogromne możliwości. Kluczowa korzyść to bardzo szerokie spektrum modyfikacji danych jakie możemy zastosować. Dane możemy poza modyfikowaniem też filtrować, łączyć z innymi danymi czy rozbudowywać o dodatkowe kolumny.

Druga kluczowa korzyść jest taka, że w PowerQuery zawsze mamy możliwość edycji źródła danych. Jeśli postanowimy przenieść naszą tabelę z danymi do innego pliku to nie jest to żaden problem dla modelu danych. Wystarczy zmienić jeden krok w PowerQuery. Jeśli dane zamiast jednym pliku będą w kilku to też sobie z tym poradzimy. Czas na przygotowanie mechanizmu pobierającego dane z wielu plików nie jest długi. Tak więc wykorzystywanie PowerQuery do ładowania danych do modelu danych to rzecz konieczna.

Zasada 2: Odfiltruj niepotrzebne dane

Odfiltrowanie danych na etapie ich pobierania daje nam dwie korzyści. Pierwsza korzyść to po prostu uniknięcie dodatkowej pracy przy tworzeniu wykresów, tabel przestawnych czy miar obliczeniowych w których musielibyśmy stosować dodatkowe filtry, aby prezentować te dane które nas interesują. Druga korzyść jest kluczowa w sytuacji, gdy mamy do czynienia z bardzo dużymi zbiorami danych. Bardzo często tworzę raporty które zawierają miliony rekordów już po odfiltrowaniu zbędnych danych. Gdyby pozostawić wszystkie dane to plik zajmowałby sporo miejsca, a więc korzystanie z niego byłoby mało komfortowe. Z drugiej strony rośnie wtedy ryzyko, że nasz komputer nie da rady przeliczyć takiego modelu danych. Im więcej danych tym większy jest model danych, a przy odświeżaniu znacznie wzrośnie zapotrzebowanie na pamięć RAM. Jeśli nie zadbamy o optymalizację ilości danych nasz raport w pewnej chwili może stać się niemożliwy do przeliczenia.

Zasada 3: Podziel kolumny danych na 3 kategorie

Im więcej kolumn zawierają Twoje tabele tym istotniejsza jest to zasada. W moim przypadku jest to jeden z ważniejszych etapów przygotowania nowego raportu, ponieważ często pracuję na danych zawierających dziesiątki lub nawet grubo ponad 100 kolumn. Analizując strukturę danych warto podzielić sobie kolumny na trzy kategorie.

Pierwsza to kolumny kluczowe, to wszystkie dane niezbędne do wszystkich analiz, zestawień, wykresów a także przeliczeń jakie na ten moment wiemy, że chcemy zastosować w raporcie. Druga grupa to kolumny, co do których jest szansa, że mogą się przydać, ale jeszcze nie mamy pewności czy na pewno. Warto je załadować do modelu danych, bo w trakcie prac nad raportem może znajdziemy dla nich zastosowanie. Zapotrzebowanie na te dane może się też pojawić, gdy nasz raport zaczniemy udostępniać innym osobom. Wtedy często pojawiają się prośby o dodatkowe tabele czy wykresy dotyczące tych danych, więc warto mieć już je pod ręką. Trzecia grupa do wszelkie kolumny, których na pewno nie potrzebujemy. Te oczywiście usuwamy w PowerQuery, żeby nie zaśmiecać naszego modelu danych. Po pewnym czasie kolumny z drugiej kategorii powinno się przyporządkować do pierwszej lub trzeciej, tak aby ostatecznie pozbyć się niepotrzebnych danych.

Zasada 4: Uporządkuj dane przed załadowaniem

Dane w tabelach w modelu danych powinny być uporządkowane, aby ułatwić sobie pracę na samym modelu danych. Porządkowanie powinno odbyć się w dwóch wymiarach. Pierwszy to posortowanie danych. Jeśli będziemy przeglądać dane bezpośrednio w modelu danych to może warto posortować je według daty, id klienta czy id produktu w zależności co najczęściej będzie nas interesowało. Zazwyczaj najlepsze jest sortowanie według daty. Drugi wymiar to kolejność kolumn. Należy ją ustalić już przed pierwszym załadowaniem danych. Kolejność kolumn może być alfabetyczna lub tematyczna. Moim zdaniem lepiej poukładać kolumny dotyczące tej samej kategorii obok siebie, bo nie zawsze pamiętamy jak dokładnie nazwaliśmy kolumnę. Tak więc lepiej kolumny dotyczące produktu trzymać obok siebie, potem kolumny dotyczące klienta, transakcji itd. To znacznie ułatwi poruszanie się pod danych. Możemy też grupować kolumny, ale to obecnie tylko w BI Desktop.

Zasada 5: Dane modyfikuj przed załadowaniem ich do modelu danych

Dane ładowane do modelu danych można zmodyfikować w dwóch miejscach. Albo modyfikując dane pierwotne albo po zaciągnięciu ich do PowerQuery. Oczywiście pierwszej sytuacji należy unikać, z tego względu, że tracimy pierwotne dane oraz po otrzymaniu nowych danych musielibyśmy zmiany wprowadzać na nowo. Wszelkie modyfikacje należy robić w PowerQuery, ponieważ tam w bardzo przejrzysty sposób widzimy dosłownie krok po kroku jak przekształcamy dane. Często te kroki trzeba zmieniać zanim otrzymamy oczekiwany rezultat, a zmiany te swobodnie wprowadzimy w PowerQuery, ponieważ nie ingeruje ono w żaden sposób w dane pierwotne. Ponadto PowerQuery ma wiele unikatowych rozwiązań, które znacznie upraszczają przekształcenia danych, a dodatkowo przeprowadzane jest to bardzo efektywnie. Trzeba tylko nabrać trochę doświadczenia, aby wiedzieć które modyfikacje wprowadzić i jak je zastosować.

Zasada 6: Utwórz połączenie z danymi bez utrzymywania ich w bezpośrednio w pliku

Jeśli nasz raport opieramy o model danych to prawie zawsze nie ma sensu przechowywanie danych bezpośrednio w pliku. Takie działanie tylko niepotrzebnie obciąża plik oraz spowalnia proces odświeżania raportu. Ponadto użytkownik zawsze może wyświetlić sobie interesujące go dane wklikując się odpowiednio w tabelę przestawną. Oczywiście pod warunkiem, że zadbaliśmy o to by istniała taka tabela do przeglądania danych. Dodając dane do modelu danych wybieramy opcję tylko połączenia jak widać to na obrazku poniżej.

Zasada 7: Sprawdź czy nie ładujesz zduplikowanych danych

Pisałem już o odfiltrowaniu niepotrzebnych danych oraz o usunięciu zbędnych kolumn. Po tych czynnościach często zapomina się, że w ich efekcie mamy część danych zduplikowanych. Na przykład pracujemy na bazie klientów, usunęliśmy kolumnę z datą modyfikacji danych klienta a każda zmiana tworzyła nowy rekord. W ten sposób mamy dużo danych powielonych a dodatkowo trudno określić potem, który zestaw informacji o kliencie jest aktualny. Dlatego najpierw trzeba było posortować dane od najnowszych, a następnie usunąć duplikaty po ID klienta. Podobnie może być, jeśli usuwamy kolumnę zawierającą zestaw danych szczegółowych do elementu jaki analizujemy np. transakcji sprzedaży. Dane które chcemy wykorzystać w modelu danych powinniśmy sprowadzić do zestawu unikatowych rekordów.

Zasada 8: Uwzględnij czy źródło danych może się zmieniać

Większość tworzonych raportów w założeniu będzie cyklicznie odświeżane. Kolejne zestawy danych możemy otrzymywać w różny sposób. Może to być plik tylko z nowymi danymi, może to być nowy plik ze wszystkimi danymi, ale inną nazwą, plik, który ma dane w innej liczbie arkuszy albo arkuszu z inną nazwą. Jeżeli wiemy, że tak może zmieniać się źródło danych warto zadbać o poprawne załadowanie danych do PowerQuery niezależnie od nazw czy ilości plików. W przeciwnym razie albo będziemy musieli za każdym razem edytować proces pobierania danych albo edytować pliki źródłowe.

Zasada 9: Nie wykonuj dużych przeliczeń danych poza modelem danych

Poszczególne narzędzia pakietu Office są optymalizowane pod zadania, pod które były tworzone. Dlatego poszczególne etapy pracy nad modelem danych trzeba realizować w odpowiednich narzędziach. Tak więc jeżeli PowerQuery służy do pobierania i modyfikowania danych to tam je modyfikujemy, ale nie wykonujemy tam żadnych skomplikowanych obliczeń na danych. Nie bez powodu wprowadzono tam pod przyciskami tylko podstawowe działania matematyczne. Proste obliczenia jak najbardziej można tam wykonać, ale bardziej skomplikowane działania zostawmy dla modelu danych. To on jest stworzony do tego, aby za pomocą miar i kolumn obliczeniowych w najszybszy sposób przeliczać ogromne zbiory danych. Podobnie nie ma sensu dodawać kolumny z formułami w danych źródłowych, skoro możemy dodać miarę w modelu danych, która wyliczy wszystkie wartości w mgnieniu oka.

Zasada 10: Ogranicz liczbę tabel i relacji do minimum

Z problemem tym spotykają się osoby tworzące duże modele danych, które wymagają całego zestawu tabel słownikowych oraz tabel pomocniczych z danymi. Używanie słowników oczywiście jest zalecane, ale czasami niepotrzebnie się je rozdrabnia. Dla przykładu do ID produktu mamy dodatkowe tabele z przypisaniem segmentu, kategorii, podkategorii i zestawu. Aby zoptymalizować model możemy te cztery tabele słownikowe połączyć w jedną większą tabelę, która będzie zawierała wszystkie te informacje dla danego produktu. Nie dość, że uprości to sam model to też przełoży się na dużo większy komfort pracy przy tworzeniu tabel przestawnych i miar.

W innym przypadku, gdy mamy tabelę słownikową, która zawiera tylko jedną kolumnę z informacjami (np. do ID produktu przypisuje kategorię) możemy tą informację przenieść do głównej tabeli. Można to zrobić na dwa sposoby. Pierwszy to odpowiednie scalenie tabel w PowerQuery i nie importowanie tabeli słownikowej do modelu danych. Drugie rozwiązanie to odwołanie się w głównej tabeli do tabeli słownikowej za pomocą formuły RELATED() i ukrycie tabeli słownikowej. Od strony pracy z tabelami przestawnymi itp. efekt jest ten sam, natomiast większa przejrzystość modelu danych zachowana jest przy pierwszym rozwiązaniu.

Jak ładować dane do modelu danych – podsumowanie

Przedstawione zasady może nie wyczerpują tematu, ale na pewno trzeba je brać pod uwagę ładując dane do modelu danych. To uniwersalne zasady wynikające przede wszystkim z doświadczenia z pracy nad różnymi strukturami danych. To właśnie etap odpowiedniego załadowania danych do modelu danych zazwyczaj zajmuje najwięcej danych. Później utworzenie odpowiednich miar, tabel i wykresów zazwyczaj jest znacznie prostsze i o wiele mniej czasochłonne. Trzymanie się tych zasad ustrzega nas przed problemami, które prędzej czy później mogą się pojawić i pochłoną czas, który można by przeznaczyć na ważniejsze rzeczy niż poprawki naszej wcześniejszej pracy.