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
• le modèle en flocons
• le modèle en constellation

Aujourd’hui, nous allons nous concentrer sur la modélisation en étoile, ce qui la caractérise, ses avantages et ses alternatives.

Disons d’emblée que la modélisation en étoile est la plus communément utilisée aujourd’hui parce qu’elle simplifie les requêtes SQL et optimise leur temps d’exécution, y compris sur de très gros volumes de données.

L’essentiel à retenir sur la modélisation en étoile d’un Data Warehouse

• La modélisation en étoile est le modèle le plus répandu pour organiser les données d’un Data Warehouse et la relation entre les différentes tables.
• Dans un schéma en étoile, chaque dimension est représentée par une table unidimensionnelle.
• La table de dimension doit contenir l’ensemble des attributs.
• La table de dimension est reliée à la table des faits à l’aide d’une clé étrangère (identifiants). Les tables de dimensions ne sont pas reliées entre elles.
• La table des faits contient une clé et une mesure.
• Le schéma en étoile est facile à comprendre et permet une utilisation optimale.
• Les tables de dimensions ne sont pas normalisées.

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

Définition du DataWarehouse

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.

Découvrez notre guide pour créer son premier Data Warehouse.

Définition des Data Marts

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.

Approche Top-Down vs Bottom-Up

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 !

En amont du DataWarehouse et des Data marts : le process ETL

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, découvrez notre article qui revient sur les deux principales solutions, Data Lake vs Data Warehouse. 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 de ces modèles, 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).

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.

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.

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. Les schémas en étoile ont une logique de jointure plus directe que les autres schémas pour récupérer des données à partir de schémas transactionnels hautement normalisés.

ontrairement aux schémas transactionnels hautement normalisés, le schéma en étoile simplifie la logique de reporting d’entreprise courante, telle que le reporting et la période sur période.

En permettant l’application de schémas de performance spécifiques aux requêtes, le logiciel de traitement des requêtes de schéma en étoile peut offrir de meilleurs plans d’exécution.

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, l’intégrité des données n’est pas bien respectée. Il n’est pas flexible en termes de besoins analytiques.

Les schémas en étoile ne renforcent pas les relations de plusieurs à plusieurs au sein des entités commerciales. 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.

2 alternatives à la modélisation en étoile

Présentation de la modélisation en flocons

La modélisation en flocons peut être utilisée pour organiser les données stockées dans un entrepôt de données. Cette technique consiste à décomposer une dimension en plusieurs niveaux de granularité, ce qui permet d’optimiser les performances et la gestion des requêtes. Les niveaux supérieurs sont moins détaillés et couvrent un éventail plus large de données, tandis que les niveaux inférieurs sont plus détaillés et spécifiques. Cela permet aux utilisateurs d’approfondir les données en fonction de leurs besoins afin d’obtenir des informations sur des domaines d’intérêt spécifiques.

L’un des principaux avantages de la modélisation en flocons est qu’elle peut contribuer à améliorer les performances des requêtes. Un autre avantage de la modélisation en flocons est qu’elle permet une plus grande flexibilité dans l’analyse des données. La modélisation en flocons peut également faciliter la maintenance et la mise à jour de l’entrepôt de données.

Dans l’ensemble, la modélisation en flocons peut aider les organisations à mieux gérer et analyser leurs données. Il est possible d’organiser les données plus efficacement, d’améliorer les performances des requêtes et d’obtenir plus facilement des informations.

Présentation de la modélisation en constellation

La modélisation en constellation est une autre technique utilisée pour organiser les données dans un entrepôt de données. Cette approche consiste à modéliser les données sous la forme d’une série de constellations interdépendantes, chaque constellation représentant un processus métier ou un domaine fonctionnel spécifique. Cela permet aux utilisateurs de naviguer facilement dans les données et de comprendre les relations entre les différents points de données.

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

Dans un modèle de constellation, chaque processus d’entreprise est modélisé comme un schéma en étoile, avec une table de faits au centre et des tables de dimensions représentant les différents aspects du processus qui l’entourent. Ces tables de dimensions peuvent être partagées entre plusieurs constellations, ce qui permet de réduire la redondance et d’améliorer la cohérence des données.

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.

Les réponses à vos questions sur la modélisation en étoile (FAQ)

Qu’est-ce que la modélisation en étoile d’un Data Warehouse ?

La modélisation en étoile est une méthode de conception de bases de données pour les Data Warehouses qui permet de représenter les données sous forme de tableaux à plusieurs dimensions. Elle se caractérise par un fait central, ou table des faits, entouré de plusieurs tables de dimensions qui fournissent des informations contextuelles sur les données.

Quels sont les composants d’un modèle en étoile ?

Les composants d’un modèle en étoile sont :

• Une table centrale des faits qui contient les mesures (ou indicateurs) de performance clés à analyser.
• Des tables de dimensions qui fournissent des informations contextuelles sur les données dans la table des faits.
• Des clés de jointure qui relient les tables de dimensions à la table des faits.

Quelle est la différence entre la modélisation en étoile et la modélisation en flocon de neige ?

La modélisation en flocon de neige est une variation de la modélisation en étoile qui normalise davantage les tables de dimensions en les divisant en tables plus petites et plus spécifiques. Cela peut rendre le modèle plus complexe et plus difficile à comprendre, mais cela peut également réduire la redondance des données et améliorer les performances de la base de données.

Quelles sont les étapes pour concevoir un modèle en étoile ?

Les étapes pour concevoir un modèle en étoile :

• Identifier les exigences métier pour les données à stocker et à analyser.
• Déterminer les mesures de performance clés à analyser dans la table des faits.
• Identifier les dimensions qui fourniront des informations contextuelles sur les données.
• Concevoir les tables de dimensions en incluant toutes les informations nécessaires.
• Concevoir la table des faits et ajouter les mesures de performance.
• Ajouter des clés de jointure pour relier les tables de dimensions à la table des faits.

CRM / Data - Plus de 15 ressources & templates gratuits à télécharger.

Découvrir