Contact: 514-605-7112 / info@lecfomasque.com

Power Pivot et Power BI: L’importance du modèle en étoiles

Publié le : 22 juin 2017

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

 

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

 

Ce sujet vous intéresse?

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.

 


NOTRE OFFRE DE FORMATIONS


Nos formations sont éligibles à la subvention PACME du Québec

et notre entreprise est datadockée en France

Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse 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

  • réponse Ludovic ,

    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

    • réponse Jean-Pierre Girardot ,

      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

      • réponse mrivest ,

        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 »

        Laisser un commentaire