Limitation pentaho/mondrian: ne pas utiliser explicitement les clefs (key) pour les membres de dimensions dans les requêtes MDX

Bon, tout est dans le titre… mais, quand même, ça nécessite quelques explications et surtout des solutions de contournement.

La problématique

Si vous venez, comme moi, du domaine décisionnel « non libre » ( Microsoft Essbase, etc.) , vous avez sûrement l’habitude d’utiliser les clefs de membres des dimensions   pour les sélectionner  directement  dans les requêtes MDX.
Ok on fait un récap:
Lorsque vous faites une requête MDX, vous pouvez sélectionner un membre de dimension soit par sa valeur affichée ( en général le nom du membre), soit par la clef utilisée par le moteur OLAP. En général, la clef  utilisée et une clef numérique donc plus facile à  manipuler.

Un exemple, vite !!

Ok, disons que vous voulez mettre en perspective  les ventes  deux de vos produits phares :

  • les [casseroles en métal argenté(c)]
  • et  les [poêles dorées à l’or fin(TM)].

La requête MDX ressemblerait à  peu près à  ça :

SELECT {[ProductHierarchy].[casseroles en métal argenté(c)], [ProductHierarchy].[poêles dorées à l'or fin(TM)]} on ROWS,
 {[Measures].[sales]} ON COLUMNS
 FROM [Commercial]

Jusque là  tout va bien, sauf que en fait la même requête doit servir pour deux pays différents. Et pour chaque pays, le nom des produits est différent.
Plus précisément, en Angleterre les deux produits s’appellent respectivement : [silver metal pan(c)] et [gilded with fine gold pans(TM)]
Et là on a un problème:  la requête précédemment crée ne fonctionne plus telle quelle à  condition que les dénominations soient remplacées par leur traduction respective!
Du coup, en Angleterre la requête qui fonctionne est celle là :

SELECT {[ProductHierarchy].[silver metal pan(c)], [ProductHierarchy].[gilded with fine gold pans(TM)]} on ROWS,
{[Measures].[sales]} ON COLUMNS
FROM [Commercial]

Alors, comment faire pour que la requête MDX fonctionne correctement quelque soit le pays (et donc les traductions) ?

L’implémentation

C’est là  qu’en tant que super spécialiste de votre moteur OLAP préféré, vous allez utiliser les fonctions différentiation par la clef .
Dans notre exemple la casserole en argent a un N° d’identifiant numérique égal  à 112 et la poêle en or fin le N° 113, quelque soit les langages.

On utilise le caractère & ( « et commercial » ou « ampersand » en anglais) pour utiliser la clef à  la place du nom.
La requête devient alors :

SELECT {[ProductHierarchy].&[112], [ProductHierarchy].&[113]} on ROWS,
{[Measures].[sales]} ON COLUMNS
FROM [Commercial]

Ok, on met tout ça en pratique.

On crée donc  la dimension ProductHierarchy dans le cube Mondrian à  partir d’une table de dimension dénommée DimProduct avec deux champs  ProductKey ( notre clef)  et ProductName:

Le fait d’avoir rempli la propriété « column » avec le nom du champ « ProductKey » va permettre un certain nombre de choses, dont l’optimisation des requêtes SQL qui sont effectuées par le moteur de Mondrian, mais aussi la possibilité de faire notre requête sur la base de la clef ProductKey.

Mondrian Error  !!

Vous exécutez la requête et là, patatras  voilà ce que l’interpréteur de requête de Mondrian nous dit :

Mondrian Error:MDX object ‘[ProductHierarchy.New Hierarchy 0].&[113]’ not found in cube ‘Commercial’

La raison en est simple, Julian Hyde (le créateur de Mondrian ) n’a tout simplement pas prévu cette fonction dans son moteur.
Voici la preuve: un ticket a été référencé dans le gestionnaire d’incident de Mondrian sous la description :Member key treated as member name in WHERE

Arggh, voilà  donc une mauvaise surprise !  Pour enfoncer le clou, ce bug a été ouvert en 2009…

Vous pourriez me dire: « tu n’as qu’à  mettre le champ ProductName dans la propriété ‘column’ et on en parle plus ! ».
Oui, en effet ça marche mais :

  1.  la problématique de l’internationalisation reste entière.
  2. De plus, il faut savoir que le moteur Mondrian va utiliser la propriété « column » pour créer ses jointures lors des requêtes SQL sous-jaçentes ( rappelons-nous que Mondrian est un moteur ROLAP-  donc basé sur le moteur de base de données !). Or en général , il vaut mieux faire les jointure sur des champs de tables qui sont indexés , ne serait-ce que pour des questions  de performance et faire un index sur un champ numériques a beaucoup plus d’efficacité que sur un champ de type caractère.

Quelle est donc la solution ?

On va donc utiliser une fonctionnalité  qui ,elle, a été implémentée:  les propriétés en conjonction avec les filtres.
Dans un premier temps on va créer une propriété basée sur le champ « ProductKey »:

Et enfin on va utiliser un filtre , basé sur cette propriété

SELECT {Filter([ProductHierarchy].[Product].Members,
([ProductHierarchy].CurrentMember.Properties(« key »)=113)
OR
([ProductHierarchy].CurrentMember.Properties(« key »)=111))
} ON ROWS,
{[Measures].[sales]} ON COLUMNS
FROM [Commercial]

Là -enfin-, vous pouvez utilisez cette requête, quelque soit les traductions des produits utilisées.

Si vous avez déjà  été dans cette situation n’hésitez pas à  commenter cet article ou à  demander des précisions !

Pentaho:calcul du pipe commercial en MDX avec Mondrian – partie 2

Dans mon précédent post j’ai posé le problème du calcul par périodes du pipe commercial.


Maintenant que l’on a la solution en SQL, on pourra valider la requête MDX.

On voudrait retranscrire ce que l’on a fait en SQL c’est à dire sélectionner les devis dont la date de devis est inférieur ( ou égal) à la fin de la période et la date de commande ou d’expiration supérieure (ou égale) au début de la période

pour rappel la requête SQL est la suivante :

… where (datedevis <= "31/01/2011") and(datecommande >= « 01/01/2011 » );

Premier essai MDX: Modélisation du cube

Imaginons que l’on ai créé un cube Mondrian basé sur la table des devis en sachant

que la table contient deux dates ( date de devis et date de commande)

Il nous faut donc deux dimensions de temps liées ces deux dates

On a donc les dimensions suivantes [TimeStart] et [TimeEnd]

Ces dimensions ne sont que des dimensions virtuelles de la dimension Time qui contient une hiérarchie par défaut dont les descendants ordonnés sont : Année,Quarter,Mois,Date:

Voici donc le rendu dans SchemaWorkBench :

Dans cette image , on remarque que les dimensions TimeStart et TimeEnd sont des Dimensions « Usage » c’est à dire qu’elles ont été crées à partir d’une véritable dimension.

Premier essai MDX: la requête

maintenant que l’on a créé le cube, on peut concevoir la requête:

On doit faire un croisement entre

  1. la période qui va de janvier à l’infini dans la dimension TimeStart pour la date de commande ( rappelez-vous la condition SQL : datecommande >= « 01/01/2011 »)
  2. et la période qui va du début de l’existence de l’informatique (merci Turing! – je laisse Snail préciser les choses) à janvier dans la dimension TimeEnd pour la date de devis (condition SQL: datedevis <= "31/01/2011")
  3. et enfin le montant
Bon alors ça donne quoi ?
déjà comment savoir la date du début de l’informatique ? On va utiliser le membre Firstchid de la dimension TimeStart auquel on va adjoindre la fonction MDX Openingperiod.
Du coup la condition 1 se résume à

OpeningPeriod([TimeStart].[Month],[TimeStart].firstchild):[TimeStart].[2011].[Q1].[January]

De même, la condition 2 correspond à ça ( grâce à la fonction ClosingPeriod et au membre Lastchild):

[TimeEnd].[2011].[Q1].[January]:Closingperiod([TimeEnd].[Month],[TimeEnd].lastchild )

Une petite explication s’impose sur ces deux ensembles:
[TimeStart].Firstchild permet d’identifier le premier enfant de cette dimension c’est à dire la première année possible, LastChild étant la dernière année possible.
OpeningPeriod permet de connaitre le dernier enfant possible à un niveau spécifié( [TimeEnd].[Month]) pour un membre spécifique( [TimeEnd].lastchild).
Pour moi, la fin du monde étant positionné à décembre 2013, la formule correspondra à
[TimeEnd].[Q4].[December].
Je vous laisse imaginer l’explication pour ClosingPeriod qui est la contraposée parfaite de la première.
Il suffit maintenant de mettre tout ça dans un bocal, rajouter la mesure Montant et d’agiter très fort. Voilà ce que ça devrait donner :
with MEMBER Measures.x as ‘
SUM( NonEmptyCrossJoin(
OpeningPeriod([TimeStart].[Month],[TimeStart].firstchild):[TimeStart].[2011].[Q1].[January],
[TimeEnd].[2011].[Q1].[January]:Closingperiod([TimeEnd].[Month],[TimeEnd].lastchild ) )
, [Measures].[Montant] )
Select Measures.x on 0
from [Pipe]
Outre le fait que j’utilise un membre calculé, je fais appel à la fonction NonEmptyCrossJoin
qui permet de faire la jointure entre les parcours des deux dimensions ( en enlevant bien sûr les tuples vides) ainsi que la fonction SUM qui permet de faire la somme du montant sur le dataset créé par le crossjoin.
Premier essai MDX: Conclusions
Ok, on a le bon montant du pipe commercial en janvier 2011, mais peut avoir la même chose pour toutes les autres périodes à partir d’une seule requête ?
ça voudrait dire que l’on puisse décrire mois par mois ce qui revient à dire qu’il faut rajouter un axe supplémentaire dans la requête pour montrer les montants pour chaque mois.
Dans bien des cas, cela se résume à rajouter un axe colonne comme ceci :
select Measures.test on rows,
Descendants([Time]. [year],(Month) on columns
from [MonCube]
Fastoche ? Pas vraiment : je vous rappelle que l’on a deux dimensions de temps différentes TimeStart et TimeEnd et que l’on ne peut pas les aligner si facilement…
Comment ? parlez plus fort…
?rajouter une troisième dimension de temps ?
Ok, mais ça suffit pas…. il faut aussi lier les trois dimensions entre elles pour que lorsque l’on se positionne par exemple sur janvier , les deux autres prennent en compte cette période.
Heureusement M$ ( l’inventeur de MDX; respect à Mosha Pasumansky) a pensé à tout : la fonction LinkMember .


Arghh !! Mauvaise Limonade: Julian Hyde, le créateur de Mondrian, ne l’a pas implémenté… voir le ticket si ça dit à quelqu’un(e)..


Dans la troisième partie de cette série on verra comment résoudre le problème:
afficher à partir d’une seule requête MDX le montant du pipe commercial Mois par Mois

Pentaho:calcul du pipe commercial en MDX avec Mondrian – partie 1

Voilà donc un cas d’école de la vraie vie comme un consultant décisionnel aime à en voir: Comment calculer mois par mois un pipe commercial ?

Bon, le besoin est simple: une entreprise veut connaître mois par mois le montant global des propositions commerciales qui ont été envoyées à ses prospects.

En général l’entreprise a un système de force de ventes qui permet d’extraire ces données là.

Par exemple , un extract serait :

N° de devis, N° de client/prospect, date du devis , date d’expiration du devis (ou date de commande),Montant du devis, etc.

Imaginons que nous voulions savoir la liste de tous les devis actifs sur le mois de janvier.

On va donc devoir identifier les cas possibles; ça se résume à cette image :

La solution SQL:

Bon là c’est facile , il suffit de retranscrire les 4 cas possibles dans les conditions de filtre:

select sum(montant) from Pipe where (datedevis>= ’01/01/2011′ and datecommande<=’31/01/2011′ or () etc…

Fastidieux, non ?…

Heureusement, Il y a une façon plus élégante de regrouper les filtres:

select sum(montant) from Pipe  where (datedevis <= ’31/01/2011′) and (datecommande >= ’01/01/2011′ );

Dans le prochain article j’aborderais la solution MDX pour Mondrian.