Pendant des décennies, pour croiser un fichier de ventes avec un fichier de clients et un fichier de catalogue produits dans Excel, la seule méthode consistait à remplir des milliers de cellules avec la fonction RECHERCHEV (ou INDEX/EQUIV).

Le résultat ? Des classeurs obèses de plusieurs centaines de mégaoctets qui mettaient cinq minutes à recalculer et plantaient régulièrement.

Avec Power Pivot et le Modèle de données relationnel, Excel se dote du même moteur en mémoire (xVelocity / VertiPaq) que Power BI et Analysis Services. Vous pouvez désormais relier des tables entre elles, stocker des dizaines de millions de lignes et calculer des indicateurs puissants avec le langage DAX.

---

1. Pourquoi adopter le Modèle de données ?

1. La fin définitive du RECHERCHEV : Plus besoin de dupliquer les colonnes d'une table à l'autre. Une relation entre deux tables permet d'utiliser les attributs clients dans un TCD de ventes instantanément.
2. Dépassement du million de lignes : Les données du modèle sont compressées en mémoire vive dans des colonnes optimisées. Vous pouvez analyser 5, 10 ou 20 millions de lignes directement dans Excel sans ralentissement.
3. Une source unique de vérité : Les règles métier et indicateurs clés sont définis sous forme de mesures DAX centralisées, évitant les erreurs de copier-coller de formules.

---

2. Fondamentaux de la Modélisation : Le Schéma en Étoile

En Business Intelligence, on structure toujours ses tables en deux catégories distinctes :

               ┌──────────────────────┐
               │    Dim_Clients       │
               │ • ID_Client (PK)     │
               │ • Nom_Client         │
               │ • Ville, Pays        │
               └──────────┬───────────┘
                          │ 1
                          │ 
                          │ *
┌──────────────────────┐  │   ┌──────────────────────┐
│     Dim_Produits     │  │   │     Dim_Date         │
│ • ID_Produit (PK)    ├──┼───┤ • Date (PK)          │
│ • Nom_Produit        │  │ * │ • Année, Mois        │
│ • Catégorie          │  │   │ • Trimestre          │
└──────────┬───────────┘  │   └──────────┬───────────┘
         1 │              │              │ 1
           │              ▼              │
           │        ┌───────────┐        │
           └───────►│Fact_Ventes│◄───────┘
                  * │ • ID_Vente│ *
                    │ • Quantite│
                    │ • Montant │
                    └───────────┘

• Les Tables de dimensions (Dim_) : Elles décrivent le qui, le quoi, le où et le quand. Chaque ligne y possède un identifiant unique (Clé Primaire, ex. ID_Client).
• La Table de faits (Fact_) : Elle enregistre les événements opérationnels chiffrés (les ventes, les commandes, les clics). Elle contient les clés étrangères qui pointent vers les dimensions, ainsi que les métriques quantitatives (Montant_HT, Quantité).

La disposition avec la table de faits au centre et les dimensions autour forme ce que l'on appelle un Schéma en étoile (Star Schema).

---

3. Créer des relations dans la Vue de diagramme

Pour créer votre premier modèle :

1. Assurez-vous d'avoir chargé vos tables dans le Modèle de données (via Power Query en cochant Ajouter ces données au modèle de données ou depuis l'onglet Power Pivot > Ajouter au modèle de données).
2. Rendez-vous dans l'onglet Power Pivot et cliquez sur Gérer (ou Données > Gérer le modèle de données).
3. Dans la fenêtre Power Pivot, cliquez en haut à droite sur Vue de diagramme.
4. Vous voyez apparaître un bloc pour chaque table.
5. Pour créer une relation : cliquez sur le champ clé de la table de dimension (ex. ID_Client dans Dim_Clients), maintenez le clic gauche enfoncé et faites-le glisser sur le champ correspondant de la table de faits (ID_Client dans Fact_Ventes).

[!NOTE] Une ligne apparaît reliant les deux tables avec le symbole 1 du côté de la dimension (valeurs uniques) et le symbole * du côté de la table de faits (valeurs multiples). C'est une relation un-à-plusieurs (1:*).

---

4. Créer une Table Calendrier dédiée

Pour analyser les performances par année, trimestre ou mois, il est impératif d'avoir une table de dates continue (sans aucun jour manquant, y compris les week-ends et jours fériés).

Power Pivot vous permet de la générer automatiquement en un clic : 1. Dans la fenêtre Power Pivot, cliquez sur l'onglet Concevoir. 2. Cliquez sur Table de dates > Nouveau. 3. Power Pivot scanne automatiquement la plage temporelle de vos données et génère une table Calendrier complète avec les colonnes Date, Year, MonthName, DayOfWeek, etc. 4. Reliez le champ Calendrier[Date] au champ Fact_Ventes[Date_Vente] dans la vue de diagramme.

---

5. Introduction au langage DAX : Colonnes calculées vs Mesures

Le DAX (Data Analysis Expressions) est le langage de formule utilisé par Power Pivot et Power BI. Il ressemble aux fonctions Excel habituelles, mais opère sur des tables et des colonnes entières.

La différence capitale à retenir :

Critère	Colonne calculée	Mesure DAX
Quand est-elle calculée ?	Lors du chargement / actualisation des données	À la volée, chaque fois que l'utilisateur filtre le TCD
Consommation mémoire	Lourde (stockée sur chaque ligne du disque/RAM)	Quasi-nulle (seul le résultat agrégé est calculé)
Usage recommandé	Uniquement pour créer des critères de découpage/tranches	Pour 95 % des indicateurs numériques (KPIs)

Écrire ses premières mesures DAX

Dans la fenêtre Power Pivot, sous la grille de données de la table Fact_Ventes, se trouve la grille de calcul (séparée par une ligne horizontale). Cliquez dans une case vide en bas et tapez vos mesures :

-- 1. Chiffre d'Affaires Total
Total Ventes := SUM(Fact_Ventes[Montant])

-- 2. Volume Total
Total Quantites := SUM(Fact_Ventes[Quantite])

-- 3. Prix Moyen de vente (avec gestion sécurisée de division par zéro)
Prix Moyen := DIVIDE([Total Ventes], [Total Quantites], 0)

-- 4. Ventes de l'Année Précédente (Time Intelligence)
Ventes N-1 := CALCULATE([Total Ventes], SAMEPERIODLASTYEAR(Calendrier[Date]))

-- 5. Croissance annuelle (%)
Croissance YoY % := DIVIDE([Total Ventes] - [Ventes N-1], [Ventes N-1], 0)

[!TIP] La fonction CALCULATE() est la fonction reine du DAX. Elle permet de modifier le contexte de filtre pour calculer un indicateur sur une période différente, un segment de clients précis ou toute l'entreprise.

---

6. Exploiter le Modèle de données dans un Tableau Croisé Dynamique

Une fois les relations tracées et vos mesures écrites :

1. Toujours dans la fenêtre Power Pivot, cliquez sur le bouton Tableau croisé dynamique (sur l'onglet Accueil).
2. Choisissez de l'insérer sur une nouvelle feuille Excel.
3. Observez le volet des champs à droite : Toutes vos tables sont réunies dans une même liste !
4. Déposez Dim_Clients[Ville] en Lignes.
5. Déposez Dim_Produits[Catégorie] en Colonnes.
6. Déposez votre mesure [Total Ventes] (signalée par une petite icône de calculatrice fx) en Valeurs.

Le tableau croise instantanément les villes des clients et les catégories de produits, tout en calculant les ventes exactes. Aucun RECHERCHEV, aucun risque d'erreur, et une fluidité absolue même sur des volumes massifs de données !

---

7. Mise en pratique : Construction d'un modèle commercial

Mission

Vous disposez de 3 tables chargées dans Power Pivot : * tbl_Ventes (ID_Commande, ID_Client, ID_Produit, Quantite, CA_HT) * tbl_Clients (ID_Client, Nom_Entreprise, Secteur_Activite) * tbl_Produits (ID_Produit, Nom_Produit, Cout_Unitaire)

Travail à réaliser :

1. Créez les relations 1:* entre tbl_Clients et tbl_Ventes, puis entre tbl_Produits et tbl_Ventes.
2. Dans tbl_Ventes, ajoutez la mesure Chiffre Affaires := SUM(tbl_Ventes[CA_HT]).
3. Créez un TCD analysant le Chiffre d'Affaires par Secteur_Activite de client.
4. Insérez un Segment sur le champ tbl_Produits[Nom_Produit].

---

Vous maîtrisez désormais les fondements de la modélisation décisionnelle moderne. Cette architecture est rigoureusement identique à celle employée dans Power BI. Dans la dernière section, nous ferons le bilan de vos acquis et explorerons les ressources pour valider votre certification.

Partagez sur les réseaux sociaux

Commentaires