Remplacer OlapQueryLog avec SSAS 2016 Tabular

Comme moi vous utilisiez peut-être l’OLAPQueryLog afin de suivre l’utilisation de vos cubes, c’était un peu le petit plus pas cher de cette fonctionnalité qui permettait de construire nos agrégations sur multidim… Or avec Tabular… plus d’agrégations et plus d’OlapQueryLog…

Comment faire pour obtenir un suivi de notre usage de cube et si possible pour pas trop cher en terme de performance ? Les Extended Event semblent être la réponse, sauf que comme d’habitude ce n’est pas simple à utiliser, voici donc la trace que j’utilise :

Pour la mise en place de ma session :

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <ObjectDefinition>
 <Trace>
 <ID>Users</ID>
 <Name>Users</Name>
 <XEvent xmlns="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
 <event_session name="Users" dispatchLatency="1" maxEventSize="4" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowMultipleEventLoss" trackCausality="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <event package="AS" name="QueryBegin" />
 <event package="AS" name="QueryEnd" />
 <target package="package0" name="event_file">
 <parameter name="filename" value="E:\Program Files\Microsoft SQL Server\MSAS13.INSTANCE1\OLAP\Log\Users.xel" />
 <parameter name="max_file_size" value="10240" />
 <parameter name="max_rollover_files" value="10" />
 <parameter name="increment" value="1024" />
 </target>
 </event_session>
 </XEvent>
 </Trace>
 </ObjectDefinition>
</Create>

Je récupère les événements « QueryBegin » et ‘QueryEnd » car l’un contient les instructions DAX/MDX et l’autre la durée de la requête.

Je peux ensuite requêter le fichier *.xel généré via cette requête (à personnaliser en fonction de vos besoins) :

SELECT
 n.value('(@name)[1]', 'varchar(50)') AS event_name,
 DATEADD(hh,
 DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
 n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
 n.value('(data[@name="TextData"]/value)[1]', 'varchar(max)') as Query,
 n.value('(data[@name="Duration"]/value)[1]', 'varchar(max)') as Duration,
 n.value('(data[@name="NTCanonicalUserName"]/value)[1]', 'varchar(max)') as username,
 CAST(REPLACE(n.value('(data[@name="RequestProperties"]/value)[1]', 'varchar(max)'),'xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"','') AS XML).value('(/PropertyList/EffectiveUserName)[1]', 'varchar(max)') AS EffectiveUserName,
 CAST(REPLACE(n.value('(data[@name="RequestProperties"]/value)[1]', 'varchar(max)'),'xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"','') AS XML).value('(/PropertyList/Catalog)[1]', 'varchar(max)') AS [Catalog],
 CAST(REPLACE(n.value('(data[@name="RequestProperties"]/value)[1]', 'varchar(max)'),'xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"','') AS XML).value('(/PropertyList/Cube)[1]', 'varchar(max)') AS [Cube],
 CAST(REPLACE(n.value('(data[@name="RequestProperties"]/value)[1]', 'varchar(max)'),'xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"','') AS XML).value('(/PropertyList/sspropinitappname)[1]', 'varchar(max)') AS [ApplicationName],
 CAST(n.value('(data[@name="RequestProperties"]/value)[1]', 'varchar(max)') AS XML)
--INTO #Tmp
FROM 
(SELECT
 CAST(event_data AS XML) AS event_data
 FROM sys.fn_xe_file_target_read_file('E:\Program Files\Microsoft SQL Server\MSAS13.INSTANCE1\OLAP\Log\*.xel', null, null, null)
) as tab
CROSS APPLY event_data.nodes('event') as q(n)

Vous noterez l’importance toute particulière que j’ai porté au noeud « RequestProperties » c’est tout simplement car en DirectQuery via PowerBi il s’agit du seul moyen afin de récupérer l’EffectiveUserName qui va me permettre de connaitre le login de mon utilisateur et de faire des analyses sur le nombre distinct d’utilisateurs de mes rapports PowerBI, d’identifier les utilisateurs avec les requêtes les plus gourmandes…