Meilleures pratiques d'affaires avec 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 AVECPOWER PIVOT


LE CFOMASQUÉ

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

Vaste experience en finance corporative Fondatrice du CFO masqué Modélisation d’affaires avancée Excel / Power tools / Power BI Consultation, formation et coaching Vente en ligne Responsable du groupe Montreal Modern Excel and Power BI

QUI SUIS-JE?

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


LE CFOMASQUÉ

PLAN DE PRÉSENTATION Meilleures pratiques d’affaires avec Power Pivot

Prérequis techniques Quelques concepts importants Compression des données Réduction du nombre de lignes Réduction du nombre de colonnes Augmentation du nombre de colonnes Optimisation du poids d’une colonne Importance des tables Optimisation du langage DAX Optimisation des requêtes sur le modèle de données Utilisation de Power Pivot 2010 Outils disponibles pour optimiser les modèles de données

À télécharger: Présentation et vidéo du webinaire Power Query, Power Pivot et cie: http://www.lecfomasque.com/a-telecharger-presentation-et-video-du-webinaire-power-query-power-pivot-et-cie/

http://www.lecfomasque.com/a-telecharger-presentation-et-video-du-webinaire-power-query-power-pivot-et-cie/




LE CFOMASQUÉ

INTRODUCTION


LE CFOMASQUÉ

INTRODUCTION

Que permet de faire Power Pivot? Loger des tables de données de plus de 1 million de lignes Croiser différentes tables pour créer un modèle de données Bonifier le modèle de données avec des colonnes calculées et des

mesures Créer des KPI Créer des hiérarchies Créer des synonymes


LE CFOMASQUÉ

INTRODUCTION

Comment se procurer Power Pivot? Excel 2016

Intégré Versions O365, Office Professional Plus et Stand Alone ont plus d’options

Excel 2010 & Excel 2013 Add-in à télécharger sur le site de Microsoft https://www.microsoft.com/en-us/download/details.aspx?id=43348

Power BI Desktop Application dissociée d’Excel mais semblable à Power Pivot

https://www.microsoft.com/en-us/download/details.aspx?id=43348


LE CFOMASQUÉ

PRÉREQUIS TECHNIQUES


LE CFOMASQUÉ


Quincaillerie requise Pour tirer le maximum de Power Pivot

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


LE CFOMASQUÉ


Comprendre les limites et les spécifications 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

Obligé d’utiliser 32-bit? Messages du type: Excel running out of memory? Depuis juin 2016, pour Excel 2013 et 2016: Large

Address Aware Functionality (LAA) Permet d’utiliser le double de mémoire virtuelle sur un O/S 64 bit, et 50%

de plus sur un O/S 32 bits

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!

https://support.microsoft.com/en-ca/kb/3160741




LE CFOMASQUÉ

QUELQUES CONCEPTS IMPORTANTS


LE CFOMASQUÉ

QUELQUES CONCEPTS IMPORTANTS Philosophie de base

BI en mode libre-service Ne pas créer un modèle de données unique pour toutes les analyses de

l’entreprise. 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 (à moins qu’on s’en serve comme outil de prototypage).

Créer le modèle de données le plus optimal pour chaque analyse.

Si le modèle doit être partagé, il faut songer à Power BI ou SSAS.

Power Pivot n’est pas une solution de bases de données. Il fonctionne en mode RESFRESH ALL. Mais on peut penser que les options Enable Load et Include in Report Refresh vont être ajoutées sous peu.


LE CFOMASQUÉ


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


LE CFOMASQUÉ


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


LE CFOMASQUÉ

COMPRESSION DES DONNÉES


LE CFOMASQUÉ


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


LE CFOMASQUÉ


Meilleur format pour la compression des données 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

Pour en savoir davantage sur la compression de données How does Power Pivot store and compress data? Script Update: What is Eating Up My Memory in Power Pivot?

https://www.google.ca/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&cad=rja&uact=8&ved=0ahUKEwiBzfyC8rHOAhUk44MKHUQGB2YQFggkMAE&url=http://tinylizard.com/how-does-power-pivot-store-and-compress-data/&usg=AFQjCNHYMQB4LqgufDlcewe7qjobOOgzDg&sig2=k6sInuK3GjotZLxl_u1Qxw

https://www.google.ca/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwiQiKCN8rHOAhVI74MKHcgZDEQQFggbMAA&url=http://tinylizard.com/script-update-what-is-eating-up-my-memory-in-power-pivot/&usg=AFQjCNH4xyDx_mycV76hJYjcEbId14r67Q&sig2=ItthTls042jbfDMcEizp6Q&bvm=bv.129391328,d.amc


LE CFOMASQUÉ

OPTIMISATION DU MODÈLE DE DONNÉES :

RÉDUCTION DU NOMBRE DE LIGNES


LE CFOMASQUÉ


Exemples de lignes à supprimer 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 PreviousYear, TotalYeartoDate, etc. et/ou que vous ajoutez ensuite des segments (slicers) sur ces calculs.


LE CFOMASQUÉ


Comment réduire le nombre de lignes


LE CFOMASQUÉ


RÉDUCTION DU NOMBRE DE COLONES


LE CFOMASQUÉ

RÉDUCTION DU NOMBRE DE COLONNES

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.


LE CFOMASQUÉ


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.


LE CFOMASQUÉ


Exemples de colonnes pouvant être supprimées (suite) 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.


LE CFOMASQUÉ


Exemple tiré de Power Pivot Pro 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.


LE CFOMASQUÉ


Comment supprimer une colonne à l’importation Approche différente selon source de données


LE CFOMASQUÉ


AUGMENTATION DU NOMBRE DE COLONES


LE CFOMASQUÉ

AUGMENTATION DU NOMBRE DE COLONNES

But: Améliorer la compression d’une colonne complexe


LE CFOMASQUÉ

AUGMENTATION DU NOMBRE DE COLONNES

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


LE CFOMASQUÉ


OPTIMISATION DU POIDS D’UNE COLONNE


LE CFOMASQUÉ


Exemples de réduction de granularité de colonnes 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)


LE CFOMASQUÉ


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.


LE CFOMASQUÉ


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)

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

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


LE CFOMASQUÉ

OPTIMISATION DU MODÈLE DE DONNÉES:

IMPORTANCE DES TABLES


LE CFOMASQUÉ


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


LE CFOMASQUÉ


Création d’une table de dates Création avec Excel Add-in


LE CFOMASQUÉ


Création d’une table de dates Création avec Power Pivot pour Excel 2016

Note: On peut aussi créer une table de dates dans Excel avec des formules, dans Power Query avec le code M ou dans Power Pivot en DAX.


LE CFOMASQUÉ


Créer des hiérarchies Permet ensuite de créer un tableau

croisé dynamique et/ou un graphique dans Excel et de forer dedans

On peut créer une hiérarchie de dates, tel qu’illustré ci-contre mais on peut aussi créer d’autres types de hiérarchies, par exemple:• Région• Magasin• Département


LE CFOMASQUÉ

OPTIMISATION DU MODÈLE DE DONNÉES:

OPTIMISATION DU LANGAGE DAX


LE CFOMASQUÉ


Convention du langage DAX : DAX FORMATTER


LE CFOMASQUÉ


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


LE CFOMASQUÉ

OPTIMISATION DES REQUÊTES SUR LE

MODÈLE DE DONNÉES


LE CFOMASQUÉ

OPTIMISATION DES REQUÊTES

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).


LE CFOMASQUÉ

OPTIMISATION DES REQUÊTES

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 (option = Visually

indicates items with no data) 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)


LE CFOMASQUÉ

MEILLEURES PRATIQUES D’AFFAIRES

AVEC POWER PIVOT 2010


LE CFOMASQUÉ

MEILLEURES PRATIQUES AVEC PP2010

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 /

Retour à Power Pivot : Design / Existing Connection / la requête en question / Edit / CTRL+V / Close / Refresh /

À nouveau : Design / Table Properties / Save


LE CFOMASQUÉ

MEILLEURES PRATIQUES AVEC PP2010

Quelques concepts importants Zone de calculs


LE CFOMASQUÉ

OPTIMISATION DU MODÈLE DE DONNÉES: OUTILS DISPONIBLES


LE CFOMASQUÉ

OUTILS DISPONIBLES

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.


LE CFOMASQUÉ

QUESTIONS?


LE CFOMASQUÉ

CONCLUSION

Apprendre Power Pivot Formation Excel – Power Tools

En ligne En classe En entreprise

Module – Power Pivot (en ligne) Rejoindre le groupe Montreal Modern Excel and Power BI Prochain webinaire: Pourquoi le CFO devraient s’intéresser à Power BI?

http://www.lecfomasque.com/produit/excel-power-tools-4-modules/

http://www.lecfomasque.com/activites-a-venir/excel-analyse-et-modelisation-de-donnees-niveau-2-powerpivot/

http://www.lecfomasque.com/formations/excel-analyse-et-modelisation-de-donnees-niveau-2/

http://www.lecfomasque.com/produit/excel-power-pivot/

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


LE CFOMASQUÉ

lecfomasque.com

Data & Analytics

Meilleures pratiques d'affaires avec Power Pivot