67
Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires LE CFO MASQUÉ MEILLEURES PRATIQUES D’AFFAIRES AVEC POWER QUERY ET POWER PIVOT

Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Embed Size (px)

Citation preview

Page 1: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUESD’AFFAIRES AVEC

POWER QUERYET

POWER PIVOT

Page 2: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

Sophie Marchand, M.Sc., CPA, CGA, MVP Le CFO masqué

15 ans d’experience en finance corporative Fondatrice du CFO masqué Consultation et formation Modélisation d’affaires avancée Excel / Power BI Responsable du groupe Montreal Modern Excel

and Power BI

QUI SUIS-JE?

Page 3: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

PLAN DE PRÉSENTATION

Introduction Meilleures pratiques d’affaires avec Power Query

Optimiser l’importation de données Optimiser les requêtes Organiser les requêtes Charger les données

Meilleures pratiques d’affaires avec Power Pivot Maîtriser quelques notions essentielles sur la compression Optimiser le modèle de données

Conclusion

Page 4: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

INTRODUCTION

Page 5: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

INTRODUCTION

Quincaillerie requise Pour tirer le maximum de vos add-ins

Windows 64 bit, Office 64 bit Processeur 4 coeurs Mémoire vive suffisante, au minimum 8 GB de RAM, mais 16 GB est

largement préférable Un SSD (Solide State Drive) est préférable

Page 6: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

INTRODUCTION

Philosophie de base BI en mode libre-service

Mon opinion personnelle: On ne doit pas créer un modèle de données unique (via PQ et/ou PP) pour

toutes les analyses. Sinon, ça reviendrait à créer un cube ou un entrepôt de données relationnel dans un outil qui n’est pas fait pour ça.

On veut plutôt créer le modèle de données le plus optimal pour chaque analyse.

Ces add-ins ne sont pas des solutions de bases de données. Ils fonctionnent en mode RESFRESH ALL.

Page 7: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

INTRODUCTION

Comprendre les limites et les spécifications Power Query

Taille des données qui peuvent être analysées par l’engin: 64-bit: Limité à la mémoire virtuelle disponible 32-bit: Limité à environ 1GB

Power Pivot Taille des données qui peuvent être analysées par l’engin:

64-bit: Limité à la mémoire virtuelle disponible 32-bit: Limité à environ 2GB Taille limite du modèle de données : 4GB

La mémoire virtuelle est une combinaison de mémoire physique (RAM) et d’espace disque. C’est là qu’un SSD va beaucoup aider!

Page 8: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES D’AFFAIRES AVEC POWER QUERY

Page 9: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Ne pas dupliquer les informations de connexion Permettre de pointer facilement vers une nouvelle source de

données 3 solutions possibles:

http://www.lecfomasque.com/power-query-faire-pointer-vos-requetes-vers-une-nouvelle-source-de-donnees/

Il est préférable de ne pas utiliser la table de paramètres sur une source de données SQL (sera vu plus loin)

Voir autre exemple de table de paramètres (date début et date fin)

Page 10: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Source: Serveur SQL

On importe des tables entières et ensuite, on fait des manipulations (suppression de colonnes, filtres sur des lignes, etc.)

On peut rédiger un script SQL mais à moins que ce script soit ultra spic n’ span, ce n’est pas recommandé (sera vu plus loin)

On peut modifier le code M pour aller chercher directement les données mais là aussi, on verra que ce n’est pas recommandé lorsqu’il y a des problèmes de performance

Page 11: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Source: Serveur SQL

Comprendre le Query Folding Le serveur source exécute lui-même des transformations demandées par

PowerQuery Le Query Folding se produit selon les circonstances, les sources et les

transformations impliquées. L’usager n’a pas vraiment de contrôle… Il est facile pour un usager de « briser » le Query Folding, ce qui peut

affecter considérablement la performance de la requête Rédiger un code M ou un script SQL pour importer des données d’un

serveur SQL ou utiliser une table de paramètres font partie des actions qui brisent le Query Folding, en plus de certaines fonctionnalités qui n’ont pas d’équivalents sur le système source

Page 12: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Source: Serveur SQL

Conclusion: Il est préférable de ne pas utiliser de code M ou de script SQL pour

importer des données d’un serveur SQL Il est préférable de ne pas utiliser les paramètres de connexion dans une

table de paramètres Excel si on rencontre des problèmes de performance

Il est préférable de construire les requêtes de sorte que toutes les actions pouvant bénéficier du Query Folding soient effectuées en premier puisque lorsqu’une action qui ne peut bénéficier du Query Folding est rencontré, il n’y a plus de Query Folding possible

Page 13: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Source: Cubes tabulaires et multidimensionnels

On choisit les colonnes que l’on souhaite importer

Page 14: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Source: ODataFeed

Selon la source, le ODATA REST API permet de filtrer les données à l’importation via l’URL

Vous pouvez également télécharger le ODATA QUERY DESIGNER

Page 15: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Source: Microsoft Exchange

Sélectionner Mail / Edit Ne pas faire LOAD car ça va prendre une éternité pour loader toute la

boîte de courriels! Tout pointe sur votre boîte de courriels donc ce n’est pas une solution

qui se partage. Il serait préférable, si on doit partager un TB connecté à Microsoft Exchange, de downloader les données quelques part et de là, faire le TB.

Exemple de connexion sur Microsoft Exchange

Page 16: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser les requêtes Penser à ce qui peut survenir dans l’avenir et enrichir nos

requêtes afin d’empêcher de futurs problèmes Par exemple, Ken Puls propose d’utiliser le | lorsque vient le

temps de fusionner des colonnes qui devront être séparées par la suite Comme le | est rarement utilisé, il est peu probable qu’on sépare des

données qui ne devraient pas être séparées Remove columns vs Remove other columns Unpivot columns vs Unpivot other columns

Page 17: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser les requêtes Excel.CurrentWorkbook() Attention, la requête va aussi chercher la table de résultats dans

la liste, si celle-ci est chargée dans Excel Excel.Workbook([Content]) n’est pas récursif mais va chercher

les feuilles, les plages de données et les tables Voir exemple

Page 18: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser les requêtes Toujours terminer une requête en attribuant les bons Data types

Page 19: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser les requêtes Type de données : Any Quand PQ ne sait pas de quel type de données il s’agit Peut prendre diverses formes quand téléchargé dans Excel ou PP ou

référé par une autre requête Certaines fonctions PQ comme REPLACE VALUES ne fonctionneront pas

correctement sur ce type de données Si on utilise APPEND ds PQ et que les 2 sets de données n’ont pas le

même TYPE de données, on va obtenir une colonne de données ANY Solution: toujours assigner un type de données en guise de dernière

étape dans une requête Voir exemple

Page 20: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser les requêtes Attention aux formats de dates Voir exemple

Page 21: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser les requêtes Créer des fonctions qui pourront être réutilisées Voir exemple simple + expliquer exemple plus complexe

Page 22: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser les requêtes Utiliser la fonction Buffer Enregistre en mémoire les résultats d’une opération

Attention! Le Query Folding ne s’applique pas dans ce cas.

Page 23: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser les requêtes Écrire le code sur plusieurs lignes (plus facile à comprendre et

débugger)

Page 24: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Fast Data load

Permet de charger les données plus rapidement, mais, ce faisant, il se pourrait qu’Excel soit hors d’usage pour toute la période d’importation/rafraîchissement de données.

Page 25: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Fast Combine

Améliore la performance de vos requêtes, mais celles-ci ignorent alors le niveau de confidentialité que vous avez attribué à vos sources de données.

Page 26: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Cache

Plus de mémoire permet de présenter plus rapidement les résultats de Query Preview, car l’info est conservée sur disque.

Page 27: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Décocher Enable Background

En décochant cette case, vous pouvez potentiellement réduire le temps de rafraîchissement des requêtes mais ce faisant, vous ne serez pas en mesure de travailler dans Excel, pendant le rafraîchissement.

Page 28: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Optimiser l’importation de données dans Power Query Rafraîchir les requêtes dans un ordre déterminé

Utiliser VBA Utiliser Power Planner

Page 29: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Organiser les requêtes Donner des noms significatifs aux étapes de transformation Préférer les noms descriptifs mais en un seul mot

Page 30: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Organiser les requêtes Donner un nom significatif aux bases de données sous-jacentes

Page 31: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Organiser les requêtes Créer des requêtes intermédiaires (connection only) Chargées seulement lorsqu’une requête qui s’attache à celle-ci

est rafraîchie Optimise la mémoire Pas d’impact sur la taille du fichier

Page 32: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Organiser les requêtes Créer des références aux requêtes intermédiaires

Page 33: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Organiser les requêtes Créer des groupes de requêtes

Page 34: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Charger les données dans Power Pivot Ne pas charger les données à la fois dans Excel et à la fois dans

PP

Page 35: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Charger les données dans Power Pivot Excel 2010 et 2013 Possibilité de corrompre le modèle de données lorsque les données

proviennent de PQ, par exemple, en renommant une table directement dans PP (en 2016, on a un message qui nous dit de faire ça ds PQ)

On ne s’en rend pas compte tout de suite mais suite à une action précise, on a éventuellement un message d’erreur

Pour voir si un modèle est endommagé, Excel / Data / Connections / choisir la Requête / Properties / Definition: Some properties cannot be changed because this connection was modified using Power Pivot Add-in…

Page 36: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Charger les données dans Power Pivot Excel 2010 et 2013 (suite) Quoi faire pour rétablir?

Supprimer la table (incluant ses mesures et ses colonnes calculées) Reconstruire la table, les mesures, les colonnes calculées

Quoi ne pas faire pour éviter la problématique? Changer le nom d’une table ds PP Renommer une colonne importée de PQ dans PP Supprimer une colonne importée de PQ dans PP IMPACT: NON REFRESHING PP

Page 37: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PQ

Charger les données dans Power Pivot Excel 2010 et 2013 (suite) Ce qui peut être fait sans problème

Ajouter/modifier/supprimer des colonnes calculées ds PP Ajouter/modifier/supprimer des relations ds PP Ajouter/modifier/supprimer des mesures ds PP Faire quelque modification que ce soit à la table dans PQ

En somme, si les tables proviennent de PQ, faire toutes les transformations de ces tables, dans PQ

Sophie Marchand
http://www.powerpivotpro.com/2014/07/i-modified-an-existing-table-in-power-query-and-now-it-wont-refresh-a-fix/
Page 38: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES D’AFFAIRES

AVEC POWER PIVOT

Page 39: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Quelques concepts importants Zone de calculs

Page 40: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Quelques concepts importants Une colonne calculée en DAX

dérivée des colonnes originales du modèle de données mêmes caractéristiques qu’une colonne importée

Une mesure DAX est définie une seule fois dans le modèle de données évaluée seulement lorsqu’utilisée dans un tableau croisé dynamique

Page Excel contenant des données provenant de Power Pivot consommation de mémoire non affectée par le volume de données dans

Power Pivot données chargées seulement quand la fenêtre Power Pivot est ouverte ou

quand un tableau/graphique croisé dynamique est mis à jour

Page 41: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Quelques concepts importants Les messages suivants doivent être interprétés comme suit:

Retrieving Data: Traitement des données (importation depuis la source dans le modèle de données)

Loading Data Model : Chargement du modèle de données en mémoire Reading Data: Requête sur le modèle de données et mise à jour des

tableaux croisés dynamiques et autres rapports

Page 42: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Quelques concepts importants Power Pivot utilise 2 sortes de compressions: Hash encoding

utilisé pour les tables de valeurs uniques (tables de dimensions) consomme de la RAM (est plus lent)

Value encoding utilisé pour les valeurs numériques et les opérations mathématiques

(tables de faits) consomme davantage du CPU

Page 43: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Quelques concepts importants Les colonnes clés des tables de faits et des tables de dimensions

devraient toujours êtres de type entier (integer) Au besoin, utiliser Power Query pour remplacer du texte par des

chiffres entiers

Sophie Marchand
Pour en savoir davantage sur la compression des données dans Power Pivot:How does Power Pivot store and compress data?Script Update: What is Eating Up My Memory in Power Pivot?
Page 44: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Réduire le nombre de colonnes

Plus particulièrement, les colonnes qui comprennent un large éventail de valeurs uniques.

Les colonnes comprenant des chaînes de texte devraient être analysées et transformées au besoin, sachant que: Power Pivot crée un dictionnaire de toutes les chaînes de texte uniques. Donc, avoir une longue chaîne de texte n’est pas un problème en soi si le

modèle contient peu de chaînes de texte distinctes. Cela peut par contre devenir un problème si, en moyenne, la longueur des

chaînes de texte est importante et qu’il y a beaucoup de valeurs distinctes.

Page 45: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Exemples de colonnes pouvant être supprimées

Toutes les colonnes d’un entrepôt de données qui sont le résultat d’un processus ETL comme : Create_Date, Update_Date, ETL_Run, etc.

Toutes les clés primaires d’une table de faits, c’est-à dire les clés qui produisent des identifiants uniques pour chaque ligne. Ces clés primaires ne permettent pas de créer des relations avec d’autres

tables et ne sont habituellement d’aucun intérêt dans les analyses. Exemple: numéro de facture

Toutes les colonnes géographiques, par exemple, pourraient être fusionnées pour n’avoir qu’une seule colonne avec un IDGeographie.

Page 46: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Toutes les colonnes d’une table de faits qui pourraient être déplacées

dans une table de dimensions, en créant un schéma en étoile (normalisation) Pas un gros impact sur la taille de la base de données, mais gros impact sur le

temps de traitement et sur la consommation de mémoire. Ne pas aller trop loin non plus dans la normalisation puisque les relations entre

les tables consomment aussi de la mémoire et nécessitent souvent d’ajouter des colonnes (clés communes).

Toutes les colonnes numériques qui pourraient être regroupées en 2 colonnes seulement, une colonne de valeurs et une colonne d’étiquettes. Attention! Cette approche n’apporte pas toujours les résultats escomptés!!!! Ça

dépend des cas et surtout du nombre de valeurs distinctes dans la nouvelle colonne globale des valeurs.

Page 47: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données PowerPivot Pro

Page 48: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Comment supprimer une colonne

Page 49: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Augmenter le nombre de colonnes

Page 50: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Exemples de colonnes qui méritent d’être fragmentées

Séparer une colonne qui comprend des montants composés de dollars et de cents en deux colonnes Regrouper ensuite par le biais d’une mesure DAX Exemple: TotalAmount= SUM([Dollars])+DIVIDE(SUM([Cents]),100) Réduit le nombre de valeurs uniques totales

Séparer une colonne qui comprend des heures composées de dates et d’heures en deux colonnes et les regrouper par le biais d’une mesure DAX par la suite

Page 51: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Réduire le nombre de lignes

Des lignes de transactions d’une table de faits pour des années historiques qui ne sont pas pertinentes pour l’analyse ou pour un volet de l’entreprise qui n’a pas besoin d’être analysé.

Des lignes d’une table de dates pour des dates qui ne sont pas pertinentes pour l’analyse, par exemple, les années historiques. Sinon, Power Pivot fait ses calculs pour toutes les années de la table de dates. Vous empirez la situation lorsque vous ajoutez des mesures de temps comme

PriorYear, YeartoDate, PriorYeartoDate, etc. et/ou que vous ajoutez ensuite des segments (slicers) sur ces calculs.

Page 52: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Supprimer des lignes

Page 53: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Optimiser le poids d’une colonne

Exemples de colonnes à optimiser en réduisant la granularité Modifier les colonnes avec des valeurs qui comprennent une portion date

et une portion temps Vérifier si vous avez besoin de la portion temps Vérifier si vous avez besoin de la portion temps en heures? en minutes?

en secondes? en millisecondes? Réduire le nombre de décimales (peut avoir un impact significatif)

Page 54: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Remplacer les colonnes calculées par des mesures DAX

Exemple: Table avec UnitPrice et Quantity n’a pas besoin d’une colonne TotalSales. TotalSales peut être définie par une mesure DAX TotalSales:=SUMX(SalesTable,SalesTable[UnitPrice]*SalesTable[Quantity])

Les colonnes calculées ou régulières occupent de l’espace dans le modèle. Les mesures sont calculées au moment d’une requête.

Meilleures d’un point de vue de consommation de ressources. Peuvent entraîner des délais au moment des requêtes par les usagers. Écrire les mesures les plus efficaces possibles. Un filtre (un segment par exemple) peut affecter plusieurs tables et donc

plusieurs mesures à la fois, augmentant d’autant le temps de traitement.

Page 55: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Remplacer les colonnes calculées par des mesures DAX

Les colonnes calculées sont recalculées au rafraîchissement de la source. Ceci signifie que ça prend plus de temps à l’ouverture. Les mesures sont recalculées lors d’une requête (en utilisant des filtres), ce qui retarde la mise à jour du tableau de bord, par exemple. Ceci peut déranger les usagers. Mais en général, on recommande néanmoins de remplacer les colonnes calculées par des mesures.

De plus, chaque cellule d’un tableau croisé dynamique est calculée indépendamment, donc plus vous ajoutez de détails dans le TCD (des sous-catégories, par exemple), plus vous compliquez les choses.

On peut utiliser le DAX (1 seul code plutôt qu’une série de mesures) pour présenter un tableau croisé dynamique détaillé (tabulaire)

http://www.simple-talk.com/sql/reporting-services/using-dax-to-retrieve-tabular-data/

Page 56: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Optimiser l’usage des segments

Chaque segment envoie 2 requêtes 1 requête envers lui-même pour savoir s’il doit

ajouter/supprimer/modifier ses boutons. 1 requête en fonction des autres segments (cross-slicers filtering).

Excel essaie de faire correspondre toutes les valeurs du deuxième segment au premier et n’affiche que les résultats correspondants (ça demande beaucoup de travail).

Excel fait ça pour tous les segments, à chaque modification. Quelques filtres croisés (cross-slicers filtering) peuvent brimer considérablement la performance (de 5 à 10 fois, parfois même plus).

Page 57: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Optimiser l’usage des segments

Chaque fois que vous ajoutez un segment, vous faites en sorte que les requêtes générées par les autres segments deviennent plus complexes, donc, retirer un segment peut permettre aux autres segments d’être plus performants.

Vous pouvez: Remplacer les segments par des filtres de rapports traditionnels au besoin Réduire les segments croisés ou fermer cette option Utiliser des mesures DAX plus performantes Utiliser le segment sur la table de dim plutôt que sur la table de faits (mais

attention aux schémas snowflakes)

http://www.lecfomasque.com/controler-plusieurs-tableaux-croises-dynamiques-avec-un-seul-menu-deroulant/

Page 58: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Optimiser le modèle de données Outils

Il existe différents outils pour vous aider à améliorer la performance de vos modèles de données. Workbook Size Optimizer: inspecte vos modèles de données élaborés dans

Power Pivot, évalue si ceux-ci peuvent occuper moins d’espace et, le cas échéant, améliore la compression.

Fichier Shema.ini: pour préciser le format de vos fichiers texte dès l’importation dans Power Pivot.

Macro rédigée par Kasper de Jonge: permet d’analyser votre modèle de données et de préciser comment chaque élément de votre modèle de données consomme de mémoire.

Page 59: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Convention du langage DAX : DAX FORMATTER

Page 60: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Convention du langage DAX Séparer un long code en plusieurs blocs (plus facile à débugger et certains

blocs peuvent être réutilisés dans d’autres calculs) Choisir des noms de calculs similaires pour les calculs qui font référence aux

mêmes champs: Sum of Revenue, Count of Revenue, Revenue YoY%, Revenue to Target

Choisir des noms de mesures les plus clairs possibles (descriptifs) Cacher les calculs non pertinents pour l’analyse (hide from client tool) Donner les bons formats

Page 61: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Création d’une table de dates Toujours créer une table de dimension de dates séparée S’assurer que la table contient des plages de données de dates en continu La colonne date de la table de dimension date devrait être en granularité de

jours Créer des relations entre les tables de faits et la table de dimension de dates Indiquer la table comme une Date Table dans Power Pivot et définir la colonne

de date qui servira de clé commune Ne jamais utiliser une date dans une colonne d’une table de faits comme

argument dans les fonctions de Time Intelligence

Page 62: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Création d’une table de dates Excel, Power Query, DAX, Excel 2016, Excel add-in

Excel 2016: Nouvelle fonctionnalité pour créer une table de dates à partir de Power Pivot.

Page 63: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Créer des hiérarchies

Page 64: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

MEILLEURES PRATIQUES PP

Excel 2010 (PQ et PP étaient 2 add-ins séparés) Pas de fonctionnalité pour charger les requêtes Power Query dans Power Pivot Mais on peut le faire autrement

Power Pivot / Design / Existing Connection / choisir source / Next /Changer le nom de table au besoin

Si on ajoute des colonnes à la requête PQ, ERROR, … was modified in PQ after the connection was added…

Retour à Excel / Data / Connections / choisir la requête problématique / Properties / Definition / CTRL+A/ CTRL+C / Power Pivot / Design / Existing Connection / la requête en question / Edit / Sélectionner tout / CTRL+V / Close / Refresh / Design / Table Properties / Save

Page 65: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

QUESTIONS?

Page 66: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

SVP, veuillez réclamer votre PUG Badge pour le groupe Montreal Modern Excel and Power BI http://

community.powerbi.com/t5/Montreal-Modern-Excel-and-Power/gp-p/MontrealMEPUG

SVP, veuillez compléter ce sondage: http://aka.ms/PUGSurvey

CONCLUSION

Page 67: Sophie Marchand Meilleures pratiques daffaires Power Query et Power Pivot

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires Site web: lecfomasque.com Cell.: 514-605-7112 Courriel: [email protected]

LE CFOMASQUÉ

lecfomasque.com