Power BI – ekstrakcja zapytań wysyłanych przez wizualizacje i Power Query

Power BI jako narzędzie raportowe opiera się na dwóch językach. Pierwszym z nich jest oczywiście język DAX służący do definiowania miar oraz obiektów kalkulowanych w tworzonym przez nas modelu. Drugim natomiast jest język M będący kluczowym elementem procesu ekstrakcji i transformacji danych w Power Query. Podczas gdy użytkownicy będą korzystać z raportów nasze modele mogą generować mnóstwo kodu DAX. Kod M z kolei jest zaszyty wewnątrz pliku pbix i oprócz pewnych możliwości użycia parametrów jest w większości przypadków niezmienny.

Czy istnieje prosty sposób na ekstrakcję kodu M z aplikacji bez konieczności manualnego przeszukiwania pliku? Czy jesteśmy w stanie magazynować zapytania DAX  generowane przez użytkowników w celu ich późniejszej analizy? Na te pytania postaram się odpowiedzieć w ramach niniejszego artykułu do lektury którego serdecznie zapraszam.

Zaczniemy od nieco prostszego zagadnienia jakim jest ekstrakcja kodu M. Myślę, że każdy z Was wie, że aby podejrzeć kod M pod konkretnym zapytaniem możemy wejść do okna Power Query i tam wybrać interesujące nas zapytanie i ze wstążki wybrać Advanced Editor gdzie znajdziemy interesujący nas kod:

Manualne “klikanie” po wszystkich zapytaniach może być nieco problematyczne – szczególnie w przypadku złożonych procesów ekstrakcji. Na szczęście istnieje pewien bardzo prosty sposób na pobranie wszystkich zapytań M w danym pliku. Jeśli chcemy wyekstrahować dane z Power BI Desktop to możemy podłączyć się do niego za pomocą Management Studio używając sposobu, który udało mi się opisać tutaj, w przypadku modeli już opublikowanych możemy wykorzystać XMLA Endpoint (artykuł tutaj). Niezależnie od tego gdzie znajduje się nasz zestaw danych sposób na ekstrakcję zapytań M jest ten sam – opiera się on na użyciu nieudokumentowanego widoku dynamicznego o nazwie $SYSTEM.DISCOVER_M_EXPRESSIONS. Jak to bywa z wszelkiego rodzaju funkcjami, które są nieudokumentowane należy uważać jednakże ta konkretna daje nam bardzo fajne rezultaty w postaci prostej tabeli z wypisanymi zapytaniami wraz z towarzyszącym kodem M:

select  
	*
from
$SYSTEM.DISCOVER_M_EXPRESSIONs

Prosto, szybko i przyjemnie. Aż tak łatwo nie będzie w przypadku zapytań DAX generowanych z Front-endu. Jeśli chcielibyśmy sprawdzić jakie zapytania generują wizualizacje w swoim domyślnym stanie (czyli przy domyślnej selekcji tzn. takiej z jaką może być zapisany raport) to rozwiązaniem może tu być Performance Analyzer. Mechanizm ten również miałem okazję opisać, tak więc jakby ktoś chciał poczytać na ten temat to zapraszam. Pokażmy sobie jak osiągnąć pożądany efekt – dlatego też stworzyłem trzy wizualizacje na raporcie (nieistotne jakie) i na wstążce przeszedłem na View i  wybrałem opcję Performance Analyzer:

Następnie w samym oknie narzędzia wybrałem Start Recording, a następnie Refresh visuals:

Celem niniejszego ćwiczenia jest dostanie listy wszystkich zapytań DAX generowanych przez wizualizacje dlatego nie analizujemy danych dostępnych w oknie narzędzia tylko klikamy Export:

W rezultacie otrzymamy plik JSON, w którym znajdują się wszystkie informacje zarejestrowane przez Performance Analyzer, włączając w to oczywiście zapytania DAX. Sam plik najlepiej będzie przetransformować do formy tabelarycznej, a posłuży nam do tego Power Query. Poniższe zapytanie powinno zwrócić nam zestaw zapytań wygenerowanych przez wizualizacje:

let
    Source = Json.Document(File.Contents("C:\Users\adria\Desktop\PowerBIPerformanceData.json")),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{1}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"name", "component", "start", "id", "metrics"}, {"name", "component", "start", "id", "metrics"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([name] = "Execute DAX Query")),
    #"Expanded metrics" = Table.ExpandRecordColumn(#"Filtered Rows", "metrics", {"QueryText"}, {"QueryText"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded metrics",{"QueryText"})
in
    #"Removed Other Columns"

W powyższym zapytaniu nie ma nic nadzwyczajnego – jedno na co warto zwrócić uwagę to krok #”Filtered Rows” gdzie przefiltrowałem wszystkie zarejestrowane wydarzenia po kategorii “Execute DAX Query” bo właśnie tutaj znajdują się wygenerowane zapytania. W rezultacie tych działań otrzymaliśmy prostą tabelę z zapytaniami:

Mamy zatem zestaw zapytań generowanych przez raport w domyślnym stanie. Oczywiście tego typu zapytania jesteśmy w stanie zebrać również po każdym kliknięciu elementu na raporcie co jest kluczowe w testowaniu wydajności i generowanych zapytań. Jeśli chodzi o przechwytywanie zapytań na zestawie danych zapisanym na portalu to również w tym przypadku mamy pewnie ograniczone możliwości. Niestety nie mamy możliwości ani założenia trace’a przy pomocy Profilera ani tym bardziej użycia Extended Events. Możemy natomiast wykorzystać DMV i widok $SYSTEM.DISCOVER_COMMANDS. Widok ten przechowuje zapytania wysłane przez otwarte sesje lub zapytania, które są aktualnie wykonywane.

Przykładowy rezultat zwrócony przez widok wygląda następująco:

Poszczególne zapytania mogą być zidentyfikowane przez identyfikator sesji czyli SESSION_SPID oraz czas wykonania zapytania (COMMAND_START_TIME). Oczywiście dane tego typu nie są trwale zapisane więc w przypadku gdy chcemy je analizować w późniejszym czasie to musimy stworzyć mechanizm, które cyklicznie te informacje będzie zbierał. Do tego celu możemy na przykład stworzyć prosty skrypt powershell, który wyekstrahuje dane do pliku płaskiego:

$server = "powerbi://api.powerbi.com/v1.0/myorg/ADCH";
$dbId = "Undocumented";
$CurrentTimestamp = Get-Date -Format "yyyyMMddHHmm";
$fileoutput = "C:\temp\logfile_$CurrentTimestamp.log";

Invoke-ASCmd -Server $server -Database $dbId -Query 'select * from $SYSTEM.DISCOVER_COMMANDS'| 
 Out-File $fileoutput

Powyższy skryt korzysta z cmdleta Invoke-ASCmd, a logowanie odbywa się za pomocą okna dialogowego, które możemy wypełnić w momencie uruchomienia. Jeśli chcielibyśmy ten skrypt podpiąć w jakimś narzędziu pod jakiś harmonogram to warto pomyśleć o użyciu service principala. Zestaw danych do którego podłączyłem się w ramach niniejszego przykładu nazywa się “undocumented” jednakże nic nie stoi na przeszkodzie aby podłączyć się do wszystkich modeli znajdujących się w danym workspace – wtedy też wystarczyła by zwykła pętla. My ograniczymy się jednakże do pojedynczego modelu – po uruchomieniu skryptu dane zostaną zapisane  w formacie XML:

Ja dla celów testowych zapisałem pliki na moim lokalnym dysku ale równie dobrze pliki moglibyśmy przechowywać np. na BLOB Storage w chmurze lub przechowywać dane np. w relacyjnej bazie danych. Mając już kilka plików z danymi możemy je załadować do Power BI transformacją folder(zainteresowanych odsyłam do przykładu opisanego tutaj). Ogólnie rzecz biorąc przetwarzanie plików XML w Power Query nie zawsze należy do najprostszych ale w tym konkretnym przypadku nie powinno przysporzyć problemów i możemy dostać się do pożądanych danych nawet ograniczając się do opcji dostępnych z interfejsu graficznego. Wygenerowany przeze mnie kod wygląda następująco:

let
    Source = Xml.Tables(File.Contents("C:\temp\logfile_202007122204.log")),
    #"Expanded Table" = Table.ExpandTableColumn(Source, "Table", {"Name", "Table"}, {"Name.1", "Table.1"}),
    #"Expanded Table.1" = Table.ExpandTableColumn(#"Expanded Table", "Table.1", {"Name", "Table"}, {"Name.2", "Table"}),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Expanded Table.1", "Table", {"Name", "Table"}, {"Name.3", "Table.1"}),
    #"Expanded Table.2" = Table.ExpandTableColumn(#"Expanded Table1", "Table.1", {"element", "simpleType", "complexType", "Attribute:targetNamespace", "Attribute:elementFormDefault", "SESSION_SPID", "SESSION_COMMAND_COUNT", "COMMAND_START_TIME", "COMMAND_ELAPSED_TIME_MS", "COMMAND_CPU_TIME_MS", "COMMAND_READS", "COMMAND_READ_KB", "COMMAND_WRITES", "COMMAND_WRITE_KB", "COMMAND_TEXT", "COMMAND_END_TIME"}, {"element", "simpleType", "complexType", "Attribute:targetNamespace", "Attribute:elementFormDefault", "SESSION_SPID", "SESSION_COMMAND_COUNT", "COMMAND_START_TIME", "COMMAND_ELAPSED_TIME_MS", "COMMAND_CPU_TIME_MS", "COMMAND_READS", "COMMAND_READ_KB", "COMMAND_WRITES", "COMMAND_WRITE_KB", "COMMAND_TEXT", "COMMAND_END_TIME"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table.2", each ([COMMAND_START_TIME] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"SESSION_SPID", "COMMAND_START_TIME", "COMMAND_TEXT"})
in
    #"Removed Other Columns"

Założyłem, że harmonogram ekstrakcji danych z DMV może wykonywać się bardzo często dlatego też warto pamiętać, że pliki mogą posiadać zduplikowane informacje (zajmiemy się tym za chwilę). Ze względu na fakt, że plików jest wiele przydatnym konstruktem może być funkcja. Z tego też powodu powyższe zapytanie zamieniłem na funkcję o udf_GetXMLLogFile, która jako parametr przyjęła ścieżkę do pliku:

let
    Source = (Path) => let
        Source = Xml.Tables(File.Contents(Path)),
        #"Expanded Table" = Table.ExpandTableColumn(Source, "Table", {"Name", "Table"}, {"Name.1", "Table.1"}),
        #"Expanded Table.1" = Table.ExpandTableColumn(#"Expanded Table", "Table.1", {"Name", "Table"}, {"Name.2", "Table"}),
        #"Expanded Table1" = Table.ExpandTableColumn(#"Expanded Table.1", "Table", {"Name", "Table"}, {"Name.3", "Table.1"}),
        #"Expanded Table.2" = Table.ExpandTableColumn(#"Expanded Table1", "Table.1", {"element", "simpleType", "complexType", "Attribute:targetNamespace", "Attribute:elementFormDefault", "SESSION_SPID", "SESSION_COMMAND_COUNT", "COMMAND_START_TIME", "COMMAND_ELAPSED_TIME_MS", "COMMAND_CPU_TIME_MS", "COMMAND_READS", "COMMAND_READ_KB", "COMMAND_WRITES", "COMMAND_WRITE_KB", "COMMAND_TEXT", "COMMAND_END_TIME"}, {"element", "simpleType", "complexType", "Attribute:targetNamespace", "Attribute:elementFormDefault", "SESSION_SPID", "SESSION_COMMAND_COUNT", "COMMAND_START_TIME", "COMMAND_ELAPSED_TIME_MS", "COMMAND_CPU_TIME_MS", "COMMAND_READS", "COMMAND_READ_KB", "COMMAND_WRITES", "COMMAND_WRITE_KB", "COMMAND_TEXT", "COMMAND_END_TIME"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded Table.2", each ([COMMAND_START_TIME] <> null)),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"SESSION_SPID", "COMMAND_START_TIME", "COMMAND_TEXT"})
    in
        #"Removed Other Columns"
in
    Source

W dalszej kolejności powstałą funkcję wywołałem dla każdego pliku znajdującego się w folderze:

let
    Source = Folder.Files("C:\temp"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".log")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Folder Path"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"Folder Path","Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FullPath"),
    #"Invoked Custom Function" = Table.AddColumn(#"Merged Columns", "udf_GetXmlLogFile", each udf_GetXmlLogFile([FullPath])),
    #"Filtered Rows1" = Table.SelectRows(#"Invoked Custom Function", each ([FullPath] <> "C:\temp\logfile_202007122203.log")),
    #"Expanded udf_GetXmlLogFile" = Table.ExpandTableColumn(#"Filtered Rows1", "udf_GetXmlLogFile", {"SESSION_SPID", "COMMAND_START_TIME", "COMMAND_TEXT"}, {"SESSION_SPID", "COMMAND_START_TIME", "COMMAND_TEXT"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded udf_GetXmlLogFile", {"SESSION_SPID", "COMMAND_START_TIME"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Duplicates", each Text.Contains([COMMAND_TEXT], "EVALUATE"))
in
    #"Filtered Rows2"

W powyższym skrypcie możecie zauważyć krok usuwający duplikaty. Jak już wspomniałem wcześniej SESSION_SPID oraz COMMAND_START_TIME powinny prawidłowo zidentyfikować pojedyncze zapytanie i to właśnie na ich podstawie usunąłem zbędne wiersze. Ostatnim krokiem była filtracja zapytań – ponieważ interesują mnie jedynie zapytania DAX wysyłane przez wizualizację dlatego też przefiltrowałem tekst zapytania tak aby posiadał słowo kluczowe “EVALUATE”, które powinno występować w każdym interesującym mnie zapytaniu. Po tych wszystkich działaniach otrzymałem następujący zbiór danych:

Na podstawie tego zbioru mogę stworzyć sobie różnego rodzaju wizualizacje czy też dokonywać analiz. Jest to tym bardziej użyteczne wtedy gdy użytkownicy sami mogą tworzyć raporty opierające się o opublikowany zestaw danych. Wbudowane w serwis Power BI raporty i metryki mówiące o tym jak używane są raporty dają wysokopoziomowy pogląd na to jak używany jest model, jednakże aby poznać nieco bardziej charakterystykę działań użytkowników musim kombinować sami.

Mam nadzieję, że przedstawione przeze mnie przykłady zainspirują Was do zgłębienia tematu i swoich własnych eksperymentów. Na ten moment to by było na tyle tak więc dzięki za poświęcony czas na lekturę i pozdrawiam.