Analiza danych: Excel jest super – ale Power BI może być jeszcze lepszy

17 marca, 2021 Tomasz Zieliński

Pierwsze kroki z Power BI

 

Nikt nie rodzi się analitykiem danych a większość ludzi dość obojętnie patrzy na kolumny cyferek. Gdy jednak dostrzeżesz u siebie skłonność do odkrywania zależności ukrytych w liczbach, będziesz potrzebować odpowiednich narzędzi. Jednym z pierwszych będzie zapewne Excel, na końcu ścieżki znajdują się języki programowania R albo Python. Czy istnieje jakiś krok pośredni? Narzędzie dla weteranów arkusza kalkulacyjnego, którzy potrzebują czegoś więcej? Tak!

Dziś przyjrzymy się aplikacji Microsoft Power BI Desktop. Rozwinięcie liter „BI” w nazwie to „business intelligence” czyli „analityka biznesowa”. Power BI potrafi być bardzo przydatny; w końcu narzędzia do analityki biznesowej oferują znacznie większe możliwości przekształcania i analizy danych, niż arkusz kalkulacyjny Excel. Power BI jest dostępny za darmo, więc każdy może wypróbować je bez zobowiązań i bez ponoszenia jakichkolwiek kosztów. Producent liczy oczywiście na to, że dzięki temu wzrośnie liczba chętnych na zakup wersji Pro, wyposażonej w funkcje pracy grupowej (nie będziemy ich dziś potrzebować).

 

Instalacja

 

Najszybciej i najwygodniej zainstalujemy PowerBI klikając w link https://aka.ms/pbidesktopstore, który przekieruje nas do Microsoft Store. Jeśli z jakichś przyczyn nie możemy z niego skorzystać, pod adresem https://aka.ms/pbiSingleInstaller znajdziemy klasyczny instalator.

 

 

Praca z narzędziem do analityki biznesowej

 

Podczas pracy z danymi typowa sekwencja działań wygląda następująco – ładujemy je do systemu, definiujemy lub korygujemy typy pól (kolumn) i ich formatowanie, następnie oczyszczamy dane ze zbędnych elementów i dopiero na tak przygotowanym zbiorze zaczynamy rzeczywistą analizę.

W niniejszym tekście przeanalizujemy logi serwisu internetowego działającego na oprogramowaniu WordPress, zapisane w formacie serwera Apache. Przykład taki został wybrany celowo – chcemy pokazać przewagę Power BI nad Excelem, w którym obróbka  owego formatu sprawiłaby dużo problemów.

Pojedynczy wiersz wczytywanego pliku wygląda następująco:

 

159.69.121.51 - - [25/Jan/2021:18:05:33 -0800] "GET /kanarek-czyli-system-poznego-ostrzegania/ HTTP/1.1" 200 22310 "-" "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 5.1; Win64; x64; Trident/5.0)"

 

Oto znaczenie kolejnych pól (więcej w dokumentacji Apache):

 

159.69.121.51 – numer IP komputera, z którego przyszło żądanie

/- -/ – dwa pola, które nie są zapisywane w istniejącej konfiguracji

[25/Jan/2021:18:05:33 -0800] – data i godzina otrzymania żądania

"GET /kanarek-czyli-system-poznego-ostrzegania/ HTTP/1.1" – żądany URL

200 – kod odpowiedzi HTTP

22310 – rozmiar wysyłanego pliku

"-" – pole referer

"Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 5.1; Win64; x64; Trident/5.0)" – pole identyfikujące przeglądarkę użytkownika

Wczytujemy dane wybierając komendę Plik → Pobierz dane → Plik tekstowy lub CSV. Widzimy okno dialogowe z następującą zawartością:

 

 

Na tym etapie podgląd planowanego importu wygląda bardzo podobnie do tego, co oferuje funkcja wczytywania danych teksowych w Excelu:

 

 

Tam jednak, gdzie Excel kończy pracę nad importem, Power BI dopiero się rozkręca. Klikamy przycisk „Przekształć dane”, na ekranie pojawia się okno Edytora Zapytań:

 

 

 

Przekształcanie danych wejściowych

 

W tym oknie dokonamy wszelkich niezbędnych przekształceń. Każdy krok staje się osobną pozycją w ramce „Zastosowane kroki”, więc możemy eksperymentować bez strachu – zawsze istnieje możliwość anulowania poprzedniego kroku.

Warto przejść na kartę Widok i zaznaczyć opcje „Jakość kolumn” oraz „Rozkład kolumn” – dzięki temu łatwiej zauważymy ewentualne usterki w przekształceniach. Aktywna opcja „Pasek formuły” pozwoli upewnić się, że wyklikana operacja rzeczywiście jest tym, o co nam chodziło.

 

 

Najpierw przeprowadzimy operacje podstawowe: usuwamy kolumny nr 2, 3 i 11 (prawy klik na nagłówku kolumny, polecenie „Usuń”) oraz łączymy kolumny 4 i 5 (zaznaczenie obu kolumn z klawiszem Shift, potem prawy przycisk myszy i polecenie „Scal kolumny”). Teraz możemy zaznaczyć kolumny 7-8 i zmienić ich typ na numeryczny (nagłówek, prawy klawisz myszy, „Zmień typ” → „Liczba dziesiętna”).

Czas na bardziej złożone przekształcenie – chcemy zmienić typ kolumny zawierającej tekstowy zapis daty i godziny żądania w taki sposób, aby była przez PowerBI traktowana jako wartość chronologiczna. Zwróćmy uwagę, że serwer WWW jest zlokalizowany w Kaliforni – potwierdza to przesunięcie czasowe +8h względem UTC.

Najpierw dwukrotnie użyjemy funkcji „Zamienianie wartości”, by usunąć najpierw lewy a potem prawy nawias kwadratowy we wspomnianej kolumnie (pierwszy krok to zamiana znaku „[“ na pustą wartość czyli w efekcie usunięcie znaku). Jeśli jednak od razu zmienimy typ kolumny na „Data / godzina / strefa czasowa”, wszystkie wartości zostaną oznaczone jako błędne. Drogą prób i błędów doszedłem do tego, że data musi być oddzielona od godziny spacją, nie dwukropkiem, więc trzecią z kolei zamianą wartości będzie podmiana „2021:’’ na „2021 ’’.

 

 

Aby osiągnąć docelowy format, musimy najpierw dokonać zamiany typu na „Data / godzina / strefa czasowa” (tym razem się uda), potem zaś drugiej zmiany na „Data / godzina” (obowiązkowo jako nowy krok). Czemu tak? Cóż, to efekt niejawnych założeń poczynionych przez autorów graficznego interfejsu użytkownika Power BI – musimy pogodzić się z tym, że niektóre efekty nie będą intuicyjne. Najważniejsze, że udało nam się osiągnąć zamierzony cel. Widzimy też, że kolumna wyświetla teraz dane zgodnie z czasem środkowoeuropejskim.

W następnej kolejności wyodrębnimy dodatkowe kolumny z treści żądania HTTP. Prawy klawisz na nagłówku kolumny, pozycja „Podziel kolumny” → „Według ogranicznika”. Parametry: spacja, najdalej z lewej strony – wydzieliliśmy typ żądania (np. GET). Potem to samo, ale spacja najdalej z prawej strony – wydzieliliśmy wersję protokołu (np. HTTP/1.1). Wreszcie trzeci raz z niestandardowym ogranicznikiem „?” (znak zapytania) najdalej z lewej strony – w ten sposób wydzielamy parametry URL-a.

Tyle nam na razie wystarczy. Pozostaje zmienić nazwy kolumn na bardziej przyjazne, od lewej będą to kolejno: ip, timestamp, request, url, parameter, protocol, status, length, referer (korekta nie poprawiać, ten błąd ma 25 lat i pozostanie z nami na zawsze) oraz useragent.

Na zakończenie pracy z edytorem zapytań w zakładce „Narzędzia główne” klikamy przycisk „Zamknij i zastosuj”. Do okna edycji możemy zawsze wrócić za pomocą przycisku „Przekształć dane”.

 

Oczyszczanie danych

 

W docelowej analizie chcemy dowiedzieć się czegoś o zachowaniach prawdziwych czytelników, jednak w logach serwera WWW znajdziemy także trochę danych śmieciowych. Musimy więc najpierw odfiltrować ślady automatyki WordPressa, wizyty wyszukiwarek, czytników RSS oraz inne tego typu pozycje. Będziemy przełączać się między widokiem raportu a widokiem danych, by określić zestaw wymaganych filtrów. Przyda nam się oczywiście nieco wiedzy na temat tego, jak wygląda komunikacja przeglądarki z serwerem WWW.

Zacznijmy od kliknięcia dowolnego wykresu słupkowego w palecie „Wizualizacje”. W obszarze roboczym pojawi się szablon wykresu. Zaznaczmy go i przeciągnijmy pozycję „request” z palety „Pola” do ramek „Oś” oraz „Wartości”. Powinniśmy zobaczyć taki obraz:

 

 

Widzimy, że żądania typu POST stanowią aż jedną trzecią wszystkich, co jest nieco zaskakujące. Przełączmy się do widoku Danych (mała ikonka z kratką po lewej stronie, środkowa z trzech). Kliknijmy ikonkę lejka w nagłówku kolumny Request i pokażmy jedynie żądania typu POST.

 

 

Widzimy, że większość takich żądań generują mechanizmy WordPressa, na którym działa analizowany serwis. My będziemy analizować jedynie żądania typu GET, wysyłane podczas normalnego wejścia na stronę. Filtr ustawimy w widoku „Raport” (pierwsza z trzech ikonek po lewej), tam przeciągamy pole „request” na ramkę „Filtry na wszystkich stronach” w palecie „Filtry” i zaznaczamy jedynie wartość „GET”. Widać, że wykres od razu dostosował się do filtra.

 

 

Nadal chcemy odsiać automaty czytające naszą witrynę. Jak to zrobić? Zajrzyjmy do pola useragent, którym przeglądarki (oraz roboty) przedstawiają się podczas wysyłania żądania. Umieśćmy w obszarze roboczym pole Tabela i przeciągnijmy tam pole „useragent”.

 

 

W tej kolumnie mamy ponad 600 różnych wartości, zmieńmy wizualizację na wykres słupkowy i posortujmy malejąco według częstości występowania (sortowanie ustawisz w menu pod trzema kropeczkami w prawym górnym rogu zaznaczonej wizualizacji).

 

 

Widzimy, że Googlebot odwiedził serwis ponad 1300 razy w ciągu doby. Jak sobie z tym poradzić? Zbudujemy nową kolumnę (pole), w której zawrzemy informacje o systemie operacyjnym. Wszystko, co wypadnie poza znany schemat, uznamy za boty i odfiltrujemy.

Wracamy więc do Edytora Zapytań (przycisk „Przekształć dane”) i tam w zakładce „Dodaj kolumnę” klikamy przycisk „Kolumna warunkowa”. Definiujemy ją w następujący sposób:

 

 

Jeśli gdzieś się pomylisz, możesz wrócić do tego okna klikając mały trybik na ostatniej pozycji listy „Zastosowane kroki”. Zapiszmy zmiany i wróćmy do raportu, czas sprawdzić proporcje wyliczonych danych.

 

 

Okazuje się, że boty wskazaliśmy poprawnie – manualna inspekcja tej kategorii ujawniła dziesiątki czytników RSS, robotów indeksujących i mechanizmów odpowiedzialnych za generowanie podglądu w komunikatorach i sieciach społecznościowych.

Przyspieszymy nieco opowieść – z dalszych analiz eliminujemy wpisy o statusie HTTP innym niż 200, pliki JavaScript, CSS oraz obrazki. Niestety, przy tej okazji możemy natknąć się na niespójności interfejsu użytkownika różnych fragmentów Power BI. Przykład – filtrowanie pola tekstowego inaczej wygląda i działa w panelu Dane (obrazek po lewej) a inaczej w panelu Raport (obrazek po prawej). Opcja „kończy się na” występuje tylko w jednym z nich.

 

 

Do utworzenia wizualizacji będziemy jeszcze potrzebować:

  • pola zawierającego godzinę (od 0 do 23), tworzymy ją jako „kolumnę niestandardową” zawierającą formułę „=Time.Hour( DateTime.Time ([timestamp] ))”
  • grupowania w kolumnie opisującej systemy operacyjne, jak na obrazku poniżej (przeglądarki desktop i mobile)

 

 

Wizualizacja danych

 

Główną przewagą Power BI nad Excelem jest to, że od razu po sporządzeniu wizualizacji możemy przeprowadzać analizy dające nam wiedzę o wzajemnych zależnościach poszczególnych zmiennych. Wstawmy do raportu następujące wizualizacje:

  • Fragmentator, pola „os” i „os (grupy)”
  • Tabela, pole „protocol”
  • Wykres skumulowany procentowy, oś „hourofday”, legenda „os (grupy)”, wartości „liczba elementów os”
  • Wykres liniowy, oś „hourofday”, legenda „os (grupy)”, wartości „liczba elementów os”
  • Skumulowany wykres słupkowy, oś “url”, wartości “liczba elementów url”, sortowanie względem “liczba elementów url” malejąco (sortowanie ustawisz w menu pod trzema kropeczkami po kliknięciu wizualizacji)

 

Jeśli teraz chcielibyśmy dowiedzieć się, ile osób przeczytało między godziną 20 a 21 na telefonie komórkowym artykuł o emotikonkach, wystarczy wykonać trzy ruchy myszką:

 

 

Poszukiwaną wartość odczytamy wskazując kursorem odpowiedni słupek – spośród 34 wejść z urządzeń mobilnych, wskazany artykuł otworzono cztery razy.

Kliknięcie elementów na kolejnych wizualizacjach filtruje bądź wyróżnia zawartość pozostałych – dzieje się to automatycznie. Podobny efekt daje się wprawdzie osiągnąć także w Excelu, jednak tam filtry utworzone przy użyciu funkcji Fragmentator (Slicer) lub Tabela przestawna pozostają oddzielnym elementem wizualnym, nie da się włączać/wyłączać filtrowania klikaniem wykresów.

Oczywiście opisane zachowanie jest opcjonalne, funkcją „Format” → „Edytuj interakcje” możemy selektywnie włączać i wyłączać związki między elementami raportu. Da się więc wstawić na jedną stronę dwa izolowane zestawy wizualizacji, by móc jednocześnie oglądać wyniki dwóch różnych wariantów filtrowania.

 

Podsumowanie

 

W niniejszym artykule pokazaliśmy, jak użyć narzędzia Microsoft Power BI Desktop do analizy logów serwera WWW. Choć praca nad surowymi logami w formacie Apache była dość żmudna, to efekt końcowy może być użyty wielokrotnie – nowy zestaw danych wczytamy za pomocą komendy „Przekształć dane” → „Ustawienia źródła danych” → „Zmień źródło”.

Narzędzia do analityki biznesowej oferują znacznie większe możliwości przekształcania i analizy danych, niż arkusz kalkulacyjny Excel. Jeśli jednak i to narzędzie nie wystarczy, musisz rozpocząć naukę programowania lub… zadzwonić do PGS Software. Z przyjemnością pomożemy ci w przygotowaniu wizualizacji skrojonych na miarę potrzeb. Czy wspominałem, że dzięki mechanizmom Machine Learning możesz nie tylko patrzeć na to, co było, lecz także przewidywać to, co dopiero będzie?

 

Praca IT PGS Software

Najnowsze wpisy