Model danych jest kombinacją kilku tabel połączonych relacjami. Choć moglibyśmy mówić o modelu danych składającym się 1 tabeli, dodatek Excel Power Pivot i aplikacja Power BI zachęcają nas do rozbicia danych na tabele faktów i wymiarów, połączonych ciągłą linią. Jakie zalety przynosi budowa modelu danych w tych narzędziach i dlaczego wykorzystanie Power Pivota w Excelu jest tak ważne? Oto 11 korzyści.

1. Integracja różnych źródeł danych dzięki Power Query

Gdy w 2010 r. pojawiła się pierwsza wersja dodatku Power Pivot, w Excelu nie było jeszcze dodatku Power Query. W związku z tym dane z programu Excel można było ładować tylko ze środka arkusza (co czyniło pliki dużo cięższe), a pobieranie danych z baz danych pozbawione było elementu transformacji danych. Od 2013 r. jedną z opcji ładowania danych z Power Query jest model danych Excel, czyli Power Pivot. Na szkoleniach tłumaczę, że model jest skrytką (pamięcią) na nasze dane, podczas gdy Power Pivot jest kluczem do tej skrytki. Tak wygląda Power Query w interfejsie Excela 365 oraz okno kończenia zapytania z checkboxem Dodaj dane do modelu danych:

Analogicznie jest w Power BI, gdzie klikając Zamknij i zastosuj, automatycznie tworzymy model danych.

Dzięki Power Query nasz model danych może być zbudowany z wielu różnorodnych źródeł:

• plików Excel czy CSV
• folderów z plikami
• tabel bezpośrednio z bazy danych SQL
• źródeł internetowych

Power Query po stronie Power BI ma pod tym kątem dużo więcej connectorów, choć wszystkie główne znajdziesz też w Excelu.

2. Zmniejszenie rozmiaru plików

Często spotykam użytkowników programu Excel, których pliki spuchły do kilkudziesięciu MB, a w ekstremalnym przypadku przekroczyły 100 MB. Taki plik długo się otwiera, jest wolny i nie daje się wysłać mailem. Jeśli skorzystamy z modelu danych, zwykle rozmiar pliku radykalnie się zmniejsza do kilku MB.

Więcej o przykładach poczytasz w eksperymencie z ładowaniem danych.

3. Możliwość załadowania ponad 1 mln rekordów

Jeśli chcesz przygotować szczegółowy raport, który pozwoli Ci przejść od danych ogólnych (np. Sprzedaż w podziale na Kategorie i Podkategorie produktu) do danych szczegółowych (pojedyncza faktura), dane w Excelu muszą mieć wysoki poziom szczegółowości (tzw. granulację). Tradycyjny Excel pomieści jednak tylko 1 048 575 wierszy + nagłówek. Co jeśli danych jest więcej? Dzięki dodatkowi Power Pivot możemy do Excela zmieścić i do 100 mln rekordów. Choć liczba ta nie jest realnym ograniczeniem zapisanym w dokumentacji, raczej bym jej nie przekraczał. W większości scenariuszy użytkowników programu Excel i Power BI, powinno to i tak wystarczyć.

4. Poprawa wydajności działania raportów

Specjalny silnik Vertipaq (przemianowany później na xVelocity) jest wykorzystywany przez Power Pivot, aby przetwarzać skompresowany model w pamięci komputera. Dzięki temu raporty działają szybko, dużo efektywniej niż raport przygotowany w tradycyjny sposób – jako kalkulacje (formuły) w komórkach. Wydajność docenią też użytkownicy końcowi, których cierpliwość raportową szacuje się na 3 sekundy. Czy Twój raport filtruje się tak szybko?

5. Uporządkowanie danych w model gwiazdy

Budowa modelu danych, przypominającego wyglądem gwiazdę, ma kilka zalet. Jedną z nich jest oddzielenie tabel transakcyjnych (faktów) od tabel słownikowych (wymiarów). Dzięki budowie między nimi relacji i odpowiednim ich nazwaniu, każdy użytkownik wie, gdzie szukać tabel służących do kalkulacji, a gdzie tabel służących do filtrowania danych (to slice and dice – jak często pisze się w literaturze anglojęzycznej). Dzięki rozbiciu danych na duże tabele faktów i małe transakcyjne, sprawimy dodatkowo, że model będzie pracował wydajniej.

6. Tworzenie dowolnych kalkulacji w języku DAX

DAX, czyli Data Analysis Expressions, to specjalny język kalkulacji obsługujący model danych. Jest wspólny dla dodatku Power Pivot, aplikacji Power BI i SQL Server Analysis Services. Jego podstawowa znajomość jest niezbędna do tego, aby w modelu danych pisać formuły. A że pisać je trzeba, pokazuje praktyka, gdyż w tradycyjnej tabeli przestawnej, o której nagrałem 5-godzinny bezpłatny kurs, wyklikać można tylko podstawowe agregacje (np. Suma, Średnia czy Licznik). Język ten jest bardzo złożony, ale pamiętajmy, że nie każdy raport będzie wykorzystywał cały potencjał jego składni. Język DAX jest skomplikowany, gdyż ma obsłużyć każdy, nawet najbardziej wymagający scenariusz analityczny. Jeśli zdarzyło Ci się walczyć w tabeli przestawną z jakąś miarą, język DAX jest jedyną drogą. Klasycznym przykładem jest liczba unikatów, nazywana w DAX DISTINCTCOUNT:

7. Transparentność źródeł i kalkulacji

Budowa modelu danych z wykorzystaniem świata zapytań Power Query, modelu danych (Power Pivot lub Power BI) i miar języka DAX pozwala stworzyć bardzo transparenty raport. Każdy użytkownik takiego przykładowego raportu w Excelu może w kilka minut prześledzić:

• Skąd są pobierane dane?
• Jak są transformowane?
• Które zapytania stają się tabelami w modelu danych?
• Jak tabele łączą się w modelu?
• Jak wygląda wzór każdej miary?

Jako firma świadcząca usługi raportowe w obszarze BI, staramy się wszystkie raporty dostarczane klientom utrzymać właśnie w tej technologii. Dzięki temu klient otrzymuje rozwiązanie, które może samodzielnie modyfikować (oczywiście posiadając odpowiednie kompetencje).

8. Praca na pełnych danych słownikowych

Czy są jakieś Produkty, które się nie sprzedają? Dane tradycyjnie analizowane z użyciem tabel przestawnych bazują na jednej tabeli i dlatego odpowiedź na takie pytanie jest w nich trudna do osiągnięcia. Dlaczego? Tabela ze sprzedażą pokazuje właśnie transakcje (czyli produkty sprzedane). Do niesprzedanych faktur nie ma. Jednak budowa modelu danych pozwoli nam pracować na pełnych danych słownikowych (z tabel wymiarów) i pytać nasze dane w dowolnych kombinacjach.

9. Możliwość pracy na kilku tabelach transakcyjnych

Podobnie jak w punkcie 8, praca na modelu danych pozwoli nam zadawać szersze pytania. Ale tym razem nie o tabele słownikowe, a o nasze transakcje z innych tabel.

• Jak wygląda realizacja sprzedaży (tabela Sprzedaż) na tle planów sprzedażowych (tabela Plany)?
• Czy do faktury sprzedażowej (tabela Sprzedaż) otrzymaliśmy przelew (tabela Przelewy)?
• Czy klient, który kupił ubezpieczenie (tabela Sprzedaż) miał szkodę (tabela Szkody)?

I znowu: zwykła tabela przestawna ogranicza się w swojej konstrukcji do jednej płaskiej tabeli. Projektując model danych, mamy możliwość połączenia kilku tabel ze sobą, tworząc już nie gwiazdę a galaktykę. Obsługa takiego modelu nadal będzie relatywnie prosta. Jednak…

10. Jedno źródło prawdy dla całej organizacji

Jednak użytkownik musi znać model danych, żeby na nim pracować. Obsługa gotowego modelu danych różni się od pracy na zwykłej tabeli przestawnej tym, że trzeba wiedzieć, którą miarą, z której tabeli i w jakim kontekście mogę się posłużyć. Przeszkolenie aktywnych analityków w tym obszarze pozwala wszystkim osobom, szczególnie tym z różnych działów, pracować „jedną soczewką” i widzieć dane w taki sam sposób. Koniec z tworzeniem własnych wyliczeń, które powodują, że zarządzający nie wiedzą, na czym stoją. Mając model, każda miara będzie miała określony wzór, który ujednolici wyniki w ramach całej organizacji.

11. Wykorzystanie tabel i wykresów przestawnych

Model danych z miarami, analogicznie jak zapytania w Power Query, jest wydzielony i w Excelu i w Power BI. Jednak praca na modelu danych z perspektywy tworzenia raportu, sprowadza się do wykorzystania zwykłych tabel i wykresów przestawnych. A do tego mamy w Excelu i Power BI interfejs drag & drop, proste tworzenie filtrów za pomocą fragmentatorów (o czym sporo mówię w kursie Tabele przestawne Excel) oraz równie łatwe tworzenie wykresów (nazywanych w Excelu przestawnymi, a w Power BI będących w sumie głównym wyborem użytkownika). Jeśli szukasz najprostszego sposobu na interaktywny raport w Excelu, tabela przestawna jest zawsze pierwszym wyborem, nawet jeśli zbudujesz model danych w Power Pivot lub w Power BI.

Widzisz inne zalety tworzenia modelu danych w Excelu lub Power BI?

Podziel się w komentarzu.