Business intelligence : modélisation dimensionnelle, ETL et tableaux de bord

Tôt ou tard, toute organisation pose le même genre de question : combien, pour quel montant, par rapport à quand, et pourquoi ça bouge. Les données pour y répondre existent déjà dans les systèmes opérationnels, éparpillées dans des schémas conçus pour tout autre chose. La business intelligence est la discipline qui consiste à remodeler ces données pour que ces questions trouvent une réponse rapide, cohérente et accessible à des gens qui n'écrivent pas de SQL.

Les outils changent sans cesse ; la modélisation, non. Un schéma en étoile bien conçu il y a vingt ans fonctionnerait très bien sur un entrepôt cloud moderne, et un schéma mal conçu décevra sur n'importe quelle plateforme. Ce guide se concentre sur cette partie durable, avec des exemples SQL qui tournent sur PostgreSQL ou Oracle.

Deux missions différentes : OLTP et OLAP

Systèmes transactionnels et systèmes analytiques ont des priorités opposées ; c'est pourquoi un même schéma ne peut pas bien servir les deux.

Un schéma opérationnel stocke chaque fait une seule fois et le reconstitue par jointures — la discipline décrite dans le guide conception de bases de données et normalisation. C'est exactement ce qu'il faut quand l'objectif est la justesse des écritures concurrentes. Pour l'analyse, cela pose deux problèmes : les requêtes exigent dix jointures que seul un spécialiste sait écrire correctement, et les mises à jour écrasent le passé, si bien que « quel était le segment de ce client au moment de cette commande » devient une question sans réponse.

Les modèles analytiques font donc un compromis assumé : accepter la redondance, conserver l'historique et optimiser pour la compréhension.

Faits et dimensions

La modélisation dimensionnelle découpe le monde en mesures et en contexte de ces mesures.

Une table de faits contient les mesures — quantités, montants, durées, comptages — ainsi que les clés étrangères vers les dimensions qui les décrivent. Elle est longue et étroite, et c'est là que vivent presque toutes les lignes.

Une table de dimension contient les attributs descriptifs qui servent à filtrer, regrouper et libeller : client, produit, magasin, date, canal. Elle est courte et large, souvent très dénormalisée, et c'est là que réside presque toute la lisibilité.

Tout commence par le grain : une phrase simple qui énonce exactement ce que représente une ligne de la table de faits. « Une ligne par ligne de commande » est un grain. « Données de ventes » n'en est pas un. Déclarez-le, écrivez-le dans la documentation, et refusez toute colonne qui ne s'y conforme pas.

CREATE TABLE dim_date (
  date_key     integer PRIMARY KEY,          -- 20260907
  full_date    date NOT NULL,
  day_of_week  varchar(9) NOT NULL,
  month_name   varchar(9) NOT NULL,
  quarter      smallint NOT NULL,
  year         smallint NOT NULL,
  is_weekend   boolean NOT NULL,
  is_holiday   boolean NOT NULL
);

CREATE TABLE dim_product (
  product_key  bigint PRIMARY KEY,           -- clé de substitution
  sku          varchar(20) NOT NULL,         -- clé naturelle issue de la source
  title        varchar(200) NOT NULL,
  category     varchar(60) NOT NULL,
  brand        varchar(60) NOT NULL,
  valid_from   date NOT NULL,
  valid_to     date,
  is_current   boolean NOT NULL
);

CREATE TABLE fact_sales_line (
  date_key     integer NOT NULL REFERENCES dim_date(date_key),
  product_key  bigint  NOT NULL REFERENCES dim_product(product_key),
  customer_key bigint  NOT NULL REFERENCES dim_customer(customer_key),
  store_key    integer NOT NULL REFERENCES dim_store(store_key),
  order_number varchar(20) NOT NULL,         -- dimension dégénérée
  quantity     integer NOT NULL,
  net_amount   numeric(12,2) NOT NULL,
  tax_amount   numeric(12,2) NOT NULL,
  discount     numeric(12,2) NOT NULL
) PARTITION BY RANGE (date_key);

Trois détails de ce modèle sont des conventions à adopter. Les dimensions utilisent des clés de substitution (surrogate keys) plutôt que les identifiants du système source, pour pouvoir versionner l'historique et pour qu'une source qui renumérote ses clés ne corrompe pas l'entrepôt. La dimension date est une vraie table, pas une expression calculée : on peut ainsi filtrer sur « jour férié » ou « trimestre fiscal » sans coder un calendrier dans chaque requête. Enfin, un identifiant sans attributs propres, comme le numéro de commande, reste dans la table de faits en tant que dimension dégénérée au lieu d'avoir une table que personne ne joindrait.

Les faits se déclinent en trois variétés, et les mélanger produit des chiffres faux. Les faits additifs (montant net, quantité) se somment selon toutes les dimensions. Les faits semi-additifs (un solde de compte, un niveau de stock) se somment selon certaines dimensions mais pas dans le temps — on prend alors la moyenne ou la dernière valeur. Les faits non additifs (un pourcentage de marge, un prix unitaire) ne se somment pas du tout : stockez le numérateur et le dénominateur, et calculez le ratio au moment de la requête.

Étoile et flocon

Un schéma en étoile place la table de faits au centre, avec une jointure vers chaque dimension. C'est toute la conception, et ses qualités sont concrètes : toute question se résout avec une jointure par dimension, les outils de BI savent le parcourir automatiquement, et le SQL reste lisible par des non-spécialistes.

SELECT d.year, d.quarter, p.category,
       SUM(f.net_amount) AS revenue,
       SUM(f.quantity)   AS units
FROM   fact_sales_line f
JOIN   dim_date    d ON d.date_key    = f.date_key
JOIN   dim_product p ON p.product_key = f.product_key
WHERE  d.year = 2026 AND p.brand = 'Northwind'
GROUP  BY d.year, d.quarter, p.category
ORDER  BY revenue DESC;

Un schéma en flocon normalise les dimensions en sortant catégorie et marque dans leurs propres tables. Il économise un peu d'espace et ajoute des jointures à chaque requête. Passez en flocon avec discernement — pour une hiérarchie vraiment profonde ou une table de référence partagée entre dimensions — et laissez le reste dénormalisé.

Quand plusieurs tables de faits partagent les mêmes tables de dimension, ces dimensions sont dites conformes, et c'est ce qui rend possible l'analyse transverse aux processus : ventes et retours se comparent par produit et par mois parce que les deux utilisent les mêmes dimensions produit et date. Les dimensions conformes sont la colonne vertébrale d'un entrepôt ; sans elles, vous n'avez qu'une collection de data marts sans rapport entre eux.

Comme les tables de faits sont volumineuses et presque toujours filtrées par période, partitionnez-les sur la clé de date et laissez le moteur élaguer les partitions. L'indexation diffère aussi de l'OLTP : les index bitmap sur des clés de dimension à faible cardinalité fonctionnent bien dans les entrepôts Oracle, et le stockage en colonnes rend beaucoup d'index inutiles. Le raisonnement derrière ces choix est détaillé dans le guide des index et plans d'exécution.

Conserver l'historique : les dimensions à évolution lente

Un client déménage. Un produit change de catégorie. Que doit afficher le rapport de l'an dernier ? La réponse est une décision de modélisation, et le vocabulaire consacré est numéroté (on parle de SCD, pour slowly changing dimensions).

• Type 0 — conserver la valeur d'origine. L'attribut ne change plus après le premier chargement. Convient à une date de naissance ou au canal d'inscription initial.
• Type 1 — écraser. Mise à jour sur place, l'ancienne valeur est perdue. Correct pour corriger de vraies erreurs, comme un nom mal orthographié. Les rapports historiques changent rétroactivement, ce qui est parfois voulu et souvent surprenant.
• Type 2 — ajouter une ligne. Insérer une nouvelle ligne de dimension avec une nouvelle clé de substitution, clore la ligne précédente via valid_to et basculer is_current. Les faits continuent de pointer vers la ligne qui était courante au moment où ils se sont produits : l'historique est préservé à l'identique. C'est le choix par défaut pour tout attribut utilisé en analyse.
• Type 3 — ajouter une colonne. Garder current_region et previous_region côte à côte. Utile uniquement pour une réorganisation ponctuelle où l'on veut comparer les deux découpages.

C'est la mise à jour de type 2 qu'il faut réussir :

-- Clore la version sortante
UPDATE dim_product
SET    valid_to = current_date - 1, is_current = false
WHERE  sku = 'BK-1042' AND is_current;

-- Ouvrir la nouvelle avec une clé de substitution neuve
INSERT INTO dim_product (product_key, sku, title, category, brand, valid_from, valid_to, is_current)
VALUES (nextval('dim_product_seq'), 'BK-1042', 'Field Guide to Estuaries',
        'Natural history', 'Northwind', current_date, NULL, true);

Deux remarques pratiques. À chaque exécution, chargez les faits après les dimensions, pour que la recherche de clé trouve la bonne version. Et ajoutez à chaque dimension un membre « inconnu » (clé 0 ou -1), afin qu'un fait arrivant avec une référence manquante puisse quand même être chargé et compté au lieu d'être silencieusement écarté.

Alimenter l'entrepôt : ETL et ELT

L'ETL extrait les données des sources, les transforme dans une couche de traitement dédiée, puis charge le résultat. L'ELT charge d'abord les données brutes dans la plateforme cible et les transforme avec la puissance de calcul de cette plateforme. L'ELT est devenu la norme là où le stockage coûte peu et où le moteur est élastique : la copie brute est conservée, les transformations deviennent du SQL versionné, et il n'y a plus de couche de transformation séparée à dimensionner.

Dans les deux cas, les mêmes préoccupations reviennent :

• Extraction incrémentale. Relire toute une source chaque nuit cesse vite de passer à l'échelle. Utilisez une colonne de watermark, ou la capture des changements (CDC) depuis le journal de transactions de la source.
• Idempotence. Un job doit produire le même résultat quand on le relance après un échec partiel. Chargez dans une table de staging, puis basculez ou fusionnez de manière atomique.
• Recherche des clés de substitution. Associez chaque clé naturelle source à la clé de dimension courante, en traitant explicitement les membres tardifs et inconnus.
• Audit. Enregistrez pour chaque chargement le nombre de lignes en entrée et en sortie, les lignes rejetées et la durée d'exécution. Quand un chiffre est remis en question trois semaines plus tard, c'est l'audit de chargement qui répond.
• Ordonnancement et dépendances. Les chargements forment un graphe, pas une liste. Orchestrez-les pour qu'une dimension ne soit jamais chargée après les faits qui y font référence. Pour des pipelines batch écrits en Java, Spring Batch fournit le traitement par chunks, la reprise sur incident et le job repository nécessaires.

Quand les volumes ou la variété des sources dépassent ce qu'un entrepôt absorbe confortablement, le pipeline dépose généralement les données d'abord dans un lac ; les patterns correspondants sont dans le guide d'architecture big data.

Data marts, cubes et couche sémantique

Un data mart est un sous-ensemble de l'entrepôt orienté sujet — finance, supply chain, marketing — construit à partir de dimensions conformes. Construits ainsi, les data marts sont des vues sur un seul et même modèle. Construits indépendamment par chaque département, ils reproduisent le problème classique : trois équipes présentent trois chiffres d'affaires différents dans la même réunion.

Les cubes OLAP pré-agrègent les mesures selon les hiérarchies des dimensions pour que le passage de l'année au trimestre puis au mois soit instantané ; on les interroge en MDX plutôt qu'en SQL. Les cubes multidimensionnels classiques ont largement cédé la place aux moteurs en mémoire orientés colonnes et aux agrégats matérialisés, mais leur vocabulaire a survécu partout : slice, dice, drill down, roll up, pivot. En SQL, GROUPING SETS, ROLLUP et CUBE produisent des lignes de sous-totaux à plusieurs niveaux en une seule passe, et les fonctions de fenêtrage gèrent cumuls, moyennes mobiles et comparaisons d'une période à l'autre sans auto-jointure.

La couche sémantique est l'endroit où vivent les définitions des indicateurs : le chiffre d'affaires, c'est le montant net hors lignes annulées ; un client actif, c'est un client ayant passé commande au cours des 90 derniers jours. Définir ces règles une seule fois — dans le modèle de l'outil de BI, dans une couche de métriques ou dans des vues de base de données gouvernées — est ce qui produit une version unique de la vérité. Quand chaque auteur de tableau de bord réimplémente la définition, les divergences sont garanties. Les vues matérialisées sont ici un détail d'implémentation utile : elles précalculent les agrégats lourds et se rafraîchissent selon un planning.

Des tableaux de bord et des KPI qui servent vraiment

La plupart des tableaux de bord échouent pour des raisons de conception plutôt que de données.

• Partez d'une décision. Chaque graphique doit répondre à une question sur laquelle quelqu'un agit. Si personne ne sait nommer l'action, supprimez le graphique.
• Donnez du contexte aux chiffres. « 1,4 M€ » ne veut rien dire seul. Comparé à l'objectif, à l'an dernier, au mois dernier, avec une tendance : là, c'est un KPI.
• Choisissez des mesures sur lesquelles on peut agir. Un indicateur que personne ne peut influencer engendre de la résignation, pas de l'amélioration.
• Définissez-le précisément et publiez la définition. Indiquez les conditions de filtre, le fuseau horaire et l'heure de rafraîchissement sur le tableau de bord lui-même.
• Affichez la fraîcheur et la complétude. Un tableau de bord qui affiche silencieusement des données périmées est pire qu'un tableau de bord en panne.
• Tenez-vous-en à un seul écran. Le détail doit être à un clic, pas dans un mur de vignettes.

Les outils de BI en libre-service permettent aux analystes de construire leurs propres vues sur le modèle gouverné, et c'est la bonne répartition des rôles : l'équipe data est responsable des dimensions conformes, du grain des faits et des définitions d'indicateurs ; les analystes, des questions et de la présentation. Sans modèle gouverné en dessous, le libre-service engendre une prolifération d'extractions contradictoires, et les bonnes habitudes de requêtage du guide des bonnes pratiques SQL comptent davantage, et non moins, quand des centaines de requêtes ad hoc s'abattent sur l'entrepôt.

Enfin, traitez la qualité des données comme une partie du produit. Complétude, validité, cohérence entre les sources, actualité et unicité se testent toutes, et des tests exécutés à chaque chargement détectent les problèmes avant qu'un directeur ne les remarque.

Points clés à retenir

• Les schémas OLTP optimisent la justesse des écritures concurrentes ; les modèles analytiques acceptent la redondance et conservent l'historique pour que les questions soient faciles à poser et que les réponses restent stables.
• Énoncez le grain d'une table de faits en une phrase avant de choisir les colonnes, et ne mélangez jamais dans une même table des lignes de sens différents.
• Préférez un schéma en étoile avec des dimensions dénormalisées, conformes et dotées de clés de substitution ; ne passez en flocon que les hiérarchies vraiment volumineuses ou partagées.
• Sachez si chaque fait est additif, semi-additif ou non additif, et stockez les ratios sous la forme de leurs composantes.
• Utilisez la dimension à évolution lente de type 2 pour les attributs servant à l'analyse, le type 1 uniquement pour corriger des erreurs, et prévoyez toujours un membre « inconnu ».
• Définissez chaque indicateur une seule fois dans une couche sémantique, et concevez les tableaux de bord autour de décisions, avec contexte, fraîcheur et définition publiée.