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".
Installer avec Codex ou Claude Copiez ce prompt, collez-le dans Codex, Claude ou un autre assistant, puis laissez-le vérifier la page du skill et l'installer pour vous.
Une commande directe contourne le prompt de vérification. Examinez la source avant de l'exécuter.
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 faitsCREATE TABLE fact_sales (
sale_key BIGINTIDENTITYPRIMARY KEY,
date_key INTNOT NULLREFERENCES dim_date(date_key),
product_key INTNOT NULLREFERENCES dim_product(product_key),
customer_key INTNOT NULLREFERENCES dim_customer(customer_key),
store_key INTNOT NULLREFERENCES dim_store(store_key),
-- Mesures additives
quantity INTNOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
discount_amount DECIMAL(10,2) NOT NULLDEFAULT0,
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 INTPRIMARY KEY, -- Format YYYYMMDD
full_date DATENOT NULL,
day_of_week INTNOT NULL, -- 1=Lundi ... 7=Dimanche
day_name VARCHAR(10) NOT NULL,
day_of_month INTNOT NULL,
week_of_year INTNOT NULL,
month_number INTNOT NULL,
month_name VARCHAR(10) NOT NULL,
quarter INTNOT NULL,
yearINTNOT 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 bornesWITH dates AS (
SELECTCAST('2010-01-01'ASDATE) AS d
UNIONALLSELECT 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)
SELECTCONVERT(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),
CASEWHEN DATEPART(WEEKDAY, d) IN (1,7) THEN1ELSE0ENDFROM 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 INTIDENTITYPRIMARY 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 DATENOT NULL,
expiration_date DATENOT NULLDEFAULT'9999-12-31',
is_current BIT NOT NULLDEFAULT1
);
-- ETL : mise à jour SCD Type 2BEGIN TRANSACTION;
-- Étape 1 : expirer l'enregistrement actuelUPDATE dim_customer
SET expiration_date =CAST(GETDATE() ASDATE),
is_current =0WHERE customer_id ='CUST-123'AND is_current =1;
-- Étape 2 : insérer la nouvelle versionINSERT INTO dim_customer (customer_id, name, email, city, segment, effective_date)
VALUES ('CUST-123', 'Jean Dupont', 'jean@new.com', 'Lyon', 'Premium', CAST(GETDATE() ASDATE));
COMMIT;
-- Requête sur une période historiqueSELECT 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;
Présence à un événement, éligibilité à une promotion
Snapshot cumulatif — colonnes types
CREATE TABLE fact_order_pipeline (
order_key INTPRIMARY KEY,
order_id VARCHAR(50),
-- Une date_key par étape
created_date_key INTREFERENCES dim_date(date_key),
approved_date_key INTREFERENCES dim_date(date_key),
shipped_date_key INTREFERENCES dim_date(date_key),
delivered_date_key INTREFERENCES 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.
