Cette publication ne fait pas partie de la bibliothèque YouScribe
Elle est disponible uniquement à l'achat (la librairie de YouScribe)
Achetez pour : 19,90 € Lire un extrait

Lecture en ligne + Téléchargement

Format(s) : PDF

sans DRM

Partagez cette publication

Du même publieur

Chapitre 15
XV DAX :Fonctions de Time Intelligence
Tous les projets BI que j’ai pu fréquenter font un usage abondant des dimensions temps: en effet, que serait le contrôle de gestion si l’on ne pouvait parler du temps? La plupart des outils d’analyse font donc référence au temps qui passe sous différentes formes : Année fiscale Mois légal Semaine de l’annéeEtc. Et, à partir de ces unités de temps, on procède à des comparaisons indispensables au contrôleur de gestion comme au chef des ventes : « Cette période de temps (Ex mois) est-elle meilleure/moins bonne que la période équivalente dans une référence de temps du passé (ex l’année dernière)? » Nous allons voir dans ce chapitre comment bien monter une dimension temps et utiliser les fonctions DAX permettant ces comparaisons.
DAX : Dimension de type temps et Time Intelligence
Qu’est-ce qu’unedimension de temps ?
Intérêt d’une dimension temps
On pourrait penser que les dimensions de temps sont optionnelles et que des formules de colonnes calculées ou desmesures permettraient de s’en passer: cela peut être vrai dans les cas très simples que l’on peut voir dans des formations … mais, dans la vraie vie, le temps va intervenir en de nombreux endroits tels que les dates de commandes, les dates de livraisons, dates de factures, de dernier audit, etc…
On serait donc amené à faire du copier/coller sur des expressions qui peuvent être complexes, ce qui doit toujours être évité si on veut faire un peu de maintenance ultérieurement !
C’est pourquoi une meilleure pratique est d’isoler les dates dans une table séparée : ladimension de type temps, afin de localiser en cet endroit les expressions sur le temps.
On pourra de plus avoir à créer deshiérarchies, cas aussi très fréquent dans l’usage du temps qui passe ; ainsi un calendrier pourrait-il avoir les niveaux année, semestre, mois et date.
Ces hiérarchies faciliteront la navigation de l’utilisateur dans les données, car la plupart du temps , il descendra du général (Ex l’année) vers le détail (Ex le mois).
Bâtir une dimension de temps
Nous abordons la une notion fondamentale pour qui veut réussir un projet, car, comme dit précédemment, son usage est des plus fréquents.
Un des 1ers points pour bien définir cette dimension est de définir sagranularité:
A-t-on besoin de dates entières (cas habituels des sociétés de négoce), ou doit-on au contraire affiner par heures (cas des systèmes plus industriels) , ou enfin une granularité de mois est-elle suffisante (cas des enquêtes financières).
Une fois que l’on a choisi cette granularité une autre notion plus technique est lacomplétude: elle est indispensable pour que les fonctions de time intelligence opèrent correctement afind’avoir toutes les valeurs dans la granularité choisie.
Il y a plusieurs méthodes pour satisfaire à ce cahier des charges :
1. Stockertoutes les valeurs dans une table du système relationnel en amont de votre modèle (souvent un data warehouse) 2. Bâtirdans une requête les données que vous récupérerez à la volée dans le modèle tabulaire lors du processing
SQL Server 2008R2-2012: PowerPivot, les langages DAX et MDX par Dominique Verrière
352
DAX :Dimension de type temps et Time Intelligence
ère La 1solution a ma préférence car elle permet de gérer par des simples mises à jour de données toutes les exceptions que nos contrôleurs de gestion savent si bien inventer !
Ainsi, sil’on vous dit : «cette année l’exercice fiscal fera 13 mois au lieu de 12» , une simple intervention dans la table de dimension résoudra ce problème. Afin de faire la démonstration d’une bonne dimension de type temps, je vais donc créer une table qui sera stockée dans le data warehouse. Une autre question que l’on va se poser, et celle des champs à ajouter dans cette table : En effet, afin d’économiser les temps de traitement dans le modèle tabulaire, nous pouvons ajouter dès la conception de la table un certain nombre de champs utilisables par la suite. Un exemple des champs les plus classiques : Année Mois Trimestre Date du jour Conception de la table de dimension temps : IFNOTEXISTS(select*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[Temps]')ANDtypein(N'U'))begincreatetable[dbo].[Temps]([DateComplete][date]notNULL,[NomJourFrancais][nvarchar](10)NULL,[NoJourMois][tinyint]NULL,[NoSemaineAnnee][tinyint]NULL,[NomMoisFrancais][nvarchar](20)NULL,[NoMoisAnnee][tinyint]NULL,[Trimestre][tinyint]NULL,[Annee][smallint]NULL,[Semestre][tinyint]NULL,[TrimestreFiscal][tinyint]NULL,[AnneeFiscale][smallint]NULL,[SemestreFiscal][tinyint]NULL,constraint[PK_Temps]primarykeyclustered([DateComplete]ASC),)endgoCette table dont la clé primaire est une date contient les informations nécessaires à nos futures analyses. Vous pourrez noter que j’ai utilisé le type date de SQL server 2008.
SQL Server 2008R2-2012: PowerPivot, les langages DAX et MDXpar Dominique Verrière353
DAX : Dimension de type temps et Time Intelligence
Remplissage de la table de dimension temps :
withMesDatesas(selectcast('20130101'asdate)asDJour,1asNombreunionallselectdateadd(day,1,DJour),Nombre+1fromMesDateswhereNombre< 2000 )insertintoTemps(DateComplete)selectDJourfromMesDatesoption(maxrecursion2000)go-- On met à jour les colonnes d'information :updateTempsset[NomJourFrancais]=DATENAME(dw,DateComplete),[NoJourMois]=day(DateComplete),[NoSemaineAnnee]=datepart(iso_week,DateComplete),[NomMoisFrancais]=datename(month,DateComplete)+str(year(DateComplete),5),[NoMoisAnnee]=month(DateComplete),[Trimestre]=datepart(quarter,DateComplete),[Annee]=datepart(year,DateComplete),[Semestre]=casedatepart(quarter,DateComplete)when1then1 when2then1when3then2when4then2endgo
Vous pourrez noter que le remplissage s’effectue en deux étapes: 1. Lapremière consiste à calculer les dates (qui seront ici notre granularité) 2. Ladeuxième consiste à mettre à jour les champs complémentaires en fonction des besoins Attention, lors de ce remplissage, il est indispensable d’obtenir desnoms uniquespour chacun des objets ; en effet lors de la manipulation par le langage DAX les agrégats seront faits sur ce nom qui devrait être unique. Dans le cas contraire, les agrégats seraient calculés sur d’autres niveaux que ceux souhaités. Afin de simplifier la requête, j’ai utilisé ici une méthode récursive: ceci m’a obligé à ajouter un paramètre supplémentaire (maxrecursion) , le nombre de pas étant limité par le langage SQL. Relations entre la dimension temps et les autres tables
Il est impératif que les éléments de la dimension temps puissent être atteints depuis les tables à analyser; c’est pourquoi une clé étrangère est nécessaire sur ces tables: cette clé garantira l’intégrité des données et permettra d’éviter des cas pour lesquels des dates seraient inconnues dans la dimension temps.
SQL Server 2008R2-2012: PowerPivot, les langages DAX et MDX par Dominique Verrière
354
DAX :Dimension de type temps et Time Intelligence
Afin de montrer des exemples un peu pluscomplexes, j’ai choisi d’utiliser une table de faits avec 2 dates ; Nous traiterons dans un premier temps le cas de la date de commande, dans un deuxième temps celui de la date de livraison.
Voici la définition de cette table de faits :
IFNOTEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[Commandes]')ANDtypein(N'U'))begincreatetable[dbo].[Commandes]([IDCommande]intidentity(1,1)notnull,[IDProduit][int]NOTNULL,[DateCommande][date]NOTNULL,[DateLivraison][date]NULL,[QuantiteCommandee][smallint]NULL,[PrixUnitaire][money]NULL,[MontantTtc][money]NULL,[MontantPort][money]NULL,constraintPK_Commandesprimarykeyclustered(IDCommande))endGOIFNOTEXISTS(SELECT*FROMsys.foreign_keysWHEREobject_id=OBJECT_ID(N'[dbo].[FK_Commandes_Produit]')ANDparent_object_id=OBJECT_ID(N'[dbo].[Commandes]'))alterTABLE[dbo].[Commandes]WITHCHECKADDCONSTRAINT[FK_Commandes_Produit]FOREIGNKEY([IDProduit])references[dbo].[Produit]([IDProduit])GOIFNOTEXISTS(SELECT*FROMsys.foreign_keysWHEREobject_id=OBJECT_ID(N'[dbo].[FK_Commandes_Temps_Commande]')ANDparent_object_id=OBJECT_ID(N'[dbo].[Commandes]'))alterTABLE[dbo].[Commandes]WITHCHECKADDCONSTRAINT[FK_Commandes_Temps_Commande]FOREIGNKEY([DateCommande])references[dbo].[Temps]([DateComplete])GOIFNOTEXISTS(SELECT*FROMsys.foreign_keysWHEREobject_id=OBJECT_ID(N'[dbo].[FK_Commandes_Temps_Livraison]')ANDparent_object_id=OBJECT_ID(N'[dbo].[Commandes]'))alterTABLE[dbo].[Commandes]WITHCHECKADDCONSTRAINT[FK_Commandes_Temps_Livraison]FOREIGNKEY([DateLivraison])references[dbo].[Temps]([DateComplete])GOVous remarquerez les trois clés étrangères nécessaires à cette table : la date de commande, la date de livraison, le code produit.
SQL Server 2008R2-2012: PowerPivot, les langages DAX et MDX355par Dominique Verrière
DAX : Dimension de type temps et Time Intelligence
On notera par ailleurs que la date de livraison peut être à NULL : ceci découle du fait que certaines commandes sont non livrées.
Nous allons mettre en œuvre maintenant cette dimension temps dans le cadre d’un projet de type tabulaire.
Mise en œuvre d’une dimension temps
Après importation des tables nécessaires, une des premières étapes consiste à marquer notre table de dimension comme une table de dates.
Ce marquage serafondamentalpour l’utilisation des fonctions de type Time Intelligence.
Vous pouvez noter que le système a également besoin de savoir quelle est la colonne de type datetime.
Voici le modèle complet :
SQL Server 2008R2-2012: PowerPivot, les langages DAX et MDX par Dominique Verrière
356
DAX :Dimension de type temps et Time Intelligence
Il y a bien 2 relations entre la table des commandes et la table de type temps :seule l’une d’elles est active (Date de commande) nous nous occuperons plus tard de la relation date de livraison. La mise en œuvre d’une simple mesure sur le total des commandes nous permet d’obtenir les chiffres désirés en fonction des années de commande :
Voici la définition de cette mesure :
Cette mesure étant, bien sûr, définie sur la table de faits.
Création de hiérarchie dans la dimension temps
Afin de permettre à l’utilisateur une meilleure navigationil peut-être de bonne pratique de créer des hiérarchies.
Pour mémoire une hiérarchie comporte un certain nombre de niveaux, chaque élément d’un niveau possédant un seul parent dans le niveau supérieur. Nous avons donc des relations de un à plusieurs du haut vers le bas ; ceci permettra une navigation plus facile en commençant paréléments les moins nombreux.
Ainsi nous pouvons voir le temps en une succession de niveaux dans le temps tels que années, semestres, trimestres, mois, dates.
Nous pouvons créer cette hiérarchie à partir du modèle graphique :
SQL Server 2008R2-2012: PowerPivot, les langages DAX et MDXpar Dominique Verrière
357
DAX : Dimension de type temps et Time Intelligence
Trier les niveaux selon un ordre logique Vous pourrez remarquer que dans ce tableau, les mois sont triés dans un ordre alphabétique ce qui ne plaira pas aux contrôleurs de gestion ! Heureusement, depuis la deuxième version deux de Powerpivot (apparue avec SQL Server 2012) , il est possible d’ajouter une colonne de tri sur chaque colonne: cette pratique était courante dans les modèles multidimensionnels.
Pour trier les mois, j’ai besoin d’une colonnequidonne l’année suivie du mois dans cet ordre; j’aurais pu mettre cette nouvelle colonnedès la conception de la table relationnelle, mais je vais choisir ici de créer une colonne calculée au sein de mon modèle tabulaire.
Qui donne ce résultat :
Il reste à configurer le tri :
SQL Server 2008R2-2012: PowerPivot, les langages DAX et MDX par Dominique Verrière
358
DAX :Dimension de type temps et Time Intelligence
Qui donne, cette fois, un résultat correct pour le contrôle de gestion !
SQL Server 2008R2-2012: PowerPivot, les langages DAX et MDXpar Dominique Verrière359
DAX : Dimension de type temps et Time Intelligence
Utiliser les fonctions de Time Intelligence :
Fonctions qui forcent le contexte à une date
Voici tout d’abord une collection de fonctions qui évalueront une expressionen début ou une fin de période; ces fonctions possèdent toutes le même squelette qui est composé de 3 parties :
Fonction (Expression à évaluer, Table de dates, [Filtre])
OPENINGBALANCEMONTH /CLOSINGBALANCEMONTH
Force le contexte à la première/dernière date du mois.
OPENINGBALANCEQUARTER /CLOSINGBALANCEQUARTER
Force le contexte à la première/ dernière date du trimestre.
OPENINGBALANCEYEAR / CLOSINGBALANCEYEAR
Force le contexte à la première/ dernière date de l’année.
Voici un exemple demise en œuvre de ses fonctions:
CaDebutMois:=OPENINGBALANCEMONTH(SUM([MontantTtc]);Temps[DateComplete])
CaFinMois:=CLOSINGBALANCEMONTH(SUM([MontantTtc]);Temps[DateComplete])
Attention, contrairement à ce que certains pensent (dont je faisais partie en tant que débutant) les agrégats en début de période sont calculés avant le début exact de cette période, c'est-à-dire sur la fin de la journée précédente. C’est pourquoi le chiffre d’affaires de début novembre est bien celui de la dernière journée d’octobre.A quoi peuvent servir ces fonctions : dans le cas de mesures semis additives ; de telles mesures sont à appliquer lorsque l’on additionne sur tous les axes sauf l’axe du temps.SQL Server 2008R2-2012360: PowerPivot, les langages DAX et MDX par Dominique Verrière
Un pour Un
Permettre à tous d'accéder à la lecture
Pour chaque accès à la bibliothèque, YouScribe donne un accès à une personne dans le besoin