Par David Laplante, Directeur, Analytique des données
Microsoft SQL Server Analysis Services (SSAS) est une technologie difficile à saisir, mais très puissante quand elle est maîtrisée. Toutefois, il semble qu’il y ait toujours quelque chose à apprendre autant en MDX qu’en modélisation OLAP.
Au cours des années, un besoin récurrent chez plusieurs clients m’échappait à tout coup :
Comment modéliser des Relations BETWEEN dans SSAS (OLAP) ?
Lorsqu’une table de faits contient un versionnement avec une plage de validité, il n’est pas évident, à première vue, que SSAS puisse supporter facilement une analyse où seraient démontrées les mesures selon leur version pour chaque jour d’effectivité.
Un moyen souvent utilisé est de créer des images à des dates prédéfinies, par exemple pour chaque fin de mois.
Bien que valable, cette approche n’est pas très efficace.
En effet, que les données soient en dur dans des tables SQL ou générées via des vues, il y a énormément de duplication d’informations.
Une valeur qui serait effective durant plusieurs mois ou même plusieurs années serait répétée pour chacune des dates image.
Ainsi, des problèmes importants de volumétrie et de performance, à la fois à la lecture des données et également à la génération du cube SSAS, seraient rencontrés car :
- Le cube contiendra énormément de données dupliquées, utilisant beaucoup plus d’espace disque que nécessaire,
- Les performances d’utilisation du cube seraient grandement affectées du fait que les requêtes sont effectuées contre un cube trop volumineux.
(Rappelons qu’un cube n’est qu’un ensemble de fichiers sur disque. De trop gros fichiers fournissent rarement des performances époustouflantes !)
Enfin, si toutes ces images étant persistées dans la base de données relationnelle, il y aurait ici aussi une surconsommation d’espace disque.
Que faire si les besoins d’analyse changent et que l’on demande soudainement que des images mensuelles deviennent quotidiennes. Couplée à la croissance du volume de données qui survient naturellement à travers les années, cette approche devient vite ingérable.
La problématique est donc la suivante :
Comment faire dans un cube SSAS pour permettre à un usager de choisir la date d’effectivité de son choix et de voir l’état des données pour cette date choisie ?
En SQL pur, il serait simple d’interroger la table avec une clause « WHERE » comme l’exemple ci-après le démontre :
« SnapshotDate BETWEEN VERSION_EFFECTIVITY_DATE and VERSION_EXPIRATION_DATE »
Mais SSAS n’offre pas de relation de type « BETWEEN » entre les dimensions et les faits.
La clé du succès réside en une utilisation judicieuse et optimisée du type de relation « Many-To-Many » que nous offre SSAS.
D’abord, il faut construire une table comportant toutes les combinaisons de « VERSION_EFFECTIVITY_DATE » et de « VERSION_EXPIRATION_DATE » qui se trouvent dans notre table de faits.
Appelons cette table VersionEffectivityDateRange.
À partir de la source de données « VersionEffectivityDateRange », il faut maintenant créer une deuxième table où se trouvera un enregistrement (record) pour chaque jour qui existe dans la plage d’effectivité. Appelons cette deuxième table « VersionEffectivityDate ».
Voici un exemple de ce à quoi pourrait ressembler la table « VersionEffectivityDate ».
On pourrait croire que ces deux tables seraient volumineuses, mais faites le test! Vous pourriez être surpris! Le nombre de valeurs distinctes est souvent plus restreint qu’il n’y paraît !
Considérant le volume de données qui ne sera plus chargé dans le cube, puisque l’on se débarrasse des images à date fixe, ainsi que le peu de colonnes et le type de données qui s’y trouvent, il en résulte des sources de données que SSAS sera en mesure de charger très rapidement.
La source VersionEffectivityDateRange servira à créer une dimension qui pourra être mise en relation directe avec la ou les tables de faits nécessaires.
De plus, cette dimension n’a besoin d’aucune hiérarchie ou autre artifice de présentation à l’utilisateur, car elle ne sera pas visible.
Dans l’exemple en image, la clé du seul attribut de la dimension, l’attribut VersionEffectivityDateRange, est une clé composée des champs VERSION_EFFECTIVITY_DATE et VERSION_EXPIRATION_DATE. Comme un membre ayant deux clés doit absolument avoir un nom, nous utiliserons le champ VersionEffectivityDateRangeName.
Pour sa part, la source VersionEffectivityDate servira à créer un groupe de mesures intermédiaire qui fera simplement le pont vers une dimension où seront listées les dates de valorisation que l’usager pourra choisir.
Appelons cette dimension SnapshotDate ! Comme un groupe de mesures vide ne peut exister (i.e : sans mesure), il suffit d’en ajouter une en donnant le nombre d’enregistrements. Cette mesure sera, elle aussi, invisible à l’utilisateur.
Ceci étant fait, il nous faut maintenant créer la dimension SnapshotDate qui sera consommée par l’utilisateur pour sélectionner une date d’effectivité. Pour bâtir cette dimension, nous pourrons, et devrions, réutiliser la même source de données qui sert de source à notre dimension Date. En revanche, pour cette dimension, il ne faut inclure que la clé de la dimension et la date.
En principe, cette dimension ne devrait contenir qu’un seul attribut, date d’image, et les membres de cet attribut ne devraient pas être additifs. Le membre par défaut pourra être attribué via le script de calcul MDX dans le cube, comme ceci :
ALTER CUBE CURRENTCUBE UPDATE DIMENSION [Snapshot Date].[Snapshot Date], DEFAULT_MEMBER = Tail([Snapshot Date].[Snapshot Date].Members,1).Item(0);
Nous avons maintenant les trois 3 objets nécessaires en place :
- La dimension Version Effectivity Date Range
- Le groupe de mesures Version Effectivity Date
- La dimension Snapshot Date
Il nous suffit alors que de créer les relations nécessaires entre tous ces objets ainsi que notre table de fait de la façon suivante :
- Relation régulière entre le groupe de mesures provenant de la table de fait comportant les versions et la dimension Version Effectivity Date Range,
- Relation régulière entre le groupe de mesures Version Effectivity Date et les dimensions Version Effectivity Date Range et Snapshot Date,
- Et pour boucler le tout, nous aurons besoin de créer une relation Many-To-Many entre le groupe de mesures provenant de la table de fait comportant les versions et la dimension Snapshot Date en spécifiant le groupe de mesures Version Effectivity Date comme groupe de mesures intermédiaire.
Après avoir rendu invisible la dimension Version Effectivity Date Range et le groupe de mesures Version Effectivity Date, nous nous retrouvons avec un cube n’ayant aucune donnée répétée inutilement. Nous en réduisons ainsi considérablement le volume et par voie de conséquence, le rendons plus performant.
Enfin, l’avantage majeur de cette solution est que nous permettons maintenant aux utilisateurs d’avoir une image des données effectives, pour chaque journée possible !
Cette approche, permettant d’avoir une relation de type Between, est réalisable grâce à la puissance et la performance de la relation Many-To-Many disponible dans SSAS.