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 lier… Hum… Je divague…
Je te vois faire n’importe quoi!
Alors voyons à quoi ça ressemble:
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) :
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 😉
La BI ça vous gagne! 
OMFG !
N’empêche que je suis forcément curieux de savoir quelle boite est responsable d’un(e) tel(le) désastre/naufrage/attentat/crime/vaste supercherie/arnaque.
Vous êtes vraiment réfractaires à toute forme de progrès
C’est surement l’âge David…
J’ai bien une idée de qui pourrait optimiser ainsi son code …. 😉
voilà une table faite pour Qlikview ? c’est magique nan ?
c’est une fusion des tables de fait , je trouve sa magique , mais l’inconvénient il aura du mal a le faire évolué , et aussi au niveau SSAS il dois faire tous le temps du warning cache pour gagner un peu de temps 😦 .
Pas loin Stéphane, pour Tableau 😉
Alors j’admets sans problème que côté requêtage on fasse n’importe quoi en T-SQL pour générer le bon dataset qui produira la visualisation souhaité. Les utilisateurs peuvent être torturés, pardon, exigeants, donc on s’adapte comme on peut.
Mais autant ça me hérisse le poil quand ce même n’importe quoi, par le culte du cargo, devient « bonne pratique » et modèle de données pour le stockage en base.
Un ami m’a appelé ce soir pour me parler de cette même modélisation (soit disant universelle, adaptée pour Tableau.. ;)) rencontrée cette semaine chez un client, on doit parler du même prestataire
@HMA : Fort possible que ce soit les mêmes « spécialistes » en effet.
D’après ce que j’en comprends, les nouveaux outils de visualisation, Tableau entre autres, utilisent cette technique comme format d’interface pour accéder aux données. L’idée étant que puisque toutes les associations entre les données sont recalculées en live « in memory », on peut tout mettre dans une même soupe au moment du requêtage et laisser la visualisation faire apparaître les liens « magiquement » (avec un quad-core, 8Go de RAM et un SSD).
En soit ça ne me gêne pas, dans un applicatif on fait souvent des choses pas forcément très élégantes pour permettre une fonctionnalité, mais c’est de la popote interne et ça ne devrait pas être sorti de son contexte.
Utiliser une technique optimisée pour de la visualisation, employée avec un moteur vertical in-memory, pour faire du stockage pérenne dans une base relationnelle classique, c’est vraiment la sortir de son contexte.
Bon allez Flo, balance le presta ! Au moins la première lettre ? :*)
Peux-tu nous en dire un peu plus sur ce que tu vas nous présenter pendant ta session aux journées SQL Server ?
C’est effectivement très mauvais.
Est-ce que l’utilisation de Tableau est une excuse valide? À ma connaissance (quasi-nulle), le produit a non seulement un assistant pour faire des jointures à l’import de données, mais supporte également les relations entres differentes tables.
@Alexis : t’es un malade toi, tu vas avoir des problèmes! Et pour le contenu de la session, suspense! (Ou plutôt: je suis à la bourre et tout n’est pas définitif! ;))
@TheDataSpecialist : quand je dis que Tableau l’utilise, ce que je veux dire c’est que si on construit un certain type de connexions multiples (de manière classique), et qu’on demande au logiciel d’afficher les données sous-jacentes, il va le faire sous ce format. On est bien d’accord, ça ne doit définitivement pas être la manière dont on lui fournit les données. Désolé si je n’ai pas été clair la première fois 😉
oulalalalalala merci de cette article. Chez mon client, j’ai le même problème !!!!!!!!!!!! Et je vais le transmettre à tout le monde en passant par mon DSI !!!!!!!!!!!!!!
Bonjour, il semblerait que la concaténation des tables de faits est une best practice pour éviter les boucles dans les modèles sur certaines solutions, évoquées ici, comme QlikView et Tableau…
Il s’agit effectivement du modèle classique en analyse décisionnelle sur des softs « agiles » avec dans l’idée de permettre à des utilisateurs lambda de s’en sortir sans taper dans du SQL hardcore. Pas de jointure : immense gain en rapidité et en performance sur ces softs.
Possibilité de créer des tableaux de bord comprenant par exemple le budget, le facturé et les achats; les uns empilés sur les autres. Un seul filtre pour les trois…
« Si on s’économise les jointures en SQL, j’ai peur de ce à quoi vont ressembler les clauses WHERE. »
On rajoute une dimension précisant le type de fichier, facile…
« Et là où les jointures reviendront en force, c’est si on veut obtenir un état avec par exemple du budget et du facturé. »
Non, vous ne semblez pas avoir compris le principe.
» Enfin, on l’a bien compris, impossible d’exposer ce modèle directement à un utilisateur. »
Il suffit de normaliser les noms de champ, de le ranger dans des dossiers sur l’outil de BI, etc..
« 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. »
Cela ne change absolument rien à l’existant, au contraire : on rajjoute des colonnes et des lignes, point barre. L’important c’est de flaguer systématiquement le type de fait et go!
Je rappelle enfin qu’il s’agit d’un principe de modélisation pour du reporting, pas du transactionnel. Je conçois que cela vous titille, je viens de l’ERP et les deux métiers diffèrent de manière gigantesque en terme de modélisation.
Je publie votre commentaire par respect pour votre temps passé à l’écrire, mais je ne pense pas que vous ayez du tout saisi de quoi je parle ici (et quand vous dites « On rajoute une dimension précisant le type de fichier, facile…« , alors qu’on parle d’accéder à des tables stockées dans une base SQL avec une requête SQL, je me dis qu’on ne parle définitivement pas de la même chose).
Soit ça, soit nous avons des avis tellement différents sur le sujet qu’échanger dessus par commentaires de blog n’est vraiment pas le bon format. N’hésitez pas à m’envoyer un mail si besoin (adresse dans la colonne de droite du blog).
PS: Et si cette réponse ne vous convient pas, laissez moi juste vous dire qu’entamer la conversation avec des phrases comme « Non, vous ne semblez pas avoir compris le principe. » n’aide pas vraiment votre cause 😉
Si je manque de clarté, c’est sans doute dû à la grippe qui me fait souffrir depuis quelques jours. Je réponds très rapidement
L’idée de ce modèle, c’est
1/ d’empiler les faits les uns au dessus des autres, typiquement avec un UNION en SQL.
2/ de flaguer les types de faits pour que tout le petit monde puisse s’y retrouver.
Sur Qlikview, l’empilement se fait avec la commande Concatenate directement dans la base de données associative chargée en mémoire (et potentiellement plus tard déversée dans des fichiers qvd mais c’est une autre histoire) à travers le script Qlikview. Cela ne concerne d’une certaine manière que le concepteur de l’application et pas le DBA.
Dans Tableau, vous ne disposez que d’une modélisation light de vos données : jointure possible mais pas d’UNION à travers l’assistant ou alors il faut saisir le code SQL a la mano, ce qui est totalement indigeste pour l’utilisateur visé par Tableau : quelqu’un du métier.
Ici, il convient de préparer les données en amont dans un DWH. Des ETL ou Alteryx permettent d’empiler tout notre petit monde en quelques secondes de travail, vous n’avez pas à vous soucier d’avoir les mêmes noms de colonnes, etc, l’outil le fait pour vous. Cela signifie surtout une base (voire un serveur complet) dédié à cela.
Quant à la praticité de la chose pour l’utilisateur ou le concepteur, elle est évidente. Pour reprendre votre exemple d’objectifs et facture -ici je l’agrege par article d’un côté et par article et client de l’autre.
Objectifs
Article |Montant Objectifs
A 1000
B 2000
Facturé
Article |Client| Montant facturé
A Toto 750
A Tata 400
B Toto 1850
Il me suffit de prendre les article, sum(montant facturé), SUM(montant objectif) pour avoir une comparaison rapide de mes deux mesures. Je peux aussi appliquer un filtre puisqu’ils partagent des colonnes.
A 1000 1150
B 2000 1850
Naturellement, il est des faits dans l’empilement n’est pas judicieux, je vous l’accorde.Dans votre exemple, j’ai quelques doutes quant à la pertinence de mettre l’inventaire dans la même table, notamment. Budget, Objectifs commerciaux et Ventes ensemble n’ont rie nde déconnant, je l’ai déjà mis en place chez des clients et cela fonctionne nickel (sur du Qlikview donc de manière autonome, je ne monte aucune base, etc..)
C’est un raisonnement totalement différent d’outils plus traditionnels, l’utilisateur final ne veut pas passer des requêtes SQL et se perdre dans des requêtes complexes. En sus, ces outils sont optimisés pour travailler ainsi.
Voilà l’interêt de la chose.
J’espère vous avoir convenablement répondu.
Et bien entendu, cela ne s’adresse que des modèles dédiés bi, reporting et jamais sur du transactionnel.
Et là, je vais chercher un kleenex.
Accessoirement, contrairement à ce que j’ai plus haut, Tableau n’est pas in-memory… à rebours de Qlikview.
Encore une fois je laisse passer le commentaire parce que je respecte le temps que vous avez du passer à l’écrire, mais encore une fois c’est complétement à côté de la plaque.
Je parle ici de modélisation dimensionnelle, utilisée pour construire le data warehouse. Le data warehouse.
Je ne parle pas du modèle utilisé dans les applications en aval, optimisée pour complètement autre chose que ce à quoi sert un data warehouse.
Je reformule: je me moque de comment Tableau ou Qlikview gèrent leur modélisation interne, ce n’est pas du tout le point de cet article. Je fais d’ailleurs 100% confiance aux gens brillants qui bossent chez ces éditeurs et qui ont à choisir la meilleure approche pour résoudre les problématiques propres à la visualisation de données, ce qui est encore une fois totalement en dehors du scope du data warehousing et de cet article.
Au passage, n’hésitez pas à lire les commentaires au dessus, qui datent d’il y a maintenant 2 ans, dans lesquels ces clarifications sont déjà faîtes…