SSAS – Kto korzysta z kostki?

SSAS new vs returning users

Pierwszy przypadek wykorzystania danych z logowania kostek i serwera dotyczy analizy użytkowników. Do wizualizacji danych użyty zostanie PowerBI. W ramach przypomnienia dodam tylko, że post opisujący dostępne metody gromadzenia danych oraz analizowania kostek i SQL Server Analysis Services dostępny jest pod adresem http://pl.seequality.net/monitorowanie-optymalizacja-ssas/. Pełny raport oraz projekt SSIS dostępny jest na github: https://github.com/seequality/seequality_ssas

Cała idea polega na tym, aby przygotować projekt SSIS, a następnie Job, który co dziesięć minut będzie pobierał aktualną listę sesji i połączeń do serwera SQL Server Analysis Services. Dzięki zgromadzonym danym będziemy w stanie następnie przygotować raport w Power BI, który pomoże nam zrozumieć kim są użytkownicy kostki oraz czy kostka jest faktycznie używana. Analiza taka może być szczególnie przydatna wtedy, gdy staramy się:

  • zrozumieć kto konkretnie używa kostki. Dzięki możliwości połączenia do Active Directory oprócz konkretncyh nazw użytkowników jesteśmy w stanie uzyskać inforamcje o dziale pracownika i jego miejsca w strukturze organizacyjnej
  • zrozumieć czy kostka jest rzeczywiście używana w takim stopniu jak tego chcemy. Jeżeli okaże się, że kostka jest używana rzadko może to oznaczać, że albo użytkownicy są niezadowoleni z tego produktu, albo nie jest on wystarczająco promowany
  • sprawdzić w jakim czasie kostka jest używana
  • poznać najaktywniejszych użytkowników oraz takich, którzy wykonują najcięższe zapytania

Rozpoczniemy oczywiście od utworzenia miejsca w którym będziemy składować dane.

Tabela do przechowywania danych o bieżących połączeniach.

Tabela do przechowywania danych o bieżących sesjach.

Następnie możemy przygotować bardzo prosty pakiet w SQL Server Integration Services, który będzie w stanie połączyć się do SSAS i pobrać informacje o bieżących sesjach i połączeniach.

This slideshow requires JavaScript.

Pakiet jest dość prosty. Dwa parametry przechowują adresy do serwera SSAS i do serwera w którym będziemy zapisywać dane z logami. Na samym początku pobieramy aktualny czas i zapisujemy do zmiennej. Zmienna ta posłuży jako klucz dla tabel i będzie pomagała w rozpoznaniu pojedynczego wykonania pakietu. Następnie dwa przepływy połączą się do SQL Server Analysis Services i wykonają zapytania MDX wykorzystujące widoki systemowe.

W celu zminimalizowania narzutu na serwer oraz zminimalizowania rozmiaru tabeli z logami w tym przypadku nie będzie zapisywana treść samego zapytania. Po dodaniu aktualnego czasu do przepływu z danymi, dane zostają zapisane do bazy danych. Tak przygotowany projekt można następnie opublikować do SSIS Catalog. Kolejnym krokiem będzie dodanie Job’a do SQL Server Agent.

Job będzie nieustannie co 10 minut sprawdzał kto jest połączony do serwera. Oczywiście jeżeli w nocy nikt nie korzysta z kostki powinniśmy dostosować harmonogram, aby zminimalizować narzut. Po kilku tygodniach można przystąpić do analizy danych.

W tym przypadku wykorzystamy Power BI Desktop jako warstwę wizualizacyjną oraz produkt do analizy danych, które pomogą nam zrozumieć kto korzysta z kostek i jak często. Poniżej przykład raportu, który można przygotować. Dane zostały wygenerowane automatycznie.

Number of distinct users over the months – liczba unikalnych użytkowników w poszczególnych miesiącach. Analiza powinna pomóc w zrozumieniu czy liczba osób korzystającyh z kostki rośnie lub maleje. Oczywiście, aby zrozumieć pełny obraz należy również uwzględnić zmiany struktuaralne w organizacji.

New vs Returning Users – analiza nowych i powracających użytkowników. Oczywiście na początku gromadzenia logów każdy użytkownik będzie traktowany jako nowy, natomaist wraz z upływem czasu liczba nowych użytkowników powinna w idealnym scenariuszu odpowiadać liczbie nowo przyjętych pracowników lub pracowników, którzy w danym momencie otrzymali dostęp do kostki. Ta, dość nietrywialna analiza, dizęki językowi DAX sprowadza się do dodania trzech prostych miar:

Session Overview – raport poglądowy, który obrazuje podsumowanie logów sesji użytkwoników. Pozwala zrozumieć, który użytkownik wykonuje najwięcej zapytań, ilu jest użytkowników, ile średnio wykonują zapytań i ile średnio spędzają czasu przeglądając kostkę. W formie tabeli można sprawdzić szczegółowe dane dotyczące sesji dla każdego użytkownika.

Usage over time – analiza używania kostki na przestrzeni czasu. Trzy różne osi czasu pokazują trzy różne perspektywy. Na samej górze możemy sprawdzić średnią liczbę zapytań do kostki, następnie liczbę otwartych sesji oraz liczbę unikalnych użytkowników. Jak widać na powyższym przykładzie użytkownicy korzystają z kostki nierównomiernie, a w maju dało się zaobserwowoać zdecydowanie większą liczbę zapytań w dwóch konrketnych dniach.

Heavy users – analiza “najcięższych użytkowników”. Filtr “Command Count” pozwala na analizę tylko tych użytkwoników, którzy wykonali więcej niż 500 zapytań, dzięki czemu możemy zignorować jednorazowych użytkowników. Nastęnie porównując średnie czasy zapytań oraz ich obiążenia dla serwera możemy znaleźć użytkowników, którzy produkują najcięższe i najbardziej kosztowne raproty. Dobrym pomysłem może być rozmowa z takimi osobami i zastanowienie się czy rzeczywiście prawidłowo korzystają z kostki i Excela czy potrzebują dodatkowych warsztatów.

Users day’s spent in the cube – kolejna analiza, która pomaga zrozumieć w jaki sposób korzystali użytkownicy z kostki na przestrzeni czasu

Users day’s count – analiza liczby użytkowników. Dzięki wykorzystaniu pakietu R możemy jeszcze dokładniej zobaczyć jak często użytkownicy łączą się z kostką. Na powyższym przykładzie logi odnoszą się do 90 dni, natomiast aż 33 użytkowników korzystało z kostki w tym czasie przez mniej niż 10 dni, a kolejne 17 użytkowników korzystało z kostki częściej niż w 10 różnych dni, ale rzadziej niż w 20 dni. Łącznie, 50 użytkowników – 70% wszystkich – korzystało z kostki rzadziej niż przez 20 dni – 22 procent czasu gromadzenia logów. W tym przypadku należałoby się zastanwoić czy dla danego serwera i danej kostki ta liczba użytkowników jest duża czy mała.

Skrypt R to tylko jedna linijka:

Users day’s count (percent) – analiza podobna do poprzedniej z tym, że pokazuje procentowo liczbę użytkowwników w konkretnych “kategoriach”.

Kod R w tym przypadku może wyglądać tak:

Users day’s count (filter) – wartro również pamiętać, że skrypty R, które znajdują się w Power BI można również filtrować oraz łączyć z innymi elementami raprotów. Dzięki temu możemy jeszcze dokładniej analizować użycie kostki – tutaj histogram będzie generowany dla wybranego miesiąca.

Prosty projekt SSIS jest w stanie połączyć się do kostki oraz zebrac informacje o sesjach i połączeniach do serwera SSAS, a co za tym idzie o użytkownikach. Wykorzystując Power BI oraz R jesteśmy natomaist w stanie zrozumieć kim są użytkownicy, jak często korzystają z kostki i kiedy. Dodając do raportu dane z Active Directory bylibyśmy również w stanie analizować użytkowników z podziałem na strukturę organizacji.

Slawomir Drzymala
Follow me on

Slawomir Drzymala

Still playing with data and .NET technologies
Slawomir Drzymala
Follow me on

Leave a Comment

Your email address will not be published. Required fields are marked *