Dimensional 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.

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.

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      INT         PRIMARY KEY,  -- Format YYYYMMDD
    full_date     DATE        NOT NULL,
    day_of_week   INT         NOT NULL,     -- 1=Lundi ... 7=Dimanche
    day_name      VARCHAR(10) NOT NULL,
    day_of_month  INT         NOT NULL,
    week_of_year  INT         NOT NULL,
    month_number  INT         NOT NULL,
    month_name    VARCHAR(10) NOT NULL,
    quarter       INT         NOT NULL,
    year          INT         NOT NULL,
    is_weekend    BIT         NOT NULL,
    is_holiday    BIT         NOT NULL,
    fiscal_year   INT,
    fiscal_quarter INT
);

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

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', 'jean@new.com', 'Lyon', 'Premium', CAST(GETDATE() AS DATE));

COMMIT;

-- Requête sur une période historique
SELECT f.total_amount, c.segment
FROM   fact_sales f
JOIN   dim_customer c ON c.customer_key = f.customer_key
                      AND c.effective_date <= f.sale_date
                      AND f.sale_date < c.expiration_date;

Types de tables de faits

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

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.