Power Pivot : Quelles sont les meilleures pratiques d’affaires ?

Cet article fait suite à mon article Power BI : Meilleures pratiques d’affaires avec Power Query. Il a pour but de vous éclairer sur les meilleures pratiques d’affaires avec Power Pivot. C’est en appliquant ces conseils que vous pourrez construire des modèles de données performants et éviter des écueuil importants.

Mesures implicites vs mesures explicites

Avant toutes choses, pour utiliser adéquatement Power Pivot, vous devez maîtriser deux notions, la notion de contexte d’évaluation d’une mesure (contexte de lignes pour les colonnes calculées et contexte de filtres pour les champs calculés) et la notion de mesures implicites vs mesures explicites.

Table vide pour contenir les mesures en DAX

Power Pivot vous offre une zone de calculs (calculation area) où vous pouvez créer vos différentes mesures en DAX. En théorie, vous devriez toujours créer une mesure dans sa table de référence. Par exemple, si vous créez une mesure de ventes, celle-ci devrait loger dans la zone de calculs de la table de ventes et si vous créez une mesure de temps, celle-ci devrait loger dans votre table de dates. Toutefois, vous pourriez parfois être confronté à certains problèmes d’importation de données dans votre modèle de données (par exemple, des importations via Power Query), qui vous forceraient à supprimer la table importée de votre modèle de données et à la ré-importer. Ce faisant, vous supprimeriez alors toutes les mesures que vous auriez insérées dans votre zone de calculs. Je recommande donc souvent de créer une table vierge dans votre modèle de données et d’y insérer vos mesures (champs calculés). De cette façon, si vous devez, pour une raison ou une autre, supprimer une table et la ré-importer de nouveau dans votre modèle de données, vous ne perdrez pas l’ensemble de vos mesures. Ceci est simplement une précaution.

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.

Conventions du langage DAX

Noms de tables, de colonnes et de mesures

D’abord, vous devriez utiliser des noms de tables significatifs, puisque ce sont ces noms de tables qui seront affichés dans l’interface de l’usager (dans les champs du tableau croisé dynamique). Sachez qu’il est aussi possible d’ajouter une description à une table ou à une mesure si nécessaire. Cette description sera notamment utilisée par l’outil “tooltip” du tableau croisé dynamique. Il est également recommandé de garder les noms de vos tables, colonnes et mesures, le plus court possible. De même, vous devriez :

• Mettre le nom d’une table de faits au pluriel (ajouter un “s” ou un “x” à la fin)
• Mettre le nom d’une table de dimension au singulier (pas de “s” ou de “x” à la fin)
  • Par exemple: VentesPartenaires (table de faits) vs Partenaire (table de dimension)
• Renommer les colonnes qui appartiendraient à deux tables mais qui auraient le même nom comme Ventes car Power View, notamment, ne permet pas de renommer les colonnes par la suite

Structure des fonctions en DAX

Ensuite, il est fortement recommandé d’utiliser certaines conventions lorsque vous écrivez des formules en DAX, le langage de programmation de Power Pivot (Data analysis expressions). Par exemple, vous devriez toujours:

• Écrire les noms de fonctions en lettres majuscules
• Mettre la première lettre d’une mesure et/ou d’une table en majuscule

Pour vous aider à construire efficacement vos formules en DAX, je vous suggère fortement le DAX Fomatter, que vous trouverez à l’adresse suivante: http://www.daxformatter.com. Ce site vous propose d’aller encore plus loin en termes de conventions et de faire en sorte que vos formules ressemblent à ceci:

Finalement, si vous avez des formules DAX qui impliquent un numérateur et un dénominateur ou une soustraction entre deux mesures ou tout autre scénario du genre, vous devriez séparer vos calculs. Par exemple, pour une mesure qui implique un numérateur et un dénominateur, vous devriez définir une mesure pour le numérateur, une mesure pour le dénominateur et une mesure qui fait la division du numérateur par le dénominateur. Ceci sera plus simple à maintenir. Si les mesures intermédiaires ne sont pas intéressantes pour l’usager, vous pourrez toujours les cacher (voir point concernant les données non visibles pour les usagers).

Intégration d’une table de dimension de dates

Vous devriez toujours inclure une table de dimension de dates dans votre modèle de données, avec les mois, les trimestres, les années et autres. Ceci facilitera largement la rédaction de formules DAX, surtout les fonctions de Time Intelligence. Cette table sera également très pratique dans des scénarios d’années financières qui ne se terminent pas au 31 décembre. Cette table devrait être construite sur une granularité de jours et inclure tous les jours sans exception et sans écart. Une colonne devrait être en format date (ne doit pas être une colonne calculée mais bien provenir de la source de données) et il faudrait ensuite indiquer à Power Pivot qu’il s’agit d’une table de dates.

Pour apprendre comment créer une table de dimension de dates rapidement, vous pouvez consulter l’article: Office App: Créer une table de dimension “temps” dans Excel.

Compression des données

Dans Power Pivot, comme dans Excel, mais encore plus dans Power Pivot, à cause du Vertipaq, il est beaucoup plus performant d’avoir plus de lignes et moins de colonnes que plus de colonnes et moins de lignes. C’est d’ailleurs pour cette raison qu’on souhaite élaborer des modèles de données en schéma en étoile (star schema) ou en flocon de neige (snowflake). De même, il est beaucoup plus performant d’importer seulement les colonnes que l’on utilisera dans une table qu’une table en entier.

Éléments non visibles pour les usagers

Vous devriez toujours cacher les éléments qui ne seront pas utiles aux usagers, i.e. les mesures, les tables et les colonnes dont ils n’auront pas besoin. Ceci permettra à l’usager de comprendre plus aisément le modèle de données. Pour ce faire, vous pouvez utiliser l’option Hide from client tools. L’élément caché en question deviendra grisé du côté de Power Pivot (mais pourra continuer d’être utilisé dans des formules) mais il n’apparaîtra pas dans les éléments visibles pour l’usager du côté d’Excel., i.e., dans ses champs disponibles pour créer un tableau croisé dynamique ou pour créer une visualisation Power View.

Hiérarchies

Lorsque possible, il est recommandé de créer des hiérarchies pour les usagers, d’abord parce que ça réduit la complexité du modèle de données et ensuite parce qu’il est plus performant de construire un tableau croisé dynamique sur une hiérarchie de champs plutôt que sur le grain d’une table.

À cet égard, vous pouvez consulter l’article: Comment et pourquoi créer des hiérarchies dans Power Pivot?.

KPI

Lorsque possible, il est recommandé de définir les KPI à même le modèle de données.

À cet égard, vous pouvez consulter l’article: Élaborez des KPI avec Power Pivot.

Taxonomie

Si votre intention est d’utiliser votre modèle sur un site Power BI en ligne, il est recommandé de définir une taxonomie pour les items de votre modèle de données.

À cet égard, vous pouvez consulter l’article: Le langage naturel désormais utilisé avec Excel, via Power BI.

Vous pouvez également définir une taxonomie à même Power Pivot, de la façon suivante:

Et vous?

Avez-vous d’autres meilleures pratiques d’affaires à suggérer? Le cas échéant, je vous invite à utiliser la zone de commentaires ci-bas pour nous en faire part.

Formation complémentaire

Pour une introduction à la modélisation de données avec Power Pivot, un complément BI d’Excel, qui permet d’importer, de croiser et de bonifier diverses sources de données externes pour finalement les analyser à l’aide de tableaux croisés dynamiques, suivez la 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é.