Conversion de temps: encore un usage sympa des SCOPE Assignments

Je bondis souvent lorsque l’on s’avise de me dire que “Tabular c’est génial, après tout les SCOPEs et les many-to-many ça ne sert que très rarement”. Effectivement si on voit SSAS comme un couche sémantique bêbête à la Business Objects sur une BdD relationnelle en étoile, que l’on fait en somme de la BI du 20ème siècle pourquoi pas. Mais lorsque l’on souhaite faire quelque chose de plus user-friendly, faire de son cube un vrai hub de données, presque une application à part entière alors je ne vois pas comment faire sans SCOPE (pensez aux Date Tool, aux discrétisations dynamiques comme les tranches d’âge) ou sans Many To Many (Conversion de devises, gestion des SCD…). Mais bon on n’est pas là non plus pour faire du tabular bashing, donc revenons à l’essentiel.

Il y a quelques semaine j’étais de passage chez un client ISV avec une problématique tellement rigolote que je me suis étonné de ne pas l’avoir rencontrée jusqu’alors: leurs données de fait sont datées en GMT mais les utilisateurs du cube sont dans le monde entier et souhaiteraient voir les données de production de leur zone dans… leur fuseau horaire.

Et ça n’est pas compliqué du tout. On a besoin de deux choses

  • Une dimensions Temps à la granularité heure
  • Une dimension technique (i.e. sans relation avec les groupes de mesure) qui schématise les fuseaux horaires.

Pour le premier point je ne vous ferai l’insulte ni de vous expliquer pourquoi on en a besoin, ni comment on le met en place Sourire. Pour le second, un simple requête en mode Table Value Constructor fait très bien l’affaire.

CREATE VIEW [dbo].[_DimUtc]
AS
WITH GmtCte
AS
(
	SELECT -12 UtcKey
	UNION ALL
	SELECT UtcKey + 1
	FROM GmtCte
	WHERE UtcKey <= 13
)
SELECT
	UtcKey,
	'UTC'
	+
	CASE
		WHEN UtcKey > 0 THEN '+'
		ELSE ''
	END
	+
	CASE
		WHEN UtcKey <> 0
		THEN CONVERT(VARCHAR,UtcKey)
		ELSE ''
	END	UtcName
FROM GmtCte
GO

Bon ça ne gère pas les demi fuseaux mais l’idée est là. On dispose bien d’un beau libellé et la clé va servir à générer le pseudo DATEADD. Une fois la dimension montée dans un cube simplifiée on a le Dimension Usage suivant:

DU

Ne reste plus qu’à écrire le SCOPE. Il spécifie que lorsqu’un UTC est sélectionné, ce dernier décale les membres heure de la dimension temps grâce à ParallelPeriod. Les chiffres agrégés sont modifiés grâce à la hiérarchie.

Scope
(
    [Date].[Hour].[Hour],
    [Utc].[Utc].[Utc]
);
    this =
    (
        ParallelPeriod
        (
            [Date].[Calendar].[Hour],
            -[Utc].[Utc].CurrentMember.Member_Key
        ),
        [Utc].[Utc].DefaultMember
    );

End Scope;

Le résultat est alors ce que l’on souhaitait, et plutôt performant:

UTC3

Voilà en espérant que cela vous serve.

A bientôt!

Bonus: Petite friandise: on peut même décider de mettre en place un DefaultMember dynamique: pour cela une petite fonction en .NET à déployer sur le serveur SSAS peut suffire. Il est alors possible de l’appeler dans le script pour mettre à jour le membre par défaut de la dimension UTC. 

public int GetCurrentTimeZoneOffset()
{
   return TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now);
}

7 réflexions sur “Conversion de temps: encore un usage sympa des SCOPE Assignments

  1. Super article, j’ai voulu faire une version Dax histoire de te dire … mais regarde c’est facile … jusqu’au moment ou ma formule : Internet Total Sales Time:=IF( COUNTROWS( VALUES( _DimUtc[UtcKey]))=1;[Internet Total Sales](PARALLELPERIOD(‘Date'[Date];VALUES(_DimUtc[UtcKey]);HOUR));[Internet Total Sales]) me retourne que PARALLELPERIOD et DATEADD supporte uniquement year, quarter, month, Day … « Mais bon on n’est pas là non plus pour faire du tabular bashing » o//

  2. Non mais sinon, plus sérieusement, ils font une analyse avancé derrière ? Parce que je ne vois pas en quoi c’est plus pratique que de nettoyer tes données via l’ETL en entrée pour mettre l’heure correspondant à la zone

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