[SSAS 2012] Croiser les niveaux fins des dimensions avec Analysis Services en mode Tabular

Après deux articles sur les termes en vogue du moment, un petit retour chez Microsoft s'impose, et notamment sur SQL Server 2012 sorti cette année.
La nouvelle version d'Analysis Services a apporté un nouveau mode qui provient directement de PowerPivot : le mode Tabular.

On ne reviendra pas sur les différences entre les versions Tabular et Multidimensionnal (toutes deux disponibles dans SQL Server 2012), ni sur le mode à adopter suivant les projets, de nombreux articles de blogs en parlent. De plus cela a fait l'objet d'une session aux TechDays de cette année (lien).

On va s'intéresser ici aux performances lors de l'interrogation d'un cube au format Tabular. En effet, ce mode  a aussi été introduit pour répondre à la problématique d'interrogation des données fines via un cube.
Avant la sortie de SQL Server 2012, la réponse typique du consultant lorsque le client demandait un rapport croisant plusieurs niveaux fins de plusieurs dimensions était : "un cube Analysis Services n'est pas fait pour ça" (note de l'auteur : ce qui en soit n'est pas faux).

On va donc effectuer un petit test rapide :

On crée deux cubes "identiques" en multidimensionnel et en tabulaire : un cube avec une mesure CA, 3 dimensions (client, produit et magasin) et 1 million de lignes.

Sur ces cubes on va appliquer la requête MDX suivante (au nommage des objets entre les deux cubes près) :

Les résultats obtenus sont les suivants :

Multidimensionnel : 28s pour l'exécution de la requête :

Tabulaire : 29s pour l'exécution de la requête :

Mais alors finalement, il n'y a pas de différence selon le moteur utilisé ?

En fait la différence se fait par rapport au langage utilisé : MDX versus DAX. Et le DAX ne peut être utilisé (aujourd'hui) qu'avec les modèles tabulaires.

Si vous attaquez votre cube en MDX, vous ne constaterez que peu de différence de performance entre les deux modes. En revanche si vous traduisez votre requête MDX en DAX sur votre modèle tabulaire, vous allez constater une nette amélioration.

Sur mon modèle tabulaire, j'exécute la requête suivante (qui renvoie le même résultat que ma requête MDX) :

On obtient une durée d'exécution de 9s (Notez que c'est à 1s près le temps d'exécution de la requête SQL pour sortir le même résultat sur les tables sources) :

On constate bien le gain appréciable de performance obtenu.

En conclusion si vous avez opté pour le mode multidimensionnel, pas de changement d'attitude à avoir, c'est encore le langage MDX que vous devrez utiliser (du moins pour l'instant).

En revanche si vous avez opté pour le mode tabulaire, il peut être vraiment intéressant de se pencher sur le langage DAX dès l'instant où vous interrogez des données fines dans les dimensions.

[SSAS] Ajouter de l'intelligence à votre cube Analysis Services (Year To Date YTD, Month To Date MTD,...)

Un article que je voulais faire depuis longtemps, donc je profite du temps que j'ai devant moi.
On donne toujours comme avantage à un cube par rapport à une base de données relationnelle, sa facilité à naviguer sur les dates.
De là découle une certaine facilité à créer des membres calculés en MDX tels que des Year To Date, des Month To Date, des 3 Jours Glissants, etc... bien qu'ils ne soient pas intégrés par défaut.

On va montrer comment les intégrer dans un cube SSAS en moins de temps qu'il ne faut pour le dire.

Conseil :
Je recommande de faire la manipulation sur un cube finalisé. Ca vous évitera de mettre les mains dans le code MDX pour rajouter des mesures ou modifier des noms.

 Processus :

• Ouvrir sa solution Analysis Services, et aller sur le cube.
• Cliquer sur "Add Business Intelligence"

• Dans la fenêtre qui s'ouvre, cliquer sur "Next", puis sélectionner "Define Time Intelligence" et cliquer sur "Next"

• Choisir ensuite une hiérarchie et le type de mesures d'analyse temporelle que vous voulez ajouter.

Remarques :

Les indicateurs créés dépendent de la hiérarchie. Il faut donc recommencer l'opération autant de fois que l'on a de hiérarchies date.

Votre dimension doit être définie comme une dimension date en spécifiant bien les niveaux (plus il y a de niveaux définis, plus le choix de mesures à rajouter est grand)

• Dans l'exemple, j'ai choisi la hiérarchie "Calendar", pour un YTD, un MTD et un 12 Mois Glissants.

• Sélectionner les mesures sur lesquelles vous voulez appliquer ses analyses. Dans l'exemple, je choisis "Internet Sales Amount" et "Internet Order Quantity", puis cliquer sur "Finish", puis encore sur "Finish" dans la fenêtre d'après.

Le travail est terminé, on va maintenant regarder comment se présentent les résultats.

Dans notre hiérarchie Temps que l'on a utilisée, un nouvel attribut nommé "Calendar Date Calculations" est disponible. Il prend pour valeur les 3 analyses que l'on a sélectionnées lors de la création (YTD, MTD, 12 mois glissants) plus la période courante (qui correspond à la valeur à date, c'est-à-dire sans analyse)

Mais qu'est-ce qui s'est passé concrètement ?

Dans le DataSourceView, l'assistant a créé une Named Calculation nommé "Calendar Date Calculations" qui correspond à notre attribut d'analyse qui vaut "Current Date".

L'assistant a créé ensuite le code MDX qui initialise notre nouvel attribut pour les valeurs YTD, MTD et 12 mois glissants. Puis fait un scope sur les mesures que l'on a sélectionnées avec les formules adéquates.

On imagine aisément que l'on peut s'appuyer sur ce code MDX pour créer des analyses qui ne seraient pas présentes dans l'assistant.

Conclusion :

Normalement vos utilisateurs seront friands de ce genre d'implémentation. Et vu le temps de mise en oeuvre, il serait dommage de les en priver.

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