Metadane modelu semantycznego Power BI [GOTOWY PLIK]

Z małym opóźnieniem, ale jest! Jak dynamicznie analizować metadane modelu Power BI – być może zastanawiałeś się nad tym nie raz.

Intro

Zapraszam Cię do lektury artykułu, który pozwoli Ci stworzyć rozwiązanie monitorujące model semantyczny osadzony w ramach Power BI Service.

Na końcu artykułu znajdziesz rozwiązanie, które możesz pobrać i z którego możesz sam skorzystać. Może być ono dla Ciebie inspiracją i możesz je oczywiście rozbudowywać.

Co ważne, rozwiązanie będzie też działać, jeśli posiadasz model SSAS/ AAS. 

Z podobnego rozwiązania skorzystałem w ramach jednego z projektów, przy którym pracowałem. Posiadaliśmy wiele modeli na kilku środowiskach, dlatego istotna była możliwość ich monitorowania na bieżąco i w sposób dynamiczny. Niekoniecznie samemu wyklikując opcje danego modelu czy też korzystając z zewnętrznych narzędzi jak DAX Studio. (O DAX Studio pisałem już w jednym z artykułów, znajdziesz go tu). Głównym założeniem była możliwość szybkiego monitorowania modelu pod kątem jego wielkości i ostatniego czasu odświeżenia obiektów w nim zawartych. Nie chciałem też wciągać tych danych do Hurtowni Danych, ponieważ zawsze interesował mnie tylko stan bieżący, nie chciałem też budować pipelineu pobierającego dane i zasilającego Hurtownię.

Jak podłączyć się do modelu semantycznego?

Pierwszy krok, to podłączenie naszego modelu semantycznego osadzonego w usłudze Power BI Service. (Działa też dla modeli SQL Server Analysis Services (SSAS) lub Azure Analysis Services (AAS). )

Tak jak wspominałem w poprzednim artykule, ważne, żeby mieć uprawnienia Adminowe na obiekcie/ modelu, który chcemy odpytać o metadane. 

Aby podłączyć się do naszego modelu w ramach opcji [Pobierz dane] wybieramy opcję [Baza danych usług SQL Server Analysis Services]. Możesz wybrać też drugą opcję dostępną na widoku.

W kolejnym kroku podajemy namiary na interesujące Cię rozwiązanie.

W naszym przypadku skorzystamy z opcji odpytania Obszaru Roboczego PBI Service przez XMLA Endpoint. Funkcja ta jest dostępna w ramach obszarów roboczych przypisanych do Premium Capacity lub licencji typu Premium Per User.
„Namiar” do połączenia znajdziesz w ustawieniach obszaru roboczego. Skorzystaj z linku znajdującego się w ramach opcji [Link połączenia].

Po uzyskaniu adresu wracamy do naszej opcji pobierania danych. W polu Serwer wklejamy pozyskany wyżej link,a w ramach pola [Baza_danych] podajemy nazwę modelu, dla którego metadane chcemy pobrać. W moim przypadku model/ baza danych nazywa się po prostu „Ladowanie”.

Ważne!
Jeśli chcesz odpytać SSAS/ AAS podajesz namiary w analogiczny sposób, tylko, że w polu [Serwer] umieszczasz nazwę swojego Serwera.

Zwróć uwagę, że wybieramy opcję [Importuj] i wklejamy zapytanie MDX, które chcemy wykorzystać.
Listę użytych zapytań MDXowych znajdziesz poniżej. W kolejnym kroku klikamy OK i w ten sam sposób ładujemy dane dla każdego z poniższych zapytań.

Metadane dotyczące samego modelu:

SELECT 
	[Name], 
	[Culture], 
	[StructureModifiedTime]
FROM $SYSTEM.TMSCHEMA_MODEL;

Metadane dotyczące tabel użytych w modelu:

SELECT 
	[Name], 
	[ID],
	[IsHidden], 
	[ModifiedTime], 
	[StructureModifiedTime]
FROM $SYSTEM.TMSCHEMA_TABLES; 

Metadane odnośnie partycji w ramach tabel. Partycje pojawią się, jeśli ładujesz dane przyrostowo. Dla uproszczenia możesz przyjąć, że partycje dzielą tabelę na mniejsze podzbiory według zdefiniowanych opcji np. daty. Dzięki temu możesz odświeżać dane tylko np. w ostatnich partycjach, dzięki czemu masz najnowsze dane, ale nie przeliczasz za każdym razem całej tabeli w modelu. O tej technice będę pisał na blogu w najbliższym czasie.

SELECT 
	[TableID], 
	[Name], 
	[RefreshedTime], 
	[RangeStart],
	[RangeEnd]
FROM $SYSTEM.TMSCHEMA_PARTITIONS;

Metadane dotyczące miar użytych w modelu:

SELECT 
	[Name], 
	[Expression], 
	[DisplayFolder],
	[IsHidden], 
	[ModifiedTime]
FROM $SYSTEM.TMSCHEMA_MEASURES;

Szczególnie ciekawe są poniższe 2 zapytania, które pozwalają nam pobrać informację o tym, jak duży jest model.

SELECT DATABASE_NAME, DICTIONARY_SIZE 
FROM SYSTEMRESTRICTSCHEMA
($System.DISCOVER_STORAGE_TABLE_COLUMNS)


SELECT DATABASE_NAME, USED_SIZE
FROM SYSTEMRESTRICTSCHEMA
($System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS)

Być może do Twojej analizy wystarczy skorzystać tylko z wybranych obiektów DMV. Decyzję pozostawiam Tobie 🙂

Załaduj wszystkie wskazane przeze mnie tabele albo te, które są dla Ciebie istotne.

Model

W przykładowym rozwiązaniu nasz model, wygląda następująco:

W rozwiązaniu znajdziesz też zestaw metryk/ miar, które pozwoli Ci śledzić ilość tabel, czasy ich odświeżeń czy też definicje wszystkich miar, które zostały użyte w modelu.

W rozwiązaniu znajdziesz np. poniższe informacje:

Uwaga! Pamiętaj, że dane dotyczące daty i godziny odświeżenia po stronie Power BI Service są prezentowane w strefie czasowej UTC.

Miary DAX do obliczania wielkości modelu semantycznego

Ciekawą opcją jest śledzenie wielkości modelu. Wielkość modeli jest bardzo istotna jeśli pracujesz w dużym środowisku, nikt oczywiście nie chce w nadmierny sposób eksploatować Premium Capacity.

Tak jak wskazałem wyżej, użyjemy do tego tych 2 tabel:

Pierwszą z nich nazwałem sobie [SlownikUzycie]

SELECT DATABASE_NAME, DICTIONARY_SIZE 
FROM SYSTEMRESTRICTSCHEMA
($System.DISCOVER_STORAGE_TABLE_COLUMNS)

Drugą z kolei nazwałem [ModelUzycie]

SELECT DATABASE_NAME, USED_SIZE
FROM SYSTEMRESTRICTSCHEMA
($System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS)

Po ich załadowaniu warto stworzyć miary DAXowe, żeby móc obliczać sobie wielkość modelu w GB lub MB.

Wielkośc modelu MB = 
( SUM ( 'ModelUzycie'[USED_SIZE] ) + SUM ( 'SlownikUzycie'[DICTIONARY_SIZE] ) ) / 1048576

Wielkość modelu GB = 
( SUM ( 'ModelUzycie'[USED_SIZE] ) + SUM ( 'SlownikUzycie'[DICTIONARY_SIZE] ) ) / 1073741824

Nominalnie, dane z tych tabel są w bajtach, stąd konieczność podzielenia wartości sumy odpowiednich wielkości przez „te duże liczby” na końcu, żeby dojść do wielkości w MB lub GB.

Analiza modelu semantycznego Power BI – gotowy plik

Udostępniam Tobie plik, z którego możesz skorzystać, żeby podpiąć się pod swoje rozwiązanie i wiedzieć jak dynamicznie analizować metadane modelu Power BI. Skorzystanie z pliku jest dość proste. Użyłem tutaj z parametrów, więc wystarczy, że wprowadzisz tam swoje wartości i jeśli jesteś Adminem rozwiązania, będziesz mógł od razu podejrzeć ciekawe metryki dotyczące Twojego modelu.

Instrukcja obsługi gotowego pliku

Pobierz plik, do którego link, znajdziesz na końcu artykułu. Jest on spakowany jako archiwum *.zip. Po otwarciu wyskoczy poniższy komunikat, możesz go zamknąć „krzyżykiem”. Chodzi tutaj o parametry.

Po pobraniu rozwiązania, przejdź do edytora Power Query. Możesz to zrobić, klikając w dowolną tabelę i wybierając opcję [Edytuj zapytanie].

Po wejściu do Edytora będę na Ciebie dostępne 2 parametry, jak na obrazku:

Kliknij na każdy z nich i wybierz opcję [Zarządzaj parametrem]

Następnie wprowadź w polu [Wartość bieżąca] namiary Twój Serwer SSAS/AAS lub XMLA Endpoint (to ten adres, który ustalaliśmy we wcześniejszej części artykułu). W kolejnym kroku podaj nazwę modelu, który chcesz odpytać.

Następnie wybierz opcję [Zamknij i zastosuj] w prawym górnym rogu Edytora.

Po powrocie do raportu odśwież dane:

Po przepięciu się na Twoje rozwiązanie, pojawi się następujący komunikat:

Możesz spokojnie wybrać opcję [Uruchom], Power BI informuje po prostu, że nastąpi wysłanie zapytania do Twojego modelu. Komunikat wyskoczy dla każdej tabeli. Pojawi się też prawdopodobnie okno logowania, musisz się uwierzytelnić względem swojego rozwiązania, tak jak robisz to normalnie, w ramach swojej pracy.

Gotowe rozwiązanie w formie archiwum *.zip możesz pobrać stąd.

Podsumowanie

Myślę, że przedstawione rozwiązanie i plik- gotowiec, z którego możesz skorzystać mogą okazać się bardzo przydatne i pokazują jak dynamicznie analizować metadane modelu Power BI Online lub SSAS/ AAS. Sam zbudowałem podobne rozwiązanie, które pozwoliło mi szybko i łatwo monitorować kilkanaście modeli, które były dostępne na wielu środowiskach. Oczywiście możesz dalej samemu zgłębiać temat dotyczący DMV i samu eksplorować tabele zawierające różne metadane. Plik, który udostępniam możesz oczywiście rozszerzać i budować swoje rozwiązanie. Daj znać, czy podoba Ci się artykuł i czy skorzystasz z rozwiązania!

Prośba

Na koniec mam do Ciebie małą prośbę 🙂 Będę bardzo wdzięczny, jeśli zechciałabyś/ zechciałbyś wypełnić ankietę dotyczącą Twoich doświadczeń z moim blogiem. Jest on bardzo krótka, wypełnienie jej zajmie Ci około minuty, a mi pozwoli zebrać informację zwrotną i przedstawiać tematy, które Ciebie interesują. Ankietę znajdziesz tutaj. Dzięki! Do następnego!