Power Pivot : construire un modèle de données dans Excel

Ce que vous saurez faire à la fin

1. Activer Power Pivot et Power Query dans Excel.
2. Importer plusieurs tables et créer des relations entre elles.
3. Construire des mesures DAX (CALCULATE, SUMX, FILTER, ALL).
4. Bâtir un cube de données et un tableau croisé dynamique multi-tables.
5. Optimiser les performances pour des modèles de plusieurs millions de lignes.

Durée : 6h pour les fondamentaux + pratique sur 1 mois. Pré-requis : Excel 2016 ou supérieur (Pro Plus, Microsoft 365 Apps for Business / E3 / E5), connaissance solide d’Excel et tableaux croisés dynamiques, fichiers Excel sources avec plusieurs tables (Ventes, Clients, Produits, Calendrier).

Étape 1 — Activer Power Pivot et Power Query

Excel > Fichier > Options > Compléments > en bas, Gérer « Compléments COM » > OK > cochez « Microsoft Power Pivot for Excel » > OK. Un nouvel onglet « Power Pivot » apparaît. Power Query est nativement intégré dans Données > Obtenir des données. Vérifiez la présence des deux avant de continuer.

Étape 2 — Comprendre l’architecture en étoile

Schéma type recommandé (dimensional model) :

         [DimClients]
              |
              |
[DimProduits]-+-[FactVentes]-[DimCalendrier]
              |
              |
         [DimMagasins]

Tables de dimensions (Dim) : descriptives, peu de lignes (clients, produits, etc.)
Table de faits (Fact) : transactions, beaucoup de lignes (ventes, paiements)
Liaison via clés étrangères (CodeClient, CodeProduit, etc.)

Étape 3 — Importer plusieurs tables avec Power Query

Données > Obtenir des données > choisissez la source : Excel, CSV, base SQL Server, dossier complet, web. Pour chaque table :

1. Charger dans l’éditeur Power Query
2. Renommer les colonnes en clair (espaces autorisés)
3. Définir le type de chaque colonne (texte, nombre, date)
4. Filtrer les lignes vides
5. Cliquer « Charger dans » > « Modèle de données » (pas dans la feuille)

Étape 4 — Créer une table Calendrier

La meilleure pratique : ajouter une table de dates explicite. Dans Power Pivot > Conception > Tables liées > Créer un calendrier :

Date début : 01/01/2020
Date fin   : 31/12/2030
Colonnes calculées :
  Année       = YEAR([Date])
  Mois numéro = MONTH([Date])
  Mois nom    = FORMAT([Date], "mmmm")
  Trimestre   = "T" & CEILING(MONTH([Date])/3, 1)
  Jour semaine = WEEKDAY([Date], 2)

Étape 5 — Créer les relations entre tables

Power Pivot > Vue diagramme. Glissez la clé d’une table vers la même clé dans une autre. Une ligne apparaît avec :

• Cardinalité : 1 vers plusieurs (1:n) ou plusieurs vers plusieurs (n:n, à éviter)
• Direction du filtre : unidirectionnelle (par défaut, recommandé) ou bidirectionnelle
• Active : oui (rouge si inactive)

Validez : un double-clic affiche les détails. Sans relations, vos calculs renvoient des chiffres absurdes.

Étape 6 — Vos premières mesures DAX

Onglet Power Pivot > Mesures > Nouvelle mesure
Nom : CA Total
Formule :
CA Total := SUM(FactVentes[MontantHT])

Nom : Quantité totale
Formule :
Quantité Totale := SUM(FactVentes[Quantité])

Nom : Prix moyen
Formule :
Prix Moyen := DIVIDE(SUM(FactVentes[MontantHT]), SUM(FactVentes[Quantité]), 0)

Format : nombre (FCFA, 0 décimales pour montants)

Étape 7 — Mesures avec CALCULATE et FILTER

CA 2025 :=
CALCULATE(
    [CA Total],
    DimCalendrier[Année] = 2025
)

CA Région Dakar :=
CALCULATE(
    [CA Total],
    DimMagasins[Région] = "Dakar"
)

CA Top 10 produits :=
CALCULATE(
    [CA Total],
    TOPN(10, ALL(DimProduits[NomProduit]), [CA Total], DESC)
)

CA Année courante :=
TOTALYTD([CA Total], DimCalendrier[Date])

CA Année précédente même période :=
CALCULATE([CA Total], SAMEPERIODLASTYEAR(DimCalendrier[Date]))

Croissance YoY % :=
DIVIDE([CA Total] - [CA Année précédente même période], [CA Année précédente même période], 0)

Étape 8 — Mesures itératives avec SUMX

SUMX itère sur chaque ligne d'une table et applique une formule.

Marge totale (HT) :=
SUMX(
    FactVentes,
    FactVentes[Quantité] * (FactVentes[PrixVenteHT] - RELATED(DimProduits[CoutAchat]))
)

Cas d'usage :
- Marge par ligne (sans colonne calculée préalable)
- Pondération (ex: panier moyen pondéré)
- Calculs row-by-row

Étape 9 — Construire un tableau croisé dynamique multi-tables

Insertion > TCD à partir du modèle de données. Faites glisser dans :

Filtres   : DimCalendrier[Année]
Colonnes  : DimCalendrier[Mois nom]
Lignes    : DimMagasins[Région]
Valeurs   : [CA Total], [Marge totale (HT)], [Quantité totale]

Vous obtenez un TCD multi-tables impossible à faire avec Excel classique sans VLOOKUP infini.

Étape 10 — Construire des KPIs visuels

Power Pivot > Mesures > sélectionnez une mesure > « Créer un KPI ». Définissez :

• Cible : valeur absolue ou autre mesure (ex: budget mensuel)
• Seuils : 80 % rouge, 80-100 % orange, > 100 % vert
• Style d’icône : feux, flèches, jauges

Le TCD affiche désormais des icônes colorées à côté des valeurs. Idéal pour dashboards exécutifs.

Étape 11 — Construire un graphique croisé dynamique connecté

Insertion > Graphique croisé dynamique. Connectez aux mêmes données. Avantages : (a) interactivité avec segments / chronologie, (b) connexion entre plusieurs graphiques (un segment filtre tous les graphiques d’une feuille), (c) mise à jour en 1 clic via Données > Actualiser tout.

Étape 12 — Optimiser les performances

Pour modèles > 1 million de lignes :

• Réduisez le nombre de colonnes importées (gardez seulement l’utile)
• Utilisez les types appropriés (entier 32 bits < 2 milliards, sinon entier 64)
• Privilégiez les colonnes calculées DAX aux formules Power Query si répétées
• Évitez les relations bidirectionnelles sauf nécessité
• Utilisez VAR pour stocker les sous-calculs et éviter les recalculs
• Activez « Détecter les rapports automatiquement » si fichier > 100 Mo

Étape 13 — Bonnes pratiques DAX professionnelles

1. Préfixez les mesures par tablename : "Ventes[CA Total]" plutôt que "CA Total"
2. Utilisez DIVIDE() au lieu de "/" (gère la division par zéro)
3. CALCULATE() est votre meilleur ami pour appliquer des contextes de filtre
4. RELATED() pour récupérer une valeur depuis une table liée
5. ALL() ignore les filtres, ALLEXCEPT() ignore tous sauf...
6. Variables (VAR ... RETURN) pour la lisibilité et les performances
7. Documentez chaque mesure avec un commentaire (-- mon commentaire)

Étape 14 — Migrer vers Power BI quand vous dépassez les limites Excel

Vous saurez qu’il est temps de migrer vers Power BI Desktop quand :

• Fichier Excel > 250 Mo (devient instable)
• Plus de 5 millions de lignes en table de faits
• Besoin de partage interactif avec 5+ utilisateurs
• Refresh automatique programmé nécessaire
• Mobile-friendly indispensable

Power BI Desktop est gratuit. Service Power BI : 10 $/user/mois. Le langage DAX est strictement le même : pas de réapprentissage.

Erreurs courantes en Power Pivot

• Importer dans une feuille au lieu du modèle : on perd toute la puissance.
• Pas de table Calendrier : impossible d’utiliser les fonctions Time Intelligence.
• Relations many-to-many partout : bug et perfs en ruine.
• Mesures avec /0 sans DIVIDE : erreur #DIV/0! qui casse tout le rapport.
• Trop de colonnes calculées vs mesures : alourdit le modèle inutilement.

Checklist Power Pivot pro

✓ Power Pivot et Power Query activés
✓ Schéma en étoile avec tables Dim et Fact
✓ Tables importées via Power Query (modèle de données)
✓ Table Calendrier créée et marquée comme telle
✓ Relations 1:n vérifiées
✓ Mesures de base (CA, Quantité, Marge)
✓ Mesures Time Intelligence (YTD, YoY)
✓ Mesures conditionnelles (CALCULATE + FILTER)
✓ KPIs visuels avec seuils définis
✓ TCD et graphiques croisés connectés
✓ Performances optimisées (variables, types, relations)
✓ Documentation des mesures et schéma
✓ Plan de migration Power BI si croissance