Design d’un Data Warehouse – Zoom sur la modélisation en étoile

Il y a en gros trois modélisations possibles pour organiser les données stockées dans un Data Warehouse : la modélisation en étoile, en flocons et en constellation.

Aujourd’hui, nous allons voir en quoi consiste la modélisation en étoile, ce qui la caractérise, ses avantages et ses limites.

Disons d’emblée que la modélisation en étoile est la plus communément utilisée aujourd’hui encore dans la mesure où elle simplifie les requêtes SQL et en réduit au maximum le temps d’exécution, y compris sur d’énormes volumes de données.

Avant d’entrer dans le coeur du sujet, il est important de bien comprendre la différence entre Data Warehouse et Data Mart.

La différence entre le Data Warehouse et les Data Marts

Un Data Warehouse est un système utilisé pour stocker les données de l’entreprise en vue de faire de l’analyse et du reporting (Business Intelligence). Les données du Data Warehouse sont importées dans le système en batch à partir des sources de données dynamiques issues des systèmes transactionnels/opérationnels de l’organisation. Les données du Data Warehouse sont largement « statiques » : elles ne font pas l’objet de mise à jour.

Les Data Marts sont des compartiments du Data Warehouse conçus pour répondre à un besoin métier spécifique. Un Data Mart regroupe et organise toutes les données nécessaires au traitement d’une problématique précise. Par exemple : toutes les données relatives aux ventes, ou bien toutes celles relatives à la logistique, au marketing, à la production, etc.

Pour prendre une image, le Data Warehouse peut être comparé à un immeuble de bureaux et les Data Marts aux différents bureaux qui le constituent.

Les Data Marts ont donc une utilité du point de vue des utilisateurs mais aussi en terme de sécurité. Au lieu que tous les utilisateurs aient accès à toutes les données stockées, l’organisation en Data Marts permet de contrôler et de restreindre les accès aux données en fonction des besoins des utilisateurs.

Il y a deux approches concurrentes pour rendre compte de la relation entre le Data Warehouse et les Data Marts :

• L’approche Top-Down, qui est l’approche historique, imaginée par le « père des Data Warehouses », l’informaticien américain Bill Inmon.  Dans cette approche, les Data Marts sont créés à partir du Data Warehouse qui réunit toutes les données au plus haut niveau de détail. Ils viennent donc après, ils en découlent. L’immeuble précède les bureaux !
• L’approche Bottom-Up, promue par Ralph Kimball, selon laquelle le Data Warehouse est la combinaison des différents Data Marts, lesquels sont conçus en premier. Les bureaux précèdent l’immeuble !

Le process ETL est utilisé pour charger en batch les nouvelles données dans le système d’analyse Data Warehouse / Data Marts. ETL est l’acronyme de Extract – Transform – Load. Comme son nom l’indique, l’ETL est le process qui consiste à extraire les données de toutes les bases opérationnelles/transactionnelles de l’entreprise (appelées OLTP, Online Transaction Processing), à les transformer pour les adapter à la structure du Data Warehouse et à les charger ensuite dedans. Techniquement parlant, l’ETL est le processus qui consiste à copier les données issues des tables des systèmes transactionnels vers les tables de données du Data Warehouse.

Ce process est assuré par des outils spécifiques. Pour en savoir plus, nous vous invitons à lire le comparatif complet que nous avons rédigé sur les outils ou logiciels ETL.

Découvrez notre comparatif complet des logiciels ETL : Cloud Vs On-Premise Vs Open Source.

Les imports dans le Data Warehouse sont réalisés suivant des batchs réguliers. Ce point est important. Contrairement aux données des bases transactionnelles qui évoluent et sont mises à jour en temps réel, les données du Data Warehouse ne sont pas mises à jour en temps réel. Elles sont essentiellement statiques. C’est pour cette raison que la dénormalisation (et les redondances qu’elle engendre) pose moins de problèmes que lorsque l’on a affaire à une base de données opérationnelles. Nous verrons tout à l’heure que la modélisation en étoile entraîne une forte dénormalisation des données.

La modélisation des données, la plupart du temps, s’effectue au niveau des Data marts. C’est pour cette raison que l’on aurait tout aussi bien pu appeler notre article « Design d’un Data Mart – Zoom sur la modélisation en étoile ». Chaque modélisation en étoile désigne le modèle d’organisation des données d’un Data mart donné.

Mais avant de voir ce qu’est le modèle en étoile, rappelons les principes de la modélisation de données en général et ses deux composantes : les tables de faits et les tables de dimensions.

La modélisation dimensionnelle du Data Warehouse : Tables de faits & Tables de dimension

Venons-en justement au sujet de la modélisation dimensionnelle. Dans un Data Warehouse (et au niveau de chaque Data mart), les données et leurs relations sont organisées suivant un modèle de données spécifique. Le choix du modèle de données structure et définit le design du Data Warehouse. Nous avons vu qu’il existait trois modélisations possibles :

• La modélisation en étoile,
• La modélisation en flocons,
• La modélisation en constellation.

Nous dirons tout à l’heure un mot du modèle en constellation, mais nous allons nous concentrer dans cet article sur la modélisation en étoile qui est la plus simple et la plus répandue.

Dans n’importe quelle modélisation dimensionnelle, il faut distinguer deux types de tables. C’est le mode d’interconnexion des tables qui caractérise une modélisation. On trouve donc :

Les tables de dimensions

Les tables de dimensions sont utilisées pour décrire les données que l’on souhaite stocker dans le Data Warehouse. Prenons un exemple. Vous êtes un retailer et vous voulez enregistrer la date, le magasin et l’employé associé à chaque achat. A chaque catégorie de données correspond une table de dimension : la table de dimension date, la table de dimension magasin et la table de dimension employé.

Chaque table de dimension peut avoir et a en général plusieurs attributs. Par exemple, si l’on prend la table de dimension employé : le prénom, le nom, la date de naissance…Si l’on prend la table de dimensions magasin : la ville, le département, le pays…

Dans la modélisation en étoile, il arrive souvent que des attributs soient déduits d’autres attributs dans le même enregistrement (nous l’illustrons plus bas dans l’article avec la dim_time du schéma des ventes). Cette redondance est délibérée et permet d’optimiser la performance des requêtes.

Dans la conception d’une modélisation dimensionnelle, le choix des dimensions et du niveau de granularité sont déterminants. Les dimensions déterminent la manière dont les données en provenance des data sources sont agrégées dans le Data Warehouse (dit autrement : la manière dont l’outil ETL « transforme » les données).

Découvrez notre Zoom sur les deux principales approches en matière d’architecture Data Warehouse : Cloud Vs On-Premise.

Les tables de faits

Les tables de faits contiennent les données que l’on souhaite voir apparaître dans les rapports d’analyse, sous forme de métriques. Les données des tables de faits sont agrégées à partir des tables de dimensions qui leur sont associées.

Une table de faits se présente sous la forme d’un ensemble de colonnes stockant les valeurs et les clés étrangères (identifiants) associées aux tables de dimensions. La combinaison des différentes clés étrangères est ce qui forme l’identité de la table de faits, sa clé primaire. La table de faits n’a donc pas besoin d’avoir une clé primaire propre : elle est définie et identifiée par une combinaison unique de clés étrangères. Autrement dit : par l’ensemble des tables de dimensions qu’elle met à contribution pour créer les reportings.

Par exemple, une table de faits peut stocker un nombre de contacts et le nombre de ventes associée à ces contacts. Elle combine deux tables de dimensions pour produire ses analyses. Chaque ligne de la table des faits correspond à une mesure.

Nous pouvons à présent voir quelles sont les caractéristiques de la modélisation en étoile.

Découvrez tous nos conseils pour choisir la technologie de votre Data Warehouse.

Focus sur la modélisation en étoile

Une image valant souvent plus que 1 000 mots, voici comment se présente une modélisation en étoile (il s’agit de la schématisation d’une modélisation en étoile du Data mart des ventes):

Comme nous l’avons dit, la modélisation en étoile est le modèle le plus simple et celui qui est le plus couramment utilisé dans le design des Data Warehouses. Le schéma ci-dessus permet de comprendre l’origine de cette appellation « en étoile ».

Dans ce modèle, la table des faits est au centre du schéma et est entourée par des tables de dimensions. Cela ressemble visuellement à une étoile – surtout lorsqu’il y a 5 branches ! Lorsque la table des faits est entourée par un grand nombre de petites tables de dimensions, on parle de schéma centipède. C’est une variante du modèle en étoile. Pour filer la métaphore, les tables de dimensions peuvent être comparées à des planètes.

Les schémas en étoile sont très utilisés pour concevoir les Data Marts. La modélisation en étoile peut être directement associée à l’approche Top-Down que nous décrit plus haut. La modélisation est obtenue par agrégation de tables de dimensions extraites du Data Warehouse.

Cette modélisation a une orientation très métier dans la mesure où chaque table des faits correspond à un objet d’étude : les ventes, les achats, la logistique, la production, etc. La table des faits contient tous les faits et toutes les mesures associées à l’objet d’étude – l’essentiel des données qu’elle contient sont des données chiffrées : montants, quantités, taux…Dans l’exemple ci-dessus, ces faits sont au nombre de deux : prix et quantités. Au-dessus de ces faits figurent les clés étrangères renvoyant vers les 5 tables de dimensions.

Les tables de dimensions correspondent aux différents axes d’analyse utilisés pour étudier l’objet d’étude. Les mesures observables dans la table des faits sont issues de l’agrégation des données stockées dans les tables de dimensions.  Visuellement parlant, les lignes de la table des faits correspondent aux faits mesurés et les colonnes aux différentes dimensions.

Comme nous le disions en introduction, le principal avantage de la modélisation en étoile est qu’il permet d’optimiser la performance et la simplicité des requêtes effectuées sur de grands volumes de données. Nous savons maintenant pourquoi. Le schéma en étoiles, par sa structure essentiellement redondante, permet d’agréger la table de faits avec n’importe quelle dimension en une unique opération de jointure.

La modélisation en étoile par l’exemple

Nous allons vous présenter deux exemples de schémas en étoile : le schéma des ventes et le schéma des achats.

Exemple #1 – Le schéma des ventes

Le rapport des ventes est l’un des rapports les plus communs. C’est celui que nous avons présenté plus haut.

Tant que le volume de données reste limité, il est possible de générer ces rapports en temps réel directement à partir des données extraites des systèmes transactionnels. Mais lorsque les données en jeu deviennent trop volumineuses, il devient nécessaire de mettre en place un Data Warehouse pour rationaliser le process.

La mise en place de ce schéma (ie. la création du Data Mart « Sales ») suppose :

• Le design du schéma, dont l’essentiel réside dans la sélection des dimensions.
• La mise en place de la tuyauterie, c’est-à-dire le paramétrage des process ETL pour importer les données à partir des data sources et les transformer suivant les contours du schéma défini.
• Le chargement des données transformées dans le Data Mart.

Le schéma des ventes contient une table des faits (en rouge) et 5 tables de dimensions (en bleu) :

• fact_sales : cette table contient les références des tables de dimensions ainsi que deux faits : prix et quantité vendue. Les 5 clés étrangères qui composent cette table forment la clé primaire de la table des faits.
• dim_sales_type : cette table de dimensions ne contient qu’un seul attribut : type_name.
• dim_employee : cette table stocke les attributs de base sur les employés : prénom, nom et date de naissance.
• dim_product : cette table de dimensions n’a que deux attributs (en dehors de la clé primaire) : le nom du produit et la catégorie.
• dim_time : cette table gère la dimension temporelle. Elle contient 5 attributs à côté de la clé primaire. Cette table est intéressante pour illustrer ce que nous disions tout à l’heure concernant les attributs qui sont extraits d’autres attributs. En l’occurrence, tous les attributs de cette table découlent de l’attribut action_date. La date de la vente contient par elle-même : la semaine, le mois, l’année et le nom du jour. Cela produit des redondances mais facilite les analyses.
• dim_store : cette table contient 5 attributs, les 4 derniers (ville, région, état et pays) découlant du premier (adresse). Cette table et la précédente permettent de voir à quel point la modélisation en étoile est essentiellement dénormalisée.

Exemple #2 – Le schéma des achats

Nous allons rapidement vous présenter un deuxième exemple de schéma en étoile. Vous comprendrez dans un instant pourquoi nous prenons le temps de vous présenter ce deuxième exemple (a priori, un seul suffit à comprendre !).

Voici à quoi ressemble un schéma des achats :

Il y a beaucoup de points communs entre ce schéma et celui des ventes. Il partage en effet trois tables de dimension avec le précédent. Les deux tables nouvelles sont :

• fact_supply-order : la table des faits, qui agrègent les données relatives aux achats et provenant des 4 tables de dimensions associées.
• dim_supplier : cette table de dimensions stocke les données relatives aux fournisseurs et utilise les mêmes attributs que la dim_store du schéma des ventes.

Découvrez tous nos conseils pour créer votre premier Data Warehouse.

Les avantages et les inconvénients de la modélisation en étoile

La modélisation en étoile comporte de nombreux avantages. Le premier d’entre eux, c’est que la table des faits est reliée à chaque table de dimensions par une seule et unique relation, une seule et unique jointure. Cela simplifie considérablement les requêtes et en améliore le temps d’exécution. Lorsque l’on travaille sur de gros volumes de données, c’est un avantage important.

La requête ci-dessous réalisée sur le Data Mart des ventes permet de connaître la quantité de téléphones vendus (tous types confondus) dans les magasins de Berlin en 2016 :

SELECT
  dim_store.store_address,
  SUM(fact_sales.quantity) AS quantity_sold
 
FROM
  fact_sales
  INNER JOIN dim_product ON fact_sales.product_id = dim_product.product_id
  INNER JOIN dim_time ON fact_sales.time_id = dim_time.time_id
  INNER JOIN dim_store ON fact_sales.store_id = dim_store.store_id
 
WHERE
  dim_time.action_year = 2016
  AND dim_store.city = 'Berlin'
  AND dim_product.product_type = 'phone'
 
GROUP BY
  dim_store.store_id,
  dim_store.store_address

La quantité de données analysée pour obtenir le résultat de la requête est considérable et pourtant la requête est très simple.

Mais la modélisation en étoile a un gros inconvénient : la redondance. Ce modèle a l’avantage de ses inconvénients…Chaque dimension est stockée dans une table de dimensions distincte ce qui entraîne une dé-normalisation. Dans notre exemple, les villes appartiennent à des régions ou à des états, lesquels appartiennent à des pays et cette relation n’est pas intégrée en tant que règle dans la base de données. En un mot, un Data Mart conçu à partir d’une modélisation en étoile stocke des données qui expriment la même information.

A cause de cette redondance, la modélisation en étoile utilise plus d’espace de stockage que les autres modèles. Cela augmente également le risque d’atteinte à l’intégrité du système, les données exprimant les mêmes informations pouvant provenir de data sources différents et engendrer des conflits.  

C’est pour cette raison que d’autres modélisations sont apparues et en particulier la modélisation en constellation. C’est sur celle-ci que nous conclurons notre article.

De l’étoile à la constellation

Les deux schémas que nous venons de présenter (ventes et achats) peuvent être considérés comme deux Data Marts, deux compartiments du Data Warehouse : un Data Mart pour les ventes et un Data Mart pour les achats. Chacun d’entre eux ne contient qu’une seule table des faits entourée de quelques tables de dimensions.

L’idée est venue à certains de combiner entre eux les différents schémas et de les fusionner dans un seul : c’est ainsi qu’est né la modélisation en constellation. Une constellation, comme chacun sait, est composée d’un groupe d’étoiles.

Le modèle en constellation se compose donc de plusieurs tables des faits avec leurs tables de dimensions respectives. Les tables de dimensions communes aux différentes tables des faits ne font pas l’objet de redondances : c’est l’un des principaux avantages cette modélisation. Cela permet de réduire l’espace de stockage nécessaire. Idéalement, il faut que les tables de dimensions partagées soient identiques et contiennent les mêmes valeurs, les mêmes attributs. Dans le cas contraire, des ajustements sont nécessaires pour que les tables de dimensions partagées conviennent aux deux besoins métiers.

Voici comment se présente la modélisation en constellation appliquée aux deux schémas en étoile étudiés plus haut :

La modélisation en étoile est une approche pour organiser un Data Warehouse. Elle a l’avantage de la simplicité. C’est pour cette raison qu’elle reste la plus communément utilisée. Si vous n’êtes pas limité en matière d’espace de stockage et que vous prenez soin de veiller à l’intégrité de vos données, la modélisation en étoile est un très bon choix, idéal pour commencer. Sinon, d’autres approches sont envisageables. L’une est la modélisation en constellation, que nous avons rapidement présentée. Il y en a également une autre, la modélisation en flocons, que nous aurons certainement l’occasion de vous présenter dans un prochain article.