dimensional-modeling

name

dimensional-modeling

description

Modélisation dimensionnelle pour le data warehousing — schéma en étoile, flocon, tables de faits et dimensions, slowly changing dimensions. À utiliser quand l'utilisateur conçoit un data warehouse, modélise des faits/dimensions ou met en place du reporting analytique. Se déclenche aussi avec "schéma en étoile", "star schema", "table de faits", "dimension", "data warehouse", "SCD", "slowly changing dimension", "modélisation dimensionnelle". Also triggers on "fact and dimension tables", "data warehouse modeling".

Modélisation Dimensionnelle

Workflow — 4 décisions dans l'ordre

1. Identifier le processus métier

Choisir UN processus à la fois (ventes, commandes, facturation, trafic web). Ne pas mélanger deux processus dans une même table de faits à ce stade.

2. Définir le grain

La décision la plus critique. "1 ligne = ?" doit s'énoncer en une phrase.

Grain	Exemple
Grain fin (transactionnel)	1 ligne par ligne de commande
Grain moyen	1 ligne par commande
Grain agrégé	1 ligne par client par mois

Règle : toujours choisir le grain le plus fin techniquement supportable. Les agrégats peuvent toujours être calculés à la requête ; l'inverse est impossible.

3. Identifier les dimensions

Questions guides : Qui ? Quoi ? Où ? Quand ? Comment ? Chaque dimension répond à l'une de ces questions pour décrire le fait.

4. Identifier les mesures (faits)

Ne retenir que les mesures numériques cohérentes avec le grain défini. Classer chaque mesure : additive / semi-additive / non-additive.

Additivité	Définition	Exemple
Additive	Somme valide sur toutes dimensions	Quantité vendue, chiffre d'affaires
Semi-additive	Somme valide sur certaines dimensions seulement	Solde de compte (pas sur le temps)
Non-additive	Pas de somme utile	Taux, ratios, prix unitaire

---

Schéma en étoile — Structure SQL

-- Table de faits
CREATE TABLE fact_sales (
    sale_key        BIGINT IDENTITY PRIMARY KEY,
    date_key        INT NOT NULL REFERENCES dim_date(date_key),
    product_key     INT NOT NULL REFERENCES dim_product(product_key),
    customer_key    INT NOT NULL REFERENCES dim_customer(customer_key),
    store_key       INT NOT NULL REFERENCES dim_store(store_key),

    -- Mesures additives
    quantity        INT            NOT NULL,
    unit_price      DECIMAL(10,2)  NOT NULL,
    discount_amount DECIMAL(10,2)  NOT NULL DEFAULT 0,
    net_amount      DECIMAL(10,2)  NOT NULL,
    tax_amount      DECIMAL(10,2)  NOT NULL,
    total_amount    DECIMAL(10,2)  NOT NULL,

    -- Clés dégénérées (identifiants source sans dimension propre)
    invoice_number  VARCHAR(50),
    line_number     INT
);

-- Dimension Date (pré-remplie, jamais via ETL en temps réel)
CREATE TABLE dim_date (
    date_key               ,  
    full_date             ,
    day_of_week            ,     
    day_name      () ,
    day_of_month           ,
    week_of_year           ,
    month_number           ,
    month_name    () ,
    quarter                ,
                       ,
    is_weekend    BIT         ,
    is_holiday    BIT         ,
    fiscal_year   ,
    fiscal_quarter 
);

Remplir dim_date en SQL (script one-shot)

-- Génère toutes les dates entre deux bornes
WITH dates AS (
    SELECT CAST('2010-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM dates WHERE d < '2030-12-31'
)
INSERT INTO dim_date (date_key, full_date, day_of_week, day_name,
                      day_of_month, week_of_year, month_number, month_name,
                      quarter, year, is_weekend)
SELECT
    CONVERT(INT, FORMAT(d, 'yyyyMMdd')),
    d,
    DATEPART(WEEKDAY, d),
    DATENAME(WEEKDAY, d),
    DAY(d),
    DATEPART(WEEK, d),
    MONTH(d),
    DATENAME(MONTH, d),
    DATEPART(QUARTER, d),
    YEAR(d),
    CASE WHEN DATEPART(WEEKDAY, d) IN (1,7) THEN 1 ELSE 0 END
FROM dates
OPTION (MAXRECURSION 10000);

---

Slowly Changing Dimensions (SCD)

Critères de choix

Type	Conserver l'historique ?	Volume delta	À utiliser si…
Type 1	Non	Faible	Correction d'erreur, attribut sans valeur analytique (ex: code postal format)
Type 2	Oui, complet	Moyen-élevé	Segment client, territoire vendeur, catégorie produit
Type 3	Partiel (1 seule transition)	Faible	Réorganisation connue à l'avance avec comparaison avant/après
Type 4 (mini-dimension)	Oui, séparé	Attributs très volatils	Profil comportemental changeant fréquemment
Type 6 (hybride 1+2+3)	Oui + snapshot courant	Complexe	Besoin de navigation historique ET valeur courante dénormalisée

SCD Type 2 — Pattern complet

CREATE TABLE dim_customer (
    customer_key   INT          IDENTITY PRIMARY KEY,  -- Surrogate key
    customer_id    VARCHAR(50)  NOT NULL,               -- Business key (NK)
    name           VARCHAR(200) NOT NULL,
    email          VARCHAR(200),
    city           VARCHAR(100),
    segment        VARCHAR(50),

    effective_date DATE         NOT NULL,
    expiration_date DATE        NOT NULL DEFAULT '9999-12-31',
    is_current     BIT          NOT NULL DEFAULT 1
);

-- ETL : mise à jour SCD Type 2
BEGIN TRANSACTION;

-- Étape 1 : expirer l'enregistrement actuel
UPDATE dim_customer
SET    expiration_date = CAST(GETDATE() AS DATE),
       is_current      = 0
WHERE  customer_id = 'CUST-123'
  AND  is_current  = 1;

-- Étape 2 : insérer la nouvelle version
INSERT INTO dim_customer (customer_id, name, email, city, segment, effective_date)
VALUES ('CUST-123', 'Jean Dupont', , , , (GETDATE()  ));

;


 f.total_amount, c.segment
   fact_sales f
   dim_customer c  c.customer_key  f.customer_key
                       c.effective_date  f.sale_date
                       f.sale_date  c.expiration_date;

---

Types de tables de faits

Type	Grain	Cas d'usage
Transactionnelle	1 ligne par événement atomique	Ventes, clics, transactions
Snapshot périodique	1 ligne par entité par période	Solde mensuel, stock fin de journée
Snapshot cumulatif	1 ligne par cycle de vie	Pipeline commande (créée → validée → expédiée → livrée)
Sans faits (factless)	Intersection sans mesure	Présence à un événement, éligibilité à une promotion

Snapshot cumulatif — colonnes types

CREATE TABLE fact_order_pipeline (
    order_key         INT     PRIMARY KEY,
    order_id          VARCHAR(50),
    -- Une date_key par étape
    created_date_key  INT     REFERENCES dim_date(date_key),
    approved_date_key INT     REFERENCES dim_date(date_key),
    shipped_date_key  INT     REFERENCES dim_date(date_key),
    delivered_date_key INT    REFERENCES dim_date(date_key),
    -- Lag entre étapes (recalculé à chaque mise à jour)
    days_to_approve   INT,
    days_to_ship      INT,
    days_to_deliver   INT,
    order_amount      DECIMAL(12,2)
);

---

Schéma flocon (snowflake) — quand l'utiliser

Normaliser une dimension (ex: dim_product → dim_category → dim_department) :

• Pour : réduit la redondance, cohérence de mise à jour.
• Contre : jointures supplémentaires, requêtes plus lentes, lisibilité dégradée.

Règle 2026 : préférer le star schema sauf si le volume de la dimension est > 10M lignes et que les attributs normalisables sont très stables. Les outils BI modernes (Power BI, Tableau, Looker) sont optimisés star.

---

Anti-patterns et garde-fous

Anti-pattern	Symptôme	Correction
Grain mixte	Mesures incomparables dans une même fact	Séparer en deux tables de faits
Clé métier comme FK	JOIN lent, problèmes SCD	Toujours utiliser les surrogate keys
Dimension fourre-tout	dim_misc, dim_attributes avec 80 colonnes	Décomposer en dimensions thématiques
Mesure non-additive stockée brute	Requêtes fausses (SUM de taux)	Stocker numérateur + dénominateur, calculer le ratio en vue
SCD Type 2 sur tout	Table de 100M lignes pour une dim de 10k clients	Choisir SCD Type 1 pour attributs sans valeur historique
dim_date absente	Filtres dates via CAST sur la fact	Toujours créer et pré-remplir dim_date
NULL en FK	Ruptures de jointure silencieuses	Créer une ligne "inconnu" (key = -1) dans chaque dimension

---

Bonnes pratiques 2026

• Nommage : préfixe fact_ / dim_ / bridge_ ; clés avec suffixe _key ; business keys avec _id ou _bk.
• Surrogate keys : INT IDENTITY (OLTP < 2 milliards) ou BIGINT sinon. Ne jamais exposer la surrogate key aux utilisateurs BI.
• Ligne "inconnu" : insérer customer_key = -1, customer_id = 'UNKNOWN' dans chaque dimension pour absorber les NULLs ETL.
• Date clé : format YYYYMMDD comme INT — plus rapide pour les range scans que DATE.
• Indexes : clustered sur la PK de la fact ; columnstore non-clustered pour les agrégations analytiques (SQL Server / Synapse).
• Partitionnement : partitionner les grandes facts par date_key (partition par année ou trimestre).
• Modèle bus : définir une matrice bus (processus × dimension) avant de coder pour identifier les dimensions conformées réutilisables.
• Tests de cohérence : vérifier COUNT(DISTINCT surrogate_key) = COUNT(*) sur les dimensions et l'absence de NULLs sur les FKs de la fact après chaque chargement ETL.