DimTemps – Step 3 « Time Calculation »

Les « time calculation » dans SSAS sont très simples à constuire; comprenez par là les calculs d’agrégations et de comparaisons temporelles. Néanmoins, si vous voulez les généraliser à toutes vos mesures cela devient un chouillat plus compliqué.

Pour bien commencer, assurez-vous d’avoir lu les articles DimTemps – Step 1 « Back To Basics » et DimTemps – Step2 « Analyses Glissantes »

1ère étape : référencer en base les fonctionnalités d’agrégation et de comparaison temporelles

Dans un premier temps, nous allons lister dans une vue ou une table (à votre bon coeur messieurs dames), la liste des fonctionnalités d’agrégation que nous voulons implémenter:

  • Défaut : aucune agrégation
  • WTD : week to date
  • MTD : month to date
  • YTD : year to date. Dans le cas de notre dimension temps basique, nous devons spécifier cette agrégation en fonction de la hiérarchie calendaire utilisée (mensuelle ou hebdomadaire)

Nous créons donc la vue dwh.DimCalendrierAgregation :


CREATE VIEW [Common].[DimCalendrierAgregation] AS
 SELECT CAST(1 AS tinyint) AS IdAgregation,cast(‘Défaut’ as nvarchar(30)) NomAgregation
 UNION SELECT CAST(2 AS tinyint),cast(‘WTD’ as nvarchar(30))
 UNION SELECT CAST(3 AS tinyint),cast(‘MTD’ as nvarchar(30))
 UNION SELECT CAST(4 AS tinyint),cast(‘YTD (Calendrier-Hebdomadaire)’ as nvarchar(30))
 UNION SELECT CAST(5 AS tinyint),cast(‘YTD (Calendrier-Mensuel)’ as nvarchar(30))
 GO

Le même travail est à effectuer sur les fonctionnalités de comparaison temporelles

  • Défaut : aucune comparaison
  • Semaine : comparaison par rapport à la semaine précédente en valeur et en pourcentage
  • Mensuelle : comparaison par rapport au mois précédent en valeur et en pourcentage
  • Annuelle : comparaison par rapport à l’année précédente en valeur et en pourcentage. Comme pour les fonctionnalités d’agrégation, cette dernière est à spécifier en fonction de la hiérarchie calendaire utilisée.

Nous créons donc la vue dwh.DimCalendrierComparaison :


CREATE VIEW [Common].[DimCalendrierComparaison] AS
SELECT cast(1 as tinyint) as IdComparaison,cast(‘Défaut’ as nvarchar(40)) as NomComparaison
 UNION SELECT cast(2 as tinyint),cast(‘Valeur S-1′ as nvarchar(40))
 UNION SELECT cast(3 as tinyint),cast(‘Ecart S-1′ as nvarchar(40))
 UNION SELECT cast(4 as tinyint),cast(‘% Ecart S-1′ as nvarchar(40))
 UNION SELECT cast(5 as tinyint),cast(‘Valeur M-1′ as nvarchar(40))
 UNION SELECT cast(6 as tinyint),cast(‘Ecart M-1′ as nvarchar(40))
 UNION SELECT cast(7 as tinyint),cast(‘% Ecart M-1′ as nvarchar(40))
 UNION SELECT cast(8 as tinyint),cast(‘Valeur A-1 (Calendrier-Hebdomadaire)’ as nvarchar(40))
 UNION SELECT cast(9 as tinyint),cast(‘Ecart A-1 (Calendrier-Hebdomadaire)’ as nvarchar(40))
 UNION SELECT cast(10 a stinyint),cast(‘% Ecart A-1 (Calendrier-Hebdomadaire)’ as nvarchar(40))
 UNION SELECT cast(11 as tinyint),cast(‘Valeur A-1 (Calendrier-Mensuel)’ as nvarchar(40))
 UNION SELECT cast(12 as tinyint),cast(‘Ecart A-1 (Calendrier-Mensuel)’ as nvarchar(40))
 UNION SELECT cast(13 as tinyint),cast(‘% Ecart A-1 (Calendrier-Mensuel)’ as nvarchar(40))
 GO

Nous allons désormais créer la vue DimCalendrierFx qui nous permettre de créer sous SSAS une dimension technique listant toutes les fonctionnalités d’agrégation et de comparaison temporelle. Comprenez par là une Junk Dimension, elle est issue du produit cartésien (CROSS JOIN) entre les deux vues créées précédemment.


CREATE VIEW [Common].[DimCalendrierFx] AS
 SELECT *
 FROM [Common].[DimCalendrierAgregation] crossjoin [Common].[DimCalendrierComparaison]
 GO

Voilà, côté DB Engine le boulot est fait, il ne reste plus qu’à usiner le tout sur SSAS.

2nde étape : créer les dimensions sous SSAS 

Bon je n’allais pas faire un chapitre juste pour implémenter la dimension temps décrite dans les deux précédents post alors j’en profite.

Niveau Data Source View, rien de plus simple, on ajoute la table dwh.DimCalendrier et les vues dwh.DimCalendrierMoisRelatif, dwh.DimCalendrierSemaineRelative et dwh.DimCalendrierFx. On pense tout de suite à mettre ces objets dans un diagramme spécifique afin de bien organiser son code et d’en faciliter la maintenance; et on crée les relations nécessaires.


On crée ensuite la dimension temps avec ces deux hiérachies, en attachant un soin tout particulier à la mise en place des relations d’attributs (et oui, les relations d’attribut c’est important sous SSAS).

La dimension temps est statique, par conséquent, toutes les relations d’attributs doivent être rigides en dehors des propriétés mois relatif et semaine relative qui glissent dans le temps.

Puis la dimension Calendrier Fx.

N.B :  la clé de la dimension est une clé multiple (IdAgregation, IdComparaison) puisque qu’il s’agit d’une Junk Dimension. De plus, la propriété IsAggregatable doit être à False pour les attributs Agregation et Comparaison.

Si vous vous dites « si j’avais su j’aurais pas venu » un petit peu de patience, prenez une aspirine (surtout si vous n’êtes pas familier des SCOPES sous SSAS).

3ème étape : implémentation des time calculation dans vos cubes

Ajouter les deux dimensions créées précédemment à votre cube, puis aller sur l’onglet Calculations

Commençons par implémenter les fonctionnalités d’agrégation :

  1. Affecter le membre défaut à l’attribut [Calendrier Fx].[Agregation] ; comme nous avons bien fait les choses, il s’agit de l’attribut [Calendrier Fx].[Agregation].[Défaut]. Cela vous permet d’avoir la valeur courante de vos indicateurs.
  2. A partir de ce membre par défaut et l’utilisation des scopes, vous aller pouvoir définir pour chaque membre de [Calendrier].[Agregation] la formule d’agrégation temporelle adhoc. Par exemple, la  valeur Week To Date se calcule tout simplement par une agrégation de type Week To Date de notre membre par défaut.

Bon si c’est trop compliqué à comprendre, récupérer le code suivant :


// Scope [Calendrier Fx].[Agregation]
// Définition du membre par défaut
ALTER CUBE CURRENTCUBE UPDATE DIMENSION [Calendrier Fx].[Agregation], DEFAULT_MEMBER = [Calendrier Fx].[Agregation].&[1];

// WTD
SCOPE [Calendrier Fx].[Agregation].&[2];
THIS = Aggregate(WTD ([Calendrier].[Calendrier-Hebdomadaire].CurrentMember),[Calendrier Fx].[Agregation].defaultMember);
END SCOPE;

// MTD
SCOPE [Calendrier Fx].[Agregation].&[3];
THIS = Aggregate(MTD ([Calendrier].[Calendrier-Mensuel].CurrentMember),[Calendrier Fx].[Agregation].defaultMember);
END SCOPE;

// YTD (Calendrier-Hebdomadaire)
SCOPE [Calendrier Fx].[Agregation].&[4];
THIS = Aggregate(YTD ([Calendrier].[Calendrier-Hebdomadaire].CurrentMember),[Calendrier Fx].[Agregation].defaultMember);
END SCOPE;

// YTD (Calendrier-Mensuel)
SCOPE [Calendrier Fx].[Agregation].&[5];
THIS = Aggregate(YTD ([Calendrier].[Calendrier-Mensuel].CurrentMember),[Calendrier Fx].[Agregation].defaultMember);
END SCOPE;

// Fin Scope [Calendrier Fx].[Agregation]

Reste à appliquer le même principe sur les fonctionnalités de comparaison.


// Scope [Calendrier Fx].[Comparaison]
// Définition du membre par défaut
ALTER CUBE CURRENTCUBE UPDATE DIMENSION [Calendrier Fx].[Comparaison], DEFAULT_MEMBER = [Calendrier Fx].[Comparaison].&[1];

// Valeur S-1
SCOPE [Calendrier Fx].[Comparaison].&[2];
THIS = IIF([Calendrier].[Calendrier-Hebdomadaire].currentmember.level.ordinal > 1,([Calendrier Fx].[Comparaison].DefaultMember,ParallelPeriod([Calendrier].[Calendrier-Hebdomadaire].[Semaine Iso],1) ),null);
END SCOPE;

// Ecart S-1
SCOPE [Calendrier Fx].[Comparaison].&[3];
THIS = IIF([Calendrier].[Calendrier-Hebdomadaire].currentmember.level.ordinal > 1,[Calendrier Fx].[Comparaison].DefaultMember – [Calendrier Fx].[Comparaison].&[2],null);
END SCOPE;

// % Ecart S-1
SCOPE [Calendrier Fx].[Comparaison].&[4];
THIS = IIF( [Calendrier Fx].[Comparaison].&[2]=0 OR [Calendrier].[Calendrier-Hebdomadaire].currentmember.level.ordinal <= 1, NULL, [Calendrier Fx].[Comparaison].&[3] / [Calendrier Fx].[Comparaison].&[2]); FORMAT_STRING(THIS) = "Percent";
END SCOPE;

// Valeur M-1
SCOPE [Calendrier Fx].[Comparaison].&[5];
THIS = IIF([Calendrier].[Calendrier-Mensuel].currentmember.level.ordinal > 3,([Calendrier Fx].[Comparaison].DefaultMember,ParallelPeriod([Calendrier].[Calendrier-Mensuel].[Mois Annee],1) ),null);
END SCOPE;

// Ecart M-1
SCOPE [Calendrier Fx].[Comparaison].&[6];
THIS=IIF([Calendrier].[Calendrier-Mensuel].currentmember.level.ordinal > 3,[Calendrier Fx].[Comparaison].DefaultMember – [Calendrier Fx].[Comparaison].&[5],null);
END SCOPE;

// % Ecart M-1
SCOPE [Calendrier Fx].[Comparaison].&[7];
THIS= IIF( [Calendrier Fx].[Comparaison].&[5]=0 OR [Calendrier].[Calendrier-Mensuel].currentmember.level.ordinal <= 3 , NULL, [Calendrier Fx].[Comparaison].&[6] / [Calendrier Fx].[Comparaison].&[5]); FORMAT_STRING(THIS) = "Percent";
END SCOPE;

// Valeur A-1 (Calendrier-Hebdomadaire)
SCOPE [Calendrier Fx].[Comparaison].&[8];
THIS = IIF([Calendrier].[Calendrier-Hebdomadaire].currentmember.level.ordinal > 0, ([Calendrier Fx].[Comparaison].DefaultMember,ParallelPeriod([Calendrier].[Calendrier-Hebdomadaire].[Annee Semaine Iso],1) ),null);
END SCOPE;

// Ecart A-1 (Calendrier-Hebdomadaire)
SCOPE [Calendrier Fx].[Comparaison].&[9]; THIS= IIF([Calendrier].[Calendrier-Hebdomadaire].currentmember.level.ordinal > 0, [Calendrier Fx].[Comparaison].DefaultMember – [Calendrier Fx].[Comparaison].&[8],null); END SCOPE;

// % Ecart A-1 (Calendrier-Hebdomadaire)
SCOPE [Calendrier Fx].[Comparaison].&[10];
THIS = IIF( [Calendrier Fx].[Comparaison].&[8]=0 OR [Calendrier].[Calendrier-Hebdomadaire].currentmember.level.ordinal = 0, NULL, [Calendrier Fx].[Comparaison].&[9] / [Calendrier Fx].[Comparaison].&[8]); FORMAT_STRING(THIS) = "Percent";
END SCOPE;

// Valeur A-1 (Calendrier-Mensuel)
SCOPE [Calendrier Fx].[Comparaison].&[11]; THIS = IIF([Calendrier].[Calendrier-Mensuel].currentmember.level.ordinal > 0, ([Calendrier Fx].[Comparaison].DefaultMember,ParallelPeriod([Calendrier].[Calendrier-Mensuel].[Annee],1) ),null);
END SCOPE;

// Ecart A-1 (Calendrier-Mensuel)
SCOPE [Calendrier Fx].[Comparaison].&[12];
THIS= IIF([Calendrier].[Calendrier-Mensuel].currentmember.level.ordinal > 0,[Calendrier Fx].[Comparaison].DefaultMember – [Calendrier Fx].[Comparaison].&[11],null);
END SCOPE;

// % Ecart A-1 (Calendrier-Mensuel)
SCOPE [Calendrier Fx].[Comparaison].&[13];
THIS= IIF( [Calendrier Fx].[Comparaison].&[11]=0 OR [Calendrier].[Calendrier-Mensuel].currentmember.level.ordinal = 0, NULL, [Calendrier Fx].[Comparaison].&[12] / [Calendrier Fx].[Comparaison].&[11]); FORMAT_STRING(THIS) = "Percent";
END SCOPE;

// Fin Scope [Calendrier Fx].[Comparaison]

Une fois ces opérations effectuées, vous n’avez plus qu’à déployer votre cube.

Exemple de résultat sous Excel :

Une petite devise Shadok pour illustrer ce post :

2 réflexions sur “DimTemps – Step 3 « Time Calculation »

  1. Pingback: Implémentation d’une DateTool avec PowerPivot « BI Thoughts & Tips

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s