Do czego służą dodatki Excel BI?
- Power Query (Pobieranie i Przekształcanie / Get & Transform)
- Power Pivot (Model danych)
- Power Map (Mapa 3D),
- Power View,
- Power BI.
Oto opis głównych zasad pracy z Excel BI & Power BI, które umieściłem na infografice 25 zasad pracy z Excel BI. Infografika stanowi podsumowanie szkolenia z Excel BI oraz Power BI, jest też wskazaniem, w jaki sposób podchodzimy do realizacji projektów w Excelu i Power BI.
Power Query – Zautomatyzuj przygotowanie danych
Są wg mnie 2 rewolucje w programie Excel. Pierwsza – wprowadzenie tabel przestawnych – mnie osobiście ominęła, bo zacząłem korzystać z Excela, gdy tabele przestawne były już standardem. Natomiast drugą – wprowadzeniem Power Query – jestem naprawdę podekscytowany. I widzę, jak wielkie oszczędności przynosi ten dodatek (od Excela 2016 już komponent) uczestnikom moich szkoleń z Excel BI. Jeśli nie znasz Power Query, koniecznie go odkryj:
- Excel 2010/2013 – pobierz plik instalacyjny ze strony Microsoft. Pamiętaj, że język i wersję (koniecznie spójną z Twoją wersją Excela: 32- lub 64-biotwą) wybierasz, pobierając plik instalacyjny.
- Excel 2016 – Power Query jest wbudowane w program. Zerknij na zakładkę Dane (Data) > Pobieranie i Przekształcanie (Get & Transform)
- Excel 365 – jw. Power Query całkowicie zastąpiło stare sposoby pobierania danych w interfejsie programu Excel
A oto najważniejsze zalety i zasady korzystania z Power Query:
1. Pobierz dane z plików, systemów źródłowych, a nawet z całych folderów bez pisania kodu VBA
Wstępem do pracy w Power Query jest pobranie danych. Najprostszym źródłem jest tabela Excel aktualnie otwartego pliku, ale planując automatyzację pobierania i przekształcania danych, warto korzystać ze źródeł zewnętrznych – plików (Excel, pliki tekstowe), folderów (możesz zaprojektować proces pobierania danych z konkretnej ścieżki) oraz złożonych źródeł danych – bazy danych SQL, chmury Azure, Internetu, API i wielu innych.
2. Nagraj ciąg poleceń przygotowania danych, tak jak nagrywa się makro
Wiele z obecnych w Query przekształceń dało się wcześniej robić ręcznie lub za pomocą programowania w języku Visual Basic for Application. Ale nigdy wcześniej nagrywanie ciągu poleceń nie było tak proste i dostępne dla każdego użytkownika programu Excel. Wszystkie „wyklikane” (tak, Power Query jest w 99% narzędziem „klikanym”) są zapisywane w oknie Zastosowane kroki, a na każdym etapie można wrócić, sprawdzić, zmienić kolejność, tak jak byś nagrywał makro lub film.
3. Łącz różne zapytania za pomocą poleceń Scal (łączenie po ID jak w bazie danych) i Dołącz (dane o takim samym układzie)
Nieczęsto wszystkie nasze dane są w jednym miejscu, dlatego rzadko zmieścisz się w jednym zapytaniu (ciągu poleceń) Power Query. Jest to żaden problem, ponieważ w ramach Query możesz swoje zapytania łączyć w poziomie (Scalanie, w j. ang. Merge) lub w pionie (Dołączanie, w j. ang. Append). W operacji scalania możesz dostrzec wiele podobnych elementów do funkcji WYSZUKAJ.PIONOWO (VLOOKUP), ale uważaj, bo jest to polecenie znacznie bardziej rozbudowane przez różne typy scalania. Polecenie Dołącz świetnie nadaje się do łączenia takich samych arkuszy w jeden końcowy plik. Co więcej, Power Query radzi sobie z różnicami w nagłówkach, dodając nowe kolumny.
4. Dokonaj agregacji poleceniem Grupuj
Za dużo danych? Potrzebujesz pre-agregacji? Chcesz coś policzyć wewnątrz Power Query? Na te 3 pytania odpowiedzią jest operacja Grupowanie według. Pozwala ona zmniejszyć ilość posiadanych danych tylko do wybranych pól – wymiarów (oznaczanych w polu Grupuj według) – przy zachowaniu oczekiwanych kalkulacji na miarach, takich jak suma, średnia, mediana, min, max, zlicz wiersze, zlicz unikatowe wiersze i tajemnicza operacja wszystkie wiersze.
5. Po ukończeniu zapytania kliknij Odśwież, aby jednym kliknięciem pobrać nowe dane
Magiczny przycisk „Odśwież” istnieje. Polecenie Odśwież znajdziesz, klikając na wynik zapytania, odświeżając je w oknie zapytań w Excelu lub klikając Odśwież na karcie Dane. Możesz też utworzyć taki przycisk samodzielnie. Najważniejsze jest to, że Power Query zapamiętuje zależności między zapytaniami i uruchamia je w niezbędnej kolejności. Sam proces pobierania danych nie blokuje Ci Excela – czekając na dane, możesz wykonywać dowolne operacje w Excelu.
Power Pivot – Zbuduj model danych
Drugim dodatkiem ułatwiającym tworzenie raportów w Excelu jest Power Pivot. Choć istnieje jego wersja dla Excela 2010, rekomenduję zacząć z niego korzystać dopiero od wersji 2013 (dostępny jest tylko dla wersji Pro Plus), gdy model danych stał się integralną częścią pliku Excel. Modelu danych nie należy jednak mylić z samym dodatkiem Power Pivot, który służy do jego obsługi. Co warto o nim wiedzieć?
1. Aby przyspieszyć pracę w Excelu i pracować z ponad 1 000 000 wierszy, dodaje dane do modelu danych
Dane do modelu danych można załadować na kilka sposobów, jednak tym rekomendowanym jest dodanie ich bezpośrednio z Power Query. Na szybko można oczywiście dodać dane bezpośrednio z tabeli lub z poziomu Power Pivot, ale to Power Query jest najlepszym źródłem – przy ładowaniu zapytania pojawia się checkbox, czy dodać dane do modelu danych. Jedną z korzyści pracy w Power Pivot jest zniesienie ograniczenia liczby wierszy do liczby komórek arkusza (1 048 576 wierszy). Dzięki temu dodatkowi praca ze złożonymi kalkulacjami zwykle przyspiesza sam arkusz, gdyż wszystkie kalkulacje odbywają się w modelu danych, a nie w komórkach.
2. Zastąp kostki OLAP, jeżeli w Twojej organizacji zbyt długo czekasz na ich zmiany
Wiele organizacji poświęciło setki godzin na stworzenie wielowymiarowych źródeł danych, nadających się do analizy (OLAP – OnLine Analytical Processing). Okazuje się, że tak przeprowadzone projekty zwykle są bardzo nieelastyczne i nie nadążają za zmianami w organizacjach. Przyszłością analityki jest self-service – samoobsługa w zakresie podłączania do danych i tworzenia między nimi połączeń. A najbardziej elastycznym rozwiązaniem jest to stworzone przez Ciebie. It’s not the big that eat the small, but the fast that eat the slow.
3. Pomyśl o swoich danych w kategoriach Miar (tabele faktów) i Wymiarów (tabele słownikowe)
Tabele faktów zawierają dane, które są agregowalne (suma, średnia, licznik, min, max) w kontekście wymiarów. Doskonałym przykładem tabeli faktów jest tabela sprzedaży, którą możemy analizować po wymiarach takich jak: obszary sprzedaży i sprzedawcy. Kalkulacje w sposób automatyczny będą pokazywały interesujące wartości w kontekście tego, co chcemy analizować – miary możemy więc rozumieć jako nasze KPI-e i wskaźniki, zaś wymiary jako filtry, rozbijające powyższe na szczegóły.
4. W widoku diagramu zaprojektuj relacje 1 do wielu na planie gwiazdy
Aby w łatwy sposób użytkownik rozumiał otrzymywane wyniki, organizuj modele w gwiazdy – w centrum umieszczaj tabele z faktami, a wokół nich buduj tabele wymiarów. Charakter relacji między miarami a wymiarami jest funkcyjny tzn. że jednemu elementowi ze zbioru A możemy przyporządkować dokładnie jeden element ze zbioru B. W praktyce oznacza to, że w tabeli wymiarów musi istnieć kolumna o niepowtarzających się wartościach (ID), która posłuży do budowy relacji. Jeśli Twój model zawiera wiele tabel faktów i wymiarów, które łączą się pomiędzy sobą i model wydaje się nieczytelny, zorganizuj go za pomocą perspektyw.
5. Utwórz tabelę kalendarza, aby korzystać z funkcji Time Intelligence.
Tabela kalendarza jest jedną z tabel wymiarów, która musi się pojawić, aby móc korzystać z funkcji Time Intelligence. W języku DAX istnieją różne funkcje do automatycznego tworzenia takich tabel, np. CALENDAR i CALENDARAUTO. Dobrą praktyką jest też wygenerowanie kalendarza etap wcześniej, jako zapytania w języku M. Ważne jest, aby daty były zawsze od początku do końca okresu.
Power Pivot – Oblicz miary w DAX
DAX, czyli Data Analysis Expressions, to biblioteka funkcji i operatorów, które można łączyć w celu tworzenia formuł i wyrażeń w modelu danych Power Pivot, Power BI Desktop czy usłudze Analysis Services programu Microsoft SQL Server. Oto najważniejsze zasady związane z jego wykorzystywaniem:
1. Zapoznaj się gruntownie z możliwościami języka DAX – nie jest tak prosty, jak się wydaje
Język DAX jest trudny, bo pisząc formuły trzeba uważać na relacje w modelu danych i kierunek filtrowania. Oznacza to, że najpierw musisz dobrze zrozumieć sam model zanim zaczniesz używać formuł DAX. Oczywiście istnieją uniwersalne scenariusze kalkulacji (określane przez społeczność „DAX pattern”), które pisze się niczym formułę Excel, ale należą one do mniejszości.
2. Zaprzyjaźnij się z funkcją CALCULATE
CALCULATE jest główną funkcją DAX, która zmienia kontekst filtrowania. Można jej składnię interpretować jako „kalkuluj jeżeli”, pamiętając, że następuje w niej wspomniana zmiana kontekstu. Wydaje się to proste na papierze, ale trudne, gdy piszemy formułę samodzielnie od podstaw.
3. Pamiętaj o poziomie filtrowania – Row Level i Filter Level – w kombinacji z funkcjami filtrującymi
Kontekst filtrowania w wierszu oznacza, że formuły zachowują się w podobny sposób jak w Excelu – operacja jest wykonywana w kontekście wiersza, wynik jest zwracany w tabeli i nie podlega zmianie. Dzięki temu wygląda jak dane pierwotne. Kontekst filtru oznacza, że wyniki funkcji kalkulujących zmieniają się w zależności od tego, co wybiera użytkownik z tabeli wymiarów. Dodając do tego punkt 2 – miara może być zaprojektowana tak, żeby kontekst wyboru zmieniał się w ramach kalkulacji. Może być np. nadpisywany przez wybrane funkcje filtrujące (np. ALL, FILTER, ALLSELECTED).
4. Uprość kalkulacje używając hierarchii oraz funkcjonalności Time Intelligence (np. YTD, QTD, MTD)
Organizuj atrybuty wymiarów w hierarchie, by usystematyzować zależności pomiędzy nimi i ułatwić analizę w jej kontekście. Mając tabelę w widoku relacji wystarczy utworzyć nową hierarchię (żółta ikona w prawym górnym rogu tabeli). Wymiar czasu w postaci tabeli „Kalendarz” jest typowym przykładem hierarchii, która określa zależności pomiędzy atrybutami (np. Rok > Kwartał > Miesiąc) i pomaga w sposób usystematyzowany analizować dane, do których się odnosi. Im bardziej złożony kalendarz (np. niestandardowy rok finansowy, podział roku na własne „periody”), tym bardziej jego tabela z hierarchią będzie nam potrzebna. To samo działanie w Excelu wymaga nie lada gimnastyki.
5. Sprawdź działanie w tabeli przestawnej
Choć DAX dostępny jest w wielu narzędziach, jego naukę najłatwiej zacząć w programie Excel z dodatkiem Power Pivot. Mamy tam możliwość wstawiania tabel przestawnych i testowania działania miar oraz filtrowania modelu danych ręcznie w celu sprawdzenia poprawności działania formuły. Zanim więc zbudujesz skomplikowany model w innych narzędziach, sprawdź jego działanie na poziomie Excela.
Przygotuj raport Excel
Idąc ścieżką Power Query > Power Pivot, potrzebujemy miejsca w Excelu, gdzie powstanie nasz raport. Do wyboru mamy Power View, tabelę przestawną czy Power Map. Co wybrać?
1. Zapomnij o dodatku Power View – wykorzystuj go tylko do najprostszych raportów
Microsoft zrozumiał ważność interaktywnej analizy i wizualizacji danych w 2013 r., gdy w najwyższej wersji programu Excel – Professional Plus – udostępnił dodatek Power View. Choć kierunek działania był słuszny, to już wykonanie dodatku w technologii Silverlight pozostawia wiele do życzenia. Dodatek poległ na najprostszych pytaniach użytkowników:
· Jak zmienić kolory na zgodne z wymaganiami?
· Jak dopasować osie wykresu?
· Jak wstawić inny wykres?
Dlatego nie polecam tworzenia raportów w tym dodatku. Sam Microsoft chyba też, bo w kolejnych wersjach Excela dodatek jest konsekwentnie ukrywany ze wstążki.
2. W ramach Excela wykorzystaj tabele i wykresy przestawne wstawiane bezpośrednio z zapytania lub modelu danych
Jeśli nie Power View to co? Zostają nam raporty oparte o tabele przestawne wstawiane na modelu danych (utworzonym w dodatku Power Pivot) lub wstawiane bezpośrednio z zapytania nagranego w Power Query. Ten drugi sposób polecany jest szczególnie tym, którzy mają Excela bez Power Pivota. Widzę tu taki problem, że Excel nie pozwala utworzyć wszystkich typów wykresów (np. punktowego czy wszystkich nowych wykresów: histogramu, kaskadowego, skrzynka-wąsy itd.) jako wykresów przestawnych. Trzeba się nieźle nagimnastykować, żeby uzyskać dokładnie taki efekt graficzny, jaki jest potrzebny.
3. Zbuduj interakcje dzięki filtrom – fragmentatorom (slicers) i osi czasu
Dobry raport pozwala odpowiedzieć na pytania podstawowe i pomocnicze, które dopiero się mogą nasunąć w toku analizy. Żeby to umożliwić potrzebujemy oddać odbiorcy raport, który będzie interaktywny. Najprostszym sposobem utworzenia interakcji w Excelu są filtry tabel i tabel przestawnych – fragmentatory. Ich angielska nazwa – slicers – pochodzi od czasownik to slice, oznaczającego krojenie na kawałki (chleba czy pizzy). W Excelu mamy możliwość wstawienia zwykłego fragmentatora oraz osi czasu (od Excela 2013), która pozwala na filtrowanie lat, kwartałów, miesięcy i dni
4. Utwórz mapy w dodatku Mapa 3-W / Power Map lub w dodatku Mapa Excel od SkuteczneRaporty.pl (z wykorzystaniem fragmentatorów)
Mając program Excel 2013 lub wyższy, mamy dostęp do dodatku z mapami dostarczonego przez Microsoft. Mowa o Power Map, który w Excelu 2013 trzeba doinstalować, a w 2016 jest wbudowany pod nazwą Mapa 3-W (3D Map). Dodatek pozwala na tworzenie analiz na mapach automatycznie pobieranych z Internetu. Do dyspozycji mamy: trójwymiarowe kolumny, wykresy punktowe i kołowe, mapę cieplną czy kartogram. O ile wykorzystywanie punktów (np. do zlokalizowania adresów) jest wskazane, o tyle kartogram ma liczne braki już na poziomie powiatów, nie mówiąc o kodach pocztowych. Dlatego osobiście wolę pracę z moimi mapami, które można podłączyć do modelu danych, fragmentatorów i dashboardów w arkuszach – mapy takie, w podziale na województwa, powiaty, gminy i kody, znajdziesz na mojej stronie.
5. Unikaj automatycznego geokodowania – przygotuj współrzędne w tabeli źródłowej
Geokodowanie to proces przypisania współrzędnych geograficznych (lub innych właściwości) do adresu (np. klienta). Automatyczne geokodowanie to proces realizowany domyślnie w dodatku Power Map. Niestety dla danych o Polsce proces ten daje często brak wyników (nie zobaczysz Klienta w ogóle) lub inne wyniki niż w rzeczywistości (zobaczysz Klienta nie tu gdzie trzeba). Dlatego rekomenduję przygotować współrzędne przed Power Map, a dopiero w Power Map je sprawdzić i analizować – mamy tam pola Długość i Szerokość Geograficzna.
Przejdź na Power BI
1. Pobierz i poznaj bezpłatną aplikację Power BI Desktop
Proces tworzenia raportu w programie Excel jest wg mnie niepełny z uwagi na braki w obszarze wizualizacji danych i projektowania interaktywnych dashboardów (patrz: nieudany eksperyment z Power View) oraz dzielenia się raportami (wysyłanie plików zamiast dostęp przez www i aplikację mobilną). W 2015 r. Microsoft postanowił to zmienić, rezygnując z rozbudowy Excela na rzecz oddzielnej aplikacji Power BI. Umieszczono w niej jednak wspomniane już: Power Query, model danych i DAX (czyli element Power Pivot) i ulepszone wielokrotnie możliwości projektowania raportów (czyli poprawiony Power View). Co więcej, możesz zacząć na nim pracę zupełnie za darmo już dziś, pobierając Power BI Desktop za darmo.
2. Zaprojektuj raport z wykorzystaniem interakcji wyróżniania i filtrowania
Z przejścia na Power BI na pewno będzie zadowolony odbiorca Twojego raportu. Oprócz wielu nowych wykresów, otrzyma także szereg interakcji, takich jak:
- Drążenie – po kliknięciu wybranego elementu wykresu (np. Kategoria produktu = meble), schodzimy niżej na poziom podkategorii (meble biurowe, stoły, krzesła itd.)
- Filtrowanie pomiędzy wykresami – kliknięcie elementu jednego wykresu powoduje odfiltrowanie innych wykresów (tego w Excelu nie było)
- Filtrowanie z wyróżnieniem – jak wyżej, tylko że równolegle widzimy, ile wynosiła wartość przed odfiltrowaniem
- Przejście do szczegółów – mając 2 raporty możesz zaplanować między nimi przejście dla wybranego elementu jako szczegółu – np. przejście z raportu ogólnego na raport szczegółowy po wybraniu konkretnego klienta
- Same filtry są też znacznie bardziej funkcjonalne niż te w Excelu – możemy tworzyć suwaki, listy wyboru czy zaawansowane filtry dat
Trudno się o tym pisze, dlatego koniecznie zobacz to w akcji
3. Opublikuj raport na PowerBI.com i podziel się nim z odbiorcami przez stronę i aplikację mobilną
Dodając do tego możliwość publikowania raportów na witrynie PowerBI.com i oglądania ich przez aplikację mobilną, otrzymujemy wreszcie produkt kompletny do tworzenia raportów. Od opublikowania raportu dzielą Cię 2 kroki: założenie konta i kliknięcie Publikuj. Po przejściu do witryny możesz w kilka sekund podzielić się raportem z odbiorcami czy zadawać pytania w języku naturalnym (np. Drogi raporcie, pokaz mi sprzedaż za wczoraj).
4. Załóż konto Pro
Konto Pro kosztuje 10$ za użytkownika / miesiąc i jest potrzebne z wielu powodów. Oto kilka z nich:
- Dzielenie się raportami
- Row Level Security (np. szef regionu Północ widzi tylko dane swojego regionu, ale już nie dane dla całego kraju)
- Współpraca z innymi użytkownikami
- Automatyczne odświeżanie danych (w tym danych live) – szczegóły w pkt. 5
Ważne, aby pamiętać, że konto Pro musi mieć każdy odbiorca. Jeśli pracujesz w dużej firmie, sprawdź, czy Power BI Pro nie jest już częścią innych aplikacji, np. niektóre plany Office365 mają to konto w pakiecie.
5. Zainstaluj Power BI Gateway, aby w pełni automatycznie odświeżać raport, np. o 7 rano
Jeśli Twoje źródła danych aktualizują się automatycznie (np. bazy danych), nic nie stoi na przeszkodzie, abyś w pełni zautomatyzował aktualizację Twojego raportu w Power BI. Będziesz do tego potrzebował pomostu między systemem a PowerBI.com. Tym pomostem jest aplikacja Power BI Gateway, która jest dostępna w dwóch wersjach – Personal (Twój login służy do pobrania danych) i korporacyjnej.