Agrégations "Usage-Based" dans SSAS sans utiliser le QueryLog

C’est quoi les agrégations Usage-Based  ?

Quand on développe un cube multidimensionnel, les agrégations sont essentielles. Une bonne pratique est de mettre en place ces agrégations en se basant sur l’usage réel du cube.

C’est ce qu’on appelle Usage-Based Optimization. On place des agrégations uniquement sur les dimensions (attributs) et les granularités réellement utilisées par les utilisateurs.

Pour le mettre en place il faut :

  1. Activer les logs (QueryLog)
  2. Lancer l’assistant Usage Based Optimization qui va créer les bonnes agrégations

Pour la première étape, il suffit d’aller dans les propriétés de votre instance SSAS et de configurer le QueryLog. Si SSAS trouve une chaine de connexion, il tracera l’usage, sur la base d’une requête tracée toutes les [QueryLogSampling] requêtes exécutées.

clip_image001
Source Technet : https://technet.microsoft.com/library/Cc917676

Le paramétrage du QueryLog est clé car il est utilisé également par le wizard d’optimisation qui va lire la table indiquée.

clip_image002

Peut-on se passer du QueryLog ?

La réponse est simple : non. Dans tous les cas, l’assistant ne sait pas récupérer les données d’usage autre part que dans cette table. Mais souvent, on conçoit ces agrégations sur un environnement d’intégration ou de développement (pour ne déployer que les agrégations dans un XMLA assez simple). On peut par exemple copier la table ou les données du QueryLog depuis la production pour la lire sur un autre environnement.

Voici le script pour créer la table :

CREATE TABLE OlapUsageLog
(
  MSOLAP_Database Nvarchar(255),
  MSOLAP_ObjectPath Nvarchar(400),
  MSOLAP_User Nvarchar(255),
  Dataset Nvarchar(4000),
  StartTime datetime,
  Duration Bigint
)

Et si on utilisait le profiler ?

Ok, j’ai mon QueryLog configuré pour mon assistant d’optimisation mais disons que je ne veux pas l’activer en production pour collecter les données. L’idée est d’utiliser le Profiler pour collecter les données et ensuite de les réinjecter dans la table en développement.

Contrairement à ce qu’on pourrait penser, on de trace pas les requêtes; ce n’est pas ce qui sert à l’optimiseur. En fait, c’est une vision « interne » de la requête qui nous intéresse (un peu comme le plan d’exécution côté SQL Server). Ce qu’il nous faut c’est “quelles dimensions sont utilisées.”

Pour cela, on trace l’événement Query Subcube dans Query Processing. Les champs à récupérer sont :

  • DatabaseName correspond à la colonne MSOLAP_Database
  • Duration
  • ObjectPath correspond à la colonne MSOLAP_ObjectPath
  • StartTime
  • TextData correspond à la colonne Dataset
  • NTCanonicalUserName correspond à la colonne MSOLAP_User

Le champ important est Textdata (Dataset) qui est le vecteur dimensionnel de la requête (ie. quels attributs sont positionnés). Le champ ObjectPath servira à pointer sur le bon groupe de mesure.

clip_image003

On peut voir qu’on a le même résultat que le QueryLog sauf qu’on a la souplesse du Profiler.

clip_image004

 

Les avantages :

  • Vous pouvez mettre des filtres (utilisateur, base, requêtes longues, etc.)
  • Vous pouvez démarrer/arrêter une séquence de profiling facilement (pour capturer une séquence de test particulière)
  • Vous n’avez plus besoin du QueryLog (et d’une base SQL Server) en production.

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