[SSAS] Exploiter et déchiffrer OlapQueryLog pour créer des rapports d'utilisation d'un cube

Nous avons souvent été confrontés à des clients voulant des rapports d'utilisation d'un cube, c'est-à-dire savoir qui utilise telle ou telle dimension ou groupe de mesures.
Et nous avons tous remarqué après avoir activé l'OlapQueryLog que les dataset étaient stockés sous la forme d'une suite de 0 et de 1.
Nous allons voir comment analyser et rendre visible ces résultats.

Première étape :

Il faut activer les logs de requêtages du cube sous Analysis Services (désactivés par défaut).
Pour cela, on doit aller dans les propriétés du serveur AS, et configurer les propriétés suivantes :

• Log \ QueryLog \ CreateQueryLogTable

Active ou désactive les logs (true ou false)

• Log \ QueryLog \ QueryLogConnectionString

Chaine de connexion à votre table de log

• Log \ QueryLog \ QueryLogSampling

Entier n indiquant que vous enregistrez dans votre table toutes n-requêtes

• Log \ QueryLog \ TableName 

Nom de votre table de log

Par exemple :



A partir de là, la table OlapQueryLog commence à se remplir au fur et à mesure des interrogations du cube. Mais comme on peut le voir, le champ dataset de cette table n'est pas exploitable en l'état :

Deuxième étape :

Elle consiste à rendre lisible les données de la table OlapQueryLog. Pour cela, il est important de comprendre le champ dataset. La suite de 0 et de 1 correspond à la séquence d'utilisation des attributs d'une dimension pour un groupe de mesures donné.

La première chose à faire est de charger la structure du cube dans une table, grâce par exemple à ce package SSIS (La manipulation est à refaire à chaque nouveau déploiement du cube, celui-ci réinitialisant la table OlapQueryLog). Il suffit de configurer les 4 variables :

• CubeServer : adresse de votre serveur Analysis Services (eviter 'localhost' et préférer le nom de la machine)
• InitialCatalog : nom de la base de données où vous voulez stocker votre table de structure
• Server : adresse du serveur SQL où vous voulez stocker votre table de structure
• TableName : nom de votre table de structure

Après exécution du package votre table aura la forme suivante :

Il suffit maintenant de dépiler les 0 et les 1 de notre dataset original. Ce que l'on peut faire avec la procédure stockée suivante :

CREATE proc [dbo].[LoadOlapQueryLog] (@User nvarchar(255)=NULL,@Cube varchar(100)=NULL,@Date datetime=NULL)

AS

CREATE TABLE TableCompteur ([number] [int] NOT NULL)

DECLARE @LongMax int

DECLARE @j int

select @LongMax=MAX(LEN(dataset)) from OlapQueryLog

set @j=1

WHILE (@j<=@LongMax)

BEGIN

INSERT INTO TableCompteur(number) values (@j)

SET @j=@j+1

END

select cs.[Server] ,

cs.[Database],

cs.[Cube],

cs.MeasuresGroupName,

cs.Dimension,

cs.Attribute,

OQL.MSOLAP_User,

StartTime,

Duration

from dbo.OlapQueryLog OQL

INNER JOIN TableCompteur

on TableCompteur.number<=LEN(OQL.dataset)

INNER JOIN CubeStructure CS

ON CS.[OLAPPATH]=OQL.MSOLAP_ObjectPath

AND CS.AttributeOrder = TableCompteur.number

WHERE substring(OQL.Dataset,TableCompteur.number,1)='1'

and (@User is null or OQL.MSOLAP_User=@User)

and (@Cube is null or cs.[Cube]=@Cube)

and (@Date is null or cast(StartTime as date)=cast(@Date as date))

DROP TABLE TableCompteur

RETURN

Après exécution de cette procédure vous obtenez la table suivante :

Elle donne bien la liste des différents attributs utilisés dans le cube de manière lisible et exploitable. Il ne vous reste plus qu'à créer des rapports se basant dessus pour diffuser les informations.