Modélisation dimensionnelle : Dimensions hétérogènes en Sur-type et Sous-type

N’ayez pas peur du nom à rallonge, le concept est finalement assez simple 😉

Cette technique super utile est évidemment couchée sur le papier par nos amis les retraités, dans leur bible incontournable (oui je parle de Kimball…).

Couverture DWH Toolkit 3rd Edition

Imaginez que vous bossez pour une compagnie d’assurance, qui vend des polices d’assurance pour plusieurs types de produit: auto, moto, habitation, personnelle…

Côté modélisation dimensionnelle (après consultation du chapitre 16 du DWH Toolkit), on voit bien une table de fait qui va couvrir les transactions des polices, permettant de suivre le cycle de vie des dossiers:

  • Création / modification du dossier (détails de l’assuré, dates de début…)
  • Création de la couverture et association à l’objet à couvrir (type de produit, options… par exemple un tout risque auto + vol)
  • Obtention d’un devis avec la génération du tarif
  • Validation du devis et création de la police effective, demande des justificatifs
  • Obtention des justificatifs et pérénisation du dossier

A cette table de fait on va associer une série de dimensions, les axes d’analyse qui nous permettront de ventiler et analyser les faits. Ici on retrouvera les différentes dates, l’assuré, la couverture et l’objet couvert, l’employé responsable du dossier, les attributs du dossier…

Alt

Seulement voilà, après réunion avec le métier, on se rend compte que les attributs des dimensions Couverture et Objet Couvert ne sont pas du tout les mêmes entre le domaine auto et habitation. En effet, il paraît assez normal qu’on ne décrive pas une maison et une voiture avec les mêmes informations.

Il est quand même à noter que pour des soucis de consolidation et de reporting transverse, on doit tout de même constituer une table de dimension « chapeau », avec une série d’attributs simples qui concerneront à la fois l’auto et l’habitation (valeur, risque, localisation géographique…).

Côté modélisation, Kimball nous recommande alors d’utiliser la technique Sur-type / Sous-type (Supertype / Subtype). Nos tables de dimension initiales deviennent des tables de Sur-type, elles détiendront les attributs communs. A ces tables on va ajouter des tables de dimension de Sous-type, à savoir Couverture Auto, Couverture Habitation, Objet Couvert Auto et Objet Couvert Habitation, qui contiendront elles les attributs spécifiques à chaque ligne de business.

Alt

Deux choses à noter:

  • On réutilise les mêmes valeurs de Surrogate Key pour les dimensions Sur-type et Sous-type, inutile d’encombrer la table de fait avec des FK supplémentaires
  • On intercale des vues entre ce schéma et les utilisateurs, pour apporter:
    • la vision consolidée (toutes les lignes de la table de fait et uniquement la dimension Sur-type)
    • des visions par ligne métier (un filtre sur la table de fait pour n’exposer que les transactions du type) et la dimension Sur-type accompagnée de la dimension Sous-type:

Alt

Plutôt cool non? On a le beurre et l’argent du beurre avec cette approche 😉

Evidemment on ne peut appliquer cette méthode que si les mesures présentes dans la table de fait sont communes à tous les processus métier (Auto, Habitation…)

Dans le cas contraire, il est nécessaire de créer des tables de fait différentes, chacune avec ses mesures propres. Notez que dans ce cas, il est toujours possible d’utiliser l’approche Sur-type / Sous-type, pour disposer d’une table de dimension permettant la consolidation et le reporting transverse. Voir pourquoi ne pas aller plus loin et créer une table de fait de consolidation, qui portera les mesures communes et la table de dimension Sur-type. Le gros avantage c’est d’être capable d’exposer en une passe les données de haut niveau sans repasser par 2 tables de bas niveau qui peuvent être plus lourdes (mais on s’en passe très bien si la solution contient un cube OLAP).

Pour aller plus loin: les chapitres 10 et 16 du Datawarehouse Toolkit 3rd Edition.

Pourquoi un datawarehouse ?

Au-delà de la réponse théorique, c’est une question à laquelle il faut bien répondre dans le contexte du projet en cours.

Le cas du jour : une recette qui ne se termine pas chez un client, principalement parce que leurs applicatifs sont des sources inépuisables d’anomalies de données.

Alors ça ne rate pas, le chef de projet MOA et le sponsor s’agacent en comité de pilotage : « Y’en a marre, ça n’en finit pas, en plus vous êtes en régie et ça nous coûte trop cher ».

Certes, mais l’objectif initial du datawarehouse, ça n’était pas d’avoir une base centralisée, consolidée, propre de la comptabilité du groupe ?

Parce que la plupart des anomalies rencontrées sont bien des erreurs dans les données, qu’elles soient mal saisies dans les systèmes comptables ou résultantes de bug de ces mêmes systèmes. Chaque anomalie résolue c’est des données nettoyées, c’est une comptabilité plus propre, c’est donc bien de la valeur ajoutée !

Celui qui en a conscience c’est le futur utilisateur de la solution, responsable du contrôle de gestion qui se doutait que sa compta n’était pas bonne, qui en a enfin la preuve, et qui est enfin capable de le détecter et de le corriger.

Reste à convaincre les décideurs…

Dilbert du 06/03/2013

Quel livre pour apprendre le MDX?

Voici l’occasion de me servir de ce blog pour demander votre aide! En effet je renouvelle la bibliothèque de notre pôle de consultants en BI Microsoft, et je suis tombé sur un os en ce qui concerne la partie MDX.

Car autant j’ai une vision assez claire des références absolues sur quasiment tous les sujets décisionnels (voir en dessous), autant mon guide du MDX, Fast Track to MDX – celui qui m’a tout appris, commence à dater un peu. Quelqu’un aurait un ouvrage à me recommander sur ce sujet? Un cookbook peut être?

Et pour référence, voici mes ouvrages recommandés par sujet. N’hésitez pas à commenter cette liste 🙂

Merci à tous dans les commentaires 😉

Savoir rester lean dans son projet décisionnel

Vous vous demandiez peut-être à quoi je passais mon début d’année (plutôt que de vous écrire des articles de 3 pages de long) ? Et bien en dehors de ma récente promotion (une fois nos expérimentations d’organisation stabilisées je vous en parlerai, soyez patients), je le consacre à la finalisation d’un projet de plusieurs mois, réalisé par un padawan de talent et supervisé par mes soins.

Padawans en tenue

Sont pas beaux mes padawans? Tenue obligatoire en clientèle!

Entre 2 anomalies à corriger, nous rédigeons la documentation et préparons le voyage de l’application de l’environnement de développement, et de notre responsabilité, à celui de production, et à la responsabilité de l’exploitation. Au passage c’est toujours pendant ce genre de transition que l’idée du devops revient me faire des yeux doux. Ou encore le concept de livraison continue. Malheureusement ici je n’ai pas réussi à convaincre le métier et l’IT de bosser en mode Agile. Mais ce n’est pas le sujet du jour, revenons à nos moutons !

La documentation technique terminée, elle est relue par le directeur d’étude (respect, ce n’est pas souvent que ça arrive). Et ce dernier s’étonne : quasiment pas de règles de gestion, un schéma en étoile simpliste, un cube à la DSV vide, des dimensions sans surprises techniques. Ok on trouve bien quelques calculs MDX, et ok la partie ETL fait pas loin d’une centaine de packages, mais ce n’est que parce qu’on a fait un bon travail et bien tout découpé en flux de données unitaires. En dehors de ça la solution semble étonnement vide pour 3 mois de développement.

Un tout petit plat gastronomique dans une grande assiette

Minimaliste mais plein de goût, comme au resto gastronomique!

Et bien pour tout vous dire, j’en suis assez fier. Parce que pour arriver à cette apparente simplicité, ce « vide » dans la solution, cela nous a demandé des efforts considérables. Un projet informatique est soumis à une certaine forme d’entropie. Plus on avance, plus des éléments viennent se rajouter, des fonctionnalités, des données, des exceptions, des contournements, et si on n’y fait pas attention, sans rien toucher au périmètre initial de l’application, la solution est tout de même devenue un plat de spaghettis indémêlables avant même la livraison du lot 1.

Lutter contre cette tendance demande un effort constant. Dès qu’on se relâche, on se retrouve avec des règles de gestion implémentées dans des vues (ou pire, dans la DSV du cube), parce que c’est rapide, plutôt que de faire les choses proprement et d’ouvrir une énième fois l’ETL, modifier les métadonnées, les flux, et tout re-tester après.

Mais il ne faut pas se leurrer : tout ça c’est de la dette technique, et rien de pire pour une solution informatique de commencer sa vie encore plus endettée qu’un étudiant américain ! Alors ne relâchez pas la lutte, pensez marathon plutôt que sprint (rien à voir avec SCRUM ;)), pensez Lean, et faîtes un cadeau au vous de dans 6 mois qui devra bosser sur le lot 2 ou corriger une anomalie: laissez-lui une solution toute propre, il vous remerciera!

Modélisation dimensionnelle à éviter : La table de faits universelle

Comme vous le savez peut-être, cette année encore je co-animerai la session Modélisation Dimensionnelle aux Journées SQL Server 2012, les 10 et 11 décembre sur Paris, avec mon camarade Charles-Henri. Cette année on passe level 300 (ça commence à causer plus sérieusement) et franchement je pense qu’on va passer un bon moment 🙂

En attendant le jour J, je voulais vous parler d’une technique qui ne sera pas présentée lors de la session : celle de la table de faits universelle. Rencontrée chez un client dernièrement, c’est une modélisation qu’on peut aussi appeler la table de faits unique. Une table de faits pour les gouverner toutes. Une table de faits pour les trouver. Une table de faits pour les amener toutes et dans les ténèbres les lierHumJe divague

Je te vois faire n'importe quoi!

Je te vois faire n’importe quoi!

Si ça avait été fait par un stagiaire, ou un client qui s’essayait au décisionnel en dilettante, je trouverais ça mignon. Sincèrement. J’applaudirais pour l’effort et on prendrait une demi-journée ensemble pour causer modélisation. Mais là c’est réalisé par une équipe de consultants spécialisés dans le décisionnel.  Et c’est facturé. Moins mignon.

Alors voyons à quoi ça ressemble:

La table de faits universelle

Dans cette même table de faits, qui s’appelle juste « Fait » (c’est plus simple) on retrouve :

  • Les ventes quotidiennes
  • L’inventaire hebdomadaire
  • Les budgets trimestriels des magasins
  • Les objectifs trimestriels des commerciaux

C’est quand même bien fait ! On a tout sous les yeux d’un seul coup. Pas besoin de jointures, les requêtes SQL sont simplissimes. Alors que reprocher à cette modélisation ?

Déjà, je vous avoue qu’en 6 ans de missions en décisionnel, je n’ai jamais vu ça. J’en ai même parlé lors d’un afterworks du GUSS, auquel étaient présents des consultants d’à peu près tous les pure-players en décisionnel Microsoft, et personne n’en avait entendu parler non plus.

Mais vous me connaissez, je n’allais pas me limiter à ça. Regardons donc ce qu’en dit la littérature :

  • Wikipedia – Fact Table : “In data warehousing, a fact table consists of the measurements, metrics or facts of a business process.”  Une table de faits pour un processus métier donc, les ventes ou l’inventaire ou les budgets… mais un seul. J’avoue, en effet, ils auraient pu insister et mettre: “ a SINGLE business process”. Mais à mon avis personne ne se doutait qu’on verrait arriver la table de faits unique.
  • Wikipedia – Base de données relationnelle : « Dans une base de données relationnelle chaque enregistrement d’une table contient un groupe d’informations relatives à un sujet (…) ». Même commentaire, et là on parle de toute la technologie de la base de données relationnelle, plus seulement du décisionnel.
  • Ralph Kimball, l’inventeur du schéma en étoile, indique lui que chaque table de faits représente un processus métier, que chacune de ces tables est reliée à des dimensions, les mêmes dimensions pour tout le monde (alors dites conformées), et que toute la valeur de la modélisation en étoile vient justement de là. Parce qu’entre nous, quitte à faire une table de fait unique, autant pas s’embêter à faire des tables de dimensions hein… Et là le lien je le fais par vers un article spécifique, mais vers le bouquin de Kimball, parce qu’à un moment il va falloir le lire ce livre si vous vous dites consultant ou développeur décisionnel.
  • Bill Inmon, l’inventeur du schéma en flocon, indique la même chose. En effet les différences entre les deux modèles se situent au niveau de la structure des dimensions et du processus de génération du modèle, pas des tables de faits.
  • Et quid de Datavault ? La troisième modélisation très contestée du décisionnel ? Là c’est pire puisqu’on normalise complètement et qu’on conserve le format source original (une table pour les clients, une table pour les magasins, une table de relation entre les 2, etc, etc). Pas de table unique en vue.

Pas de chance, la littérature ne fait donc aucune mention de cette technique, et c’est même plutôt l’inverse qui est recommandé : créer une table de faits par processus métier. Soit dans notre cas, 4 tables : ventes, inventaires, budgets et objectifs.

Je précise au passage que dans ces sources, il ne faut pas interpréter la phrase « la table de faits est au centre du schéma en étoile » comme une indication qu’il n’y en ait qu’une seule. En effet un datawarehouse ce n’est pas un mais plusieurs schémas en étoile, plusieurs datamarts, autant qu’il y a de processus métier. Et en théorie l’ensemble de ces étoiles s’appelle une constellation, mais ça devient trop poétique donc on emploie rarement le terme.

D’une manière plus pratique, si on abandonne la littérature et qu’on s’interroge sur les mérites d’une telle modélisation, on peut se faire les réflexions suivantes :

  • Performances
    • A priori elles ne seront navrantes. En effet pour aller chercher un élément particulier de la table (les budgets), le moteur doit parcourir toutes les lignes de la table (les ventes, les inventaires…). C’est largement inefficace.
    • L’index le plus rapide de tous est l’index cluster (celui qui dicte comment les données sont écrites sur le disque). Comme vous le savez, on ne peut en définir qu’un seul par table (par définition). Tout mettre dans la même table c’est donc se priver d’un des meilleurs outils d’optimisation de la base de données. A la place d’en avoir un par processus métier, il n’y en aura qu’un seul, qui en plus ne sera pas très bon. Car évidemment, l’index s’optimise différemment en fonction du sujet. On indexe les ventes (par jour/magasin/produit) différemment qu’on indexe les objectifs (par trimestre/commerciaux). Et croisez les doigts pour que l’unicité des lignes des 4 processus métiers tiennent en moins de 16 colonnes.
    • Même remarque pour le partitionnement.
  • Confort d’utilisation / Qualité du requêtage
    • Si on s’économise les jointures en SQL, j’ai peur de ce à quoi vont ressembler les clauses WHERE. Et on n’a pas intérêt à se tromper sur ces filtres, sans quoi on va additionner des choux et des carottes (des quantités de ventes et des quantités d’inventaires). Le risque métier est important avec cette approche, il est inexistant avec la modélisation classique.
    • Et là où les jointures reviendront en force, c’est si on veut obtenir un état avec par exemple du budget et du facturé. Il faudra en effet faire une auto jointure (en FULL OUTER JOIN) de la table unique sur elle-même. Ce sera douloureux en écriture de requête et en performance.
    • Enfin, on l’a bien compris, impossible d’exposer ce modèle directement à un utilisateur. Il faudra définir un modèle de métadonnées devant chaque outil de reporting (Excel, Tableau, SSAS, SSRS…). Attention au coût de développement masqué.
  • Maintenabilité / Evolutivité
    • J’ai peur que l’ajout d’un nouveau processus métier (comme il est prévu dans le lot 2 j’imagine ?) ne se traduise par l’ajout de nouvelles colonnes dans cette table. Dans ce cas il faudra changer toutes les requêtes déjà développées (clauses WHERE, agrégations), toutes les métadonnées, et toutes les optimisations déjà réalisées. En somme il faudra tout refaire. A chaque évolution.
    • Enfin, si on s’enferme dans cette architecture, impossible de trouver un prestataire digne de ce nom qui assurera la TMA ou les évolutions sans d’abord tout refondre.

Bon et bien on le voit, si c’est une nouvelle théorie, c’est l’équivalent de remplacer les groupes sanguins par les signes du zodiaque pour déterminer la compatibilité dans les transfusions sanguines. De temps en temps ça va marcher, certes, mais sur le long terme…

Et sinon, comment modéliser ça de manière satisfaisante ?

En identifiant les dimensions utilisées pour chaque processus métier, leur grain, et en construisant les tables de fait en fonction (c’est dans le livre, ou dans le webcast) :

Oh la jolie étoile!

PS : Les périodes temporelles diverses (semaines, trimestres) sont gérées directement dans la dimension temps.

Là on dispose d’une constellation composée de 4 étoiles, qui utilise des dimensions conformées (partagées), qui répond aux problématiques de performance, de confort d’utilisation et de maintenabilité. Si on souhaite intégrer un nouveau processus métier, on ajoute une nouvelle table de faits, sans avoir à modifier l’existant. Chaque processus peut évoluer indépendamment des autres. Chaque amélioration d’une dimension profite à toutes les analyses.

De tout ça on en reparle lundi 10 décembre, aux Journées SQL Server 2012. Inscrivez-vous 😉

Décisionnel Agile : Réaliser son Datawarehouse en itérations agiles

Ça y est, vous avez bien intégré les valeurs de l’Agilité et vous vous êtes décidés pour construire votre datawarehouse en utilisant une méthode Agile. Excellent !

Déjà vous allez devoir convaincre les gens autour de vous que c’est possible. En effet la réaction naturelle dès qu’on parle de construire une solution décisionnelle en mode Agile c’est l’incrédulité. Autant pour la partie reporting personne ne voit vraiment de problème, souvent c’est même le contraire, autant côté back end – l’ETL et le datawarehouse à proprement parler – les reproches fusent :

  • Les alimentations sont lourdes et compliquées, on ne peut pas les réaliser rapidement
  • L’entrepôt est monolithique, vu les volumes on ne pourra pas le faire évoluer facilement

Pourtant, dès 1998 Kimball (notre père à tous 😉 ) publie les 3 concepts de base (PDF) du cycle de vie du datawarehouse :

  1. Mettre l’accent sur l’ajout de valeur métier pour toute l’entreprise
  2. Délivrer les données à travers les dimensions
  3. Développer une solution de façon itérative, livrer des incréments compréhensibles, plutôt que de travailler en mode big bang.

Vous avez vu la 3 ? Et la 1 ? Quel talent ce Ralph, en 1998 il faisait déjà de l’Agilité 🙂

Si le pape du datawarehousing dit qu’il faut le faire – notez qu’il ne dit pas qu’on devrait le faire, mais qu’il faut le faire – c’est bien que c’est faisable non ! Le tout c’est de savoir comment.

Dilbert.com

Là encore on écoute Monsieur Kimball, qui applique parfaitement l’Agilité et qui nous donne le mantra du découpage (Slide 19) : « Meaningful but Manageable » – « Porteur de sens mais Gérable ».

Point de vue implémentation l’objectif devient d’identifier la plus petite fonctionnalité qu’on puisse livrer à l’utilisateur qui porte encore du sens par rapport à ses besoins. Si on suit la méthodologie Kimball, cela veut dire qu’on va se concentrer sur un seul processus métier à livrer à chaque fois. Autrement dit, une seule table de fait (ou une poignée de tables) et les quelques dimensions qui lui vont bien. Soit on livre ces tables et on les rend accessibles en SQL, donc exploitable par l’utilisateur, soit on les accompagne d’un petit rapport SSRS ou un petit cube SSAS tout simple généré en une demi-journée. Là on a une itération courte et qui apporte de la valeur.

Oui mais même une table de fait et 4 dimensions ça peut prendre bien plus que 2 semaines à fabriquer, recetter et livrer ! Si on a des problèmes de qualité de données, si on fait du multi-source…

Ok. Alors on va se concentrer sur une seule source de données (une seule instance, un seul applicatif source…), sur un seul périmètre de données dans une même source (la France parmi le monde…), pour réduire le périmètre métier de la fonctionnalité, tout en maintenant son intérêt pour l’utilisateur, jusqu’à obtenir quelque chose d’unitaire et de livrable.

Par exemple, dans le cas d’une solution décisionnelle RH, on commencerait par importer uniquement les données actuelles, sans reprise d’historique. On récupèrerait uniquement les effectifs, uniquement d’une source choisie et d’une seule instance de cette source. On ne ferait de la qualité de données que de bas niveau (unicité, validation des types…) en rejettant tout ce qui n’est pas conforme. On ne ferait que peu de transcodage (uniformisation des critères type sexe, situation familiale…). Et on arriverait à générer un premier rapport simple mais précis sur la situation actuelle des effectifs sur ce premier périmètre. A partir de là on pourra itérer pour améliorer la couverture et/ou la qualité de la solution, en fonction des priorités arbitrées par l’utilisateur. Parfait ! 😉

Ce qui est important, c’est qu’il faut découper son projet selon l’axe métier, le périmètre fonctionnel, et pas selon l’axe technologique. Il faut faire du bout en bout (de l’ETL au reporting) en traitant un sujet simple, plutôt que de faire tout l’ETL, avant de passer à une autre brique :

Découper son datawarehouse en itération agiles

Il faut penser ses fonctionnalités comme les vertes, et pas comme les rouges!

Au départ cela peut paraître difficile, voir même contre-productif. Et ça le sera d’ailleurs certainement au début. Mais cela mène à une plus grande expertise, une plus grande maîtrise de la solution de bout en bout et à une bien meilleure satisfaction client. En 2 semaines on va pouvoir lui générer un premier rapport qu’il pourra effectivement employer pour améliorer son quotidien.

Evidemment il existe des cas particuliers. Si vous êtes à fond dans la qualité de données, on peut considérer qu’une table de transcodage propre, avec une petite interface d’administration en ASP.NET, c’est une fonctionnalité qui a de la valeur pour l’utilisateur. Dans ce cas on n’est pas obligé d’aller jusqu’au modèle dimensionnel pour avoir de la valeur.

Et une fois que votre datawarehouse sera monté et stable, vous pourrez faire des itérations facilement au niveau de SSAS, SSRS ou tout autre outil de haut niveau, là on retombe sur quelque chose de plus facile à gérer.

Un conseil important au niveau de l’ETL : perdez le réflexe de vouloir importer tous les champs de tous les fichiers ou tables sources. Il faut savoir minimiser la largeur (le nombre de colonnes) à importer après la collecte (Extract d’ETL) dans le phase de nettoyage/transformation (Transform d’ETL). Chaque colonne a un coup de maintenance, que vous l’utilisiez ou pas, elle a un impact sur les performances (largeur du buffer), sur la maintenabilité (les bugs qu’elle peut générer) et dans l’évolutivité (lourdeur des métadonnées à mettre à jour). Il faut savoir rester lean de bout en bout, même si nos instincts d’écureuils de la forêt nous encouragent à stocker toutes les noisettes qu’on peut trouver !

Une noisette pour les diriger toutes!

Si vous voulez stocker ces informations parce qu’elles sont périssables, utilisez ce type d’architecture comportant une base d’archivage des données au format source. Vous pourrez l’utiliser si besoin de reprise d’historique :

Archivage des données périssables

Enfin, tout cycle de développement Agile commence par une phase d’initialisation : rencontre des intervenants, découverte des premiers besoins, installation des produits, prototypage… qui ne sont pas à proprement parler des itérations. C’est normal, pas d’inquiétudes à avoir, la transition vers les itérations se fera naturellement quand on vous demandera la première date de livraison 🙂

Voilà, on a fait le tour de l’itération décisionnelle, n’hésitez pas à poser vos questions dans les commentaires. Mais notez tout de même que cela ne suffit pas pour avoir un vrai décisionnel Agile. La brique essentielle qu’il manque encore c’est le cycle d’amélioration continu, tant au niveau de l’équipe que du datawarehouse. Ça c’est un sujet pour un autre jour 😉