Tryby pobierania danych czyli Power BI-owy ból głowy

Na pytanie z czym kojarzy się Power BI, większość użytkowników z pewnością odpowie, że największą jego zaletą są imponujące możliwości wizualizacji danych czy tworzenie dynamicznych pulpitów nawigacyjnych. I trudno się z tym nie zgodzić ponieważ rzeczywiście intuicyjność i potęga możliwości tego narzędzia jest ogromna. Bardziej świadomi użytkownicy Power BI mogliby odpowiedzieć także, że kojarzy im się z zaawansowanymi wyliczeniami, analizami czy bardzo użytecznym językiem DAX. I po raz kolejny nie można się z tym nie zgodzić ponieważ Microsoft wyposażył nas jako użytkowników, w naprawdę zacne narzędzie, w którym ogranicza nas tylko wyobraźnia a próg wejścia wcale nie jest aż taki wysoki.

Zanim jednak zaczniesz tworzyć efektowne raporty, musisz pobrać dane do modelu a więc Twoim zadaniem będzie połączenie się do odpowiedniego źródła danych. I tutaj mogą i powinny (!) pojawić się pierwsze pytania i wątpliwości związane z różnymi trybami połączeń do źródeł danych. W Power BI występują trzy dostępne tryby. Są to tryb Import, Direct Query oraz Live Connection zwany także Połączeniem na żywo. Znajomość zalet i wad tych rozwiązań jest kluczowa dla twojego raportu i jego funkcjonalności w przyszłości ponieważ nie wszystko będzie można wykonać w konkretnym trybie połączenia. Niestety błędy popełnione na początku będą miały swoje konsekwencje podczas dalszej pracy i nie zawsze będą odwracalne.

Pomyślcie sami nawiązując do codziennych bardziej przyziemnych spraw, że gdy robicie remont salonu i na tym etapie nie zadbacie o kanał maskujący kable w ścianie, gdzie za kilka chwil tą samą ścianę ozdobi piękny wielki telewizor to efekt będzie mało zadowalający przez plączące się czarne kable zasilające. Czy w tym momencie możecie to zmienić? Z pewnością tak ale jest to bardzo kłopotliwe i nie odbędzie się bez ponownego rozkucia ściany prawda? A wszystko przez to, że na początkowym etapie albo nie pomyśleliśmy o wszystkim, albo nie przewidzieliśmy potrzeb.

Kolejnym przykładem obrazującym podobny efekt będzie składanie kostki Rubika. Aby szybko i prawidłowo ją złożyć wymagana jest konsekwencja wykonywanych ruchów oraz znajomość sekwencji pozwalających to zrobić w sposób właściwy. Jeżeli na pierwszym etapie popełnimy błąd to z każdym kolejnym krokiem będzie już tylko to bardziej problematyczne. W takim wypadku może czasami lepszym pomysłem będzie zacząć składać kostkę od nowa niż szukać błędów?

Idąc tym tropem wróćmy do głównych aktorów tego artykułu, czyli trybów pobierania danych. Gdy podczas pierwszego kroku, czyli nawiązania połączenia ze źródłem danych popełnimy błędy lub nie przewidzimy naszych dalszych działań może to być początek naszej „złej passy” w tym raporcie. Jak wspomniałem wcześniej, każdy tryb ma swoje za i przeciw i to od Ciebie zależy co, gdzie i jak potrzebujesz zrobić. Poniżej zapoznajmy się z tymi elementami aby ten przysłowiowy Power BI-owy ból głowy Ciebie nie dopadł, a Twoje raporty były w pełni funkcjonalne.

Zanim omówimy sobie bardziej szczegółowo tematykę pobierania danych, zastanówmy się nad kilkoma pytaniami, które warto sobie postawić zanim zaczniemy projektować i modelować raport. To takie troszkę złote myśli, które mogą pomóc podjąć często niełatwe decyzje:

• W jaki sposób i jak często dane zaimportowane do Power BI będą aktualizowane?
• Czy dane będą wyświetlane i porównywane w różnych okresach na pulpitach w czasie rzeczywistym?
• Jak duży jest zbiór danych i jak szybko przyrasta o nowe wiersze danych?
• Czy użytkownicy powinni mieć możliwość ingerencji w źródło danych czy potrzebujesz uzyskać „zarządzanie jednym źródłem prawdy” dla wszystkich?

Jeżeli chociaż nad jednym pytaniem zastanowiłeś się przez chwilkę to już jest powód do głębszej refleksji aby w późniejszym etapie nie wracać do błędów popełnionych na początku jak w tym przykładzie z kostką Rubika.

A więc przechodząc do meritum w Power BI Desktop masz do wyboru ponad 170 różnych konektorów począwszy od plików o strukturze płaskiej (jak np. pliki Excel czy csv) poprzez silniki baz danych (SQL Serwer, Oracle itp.), rozwiązania Big Data czy źródła w chmurze. Wszystkie te łączniki umożliwiają pobranie danych do modelu ale nie zawsze wszystkie tryby będą dostępne.

Na ogólnym poziomie zrozumienia można powiedzieć, że w trybie importu dane są importowane do modelu a więc Power BI fizycznie pobierze dane z wybranego źródła i przechowa je w swoim magazynie danych w pamięci. Jest to najbardziej elastyczny sposób przetwarzania danych. W trybie Import nie ma ograniczeń związanych z modelowaniem, relacjami czy tworzeniem kolumn i miar w DAX. Nie pozwala jednak na odświeżanie danych, w trybie rzeczywistym. Po zaimportowaniu dane pozostaną statyczne do momentu kolejnego odświeżenia, które może być zainicjonowane ręcznie lub automatycznie zgodnie z zaplanowanym harmonogramem odświeżania, w modelu semantycznym. Konieczne do tego będzie zainstalowanie aplikacji Data Gateway lub w polskiej wersji językowej bramy danych odpowiedzialnej za ładowanie danych do usługi Power BI.

Niewątpliwymi zaletami tego trybu jest brak ograniczeń modelowania danych i tworzenia nowych kolumn obliczanych w modelu co nie będzie możliwe, w innych trybach. Tryb ten będzie idealny przy pracy z danymi lokalnymi lub jednorazowymi zestawami danych. Wadą będzie z pewnością aktualizacja danych oraz wydajność przy dużych plikach, gdzie mogą zdecydowanie spowalniać model. Aspektem, który też warto wziąć pod uwagę są ograniczone możliwości współdzielenia gdy raport ma być używany przez wiele osób z zespołu.

Tryb Direct Query w Power BI umożliwia bezpośredni dostęp do danych w czasie rzeczywistym, bez ich importowania do modelu Power BI. Dane pozostają w źródle (np. bazie danych), a zapytania są wykonywane dynamicznie przy każdej interakcji z raportem.  Na tym etapie warto wziąć pod uwagę obciążenie serwera źródła danych. Może to wymagać zwiększenia jego zasobów, co generuje dodatkowe koszty. Niestety nie wszystkie funkcjonalności będą także dostępne dla użytkownika.

Poniżej obrazy pokazujące pobranie danych za pomocą instrukcji SQL z bazy SQL Serwer w trybie Import oraz Direct Query. Zostaną pobrane dane dla przefiltrowanych dwóch regionów oraz kolumny wyliczeniowe z kosztami i wartościami sprzedaży. Przy pierwszym spotkaniu można nie zauważyć wielkiej różnicy ale gdy zaczniemy pracować z danymi będą widoczne pewne ograniczenia.

Pierwszym ograniczeniem będzie brak wykorzystania wszystkich funkcjonalności Power Query. Mówiąc prostym językiem będzie można używać tylko funkcji, które są kompatybilne z T-SQL. Transformacje, które nie mogą być przetłumaczone na zapytanie SQL będą niedostępne dla użytkownika w trybie Direct Query więc zostanie wymuszone przełączenie się do trybu importu.

Poniżej prosty przykład obrazujący takie zdarzenie. Potrzebuję przekształcić kolumnę tekstową na wielkie litery. W trybie Import bez problemu mogę to zrobić ponieważ jak już wiemy dane są zaimportowane do Power BI, natomiast w Direct Query operacja taka nie będzie możliwa jak poniżej:

Takich ograniczeń będzie zdecydowanie więcej i ten artykuł zdecydowanie nie wyczerpie tego tematu więc zachęcam do bardziej szczegółowej ich analizy. Najważniejszymi będą z pewnością wspomniane ograniczenia transformacji w Power Query oraz fakt, że niektóre funkcje DAX, szczególnie te bardziej złożone, nie są obsługiwane w trybie Direct Query.

I na tym etapie już powinniśmy zobaczyć pewne niedogodności dla użytkownika. Jeżeli bardzo by mi zależało na wykonaniu takich operacji byłbym zmuszony wykonać je na źródle danych czyli w bazie SQL jeżeli oczywiście mam takie uprawnienia itp.

Stosując terminologię sportową można by powiedzieć, że w meczu Import vs Direct Query jest wynik 1:0.

Po załadowaniu danych do Power BI Desktop od razu widać kolejne ograniczenia, jak chociażby brak możliwości tworzenia kolumn obliczeniowych w DAX. Takie ograniczenie może być dość bolesne dla użytkownika, który potrzebuje tworzyć zaawansowane analizy za pomocą DAX.

W trybie Import nie ma żadnych przeciwskazań aby wykonać kolejne obliczenie, natomiast w Direct Query ponownie nie można tego zrobić. Jeżeli potrzebowałbym utworzyć nową kolumnę musiałbym udać się do źródła czyli bazy SQL. Mógłby także w obydwu przypadkach utworzyć miarę co często jest bardzo dobrym pomysłem ale są określone przypadki gdzie potrzebuję wymusić iterację na tabeli więc taka kolumna jest mi niezbędna.

Wygląda na to, że Import prowadzi w meczu już 2:0.

Jeżeli na tym etapie użytkownik zdecyduje się przełączyć się do trybu import bo ograniczenia są nieakceptowalne może to zrobić ale jest to operacja nieodwracalna.

Ale mecz się jeszcze nie skończył więc dajmy szansę Direct Query na odpowiedzenie.

Tryb ten jest idealny jeżeli potrzebujemy mieć zawsze najaktualniejsze dane w trybie rzeczywistym. Poniżej przykład jak można odświeżać dane w raporcie aby mieć je zawsze aktualne.

Dla zobrazowania tego przykładu przygotowałem dwie identyczne tabele (jedna z trybu Import, druga z tryby Direct Query). We właściwościach strony raportu można teraz włączyć odświeżanie strony gdy zostaną wykryte zmiany w źródle lub zaplanować odświeżanie według potrzeb.

Zaplanuję odświeżanie co 10 sekund (z punktu biznesowego tak częste odświeżanie nie jest przydatne ale na potrzeby tego przykładu będzie wygodniejsze).

Zwróćcie uwagę, że tabela, która powstała na źródle Direct Query zawsze odświeża się co 10 sekund i będzie zawsze przechowywać aktualne dane. Jeżeli taki efekt chciałbym uzyskać w trybie Import musiałbym odświeżać dane ręcznie za każdym razem.

To chyba oznacza, że Direct Query strzeliło bramkę kontaktową i wynik na wirtualnej tablicy jest 2:1.

Analizując dalej wady i zalety tych dwóch trybów, weźmy na tapetę wydajność naszych rozwiązań.  Do jej zbadania włączyłem funkcjonalność Power BI – analizator wydajności i rozpocząłem nagrywanie. Widać, że zapytania oparte na Direct Query potrafi się wykonywać wiele razy dłużej a spowodowane to jest bezpośrednim połączeniem do bazy danych. Za każdym razem gdy odświeżamy zapytanie zostaje wysłane ponowne zapytanie więc można też czekać za odpowiedzią chociażby przez dostępność bazy itp. Efekt ten jest widoczny poniżej gdzie w trybie Direct Query najwięcej kosztuję zapytanie bezpośrednie do bazy, którego w ogóle nie ma w trybie importu bo przecież dane są zaimportowane do pliku.

Czyli można z tego wyciągnąć wniosek, że wydajność na pewno też nie będzie najlepsza w trybie Direct Query. Ale z kolei gdy mamy ogromne ilości danych i nie potrzebujemy ich bardzo szczegółów analizować za pomocą miar ten tryb będzie dla nas idealny. W tej rozgrywce przypisuję po jednej bramce dla każdej drużyny.

Czyli końcowy wynik meczu to 3:2 na korzyść trybu Import. Ale to wszystko zależy co użytkownik potrzebuję więc wynik ten dla każdego może być inny.

W komentarzu pod tym artykułem podzielcie się swoim wynikiem tego meczu

Jest dostępny jeszcze trzeci tryb pobierania danych czyli Live Connection.

Jego niewątpliwymi zaletami są możliwości bezpośredniego połączenia z modelem danych w chmurze (np. z modelem semantycznym w usłudze Power BI), centralne zarządzanie modelem czy współdzielenie modelu. Live Connection warto użyć gdy dane muszą być zawsze aktualne lub gdy model danych powinien być zarządzany centralnie i wykorzystany w wielu raportach. W takiej sytuacji warto przygotować model danych, na którym będzie pracowało wielu użytkowników w swoich raportach. Nie wolno też zapomnieć, że w tym trybie jest także całkiem sporo ograniczeń. Przede wszystkim nie jest dostępne w ogóle Power Query (wszystkie przekształcenia zostały już wykonane przez twórcę raportu) oraz nie można modelować danych w Power BI czy tworzyć nowych kolumn (widok tabeli, w tym trybie nie jest dostępny) – jak poniżej. Dostępne są za to wszystkie miary, które zostały utworzone przez twórcę tego datasetu co może być ogromnym plusem dla mniej wtajemniczonych w DAX użytkowników.

Tryb Live Connection jest idealny dla dużych organizacji, które zarządzają danymi w centralnych modelach i wymagają spójności w raportowaniu.

Gdy już mamy świadomość jak to wszystko działa wystarczy podjąć odpowiednią decyzję😊

Ale właśnie taka decyzja może przyprawić nas o ten przysłowiowy ból głowy bo wszystko zależy od tego gdzie znajdują się twoje dane i w jaki sposób będą wykorzystywane w przyszłości. Odpowiedź na te pytania wcale nie jest taka oczywista i wybór będzie zależał od indywidualnych potrzeb i wymagań użytkowników raportu. Aby ułatwić Wam troszeczkę podjęcie decyzji poniżej w tabelce prezentuję najważniejsze aspekty konkretnych trybów.

Podsumowując, które rozwiązanie jest najlepsze i które powinniście wybrać zdecydujcie sami. Spróbujcie rozważyć za i przeciw wszystkich możliwości i zagrożeń. Rozegrajcie w swojej głowie mecz pomiędzy trybami i nie zapomnijcie pochwalić się jego wynikiem w komentarzu😊

Powodzenia😊