SQL Server 2008-2016

Dominique Verrière

Dialogue Formation Informatique

SQL Server 2008-2016

Découverte du langage MDX partie 4

SQL Server : Le blog de Dominique Verrière 2

Introduction

Ce petit fascicule fait partie d’une liste de notes que je publie gratuitement sur mon site.

J’essaie de mettre dans ces notes mon expérience de terrain en toute indépendance de Microsoft.

Dominique Verrière

Table des matières Utiliser la dimension temps.......................................................................................... 6

Introduction ........................................................................................................................................................... 6

Parrallelperiod ....................................................................................................................................................... 7

Exemple du mois équivalent de l’année précédente ........................................................................................ 8

PeriodsToDate ..................................................................................................................................................... 11

Aller plus loin ............................................................................................................. 13

Contacter l’auteur ...................................................................................................... 14

Sommaire

SQL Server : Le blog de Dominique Verrière 5

Utiliser la dimension temps

SQL Server Le blog de Dominique Verrière

Utiliser la dimension temps

Introduction Nous allons, dans ce petit article, utiliser deux des principales fonctions applicables à la dimension temps.

Qu’est-ce qu’une dimension temps ?

Une dimension bien particulière, de type time, sur laquelle des fonctions spécifiques aux temps sont

disponibles.

Par ailleurs certains agrégats ne sont disponibles qu’avec ce type de dimension.

Bien monter une dimension temps est fondamental pour la réussite (ou l’échec) du projet cube.

La dimension est bien de type time ?

Oui, c’est le cas ici ; à défaut rien ne fonctionnera !

Une dimension de type time contient des hiérarchies utilisateur, nous allons naviguer dans ces hiérarchies :

Utiliser la dimension temps

SQL Server : Le blog de Dominique Verrière 7

Parrallelperiod Voici sans doute l’une des fonctions les plus utilisées en MDX.

Son but :

Donner une période de temps équivalent dans le passé (en général, même s’il est possible d’aller dans le

futur).

Grâce à elle, tous les ratios seront possibles :

Sa syntaxe :

PARALLELPERIOD( [«Level»[, «Numeric Expression»[, «Member»] ] ] )

Cette fonction rend un membre en se déplaçant en trois étapes :

J’appelle d’ailleurs cela « l’algorithme des échelles »

Le troisième paramètre est quant à lui, plus rarement utilisé car il permettra de partir d’un autre endroit que

l’emplacement par défaut (currentmember).

Evolution par rapport au même mois de l’année dernière

Progression par rapport à samedi dernier à la même heure

Etc.

1. On remonte au niveau (level) du premier paramètre

2. On se déplace à gauche de la valeur du deuxième paramètre

3. On redescend du même nombre ‘d’étages’ que celui remonté à l’’étape 1

Utiliser la dimension temps

SQL Server Le blog de Dominique Verrière

Exemple du mois équivalent de l’année précédente

Tout d’abord une petite introspection pour montre un problème « sympathique »:

with member [Measures].[MoisAnneePrec] as PARALLELPERIOD( [Date Commande].[Calendrier Legal].[Annee],1).name select [Measures].[MoisAnneePrec] on columns, [Date Commande].[Calendrier Legal].[Mois] on rows from [Analyse Ventes2012];

Oups !

Que se passe-t-il ?

Juillet équivalent à janvier ????

C’est dû au fameux algorithme des échelles, la vraie cause étant que cette dimension temps n’est pas

correctement montée !

Tout se régularise ensuite :

Utiliser la dimension temps

SQL Server : Le blog de Dominique Verrière 9

Amusant non ?

Passons maintenant à nos ratios, en prenant bien garde de ne pas étudier les premières années :

with member [Measures].[CaAnneePrec] as ( [Measures].[Montant Ventes], PARALLELPERIOD( [Date Commande].[Calendrier Legal].[Annee],1) ) member [Measures].[Evolution] as ([Measures].[Montant Ventes] - [Measures].[CaAnneePrec]) / [Measures].[CaAnneePrec] select { [Measures].[Montant Ventes], [Measures].[CaAnneePrec], [Measures].[Evolution] } on columns, non empty [Date Commande].[Calendrier Legal].[Mois] on rows from [Analyse Ventes2012];

Utiliser la dimension temps

SQL Server Le blog de Dominique Verrière

Tout cela n’est pas très formaté…

with member [Measures].[CaAnneePrec] as ( [Measures].[Montant Ventes], PARALLELPERIOD( [Date Commande].[Calendrier Legal].[Annee],1) ) ,format_string="currency" member [Measures].[Evolution] as [Measures].[Montant Ventes] / [Measures].[CaAnneePrec] ,format_string="percent" select { [Measures].[Montant Ventes], [Measures].[CaAnneePrec], [Measures].[Evolution] } on columns, non empty [Date Commande].[Calendrier Legal].[Mois] on rows from [Analyse Ventes2012];

(Après révision de la formule de calcul et des format_string)

Un développeur MDX sérieux prendra garde aux divisions par zéro.

Utiliser la dimension temps

SQL Server : Le blog de Dominique Verrière 11

PeriodsToDate

Cette fonction a pour syntaxe :

PERIODSTODATE( [«Level»[, «Member»] ] )

Son but, contrairement à ce que son nom liasse présager, permet d’obtenir toutes les périodes de temps

jusqu’à un membre donné, qui, s’il n’est pas précisé est le currentmember de la hiérarchie que l’on parcourt.

Elle servira, entre autres, à obtenir des cumuls glissants.

Petit exemple :

Les trimestres jusqu’au troisième trimestre 2017 :

select [Measures].[Montant Ventes] on columns, PERIODSTODATE([Date Commande].[Calendrier Legal].[Annee], [Date Commande].[Calendrier Legal].[Trimestre].&[2017]&[3]) on rows from [Analyse Ventes2012];

Est-il possible d’obtenir un « vrai to date »

Nous avons déjà évoqué les fonctions VBA pour MDX, nous allons y avoir de nouveau recours.

Notre premier besoin : obtenir une mesure calculée qui donne le trimestre actuel (selon la date système).

Quelque chose comme :

with member [Date Commande].[Calendrier Legal].TrimestreActuel as STRTOMEMBER("[Date Commande].[Calendrier Legal].[Trimestre].&[2017]&[3]").name select [Date Commande].[Calendrier Legal].TrimestreActuel on columns from [Analyse Ventes2012];

Mais, en dynamique, au moyen des fonctions VBA :

with member [Measures].TrimestreActuel as ".&[" + vbamdx!cstr(vbamdx!year(vbamdx!now())) + "]&["

Utiliser la dimension temps

SQL Server Le blog de Dominique Verrière

+ vbamdx!cstr(vbamdx!datepart("Q",vbamdx!now())) + "]" select [Measures].TrimestreActuel on columns from [Analyse Ventes2012];

Je me suis limité à la fin du uniquename, pour pouvoir l’utiliser dans une hiérarchie de mon choix.

On note que le keycolumns de mon niveau Trimestre est constitué de l’année suivi du numéro de trimestre.

Voici le résultat complet :

with member [Measures].TrimestreActuel as ".&[" + vbamdx!cstr(vbamdx!year(vbamdx!now())) + "]&[" + vbamdx!cstr(vbamdx!datepart("Q",vbamdx!now())) + "]" select [Measures].[Montant Ventes] on columns, PERIODSTODATE ( [Date Commande].[Calendrier Legal].[Annee], strtomember("[Date Commande].[Calendrier Legal].[Trimestre]" +[Measures].TrimestreActuel) ) on rows from [Analyse Ventes2012];

Sachant que je poste cet article en juin 2017, nous sommes bien avec deux trimestres !

Naturellement les mesures calculées ont vocation à être embarquées dans le cube, mais c’est un autre

sujet !

Utiliser la dimension temps

SQL Server : Le blog de Dominique Verrière 13

Aller plus loin

Vous pouvez, au choix :

Pratique du langage MDX

Me voir ‘en live’ chez Orsys

Attendre le prochain article

Acheter mon livre sur le sujet :

Contact

SQL Server Le blog de Dominique Verrière

Contacter l’auteur

Dominique Verrière est un consultant spécialisé sur les technologies SQL Server : moteur relationnel, SSIS, SSRS et SSAS. Il intervient dans les entreprises pour des missions d'audit, de suivi de performances, d'administration de bases de données. Les bases de données dont il s'occupe peuvent contenir plusieurs milliards de lignes et atteindre des Téra octets; son expérience du terrain est donc significative. Afin de compléter cet article, un blog www.dominiqueverriere.fr est régulièrement mis à jour avec des articles sur les nouveautés ou expériences nouvelles de l'auteur