Power BI – Data profiling oraz Fuzzy Matching

Do tej pory jeśli ktoś mówił o takich jak funkcjonalnościach jak Data Profiling czy Fuzzy Matching w stosunku do narzędzi ze stosu technologicznego platformy danych Microsoft, zapewne miał na myśli wchodzące w skład SQL Server oprogramowanie Integration Services. Sytuacja ta zmienia się wraz z ostatnią (październik 2018) aktualizacją Power BI gdzie obie funkcjonalności zostały dodane do edytora zapytań (Power Query) i co ciekawe w kilku aspektach są dużo lepsze niż wspomniany SSIS! Jak one działają i co mają do zaoferowania? Postaram się to wyjaśnić na kilku bardzo prostych przykładach.

Tak jak każda nowość w Power BI tak i wspomniane funkcjonalności w momencie ich wprowadzenia są w fazie preview, dlatego też pierwszym krokiem jaki należy przedsięwziąć aby je przetestować jest ich włączenie. Aby to zrobić należy standardowo wejść w opcje narzędzia, zaznaczyć te opcje, które nas interesują i zrestartować Power BI Desktop:

Po włączeniu mamy je do dyspozycji tak więc sprawdźmy jak się one sprawują w praktyce. Do pierwszego testu wykorzystamy standardowo dane znajdujące się w FactInternetSales bazy AdventureWorksDW. W standardowy sposób pobrałem tą tabelę do pliku Power BI Desktop w trybie Import. Zaraz potem przełączyłem się do Edytora zapytań gdzie już powinienem mieć możliwość skorzystać z omawianych funkcjonalności:

W edytorze zapytań pojawił się zaraz pod nagłówkami kolumn zielony pasek, który jest pierwszą oznaką omawianych funkcjonalności na jaką się natkniemy:

Po najechaniu na niego myszą otrzymamy podstawowe informacje związane z profilowaniem danej kolumny takie jak:

• liczba wypełnionych wierszy
• liczba pustych wierszy
• liczba błędnych wierszy

Warto zwrócić uwagę na fakt, że dane profilowe, które są nam wyświetlane nie są obliczane na podstawie całego zbioru, a jedynie pierwszego tysiąca wierszy. Pasek koloru zielonego wskazuje procent wypełnienia wartościami, jeśli mamy kolor ciemnoszary jest to wskaźnik wartości pustych. Łatwo to daje nam pogląd na stosunek wartości pustych do wypełnionych w danej kolumnie. Aby dostać nieco bardziej ciekawy rezultat wykonajmy operację replace na jednej z kolumn w naszej tabeli(CurrencyKey) tak aby uzyskać wartości puste:

Po wykonaniu powyższej operacji otrzymałem następujący wskaźnik:

W dolnej części podpowiedzi okna z wynikiem profilownia mam możliwość kliknięcia “Remove Empty” aby po prostu odfiltrować dane, które są puste. W tym przypadku operacja ta będzie się odnosić do całego zbioru danych, a nie tylko wspomnianego tysiąca wierszy.

Aby móc dalej sprawdzić funkcjonalność wykonałem następujące funkcje transformujące:

   #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"CurrencyKey", type text}}),
   #"Replaced Value1" = Table.ReplaceValue(#"Changed Type","6","A",Replacer.ReplaceText,{"CurrencyKey"}),
   #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"CurrencyKey", Int64.Type}})

Czyli nic innego jak:

1. zmieniłem typ kolumny na tekst,
2. zmieniłem wartość “6” na “A”,
3. Zmieniłem typ danych kolumny z powrotem na całkowitoliczbowy.

Oczywiście powyższa sekwencja kroków wygenerowała błąd. W takim przypadku testowane przez nas narzędzie profilowania wygenerowało już nieco inny rezultat (zwróćcie uwagę na to, że jeśli kolumna ma błędy to jedyną wyświetlaną statystyką jest procentowy udział tych błędów):

Kolor czerwony w tym konkretnym przypadku symbolizuje błędy. Warto odnotować, że jeśli mamy jakiekolwiek błędy to okno profilowania zasugeruje nam oczywiście możliwość ich odfiltrowania. Oczywistym jest fakt, że okno pojawiające się po najechaniu myszą może być przydatne ale nie jesteśmy na nie jest to jedyna dostępna dla nas opcja. Przechodząc na wstążce na zakładkę View zaznaczając opcję Column quality dostaniemy jeszcze inny sposób wizualizacji:

Jest nim belka z graficznymi wskaźnikami procentowego udziału błędów, danych uzupełnionych i nieuzupełnionych w danej kolumnie:

Kolejną opcją profilingu dostępną w Power BI jest Column distribution. Opcja ta daje nam pogląd na rozkład wartości w naszym zbiorze danych:

Graficznie przedstawia się to w następujący sposób:

Liczba przy słowie distinct oznacza oczywiście liczbę unikalnych wartości w kolumnie. Liczba przy słowie unique oznacza ile jest takich wartości, które nie powtórzyły się w danym zbiorze. Same słupki mają dać pogląd o rozkładzie wartości ale same w sobie nie niosą konkretnej informacji. To czego mi osobiście brakuje i co chętnie bym zobaczył to możliwość profilowania na podstawie większego/całego zbioru danych oraz możliwość zachowania tych danych w postaci tabeli – to by było naprawdę coś fajnego.

Kończąc powoli wątek profilowania danych w Power BI wspomnę jeszcze o funkcji, która może nie jest bezpośrednio związana z omawianą funkcjonalnością ale pozwala nam stworzyć profil danych. Jest nią oczywiście Table.Profile.

Funkcja ta oblicza dla nas następujące statystyki:

• minimum
• maximum
• średnia
• odchylenie standardowe
• zliczenie
• zliczenie wartości pustych
• zliczenie wartości unikalnych

Przykładowy rezultat działania wspomnianej funkcji wygląda następująco:

Oczywiście te oraz inne statystyki możemy policzyć na inne sposoby, chociażby używając R/Python ale mimo wszystko warto znać natywne funkcjonalności Power BI. To by było na tyle jeśli chodzi o możliwości profilowania, warto mieć na uwadze fakt, że Microsoft zapowiedział, że kolejne możliwości będą skutecznie dostarczane do tej funkcjonalności tak więc nie pozostaje nam nic innego jak trzymać kciuki i czekać na kolejne aktualizacje.

Kolejną funkcjonalnością której się przyjrzymy jest Fuzzy Matching czyli możliwość łączenia dwóch zbiorów na podstawie podobieństwa wartości. W celu zobrazowania tej funkcjonalności wykorzystamy trzy tabele wprowadzone bezpośrednio do Power BI. Ich definicje znajdziecie poniżej:

Tabela Cities posłuży nam jako wzorowy zbiór wartości (słownik), do których chcemy dołączyć pasujące wiersze z innej tabeli:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk8sKq5KLE9UitWJVvJOLMkvz0xOBXOcMlNzirPzFZwyE482QeSDq5KrUpMz88Ac95TEoy3F2RCNRYnZhzeXK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [City = _t])

in
    Source

Tabela CityToMatch z wartościami, które chcemy dołączyć do przedstawionego wyżej słownika. Zawiera liczne literówki i błędne zapisy:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk8sKq5KLE9UitWBcJDZQAkw27soMTsfwnRPScwrzgYznTJTc4qz83WdMhNzEtFFjjZBhIKTq1KTM/MgxiSW5JdnJqeicFJhtsEsBlqWDLUsHCGGYqWCk55SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CityToMatch = _t])
in
    Source

Tabela MatchTable z mapowaniem niektórych wartości testowych do wartości wzorcowych:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i5KzM4vV9IBMw5vLleK1YlWcspMzSnOzldwykw82pQIlEQSyEnEq0QXKkBIDdSY8MSi4qrEcpAklKkUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [City = _t, CityToMatch = _t]),
 in
    Source

 

Jak możecie się domyślać, to co spróbujemy zrobić to złączyć tabelę Cities z CityToMatch. Samo złączenie przebiega w standardowy sposób wykorzystując transformację Merge Queries dostępną ze wstążki:

W oknie tworzenia złączenia mamy do dyspozycji nowe możliwości złączenia rozmytego:

Z opcją tą związany jest szereg właściwości tj.:

Set treshold – jest to minimalny próg podobieństwa powyżej którego dopasowana wartość zostanie uznana za pasującą. Przyjmuje wartości od 0 czyli kompletny brak podobieństwa do 1 czyli dokładnie taka sama wartość. Wartość 1 odpowiada standardowemu złączeniu bez logiki rozmytej. Domyślną wartością jest 0.8.

Ignore case – wielkość liter zostanie zignorowana podczas porównywania.

Ignore spaces – spacje zostaną zignorowane podczas porównywania.

Maximum number of matches – maksymalna liczba podobnych wartości jaką chcemy znaleźć dla pojedynczego wiersza.

Transformation table – jeśli mamy tabelę z mapowaniem dla niektórych wartości możemy ją tutaj wskazać.

Na samym początku naszego testu spróbujemy dołączyć wartości na standardowych, domyślnych wartościach. W rezultacie otrzymany został następujący rezultat:

Jak widać algorytm dosyć łatwo poradził sobie z brakami liter lub z pojedynczymi literówkami. Do tabeli CityToMatch dorzuciłem miasto Szczecinek, a w samej definicji złączenia  zmieniłem próg z 0.8 na  0.2. Rezultat wygląda tak, że dostaliśmy dobre mapowanie skrótu Bielsko B. ale dodatkowo do Szczecina dorzucony został Szczecinek:

W tego typu analizie wykonywane jest proste porównywanie ciągów znaków i wyliczany jest współczynnik podobieństwa tak więc pewne jest to, że zdarzą się błędy tego typu jak przypisanie dwóch podobnych miast do siebie. Ważną rolę w czyszczeniu danych pełni osoba będąca tzw. Data Steward’em, która nie tylko może decydować czy mapowanie przebiegło pomyślnie, ale również wskazywać własne mapowania w tabeli, która następnie będzie dołączona do całego procesu jako Transformation Table.

W momencie gdy dołączyłem tabelę MatchTable, która w moim przykładzie pełni rolę Transformation Table do złączenia oczywiście otrzymałem dosyć jednoznaczny komunikat:

Po zmianie nazwy pierwszej z kolumn w mojej tabeli na “From” domyślałem się, że kolejna ma się nazywać “To” i w tym przypadku się nie pomyliłem:

Tak więc zmieniłem nazwy w mojej tabeli mapującej i rezultat po podpięciu prezentuje się już całkiem nieźle:

To czego mi brakuje w tym algorytmie na dzień dzisiejszy i co bardzo by wzbogaciło całą funkcjonalność to z całą pewnością:

• możliwość podejrzenia stopnia podobieństwa,
• możliwość zdefiniowania nie tylko minimalnego progu ale również maksymalnego lub nawet zakresu wartości dla progu,
• możliwość zdefiniowania tabeli podobnej do Transformation table gdzie będę mógł podać wartości, które nie powinny być mapowane (aby uniknąć Szczecina ze Szczecinkiem które technicznie są bardzo podobne i wyliczony próg będzie bardzo wysoki. Oczywiście aby uniknąć ich zmapowania mogę stworzyć sobie taką tabelę i wykonać złączenie Left Anti, ale mimo wszystko fajnie byłoby to mieć prosto “z pudełka”),
• możliwość ignorowania znaków specjalnych bądź znaków zdefiniowanych przez użytkownika
• możliwość użycia czegoś na wzór Fuzzy Grouping z Integration Services

Ogólnie mogę powiedzieć, że obie dodane niedawno funkcjonalności są bardzo ciekawe i pomocne. Mam nadzieję, że z czasem zostaną one rozszerzone o dodatkowe rzeczy co pozwoli zwiększyć ich zastosowanie. Każda kolejna funkcjonalność rozszerzająca możliwości Power BI jest przeze mnie mile widziana i nie inaczej jest w tym przypadku.