L’objectif de cet article est de montrer comment implémenter une DateTool en Tabular.
Sur les cubes multidimensionnels, ce n’est pas bien compliquer, on arrive « facilement » à nos fins à l’aide des scopes assignments. Mais étant donné que cette notion n’existe pas en DAX, voyons comment mettre en place ce type de solution.
Le concept est le suivant :
- simplifier les métadonnées mises à disposition des utilisateurs. Plus un modèle est simple, plus les utilisateurs l’appréhendent facilement et l’utilise efficacement,
- la déclinaison d’une mesure calculée selon les différentes fonctionnalités temporelles se fait par l’intermédiaire d’une dimension technique.
Mise en place de la dimension technique
La première étape consiste à énumérer de manière exhaustive la liste des fonctions temporelles que vous désirez implémenter. Car n’ayant pas de moyen de mettre en place des scopes, la solution finale sera beaucoup moins flexible et dynamique que sa cousine « MDX-ienne »; et vous devrez refaire une passe sur toutes vos mesures pour implémenter de nouvelles fonctions temporelles (vous comprendrez un peu plus tard)
Pour notre exemple, je pars sur l’implémentation des fonctionnalités suivantes :
- Date Value : affichage de la valeur d’une mesure selon le grain temporel relatif au context filter. Il s’agit de la valeur par défaut à afficher
- LY : valeur de la mesure pour l’année précédente (Last Year)
- YoY : évolution (en valeur) de la mesure par rapport à l’année précédente (Year over Year)
- YoY (%) : évolution (en pourcentage) de la mesure par rapport à l’année précédente
- Iso LY : idem que LY mais basé sur le calendrier ISO
- Iso YoY : idem que YoY mais en se basant sur le calendrier ISO
- Iso YoY (%) : idem que YoY (%) mais en se basant sur le calendrier ISO
- WTD : valeur cumulée de la mesure depuis le début de la semaine jusqu’à date (Week To Date)
- MTD : valeur cumulée de la mesure depuis le début du mois jusqu’à date (Month To Date)
- YTD : valeur cumulée de la mesure depuis le début de l’année jusqu’à date (Year To Date)
- Iso YTD : valeur cumulée de la mesure depuis le début de l’année ISO jusqu’à date
Toutes ces fonctionnalités sont listées dans une feuille Excel, un simple tableau à 2 colonnes suffit amplement (1 colonne pour le libellé de la fonction, 1 autre pour gérer l’ordre d’affichage des fonctions dans les Pivot Table) :
Cette table est remontée dans le Data Model par l’intermédiaire d’une Linked-Table (cf. post sur la table temps universelle pour vos POCs PowerPivot pour plus d’informations sur la mise en place d’une table liée).
Une fois la table remontée dans PowerPivot, il est nécessaire d’ajouter une mesure permettant d’indiquer si la dimension technique est utilisée ou non. Cela permettra de mettre en place un pseudo scope assigments lors de la création des mesures :
Has Date Function := HASONEVALUE(CalendarFx[Date Function])
Bien évidemment, cette mesure n’a aucun intérêt à être manipulée par les utilisateurs, elle doit donc être masquée.
Définition et mise en place du Design-Pattern à appliquer sur les mesures calculées
Une fois la dimension technique créée, il faut mettre en place le design pattern qui sera appliqué à toutes les mesures :
- création d’un template qui sera utilisé pour créer les mesures
- découplage de la mesure pour faciliter la maintenance (et oui faut toujours penser à nos petits collègues qui passent derrière nous …) :
- mise en place d’une mesure technique de base (implémentation de la mesure calculée qui sera utilisée pour la valeur par défaut). Il va de soi qu’elle doit être masquée.
- mise en place de la mesure calculée finale. Cette dernière est créée tout simplement à partir du template et de la mesure technique de base par simple recherche/remplace
Définition du Template
Il suffit de :
- tester si la dimension technique est utilisée, si oui définir la logique de calcul pour chaque fonction, sinon utiliser la dimension technique de base
- implémenter la logique de calcul pour chaque fonction
Passage en revue des fonctions temporelles à implémenter pour notre exemple:
- Date Value : affichage de la mesure technique de base
- LY : il suffit de calculer la valeur de la mesure technique de base sur l’année précédente :
CALCULATE( MyMeasureBase; FILTER( All(Calendar);CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])) );
- YoY : Date Value – LY si les deux valeurs ne sont pas vides
IF( ISBLANK(MyMeasureBase) || ISBLANK(CALCULATE( MyMeasureBase; FILTER( All(Calendar);CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])) )); BLANK(); MyMeasureBase - CALCULATE( MyMeasureBase; FILTER( All(Calendar);CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])) ) )
- YoY (%) : idem que pour YoY mais en calculant l’évolution en pourcentage (MyMeasureBase / MyMeasureBase Last Year – 1) * 100
IF( ISBLANK(MyMeasureBase) || ISBLANK(CALCULATE( MyMeasureBase; FILTER( All(Calendar);CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])) )); BLANK(); (MyMeasureBase / CALCULATE( MyMeasureBase; FILTER( All(Calendar);CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])) ) - 1 ) * 100 )
- Iso LY : idem que LY mais basé sur le calendrier ISO
CALCULATE( MyMeasureBase; FILTER( All(Calendar); CONTAINS(VALUES(Calendar[Same Weekday Last Year]);Calendar[Same Weekday Last Year];Calendar[Date]) ) )
- Iso YoY : idem que YoY mais en se basant sur le calendrier ISO
IF( ISBLANK(MyMeasureBase) || ISBLANK( CALCULATE( MyMeasureBase; FILTER( All(Calendar); CONTAINS(VALUES(Calendar[Same Weekday Last Year]);Calendar[Same Weekday Last Year];Calendar[Date]) ) ) ); BLANK(); MyMeasureBase - CALCULATE( MyMeasureBase; FILTER( All(Calendar); CONTAINS(VALUES(Calendar[Same Weekday Last Year]);Calendar[Same Weekday Last Year];Calendar[Date]) ) ) )
- Iso YoY (%) : idem que YoY(%) mais en se basant sur le calendrier ISO
IF( ISBLANK(MyMeasureBase) || ISBLANK( CALCULATE( MyMeasureBase; FILTER( All(Calendar); CONTAINS(VALUES(Calendar[Same Weekday Last Year]);Calendar[Same Weekday Last Year];Calendar[Date]) ) ) ); BLANK(); (MyMeasureBase / CALCULATE( MyMeasureBase; FILTER( All(Calendar); CONTAINS(VALUES(Calendar[Same Weekday Last Year]);Calendar[Same Weekday Last Year];Calendar[Date]) ) ) -1 ) *100 )
- WTD : week to date
CALCULATE( MyMeasureBase; FILTER( All(Calendar); Calendar[Date] >= MAX(Calendar[Date]) + 1 - LOOKUPVALUE(Calendar[Day Of Week];Calendar[Date];MAX(Calendar[Date])) && Calendar[Date] <= MAX(Calendar[Date]) ))
- MTD : month to date
TOTALMTD( MyMeasureBase; Calendar[Date] );
- YTD : year to date
TOTALYTD( MyMeasureBase; Calendar[Date] )
- Iso YTD : year to date en utilisant le calendrier ISO
IF( HASONEVALUE(Calendar[Iso Year]); CALCULATE( MyMeasureBase; FILTER( All(Calendar); Calendar[Iso Year] = VALUES(Calendar[Iso Year]) && Calendar[Date] <= MAX(Calendar[Date]) ) ); BLANK() )
Une fois que toutes les logiques de calcul ont été mises à plat, il suffit d’encapsuler le tout dans une seule mesure en faisant bien attention de tester la présence ou non de la dimension technique dans le context filter. On obtient alors un template de ce type :
MyMeasure :=
IF(
'CalendarFx'[Has Date Function];
SWITCH(
VALUES('CalendarFx'[Date Function]);
"Date Value";
MyMeasureBase;
"LY";
CALCULATE( MyMeasureBase; FILTER( All(Calendar);CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])) );
"YoY";
IF(
ISBLANK(MyMeasureBase)
|| ISBLANK(CALCULATE( MyMeasureBase; FILTER( All(Calendar);CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])) ));
BLANK();
MyMeasureBase - CALCULATE( MyMeasureBase; FILTER( All(Calendar);CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])) )
);
"YoY (%)";
IF(
ISBLANK(MyMeasureBase)
|| ISBLANK(CALCULATE( MyMeasureBase; FILTER( All(Calendar);CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])) ));
BLANK();
(MyMeasureBase / CALCULATE( MyMeasureBase; FILTER( All(Calendar);CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])) ) - 1 ) * 100
);
"Iso LY";
CALCULATE( MyMeasureBase; FILTER( All(Calendar); CONTAINS(VALUES(Calendar[Same Weekday Last Year]);Calendar[Same Weekday Last Year];Calendar[Date]) ) );
"Iso YoY";
IF(
ISBLANK(MyMeasureBase)
|| ISBLANK(
CALCULATE( MyMeasureBase; FILTER( All(Calendar); CONTAINS(VALUES(Calendar[Same Weekday Last Year]);Calendar[Same Weekday Last Year];Calendar[Date]) ) )
);
BLANK();
MyMeasureBase -
CALCULATE( MyMeasureBase; FILTER( All(Calendar); CONTAINS(VALUES(Calendar[Same Weekday Last Year]);Calendar[Same Weekday Last Year];Calendar[Date]) ) )
);
"Iso YoY (%)";
IF(
ISBLANK(MyMeasureBase)
|| ISBLANK(
CALCULATE( MyMeasureBase; FILTER( All(Calendar); CONTAINS(VALUES(Calendar[Same Weekday Last Year]);Calendar[Same Weekday Last Year];Calendar[Date]) ) )
);
BLANK();
(MyMeasureBase /
CALCULATE( MyMeasureBase; FILTER( All(Calendar); CONTAINS(VALUES(Calendar[Same Weekday Last Year]);Calendar[Same Weekday Last Year];Calendar[Date]) )
) -1 ) *100
);
"WTD";
CALCULATE(
MyMeasureBase;
FILTER(
All(Calendar);
Calendar[Date] >= MAX(Calendar[Date]) + 1 - LOOKUPVALUE(Calendar[Day Of Week];Calendar[Date];MAX(Calendar[Date]))
&& Calendar[Date] <= MAX(Calendar[Date])
));
"MTD";
TOTALMTD( MyMeasureBase; Calendar[Date] );
"YTD";
TOTALYTD( MyMeasureBase; Calendar[Date] );
"Iso YTD";
IF(
HASONEVALUE(Calendar[Iso Year]);
CALCULATE(
MyMeasureBase;
FILTER(
All(Calendar);
Calendar[Iso Year] = VALUES(Calendar[Iso Year])
&& Calendar[Date] <= MAX(Calendar[Date])
)
);
BLANK()
);
BLANK()
);
MyMeasureBase
)
Exemple de mise en œuvre
Pour avoir le chiffre d’affaire des ventes internet, je commence donc par définir la mesure de base :
Revenue Base:=SUM(InternetSales[SalesAmount])
Je crée ensuite la mesure Revenue à l’aide du Template et le tour est joué. Le résultat est le suivant :
Bon certes, la solution comme je vous le disais au début de l’article n’est pas aussi sexy qu’en multidimensionnel, ni même aussi flexible. Car je le répète si vous voulez ajouter des fonctions temporelles à votre DateTool, il vous faudra modifier le Template et l’appliquer à toutes vos mesures. Et là c’est beaucoup moins fun 😦 . Mais bon sur une solution SSAS Tabular on doit pouvoir s’en sortir en modifiant le XMLA ;-). Au passage je vous invite à lire l’article de David Joubert sur la conversion d’un modèle Tabular en modèle multidimensionnel; cela devrait vous donner des idées.
Retrouvez les sources sur SkyDrive.
MS BI Addict 