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 https://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.
CREATE TABLE [dbo].[SSAS_Connections]( [CONNECTION_ID] [INT] NULL, [CONNECTION_USER_NAME] [NVARCHAR](255) NULL, [CONNECTION_IMPERSONATED_USER_NAME] [NVARCHAR](255) NULL, [CONNECTION_HOST_NAME] [NVARCHAR](255) NULL, [CONNECTION_HOST_APPLICATION] [NVARCHAR](255) NULL, [CONNECTION_START_TIME] [DATETIME] NULL, [CONNECTION_ELAPSED_TIME_MS] [BIGINT] NULL, [CONNECTION_LAST_COMMAND_START_TIME] [DATETIME] NULL, [CONNECTION_LAST_COMMAND_END_TIME] [DATETIME] NULL, [CONNECTION_LAST_COMMAND_ELAPSED_TIME_MS] [BIGINT] NULL, [CONNECTION_IDLE_TIME_MS] [BIGINT] NULL, [CONNECTION_BYTES_SENT] [BIGINT] NULL, [CONNECTION_DATA_BYTES_SENT] [BIGINT] NULL, [CONNECTION_BYTES_RECEIVED] [BIGINT] NULL, [CONNECTION_DATA_BYTES_RECEIVED] [BIGINT] NULL, [DiscoveryTime] [DATETIME] NULL )
Tabela do przechowywania danych o bieżących sesjach.
CREATE TABLE [dbo].[SSAS_Sessions]( [SESSION_ID] [NVARCHAR](255) NULL, [SESSION_SPID] [INT] NULL, [SESSION_CONNECTION_ID] [INT] NULL, [SESSION_USER_NAME] [NVARCHAR](255) NULL, [SESSION_CURRENT_DATABASE] [NVARCHAR](255) NULL, [SESSION_USED_MEMORY] [INT] NULL, [SESSION_PROPERTIES] [NVARCHAR](255) NULL, [SESSION_START_TIME] [DATETIME] NULL, [SESSION_ELAPSED_TIME_MS] [NUMERIC](20, 0) NULL, [SESSION_LAST_COMMAND_START_TIME] [DATETIME] NULL, [SESSION_LAST_COMMAND_END_TIME] [DATETIME] NULL, [SESSION_LAST_COMMAND_ELAPSED_TIME_MS] [NUMERIC](20, 0) NULL, [SESSION_IDLE_TIME_MS] [NUMERIC](20, 0) NULL, [SESSION_CPU_TIME_MS] [NUMERIC](20, 0) NULL, [SESSION_LAST_COMMAND_CPU_TIME_MS] [NUMERIC](20, 0) NULL, [SESSION_STATUS] [INT] NULL, [SESSION_READS] [NUMERIC](20, 0) NULL, [SESSION_WRITES] [NUMERIC](20, 0) NULL, [SESSION_READ_KB] [NUMERIC](20, 0) NULL, [SESSION_WRITE_KB] [NUMERIC](20, 0) NULL, [SESSION_COMMAND_COUNT] [INT] NULL, [DiscoveryTime] [DATETIME] NULL )
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.
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.
Select
SESSION_ID ,
SESSION_SPID ,
SESSION_CONNECTION_ID ,
SESSION_USER_NAME ,
SESSION_CURRENT_DATABASE ,
SESSION_USED_MEMORY ,
SESSION_PROPERTIES ,
SESSION_START_TIME ,
SESSION_ELAPSED_TIME_MS ,
SESSION_LAST_COMMAND_START_TIME ,
SESSION_LAST_COMMAND_END_TIME ,
SESSION_LAST_COMMAND_ELAPSED_TIME_MS ,
SESSION_IDLE_TIME_MS ,
SESSION_CPU_TIME_MS ,
SESSION_LAST_COMMAND_CPU_TIME_MS ,
SESSION_STATUS ,
SESSION_READS ,
SESSION_WRITES ,
SESSION_READ_KB ,
SESSION_WRITE_KB ,
SESSION_COMMAND_COUNT
from $System.discover_sessions;
select CONNECTION_ID ,
CONNECTION_USER_NAME ,
CONNECTION_IMPERSONATED_USER_NAME ,
CONNECTION_HOST_NAME ,
CONNECTION_HOST_APPLICATION ,
CONNECTION_START_TIME ,
CONNECTION_ELAPSED_TIME_MS ,
CONNECTION_LAST_COMMAND_START_TIME ,
CONNECTION_LAST_COMMAND_END_TIME ,
CONNECTION_LAST_COMMAND_ELAPSED_TIME_MS ,
CONNECTION_IDLE_TIME_MS ,
CONNECTION_BYTES_SENT ,
CONNECTION_DATA_BYTES_SENT ,
CONNECTION_BYTES_RECEIVED ,
CONNECTION_DATA_BYTES_RECEIVED
from $System.discover_connections;
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.
USE [msdb]
GO
/****** Object: Job [get_and_load_ssas_sessions_and_connections] Script Date: 2017-07-25 10:59:02 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 2017-07-25 10:59:02 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'get_and_load_ssas_sessions_and_connections',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Get and load SSAS sesssions and connections to check who is accesing the cube',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Get and load data] Script Date: 2017-07-25 10:59:02 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get and load data',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/ISSERVER "\"\SSISDB\ssas_log\ssis_get_ssas_sessions_and_connections\GetSSASSessionsAndConnections.dtsx\"" /SERVER "\".\sql2016\"" /X86 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Everyday_evey_10_minutes',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20170725,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'27a70b88-412f-4af0-9cda-759e5c862718'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
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:
X Customers = DISTINCTCOUNT(SSAS_Sessions[User name])
X Returning Customers =
COUNTROWS(
CALCULATETABLE(
VALUES(SSAS_Sessions[User name]),
VALUES(SSAS_Sessions[User name]),
FILTER(
ALL(SSAS_Sessions),
SSAS_Sessions[Discovery time] < MIN(SSAS_Sessions[Discovery time])
)
)
)
X New Customer = [X Customers] - [X Returning Customers]
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:
hist(dataset$"Discovery time date", labels=TRUE, col="green")
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:
histPercent <- function(x, ...) {
H <- hist(x, plot = FALSE)
H$density <- with(H, 100 * density* diff(breaks)[1])
labs <- paste(round(H$density), "%", sep="")
plot(H, freq = FALSE, labels = labs, ylim=c(0, 1.08*max(H$density)),...)
}
histPercent(dataset$"Discovery time date", col="gray")
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.
- Docker dla amatora danych – Tworzenie środowiska (VM) w Azure i VirtualBox (skrypt) - April 20, 2020
- Power-up your BI project with PowerApps – materiały - February 5, 2020
- Docker dla “amatora” danych – kod źródłowy do prezentacji - November 18, 2019
I co chodzi w funkcji RETURNING CUSTOMERS? dlaczego jest dwa tacy VALUES?