PowerPivot Excel2013 – Table temps universelle pour vos POCs


Le triptyque Excel 2013, PowerPivot et Power View forme une excellente plateforme nous permettant de (dé)montrer à nos clients :

  • les biens fondés de BI Self-Service
  • la puissance et la performance du moteur xVelocity (ou VertiPaq)
  • la simplicité d’utilisation de ces outils, puisque désormais tout est intégré dans Excel

Bon je ne vais pas faire un n-ième post sur Excel 2013 et les fonctionnalités BI, pour ceux qui désireraient plus d’infos sur le sujet, je ne saurai que vous renvoyer aux excellents articles de :

pour ne citer qu’eux, désolé pour les autres…

Bon vanter les mérites d’une table temps universelle, je ne citerai que les points suivants :

  • un POC s’effectue dans un court délai => tout gain de temps est donc bon à prendre
  • la demande de POCs PowerPivot est sans cesse grandisssante => rien ne sert de réinventer la roue
  • vous serez ammener à faire vos démos dans des environnements dépourvus de SQL Server => pour l’universalité de notre solution autant partir sur les fonctionnalités offertes par Excel et PowerPivot plutôt que de s’appuyer sur un SGBDR.

Passons désormais en mode tutoriel pour la réalisation de notre dimension Temps.

La première étape consiste à créer une table liée (Linked Table) contenant la liste des dates qui me sera utile pour mon POC

Je commence par ouvrir Excel, et créer sur une feuille vierge la liste de dates désirée. Au passage je n’oublie pas de mettre un entête de colonne et de renommer mon onglet. (Ce n’est pas parce qu’on est en mode POC qu’il faut travailler comme un cochon !)

Calendar_Sheet

Pour ajouter la liste ainsi créée à un Data Model PowerPivot, il suffit de sélectionner la plage de valeurs, d’aller dans le menu PowerPivot et de cliquer sur Add To DataModel. Lors de l’ajout de votre table au DataModel, n’oubliez pas de cocher la case « My table has Headers »

Calendar_AddLinkedTableToDataModel

Le module PowerPivot s’ouvre alors en ayant importé notre table liée (Cf. menu Linked Table qui vous permettra de gérer la mise à jour de vos tables liées).

Calendar_PowerPivot1

Notez au passage :

  • que le nom de la table liée est Table1 et qu’il me faut donc le modifier (prochaine étape)
  • qu’il me faut renommer la table sous PowerPivot, car elle ne prend pas par défaut le nom de l’onglet

Pour renommer la table liée, je repars sur ma feuille Excel, je sélectionne le menu Design et je nomme ma table correctement :

Calendar_DesignRenameTableName

J’obtiens alors un modèle tout simple mais tout propre ;-)

Calendar_PowerPivot2

Voyons maintenant comment enrichir en DAX cette liste toute bête pour implémenter une dimension Temps digne de ce nom. Tout ce passe désormais dans PowerPivot (c’est juste pour l’exercice car on pourrait aussi enrichir notre liste de dates directement sous Excel)…

On commence par marquer notre table comme étant notre dimension Temps :

menu Design -> Mark as Date Table puis sélectionnez le champs de type Date qui va bien

Calendar_PowerPivot_MarkAsDateTable

Il ne nous reste plus qu’à créer les colonnes calculées.

Déclarons dans un premier temps, l’ensemble des propriétés basiques d’une dimension Temps (cf. article) DimTemps – Step 1 « Back To Basics »

  • je commence par ajouter la colonne Calendar Id qui dans mon cas est une AlternateKey. Je ne fais que formater la date en entier au format YYYYMMDD
[Calendar Id] =INT(FORMAT([Date];"YYYYMMDD"))
  •  il me faut ensuite l’ensemble des informations calendaires traditionnelles :
[Date (Format)] = FORMAT([Date];"DD-MMM-YYYY")
[Year] = YEAR([Date])
[Month] = MONTH([Date])
[Month Name] = FORMAT([Date];"MMMM")
[Year Month] = INT(FORMAT([Date];"YYYYMM"))
[Year Month Name] = FORMAT([Date];"YYYY, MMMM")
[Weekday] = FORMAT([Date];"DDDD")
[Day Of Week] = WEEKDAY([Date];2)
[Day Of Month] = DAY([Date])

Et voilà nous arrivons aux limites des fonctions traditionnelles de manipulations de dates en DAX, il faut donc se creuser un peu les méninges pour aller plus loin.

  • Pour avoir le n° du jour de l’année, il suffit de faire la différence entre la date et le premier jour de l’année et de transformer le résultat en nombre entier
[Day Of Year] = Calendar[Date]-STARTOFYEAR(Calendar[Date]) + 1
  • Pour disposer d’un calendrier hebdomadaire ISO :
[Iso Week] = WEEKNUM([Date];21)  (La valeur 21 de weeknum non documentée permet d’avoir le numéro de semaine ISO)
[Iso Year] = IF( AND(WEEKNUM([Date];21) < 5;WEEKNUM([Date];2) > 50);[Year]+1;IF(AND(WEEKNUM([Date];21) > 50;WEEKNUM([Date];2) < 5);[Year]-1;[Year]))
[Iso Year Week] = IF( AND(WEEKNUM([Date];21) < 5;WEEKNUM([Date];2) > 50);[Year]+1;IF(AND(WEEKNUM([Date];21) > 50;WEEKNUM([Date];2) < 5);[Year]-1;[Year])) & " week " & FORMAT(WEEKNUM([Date];21);"00")

Ajoutons ensuite les propriétés qui permettront de réaliser des analyses glissantes (cf. article DimTemps – Step2 « Analyses Glissantes »). Merci à Chris Webb pour son exemple d’implémentation sur les jours et mois relatifs qui je me suis empressé d’enrichir avec les semaines relatives :

[Relative Date Offset] = INT([Date] - TODAY())
[Relative Date] = IF([Relative Date Offset]=0; "J "; "J " & IF([Relative Date Offset]>0; "+"; "") & [Relative Date Offset])
[Relative Month Offset] = ((12 * YEAR([Date])) +  MONTH([Date])) - ((12 * YEAR(TODAY())) +  MONTH(TODAY()))
[Relative Month] = IF([Relative Month Offset]=0; "M "; "M " & IF([Relative Month Offset]>0; "+"; "") & [Relative Month Offset])
[Relative Week Offset] = INT(([Date] - (TODAY() - WEEKDAY(TODAY();2)+1))/7)
[Relative Week] = IF([Relative Week Offset]=0; "W "; "W " & IF([Relative Week Offset]>0; "+"; "") & [Relative Week Offset])

Pour améliorer les rendus et faciliter l’utilisation de votre modèle aux utilisateurs n’oubliez pas de :

  • de trier correctement vos colonnes (exemple le nom du mois doit être trié en fonction du n° du mois, etc.)

PowerPivot_SortByColumn

  • de déclarer des hiérarchies (dans mon exemple un calendrier mensuel et un calendrier hebdomadaire)

PowerPivot_CalendarHierarchies

Comme d’habitude, retrouvez le sample sur SkyDrive http://sdrv.ms/VlpB3A

Voilà pour vos POCs çà devrait suffire ….

About these ads

5 réflexions sur “PowerPivot Excel2013 – Table temps universelle pour vos POCs

  1. Pingback: PowerPivot SamePeriodLastYear on non contiguous date selections « BI Thoughts & Tips
  2. Pingback: Implémentation d’une DateTool avec PowerPivot « BI Thoughts & Tips
  3. Pingback: PowerPivot Excel2013 - Table temps universelle ...
  4. Pingback: PowerPivot Excel2013 – Table temps universelle pour vos POCs | Bienvenue à BM Formation

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