Power Pivot et Power BI: L’importance du modèle en étoile ⭐️

Publié le 22 juin 2017
par Sophie Marchand M.Sc., CPA, CGA, MVP
Modèle en étoile final

Power Pivot et Power BI: L’importance du modèle en étoile ⭐️

J’ai commencé à utiliser Power Pivot à sa sortie (add-in pour Excel 2010 à l’époque). Je me souviens  qu’au début, je n’étais pas certaine de comprendre pourquoi il était si important de construire un modèle de données en étoile (ou du moins un modèle qui ressemble le plus possible à un modèle en étoile) pour pouvoir utiliser Power Pivot adéquatement (et aujourd’hui Power BI). Au fil des ans, chaque fois que j’ai eu à répondre à des problématiques du côté de Power Pivot, je suis toujours revenue à la base et la réponse s’est souvent trouvée dans le fameux modèle en étoile, que certains usagers choisissent malheureusement d’ignorer. Le présent article vous présente un cas concret où se passer d’un modèle en étoile entraîne des problématiques dans le tableau croisé dynamique. Ceci vous permettra de réaliser, tout comme moi, l’importance de bien modéliser ses données avant de les analyser.

 

Données de départ

Pour illustrer le tout, nous allons utiliser les données de la table suivante. Notez que les données sont présentées par période et qu’il n’y a pas de dates associées.

Modèles en étoile - Données de base

 

Mesures en DAX

Supposons maintenant que vous décidez d’analyser cette table de données, sans créer de modèle de données en étoile et que vous y ajoutez les deux mesures suivantes:

Modèle en étoiles - Somme des ventes

Modèle en étoile - Ventes YTD

 

Tableau croisé dynamique – Période 2

Lorsque vous analyserez les données dans un tableau croisé dynamique, à la période 2, l’information présentée sera exacte. On y  retrouvera les filiales en étiquettes de lignes et les mesures dans les valeurs. Notez qu’un tableau croisé dynamique qui ne comprendrait que les noms de filiales en étiquettes de lignes présenterait les filiales A, B et C.

Modèle en étoile - Période 2

 

Tableau croisé dynamique – Période 3

Toutefois, lorsque vous analyserez les données pour la période 3, vous rencontrerez un souci. En effet, en cliquant sur la période 3, dans le segment, vous ne verrez apparaître que les filiales B et C. Le total des ventes YTD sera le bon et considérera les ventes de la filiale A mais le tableau ne présentera pas ce résultat sur une ligne individuelle.

Modèle en étoile - Période 3

 

Explications

À la période 3, il n’y a aucune vente dans la filiale A. Toutefois, comme il y avait des ventes dans les périodes 1 et 2, il y a un montant de ventes YTD pour la filiale A, à la période 3. Mais comme vous demandez de filter votre tableau croisé dynamique selon la période 3, vous ne verrez pas la ligne représentant la filiale A. C’est comme si vous aviez filtré votre table sur la période 3. Vous n’auriez aucune ligne correspondant à la filiale A.

 

Solution

La solution sera de construire un modèle de données en étoile, avec notamment une dimension période et d’utiliser cette dimension dans votre segment et dans votre tableau croisé dynamique. Ça fait d’ailleurs partie des meilleures pratiques d’affaires avec Power Pivot.

 

Solution pas à pas : Power Query

Dans un premier temps, vous devez aller dans l’éditeur de requêtes et ajouter une clé de période dans votre table de transactions, tel qu’illustré ci-dessous.

Éditeur de requête - Ajout colonne

 

Il s’agit simplement d’ajouter une colonne personnalisée avec l’information suivante:

Colonne personnalisée DimPériodes

 

Vous devez ensuite vous créer une dimension Filiale, une dimension Canal et une dimension Période, avec la même clé de période que dans votre table de transactions.

DimFiliales

DimCanal

DimPériodes

 

Pour créer ces tables de dimension, vous n’avez qu’à cliquer sur votre table de transactions avec le bouton droit de votre souris et choisir Reference. Vous ferez ainsi une référence à votre table de transactions. Ensuite, vous supprimez toutes les colonnes sauf la colonne de la dimension, par exemple Filiale, et vous supprimez les doublons.

Référence à une autre requête
 
 

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

 

Création du modèle en étoile

Finalement, vous créez un modèle de données en étoile comme le suivant:

Modèle en étoile final

 

Vous pouvez alors créer les mesures suivantes:

Mesure modèle étoile Ventes

Mesure modèle en étoile Ventes YTD

 

Notez qu’elles utilisent les périodes de la nouvelle table de dimension DimPériodes.

 

Résultat final

En utilisant les années et les périodes de la table de dimension DimPériodes dans les segments du tableau croisé dynamique et en utilisant les filiales de la table DimFiliales dans les lignes du tableau croisé dynamique, vous constaterez que les ventes YTD présenteront les bonnes informations, autant pour la période 2 que la période 3.

TCD période 2

TCD Période 3

 


 

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Si vous souhaitez mieux comprendre comment fonctionne Power Pivot et la modélisation de données, jetez un coup d’œil à notre formation Excel – Introduction à Power Pivot et aux modèles de données.
 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Commentaires - Excel – Introduction à Power Pivot et à la modélisation de données

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

7 réflexions sur “Power Pivot et Power BI: L’importance du modèle en étoile ⭐️”

  1. Christophe Labbé

    Bonjour,

    En sélectionnant dans le TCD afficher les ligne sans données on obtient le même résultat.

    Merci par ailleurs pour vos précieuses informations et conseils 😀

    Bien à vous

  2. Bonjour,
    Merci pour ce nouvelle article.
    Si je peux me permettre, vous présentez souvent (presque toujours) des exemples plutôt commerciaux pour lesquels je trouve finalement assez simple de créer des modèles en étoiles. (En tout cas intelligible)
    Pour ma part je travail en industrie, j’ai dû créer un modèle reprenant plusieurs tables de faits. (Les volumes produits et les temps d’arrêts par jour, par ligne et par motif). Sije veux créer un modèle en étoile c’est alors ma table de dimension date qui se retrouve au centre par ce que c’est le seul élément commun entre ces tables de faits. D’autres tables de dimensions se retrouvent également communes entre ces tables de fait comme la ligne de production par exemple. Mon modèle comporte donc des liens circulaires, ce qui le rend à priori particulièrement instable voir, plus grave, inexact lors des analyses.
    Comment faire lorsqu’un modèle nécessite plusieurs table de faits afin d’éditer des tableaux de bord globaux?
    Est-ce vraiment possible?
    Un fichier Excel peut il comporter deux modèles distinct pour pouvoir éditer des tableaux de bord depuis ce seul fichier?
    Peux-t’on créer des tableaux de bord dans un seul fichier Excel en provenance de modèles d’autres fichiers?
    Merci d’avance pour le temps que vous voudrez bien accorder à ma réponse. D’avance je m’excuse de ne pas pouvoir partager mon fichier qui comporte des données confidentielles.
    Bien à vous
    Ludovic

  3. En, fait votre modèle comprend plusieurs tables de fait. Pour chaque table de fait vous créez un modèle en étoile, dimension temps , produit, ligne de production, plan de charge, nomenclature, … et vos modèles en étoile sont liés entre eux, ce qui effectivement créer un modèle global p multi-étoile avec la plupart du temps des relations plusieurs à plusieurs pour les relier entre eux, mais cela se gère très bien avec PowerPivot.
    Donc vous devez créer un seul modèle à partir de plusieurs sources de données qui sera il est vrai plus complexe que la plupart des modèles commerciaux, quoique, on peut également avoir plusieurs tables de faits également, ventes, budget, salaires, notes de frais, …

    En espérant avoir répondu à votre commentaire

  4. Je travaille aussi avec plusieurs tables de faits.
    Depuis longtemps j’ai appris à les traiter en “union all” dans sql pour avoir une seule table de fait.
    J’utilise plutôt ssas mais, pour un powerpivot, on peut utiliser la fonction append de powerquery, ça revient un peu au même.
    On se trouve à avoir un seul modèle en étoile et pas de problème de granularité, disons pour des comparaisons réel vs budget ou une mesure serait un calculate de “montant where propriété = actuel” et un autre serait un calculate de ” montant where propriété = budget”

  5. Christophe (Réunion island)

    Bonjour. Merci pour ce tutoriel.
    Toutefois la 2ième formule du chapitre “mesures en DAX” ne fonctionne pas de mon côté.
    M’y prendrais-je mal? je tape la formule à côté de “fx” dans “ajouter une colonne” de Power Pivot.
    Ou la formule contient t’elle une petite erreur svp?
    merci de votre aide.
    Christophe

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top