[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.

Publié dans Analysis Services
One comment on “[SSAS] Exploiter et déchiffrer OlapQueryLog pour créer des rapports d’utilisation d’un cube
  1. arnaud dit :

    Outil très utile pour nous! Pas besoin de réinventer la roue, tout est fait par vos soins! Merci
    Arnaud

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

Microsoft
Communauté
PASS
Twitter
Follow La BI et les outils Microsoft on WordPress.com
%d blogueurs aiment cette page :