Modélisation de données

Une base de données en troisième forme normale est sûre à l’écriture, mais elle coûte cher à lire. Résoudre ce compromis suppose de modéliser à plusieurs niveaux : conceptuel (entité-association), logique (étoile, flocon), physique (partitionnement, clustering). Chaque niveau répond à une question distincte, et chaque figure de ce chapitre s’y situe.

1. Le modèle conceptuel décrit les objets métier et leurs relations, indépendamment de toute technologie. Il répond à la question : de quoi parle-t-on ?
2. Le modèle logique organise ces objets en tables adaptées aux usages visés. Il répond à la question : comment ranger les données pour l’usage prévu ?
3. Le modèle physique décide comment le moteur stocke et parcourt réellement les données. Il répond à la question : comment lire le moins d’octets possible ?

Le schéma en étoile (star schema)

Le modèle dimensionnel, formalisé par Ralph Kimball, répond au problème de lecture étant difficile dans modèle normalisé. Il place au centre une table de faits qui porte les mesures et les clés étrangères vers son contexte, et autour des tables de dimensions qui décrivent ce contexte de façon volontairement aplatie.

Figure 22. Exemple de schéma en étoile, tiré du jeu de données Adventure Works. Le nommage Fact_ / Dim_ distingue visuellement les deux rôles.
Source : Microsoft Learn, « Understand star schema and the importance for Power BI ». Reproduction à usage pédagogique ; conditions de réutilisation Microsoft à vérifier avant diffusion publique.

On lit la figure dans l’ordre de la conception.

1. Table de faits. Au centre, FactResellerSales : c’est la seule table qui référence toutes les autres.
2. Colonnes. Trois familles : l’identifiant de la ligne, les clés étrangères (…Key), et les mesures que l’on agrège (OrderQuantity, TotalProductCost, SalesAmount).
3. Dimensions. Les tables autour (produit, date, employé, revendeur, territoire) portent chacune une clé et des attributs descriptifs.
4. Granularité. Ce que représente une ligne de faits. Il se décide d’abord, car il fixe la signification de chaque mesure.

La forme en étoile signifie qu’une seule jointure relie chaque dimension à la table de faits, et qu’aucune dimension n’est reliée à une autre.

Du schéma en étoile au schéma en flocon (snowflake schema)

Partons de la figure 22 et appliquons aux dimensions aplaties la normalisation vue dans la partie précédente.

(a) Principe : une dimension normalisée

(b) Vue complète : le schéma ramifié

Figure 23. Du schéma en étoile au schéma en flocon. (a) Une dimension peut être normalisée en sous-dimensions. (b) Le schéma complet prend alors une forme ramifiée, tandis que la table de faits conserve son grain.
(a) Source : Microsoft Learn, « Understand star schema and the importance for Power BI » ; reproduction à usage pédagogique, conditions Microsoft à vérifier avant diffusion publique. (b) Source : SqlPac, « Snowflake-schema-example.png », Wikimedia Commons, CC BY-SA 3.0 et GFDL.

Le point essentiel est ce qui ne change pas. La table de faits est identique dans les deux volets : mêmes mesures, mêmes clés étrangères, même grain. Seules les dimensions se divisent.

Dans le volet (a), les deux tables qui ne touchent pas l’étoile sont la sous-catégorie et la catégorie : la dimension produit cesse de porter directement la catégorie, elle référence une sous-catégorie, qui référence une catégorie. Dans le volet (b), la même opération est appliquée à plusieurs dimensions à la fois, et la silhouette en étoile devient ramifiée — d’où le nom de flocon. Atteindre le nom d’une catégorie depuis Fact_Sales y demande désormais deux jointures, Dim_Product puis Dim_Product_Category, là où l’étoile n’en aurait demandé qu’une.

Critère	Étoile	Flocon
Dimensions	Aplaties	Normalisées
Jointures	Moins nombreuses	Plus nombreuses
Redondance	Plus élevée	Plus faible
Lecture par l’analyste	Plus directe	Plus technique

Le flocon réduit la redondance et facilite le partage d’une même hiérarchie entre plusieurs tables de faits, au prix de jointures supplémentaires et d’une lecture moins immédiate. En révision, retenez la distinction sous cette forme : l’étoile a des dimensions plates, le flocon des dimensions éclatées, et le fait ne change pas.

On prend l’étoile quand l’analyste interroge directement le modèle : un tableau de bord, une exploration ad hoc, un outil de BI. On prend le flocon quand une même hiérarchie sert plusieurs faits, ou quand une dimension est trop volumineuse pour recopier catégorie et marque à chaque ligne. L’étoile accélère la lecture ; le flocon accélère la maintenance.

Modéliser avant d’implémenter : entité-association

Les schémas en étoile et en flocon relèvent déjà du modèle logique : ce sont des tables. En amont, le modèle entité-association décrit le domaine sans choisir de technologie. Trois briques : les entités, objets identifiés par une clé ; les attributs, qui les décrivent ; les associations, qui les relient, munies de cardinalités.

Figure 24. Exemple de diagramme entité-association pour une pizzeria. Le modèle sépare les clients, commandes, lignes, produits, livraisons et paiements avant leur traduction en tables physiques.
Source : Khishigdelger Ganbold, « Is my way of creating an entity relationship diagram correct? », Database Administrators Stack Exchange, CC BY-SA 4.0.

On lit la figure en trois passes.

1. Entités. Six objets du domaine : client, en-tête de commande, ligne, pizza, livraison, paiement.
2. Attributs et clés. (PK) identifie l’entité, (FK) référence une autre entité. Le reste décrit.
3. Relations. Un client passe des commandes ; une commande a plusieurs lignes ; chaque ligne référence une pizza ; livraison et paiement suivent la commande.

Du diagramme aux tables, deux décisions restent à prendre.

• Structure des tables. La disposition des données : quelles colonnes, quels types.
• Contraintes. Elles protègent ce que le modèle a décidé : clé primaire, clé secondaire, UNIQUE, et CHECK, qui impose une condition sur les valeurs insérées ou mises à jour (par exemple un montant strictement positif).

Lire les cardinalités

Une cardinalité dit combien d’occurrences d’une entité peuvent être reliées à l’autre. La notation en patte d’oie, ou crow’s foot, l’inscrit à chaque extrémité du lien.

Figure 25. Cardinalités en notation « patte d’oie ». Les symboles combinent une cardinalité minimale, zéro ou un, et une cardinalité maximale, un ou plusieurs.
Source : Metabase, « Entity relationship diagram (ERD) ». Aucune licence ouverte explicite identifiée ; reproduction à usage pédagogique, à valider avant diffusion publique.

Chaque terminaison combine une cardinalité minimale (zéro ou un : relation facultative ou non) et une cardinalité maximale (un ou plusieurs : la relation peut se répéter). Trois symboles :

• le cercle : zéro, relation facultative ;
• la barre : un, relation obligatoire ou unique ;
• la patte d’oie : plusieurs.

La lecture en toutes lettres ne change pas d’un outil à l’autre, contrairement au dessin. Deux exemples :

• un client passe zéro ou plusieurs commandes : cercle et patte d’oie ;
• une ligne appartient à une et une seule commande : double barre.

Le côté « plusieurs » porte en général la clé étrangère.

Du modèle logique au schéma physique

La dénormalisation facilite l’analyse : les attributs utiles sont déjà là, les jointures se réduisent. Elle produit aussi des lignes plus larges et une table plus longue. Sans organisation physique de ces lignes, une question sur un jour coûte autant qu’une question sur toute l’histoire.

L’indexation accélère les requêtes en créant des index sur les colonnes filtrées ou jointes : c’est le réflexe des bases transactionnelles. Sur un entrepôt comme BigQuery, deux autres leviers prennent le relais :

• Le partitionnement découpe la table en segments, souvent un par date. Filtrer sur une date élimine directement les segments qui ne concernent pas ce jour.
• Le clustering trie les lignes à l’intérieur de chaque segment, par exemple par pays. Filtrer sur le pays permet alors de ne lire que les blocs concernés dans le segment, sans parcourir les autres pays du même jour.

Le partitionnement réduit le nombre de segments à lire ; le clustering réduit ce qu’on lit dans chaque segment. On les combine : d’abord les jours, ensuite le pays à l’intérieur de chaque jour.

Figure 26. Combiner partitionnement et clustering dans BigQuery. Le partitionnement segmente la table ; le clustering ordonne les données au sein des segments afin de réduire les blocs parcourus.
Source : Google Cloud, « Introduction aux tables en cluster », documentation BigQuery, CC BY 4.0 sauf indication contraire.

De gauche à droite :

1. Non organisée. Les lignes sont dans l’ordre d’arrivée. Une requête sur une date parcourt tout.
2. Clusterisée. Même contenu, trié par Country. Le clustering réorganise, il ne découpe pas.
3. Partitionnée et clusterisée. Un segment par Order_Date, et à l’intérieur un tri par Country.

Le gain est donc double : le partitionnement limite le volume ouvert, le clustering affine encore ce volume. Ce second gain n’est pas automatique. Il dépend de la requête : filtrer d’abord sur la première colonne de clustering, sinon presque aucun bloc n’est écarté.

Anatomie d’une requête analytique

Six clauses suffisent à couvrir la quasi-totalité des interrogations que vous écrirez à partir de la séance suivante.

Clause	Rôle
SELECT	Choisir les colonnes et les mesures calculées
FROM … JOIN … ON	Désigner la table de départ et lier les tables associées
WHERE	Filtrer les lignes, avant tout regroupement
GROUP BY	Regrouper les lignes selon une ou plusieurs colonnes
HAVING	Filtrer les groupes, après agrégation
ORDER BY	Trier le résultat

Appliquée au schéma en étoile des ventes, une requête complète prend la forme suivante.

SELECT p.categorie,
       d.mois,
       SUM(v.montant) AS chiffre_affaires
FROM fait_vente AS v
JOIN dim_produit AS p ON v.id_produit = p.id_produit
JOIN dim_date AS d ON v.id_date = d.id_date
WHERE d.annee = 2024
GROUP BY p.categorie, d.mois
HAVING SUM(v.montant) > 100000
ORDER BY p.categorie, d.mois;

NoteÀ retenir

• Trois niveaux : conceptuel (le domaine), logique (les tables), physique (comment les lire).
• L’étoile aplatit les dimensions pour lire plus vite, au prix de la redondance que la 3FN évitait.
• Le grain de la table de faits se choisit en premier : il fixe le sens de chaque mesure.
• Étoile = dimensions plates. Flocon = dimensions normalisées. Le fait, lui, ne change jamais.
• L’entité-association vient avant les tables : elle décrit le domaine, sans choisir de technologie.
• Le partitionnement élimine des segments entiers ; le clustering trie à l’intérieur. Encore faut-il filtrer dans le bon ordre pour en profiter.
• WHERE filtre les lignes, HAVING filtre les groupes — après le GROUP BY.