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

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.