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.
