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.
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:
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.
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.
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.
Il s’agit simplement d’ajouter une colonne personnalisée avec l’information suivante:
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.
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.
Création du modèle en étoile
Finalement, vous créez un modèle de données en étoile comme le suivant:
Vous pouvez alors créer les mesures suivantes:
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.
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 :
La 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é.
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
Bonjour,
En fait, non. Pas dans ce cas-ci. Vous pouvez le tester de votre côté.
Au plaisir,
Sophie
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
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
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”
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
Bonjour Christophe,
Il ne faut pas ajouter une colonne mais plutôt créer une nouvelle mesure. Ce sont des options différentes.
Au plaisir,
Sophie