Disclaimer : j’ai écrit cet article en 2010 et il était hébergé sur le site de mon ancienne boite, Bewise. Malheureusement, le site de Bewise n’existe plus et comme on me demande encore cet article, je le reposte ici.

 

Introduction

Dans cet article, nous allons étudier quelques cas simples nécessitant d’avoir recours à une requête MDX. Il est destiné aux développeurs décisionnels qui pourront s’en aider pour créer des membres calculés ou concevoir des rapports plus complexes par exemple.
Vous devez connaître le MDX pour cet article. Le langage MDX est le langage de requêtage des bases de données multidimensionnelles (les cubes pour faire simple). Il est utilisé par SQL Server Analysis Services (et dans sa forme « standard » par Hyperion). Le but de cet article n’est pas de vous apprendre le MDX. L’apprentissage de ce langage prend du temps car il est assez complexe (on doit penser en n-dimensions).
J’irai donc à l’essentiel en proposant des « uses cases » concrets et opérationnels.

 

Quelques rappels MDX

Juste quelques rappels pour la bonne lecture de cet article :

A la base, un SELECT

La base d’une requête MDX est une instruction SELECT. Il est bien plus complexe qu’en SQL mais nous allons essayer de le formater toujours de cette façon :

SELECT
{<Liste de mesures>} on 0,
<dimension1> * <dimension2> * <dimension-n> on 1
FROM [Cube]
WHERE { <filtre> }

 

Set et tuple

Un Set pointe un ou plusieurs membres de dimension. Sa notation utilise les accolades {}.
Ex : { [Date].&[1234] } ou { [Géo].[Pays].[France] , [Géo].[Pays].[Allemagne] ]

Un tuple est le croisement de dimensions et d’une mesure. On peut l’assimiler à une cellule du cube. Sa notation utilise les parenthèses ().
Ex : ([Date].&[1234], [Measures].[Qté Vendue])

 

Membre calculé

Pour nos requêtes nous aurons besoin de membres calculés. Evidemment, nous n’allons pas les créer dans le cube mais les injecter dans notre session.
Pour cela, il faut utiliser la syntaxe suivante :

WITH MEMBER [xxx] AS <MDX statement>
SELECT …

 

La base de données d’exemple

Je me base sur la base Adventure Works que vous pouvez trouver sur CodePlex. Il existe un DatawareHouse (AdventureWorks2008DW) et un projet Analysis Services qui vous permettra de déployer le cube.
Ce projet est complet mais complexe car il fait intervenir toutes les notions et subtilité de SSAS. Sans rentrer dans le détail, il vous permettra d’avoir un cube à disposition sans avoir à maîtriser toutes ces notions.
Les téléchargements sont à cette adresse : http://sqlserversamples.codeplex.com/#ssas

 

Les requêtes

PROPERTIES()

Utilisation :

Properties() est sans doute la fonction MDX que j’utilise le plus souvent avec CurrentMember ; principalement parce que l’un ne va pas sans l’autre. Properties permet de récupérer une propriété sur un membre de dimension.

On distingue les propriétés natives (dites intrinsèques), inhérentes à tout membre comme son nom unique, sa valeur, etc. mais aussi les propriétés définies à la construction du cube. On peut ainsi récupérer un autre attribut ou une valeur liée au membre.

Exemple :

Dans le premier exemple, on récupère l’attribut Day Of Year pour lier le n° du jour dans l’année à la date remontée sans faire de croisement de dimension.

WITH MEMBER JourDansAnnee AS
[Date].[Calendar].CurrentMember.Properties("Day Of Year")
SELECT
{[JourDansAnnee], [Measures].[Internet Sales Amount]} ON 0,
[Date].[Calendar].AllMembers ON 1
FROM [Adventure Works]

Dans le second exemple, on récupère une propriété intrinsèque qui est la valeur du membre, fortement typée ; ici, on ramène la date.

WITH MEMBER [DateTypée] AS
[Date].[Calendar].CurrentMember.Properties("Member_Value", TYPED)
SELECT
{[DateTypée], [Measures].[Internet Sales Amount]} ON 0,
[Date].[Calendar].AllMembers ON 1
FROM [Adventure Works]

Evidemment pour ce second exemple, nous aurions pu écrire :

[Date].[Calendar].CurrentMember.MEMBER_VALUE 

L’usage le plus fréquent que j’en fais est dans la construction d’un paramètre dans Report Builder. En effet, il faut non par récupérer le label du membre mais son nom unique pour l’injecter dans la requête MDX. J’utilise donc la propriété MEMBER_UNIQUE_NAME de cette façon :

WITH
MEMBER [ParameterCaption] AS [Date].[Calendar].CurrentMember.NAME
MEMBER [ParameterUniqueName] AS [Date].[Calendar].CurrentMember.UNIQUENAME
SELECT NON EMPTY {[ParameterCaption],[ParameterUniqueName]} ON 0,
[Date].[Calendar].AllMembers ON 1
FROM [Adventure Works]

 

Note : On a toujours tendance à naturellement tout définir comme attribut d’une dimension, pensez que si vous avez uniquement besoin d’une valeur pour de l’affichage, configurez là comme une propriété (cf. l’article suivant : https://blog.djeepy1.net/2010/05/21/tuning-des-attributs-de-dimension-dans-analysis-services/)

 

PARALLELPERIOD

Utilisation :

La fonction ParallelPeriod permet de récupérer un Set particulier dans une dimension temps. Plus précisément, ParallelPeriod permet de récupérer les membres équivalents à un Set donné mais sur une période antérieure ou future. Par exemple, si je suis sur janvier et février 2008, je peux récupérer janvier et février sur une autre année.

Cette fonction est spécifique aux dimensions temporelles qu’Analysis Services traitent de façon spéciale. Elle fonctionne sur les hiérarchies définies par l’utilisateur.

On lui passe les arguments suivants :

PARALLELPERIOD( Niveau, Nb de période à remonter, Set)

• Le niveau dans la hiérarchie, l’année par exemple
• Le nombre de saut à faire sur ce niveau (chiffre négatif pour aller dans le futur)
• Les éléments que l’on souhaite transpose

Exemple :

Dans cet exemple, on récupère le même jour mais sur le trimestre précédent (1^er avril à 1^er janvier) :

SELECT
NON EMPTY {} ON 0,
PARALLELPERIOD([Date].[Calendar].[Calendar Quarter], 
   1, [Date].[Calendar].[Date].&[20070401]) on 1
FROM [Adventure Works]

Dans cet autre exemple, on récupère le tuple correspondant au même jour l’année précédente :

WITH MEMBER [N-1] AS
( 
    PARALLELPERIOD([Date].[Calendar].[Calendar Year], 
    1, [Date].[Calendar].CurrentMember), [Measures].[Sales Amount]
)
SELECT
{[Measures].[Sales Amount], [N-1]} ON 0,
[Date].[Calendar].[Date].AllMembers ON 1
FROM [Adventure Works]

Cet exemple est le plus utilisé pour faire des calculs de progression sur des périodes ou des KPI.

Dans le même genre :

Quand on traite avec d’autres dimensions qui ne sont pas temporelles, on peut utiliser la fonction Cousin qui permet de naviguer de la même façon dans une hiérarchie.

 

YTD et PERIODSTODATE

Utilisation :

YTD est l’acronyme de Year To Date. Encore une fois, cette fonction est spécifique aux dimensions de temps.

Cette fonction a pour but de ramener un Set contenant l’ensemble des membres sur un niveau d’une hiérarchie jusqu’à un certain membre en commençant par le premier membre de l’année. Par exemple, elle ramènera l’ensemble des dates entre le 1^er janvier et une date donnée.

On utilise principalement cette fonction pour faire des cumuls ; et souvent en conjonction avec ParallelPeriod pour faire des comparaisons date à date (comme un chiffre d’affaire à date et son N-1).

PeriodsToDate est la version générique de cette fonction qui permet de la généraliser à toute échelle de temps.

Exemple :

Dans cet exemple, on ramène l’ensemble des dates entre le 1^er janvier et le 8 mai 2006

SELECT
NON EMPTY {} ON 0,
YTD([Date].[Calendar].[Date].&[20060508]) ON 1
FROM [Adventure Works]

Dans cet autre exemple, on calcule le cumul des ventes sur les semaines de la dimension :

WITH MEMBER [SalesYTD] AS
AGGREGATE(
   YTD([Date].[Calendar Weeks].CurrentMember),
   [Measures].[Sales Amount]
)
SELECT {[Measures].[Sales Amount], [SalesYTD]} ON 0,
[Date].[Calendar Weeks].[Calendar Week].AllMembers ON 1
FROM [Adventure Works]

 

Dans le même genre :

On trouve aussi les fonctions MTD et QTD respectivement pour Month To Date et Quarter To Date qui, vous l’aurez compris font la même chose sur un mois ou un trimestre.

 

TAIL et HEAD

Utilisation :

Ces fonctions permettent de récupérer les n-premiers ou les n-derniers éléments d’un Set. On peut l’utiliser pour récupérer les 6 derniers mois par exemple.

Exemple :

SELECT
NON EMPTY {} ON 0,
TAIL([Date].[Calendar].[Date].Members, 6) ON 1
FROM [Adventure Works]

Dans le même genre :

On trouve tout un wagon de fonctions permettant de manipuler les ensembles de membres. On peut citer Subset, Lag, PrevMember, ou Filter que nous verrons plus loin.

 

ORDER

Utilisation :

Comme son nom l’indique, cette fonction permet d’ordonner un ensemble de membres. On peut ordonner par une des propriétés de l’attribut comme sa valeur ou bien par des valeurs du cube.

On utilise souvent cette fonction en combinaison avec Head ou Tail pour construire des Set dynamiques comme le Top 10 des clients.

Attention quand on travaille avec des membres dans une hiérarchie, l’ordre respectera le placement des membres dans celle-ci. Il faut préciser le paramètre BASC ou BDESC pour s’en affranchir.

Exemple :

Dans cet exemple, on renvoie les ventes par date décroissante

SELECT
{[Measures].[Internet Sales Amount]} on 0,
ORDER([Date].[Calendar].AllMembers, [Date].[Calendar].CurrentMember.Value, BDESC) on 1
FROM [Adventure Works]

Dans cet autre exemple, on retourne la liste des promotions par chiffre d’affaires réalisé en 2008.

SELECT
{[Measures].[Sales Amount]} on 0,
ORDER([Promotion].[Promotion].AllMembers, [Measures].[Sales Amount], DESC) on 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2008]

Dans le même genre :

On peut citer HIERARCHIZE qui permet d’ordonner des membres en respectant une hiérarchie.

 

UNKNOWNMEMBER

Utilisation :

L’UnkownMember, ou membre inconnu, est utilisé pour gérer les valeurs NULL du DatawareHouse (l’absence de valeur d’un point de vue métier). Par exemple, si des lignes de vente n’ont pas de produit associé (ProductKey = NULL), elles seront reliées aux « Produits inconnus » qui sera ce membre technique de la dimension produit.

Ce membre un peu particulier reste un membre à part entière des dimensions qui remonte dans les requêtes (et qui peut être perturbant ou fausser des chiffres).

Exemple :

Dans cet exemple, on récupère explicitement les ventes associées à aucun produit

SELECT
{[Measures].[Sales Amount]} ON 0,
[Product].[Product].UNKNOWNMEMBER ON 1
FROM [Adventure Works]

Dans cet autre exemple, on souhaite exclure les ventes non affectées par exclusion de l’UnknownMember du Set sur l’axe 1.

SELECT
{[Measures].[Sales Amount]} ON 0,
{[Product].[Product].AllMembers - [Product].[Product].UNKNOWNMEMBER} ON 1
FROM [Adventure Works]

 

Dans le même genre :

Il n’existe pas de raccourci pour obtenir le membre racine (aussi appelé « All Member » – à ne pas confondre avec la fonction AllMembers). On utilisera une autre expression qui récupère le premier élément de la dimension :

SELECT
{[Measures].[Sales Amount]} ON 0,
[Product].[Product].Members.Item(0) ON 1
FROM [Adventure Works]

STRTOSET

Utilisation :

Cette fonction permet de convertir une chaîne de caractère en un Set utilisable dans une requête. On l’utilise notamment pour injecter des paramètres dans une requête.

Attention à ne pas abuser de cette fonction. Il m’est arrivé de voir tout un jeu de membres calculés dans une solution basés sur des conversions de chaînes, ce qui dégrade les performances.

Exemple :

Dans cet exemple, on injecte dynamiquement le n° du mois dans la requête. La fonction STRTOSET transformera la chaîne de caractère au membre correspondant dans la dimension date.

SELECT
{[Measures].[Sales Amount]} ON 0,
[Sales Territory].[Sales Territory Country].Members ON 1
FROM [Adventure Works]
WHERE STRTOSET("[Date].[Calendar].[Month].&[2008]&[" + @NumMois + "]")

Dans le même genre :

Il existe le parfait inverse à STRTOSET qui transforme un ensemble de membre en chaîne de caractères : c’est SETTOSTR. Dans le même style on citera SETTOARRAY, STRTOMEMBER, TUPLETOSTR, etc.

 

FILTER

Utilisation :

Filter permet, comme son nom l’indique, de filtrer un ensemble de membre en fonction d’une expression.

Contrairement à ce que l’on peut imaginer, la clause WHERE en MDX permet de pointer un sous-cube ou un slice dans le cube, c’est pourquoi elle n’est pas de la forme Dimension = Valeur mais attend un Set. En revanche, la fonction FILTER fonctionne quasiment comme la clause WHERE en SQL.

On lui passe les arguments suivants :

FILTER( Set de base, Condition)

• Le Set de base est l’ensemble de membres sur lequel s’effectue la recherche ; on prend souvent AllMembers.
• La condition, une expression qui doit renvoyer vrai ou faux. La condition peut porter sur une mesure du cube ou bien sur une propriété de dimension

Exemple :

Dans ce premier exemple, on récupère les ventes pour un produit donné. On utilise souvent ce type de requête de façon paramétrée avec des dimensions à forte cardinalité (une référence par exemple) :

SELECT
{[Measures].[Sales Amount]} ON 0,
FILTER(
   [Product].[Product].Members,
   [Product].[Product].CurrentMember.Name = "Casque" ) ON 1
FROM [Adventure Works]

Dans cet autre exemple, on filtre sur la valeur d’une mesure, ici les revendeurs avec un CA de plus de 100 000$ :

SELECT
{[Measures].[Reseller Sales Amount]} ON 0,
FILTER(
    [Reseller].[Reseller].Members,
    [Measures].[Reseller Sales Amount] > 100000) ON 1
FROM [Adventure Works]

Dans le même genre :

Dans le cas où l’on filtre sur une mesure, on peut privilégier l’utilisation de fonctions comme TOPCOUNT, TOPSUM ou TOPPERCENT pour ramener la fourchette haute d’un Set (cf. aussi les fonctions TAIL/HEAD présentées ci-dessus).

 

AGGREGATE

Vous utilisez implicitement la fonction AGGREGATE en permanence sans le savoir. Le simple fait de requêter un tuple appelle la fonction d’agrégation.

Ecrire cette requête…

SELECT ([Measures].[Sales Amount]) ON 0
FROM [Adventure Works]

…implique qu’on agrège les ventes sur toutes les dimensions.

On peut la traduire par le tuple suivant :

SELECT
(
[Measures].[Sales Amount],
[Date].[Calendar].[All Periods],
[Product].[Product Categories].[All Products],
...
) ON 0
FROM [Adventure Works]

On peut remplacer un membre par un Set mais tout change quand on passe le tuple formé en tant que membre calculé. On obtient l’erreur suivante : « la fonction attend une expression de chaîne ou numérique pour l’argument. Une expression d’ensemble de tuples a été utilisée. »

WITH MEMBER [Ventes] AS
(
 {
  [Date].[Calendar].[Calendar Year].&[2005],
  [Date].[Calendar].[Calendar Year].&[2006]
 },
 [Measures].[Sales Amount]
)
SELECT {[Ventes]} ON 0
FROM [Adventure Works]

 

Dans un membre calculé, il faut préciser explicitement l’agrégation via la fonction AGGREGATE qui utilisera la fonction d’agrégation définie pour la mesure dans le cube.

WITH MEMBER [Ventes] AS
AGGREGATE(
 {
   [Date].[Calendar].[Calendar Year].&[2005],
   [Date].[Calendar].[Calendar Year].&[2006]
 },
 [Measures].[Sales Amount]
)
SELECT {[Ventes]} ON 0
FROM [Adventure Works]

 

Dans le même genre :

AGGREGATE s’appuie sur ce qui est défini comme fonction d’agrégation dans le cube. Mais on peut vouloir forcer une somme avec SUM, une moyenne avec AVG (c’est d’ailleurs le seul moyen de faire une moyenne, via un membre calculé) ou un comptage avec COUNT. Evidemment, il en existe bien d’autres.

 

EXISTS et NONEMPTY

Utilisation :

Un besoin fréquent est de récupérer les membres d’une dimension pour lesquels il existe des valeurs. C’est souvent le cas quand on veut remplir un paramètre (dans Report Builder par exemple) en limitant les valeurs à un certain contexte.

Il y a de nombreuses façons de le faire dont certaines ont été évoquées ici mais les fonctions EXISTS et NONEMPTY remplissent aussi ce rôle et surtout seront utilisables dans un membre calculé.

Exemple :

Dans cet exemple, on récupère la liste des dates pour lesquelles une vente particulière existe (ici un produit en particulier).

SELECT {} ON 0,
EXISTS(
[Date].[Date].Members,[Product].[Product Categories].[Product].&[348],
"Internet Sales"
) ON 1
FROM [Adventure Works]

 

On retrouve le même résultat avec NONEMPTY.

SELECT
NONEMPTY(
[Date].[Date].Members,
([Measures].[Sales Amount],[Product].[Product Categories].[Product].&[348])
) ON 0
FROM [Adventure Works]

 

Enfin, on peut aussi « slicer » le cube avec une clause WHERE mais là on exclut la possibilité de créer un membre calculé directement dans le cube.

SELECT {} ON 0,
{[Date].[Date].Members} ON 1
FROM [Adventure Works]
WHERE {[Product].[Product Categories].[Product].&[348]}

 

 

Conclusion

En passant en revue des fonctions MDX, nous avons pu voir de nombreuses requêtes utiles pour explorer un cube ou créer des membres calculés. Evidemment, la spécificité des métiers à analyser fait qu’il est difficile d’avoir un jeu précis de requêtes. Néanmoins, une bonne connaissance du catalogue de fonctions MDX permet de maitriser la navigation dans les dimensions et les mesures.

Dans la réalité, on s’appuie bien évidemment en premier lieu sur les designers de requête comme celui de Report Builder qui inclut la gestion des paramètres. Mais il est de temps en temps nécessaire de rentrer dans le code MDX généré pour l’optimiser, l’arranger ou le simplifier.

La notion de performance n’a pas été abordée dans cet article. En effet, il y a souvent de nombreuses façons d’écrire les requêtes MDX et une des conséquences peut être les performances meilleures ou moins bonnes avec l’une ou l’autre écriture.

Mais notez bien que, comme pour le moteur SQL, il y a une phase de parsing et d’optimisation qui conduisent souvent à une écriture commune et optimisée par le moteur OLAP lui-même. C’est pourquoi il faut toujours prendre en compte l’aspect lisibilité et maintenabilité d’une requête ; déjà que ce langage est complexe.