Microsoft Power BI

Avec les Power BI, Microsoft dote les utilisateurs de solutions puissantes pour construire leurs reportings et analyses.

Les Powers BI sont des add’in Excel, aussi disponibles dans Office 365 (plan E3). Ils répondent avant tout à un besoin d’autonomie et de puissance pour des utilisateurs adeptes du data crunching. Pouvoir exploiter de la donnée brute en quantité en provenance de différentes sources, la retraiter, la réconcilier pour en faire un ensemble cohérent et exploitable au moyen de représentations dynamiques, tout en restant dans une solution bureautique courante était un besoin récurrent et péniblement réalisable. Au moyen des Power BI, Microsoft offre un panel de solutions qui simplifie grandement la tâche des utilisateurs et ce qui fait entrer l’éditeur dans la famille des éditeurs de BI agile d’une bien belle manière.

Power BI regroupe 4 modules complémentaires :

  • Power Query : un ETL « light » accessible pour les métiers
  • Power Pivot : une interface permettant de charger des données multi-sources ou résultant de Power Query pour bâtir des modèles analytiques, qui seront le socle à de multiples représentations dynamiques dans Excel, partageable dans SharePoint
  • Power View : une autre manière d’exploiter très simplement les modèles conçus avec Power Pivot pour bâtir des restitutions interactives sous la forme de slides
  • Power Map : la possibilité d’une représentation cartographique des données animées dans le temps, reposant sur Bing Map

Notons que Power Pivot et Power View étaient déjà disponibles dans des versions ultérieures (Power View uniquement dans SharePoint) et que Power Query fonctionne avec Excel 2010.

Avec ces quatre modules, il devient alors possible de bâtir à partir d’Excel de véritables applications de reporting analytique sans être contraint de recourir à des solutions techniques structurantes ou de tomber dans ce que l’on appelle de « l’Excelerie », souvent trop délicat à l’usage ou à maintenir sur le moyen terme. Les cas d’usages sont multiples mais peuvent se regrouper en deux catégories :

    • Permettre à des utilisateurs de bâtir de manière autonome leurs analyses en croisant différentes sources d’informations (données disponibles dans le décisionnel d’entreprise, dans les applications de gestion, dans des fichiers bureautiques des utilisateurs ou encore sur le web…) pour répondre à un besoin urgent, ponctuel ou récurrent
    • Permettre de maquetter ou de concevoir un prototype opérationnel afin de valider les concepts avant d’engager des travaux plus structurants s’inscrivant dans le respect de la gouvernance décisionnelle établie par l’entreprise, mais qui requièrent des délais et des budgets bien plus importants, tout en sachant que le prototype permettra de répondre aux besoins des métiers le temps que sa réintégration dans le décisionnel d’entreprise soit finalisée.

Démarche progressive autour des Power BI

Détaillons maintenant les fonctionnalités qui sont offertes au moyen des Power BI :

Power Query : un ETL « light » accessible pour les métiers

Le principe de Power Query est relativement simple : collecter des données depuis différents environnements, dans une forme plus ou moins structurée, pour leur appliquer un processus de transformation et ainsi les mettre en conformité par rapport à des besoins de reporting et d’analyse.

La création des différentes opérations de retraitement se fait dans un mode pas à pas qui permet de constater le résultat intermédiaire à l’issue de chaque étape, sans que cela nécessite la moindre ligne de code. Chaque opération est enregistrée en séquentiel et l’utilisateur peut revenir à tout moment sur l’une de ces opérations. Il est également possible de combiner le résultat de plusieurs chaînes d’opération sous la forme de jointures ou de bloc de données complémentaires. Une fois validée, elles sont enregistrées afin de pourvoir être rejouées pour mettre à jour les données au fil du temps.

Les source de données :

  • Le Web
  • Les fichiers (Excel, csv, xml, txt)
  • Les bases de données tradistionnelles (SQL Server & Azure, Microsoft Access, Oracle, BD2, MySQL, Sybase, PostgreSQL, Teradata)
  • Diverses autres sources dont Hadoop et Windows Azure HD Insight, les listes SharePoint, Exchange, Active Directory…

Les données importées apparaissent alors sous la forme d’une table au sein d’une interface proposant les différentes opérations de retraitement

Les opérations de retraitement :

Microsoft Power Query offre un large panel d’opérations de retraitement qui peuvent se classer en deux sous-familles :

  1. Les opérations de structure qui permettent de définir la forme du jeu de données manipulé :
    • Découpage des colonnes/champs
    • Nommage des colonnes/champs
    • Omission de lignes ou des colonnes/champs
    • Limitation du nombre d’enregistrements
    • Définition du typage des colonnes/champs
  2. Les opérations de transformation sur le jeu d’enregistrement :
    • Génération de clés uniques
    • Dédoublonnage
    • Filtrage et tri
    • Suppression de lignes en erreur
    • Découpage de valeurs
    • Création de colonnes dérivées via des fonctions à l’instar d’Excel
    • Remplacement de valeurs
    • Regroupements / agrégations
    • La possibilité de joindre le jeu d’enregistrements à un second

Toutes ces opérations sont mémorisées dans une chaine séquentielle de traitements facilement accessible (en mode avancé), en conservant la possibilité de visualiser l’état intermédiaire du jeu d’enregistrement à chaque étape de transformation.
Le mode avancé permet d’éditer le script généré, et même de le modifier. Lorsque l’on consulte la documentation relative aux formules exploitables dans le cadre de Power Query, on s’aperçoit que les possibilités de la solution vont bien au-delà des simples, mais déjà fortement appréciables, opérations disponibles dans les menus et icônes de l’interface. Il est par exemple possible de créer une requête qui va générer dynamiquement un calendrier sans aucune source de données, via le recours à des variables et à des fonctions récursives. Cela ouvre des perspectives intéressantes.

Power Query Panneau de retraitement des données

Le résultat est stocké sous la forme d’une requête exploitable dans le classeur Excel ou directement chargeable dans un modèle Power Pivot. Le catalogue de requêtes reste disponible au sein du classeur Excel, facilement consultable sous la forme de table en prévisualisation.

Power Query Catalogue des transformations

Enfin et pour clore ce brief sur Power Query, il nous semble important de présenter quelques retours d’expériences. Nous avons, dans le cadre d’un POC concluant, chargé dans Power Query un fichier de plus de 1 100 000 de lignes et de 140 colonne sans le moindre problème sur un PC portable core i7 de 8 Go de RAM et un disque SSD. Dans ce cadre l’un des besoins était d’avoir une vision historisée des positions de stock sur une année glissante, soit 12 périodes. Il a donc fallut croiser via une jointure full (produit cartésien) d’une résultat d’environ 13 000 000 de lignes avant de les filtrer sur les dates, pour au final générer une table d’historique d’environ 3 400 000 lignes directement montée dans le modèle Power Pivot (Excel ne permettant pas l’affichage au-delà de certaines limite). L’intégralité de ce traitement est exécutée en moins de dix minutes avec un taux d’erreurs liées aux données de 0.1%.

Power Pivot : Création de modèles de données analytiques

Power Pivot est le module central des Power BI dans la mesure où cet add’in permet de concevoir les modèles de données en support aux éléments de restitution et d’analyse dynamiques.

Power Pivot - Architecture applicative

La modélisation :

Power Pivot permet aux utilisateurs de charger différentes sources de données, pour charger l’ensemble des tables nécessaires à la constitution du périmètre d’analyse. Chacune des tables est consultable dans une forme tabulaire, comme dans Excel, ou sous la forme d’une représentation graphique. Les connecteurs couvrent un large panel de sources de données allant des grands SGBDR relationnels aux cubes SSAS, des fichiers plats au flux oData en passant par hadoop, sans oublier la possibilité d’exploiter les résultats de traitements Power Query.

Représentation graphique ou tabulaire du model Power Pivot

Les utilisateurs peuvent par la suite, sur chacune des tables, procéder à un certain nombre d’opérations :

  • Renommage de colonne pour utiliser des termes métiers
  • Donner une description de la colonne
  • Définir les jointures en les tables
  • Masquer les colonnes n’ayant pas de valeur fonctionnelle
  • Définir le typage des colonnes
  • Préciser les modalités de tri, pouvant entre autre faire référence aux valeurs d’une autre colonne
  • Créer des colonnes calculées en utilisant le langage DAX, pouvant faire référence à des colonnes disponibles dans une autre table
  • Concevoir des hiérarchies
  • Définir des perspectives
  • Créer des mesures agrégées et des KPI

Il est important de préciser que le langage utilisé avec Power Pivot pour écrire les formules est le langage DAX qui diffère des formules Excel, même s’il s’en approche. Il permet de faire un certain nombre de calculs dynamiques très puissant avec des relations entre les tables, des filtres, des comparatifs de période. Notons également que la bibliothèque des formules traitant des date-time (Time intelligence) est largement dotée avec des fonctions permettant de calculer les périodes parallèles ou les cumuls à date à l’instar de ce qu’il est possible de faire dans un cube OLAP avec du MDX.

Toutes ces spécifications seront directement exploitables dans le modèle de données visibles par les utilisateurs et leur permettront un usage rapide et simplifiés lors de la création de tableaux et graphiques croisés via Power Pivot, Power View ou encore Power Map, ne se concentrant plus que sur la représentation des données et sur l’esthétisme qu’ils souhaitent appliquer au moyen du large panel de fonctionnalités d’Excel.
Nous précisons que nous considérons être la meilleures pratique celle qui consiste à créer indicateurs, KPI, hiérarchies et à gérer la sémantique exclusivement au sein du modèle afin de rationaliser et centraliser ces opérations pour en faciliter le déploiement et la maintenance mais également pour assurer la mise ne place d’une certaine gouvernance permettant de garantir que tous les utilisateurs parlent bien de la même chose.

Power Pivot - TCD Panel

La création du reporting analytique :

Il existe plusieurs possibilité pour exploiter le modèle de données Power Pivot. Le choix en dépend à la fois de l’usage et des contraintes de représentation. Ces possibilités sont :

  • S’appuyer sur les tableaux et graphiques croisés d’Excel
  • Créer les tableaux croisés dans des feuilles qui seront masquées mais auxquels il sera fait référence via formules Excel au sein de tableaux déstructurés
  • Recourir à Power View, autre module Power BI, pour une analyse dynamique plus interactive
  • Exploiter le dernier né des modèle Power BI, Power Map, pour une représentation géo-temporelle des données lorsqu’elles s’y prêtent

Les tableaux et graphiques croisés dynamiques restent le moyen le plus rapide de représenter les données avec les différentes possibilités de cosmétiques qu’offre Excel et surtout les possibilités d’analyse exploratoire natives qui exploitent pleinement le modèle de données sous-jacent de Power Pivot. La seconde force de ces tableaux et graphiques croisé est de restituer automatiquement les évolutions des données sans retoucher aux restitutions : si une dimension intègre de nouveaux membres au fil des mises à jour des données, ils apparaitront automatiquement. La limite de l’exercice reste les contraintes de représentation liées aux tableaux croisés.

Lorsque l’on souhaite sortir des contraintes de représentation liées aux tableaux croisés dynamques, comme c’est le cas avec des tableaux déstructurés, il est toujours possible d’exploiter le contenu de ces tableaux via la formule LIREDONNEESTABCROISDYNAMIQUE() pour peupler des cellules. Le contenu de ces cellules restera dynamique via l’usage de slicers interconnectés aux tableaux croisés. Néanmoins cette solution fait perdre les capacités d’analyse exploratoire et présente l’inconvénient de ne pas représenter automatiquement les nouveaux membres des dimensions au fil des mises à jour des données. De ce fait il est important de proposer des tableaux avec un niveau de détail adéquat si l’on ne veut pas être obliger de retoucher son reporting à chaque mise à jour.

Dans ces deux cas d’usage, il est important de préciser le rôle des slicers. Les slicers qui agissent comme des filtres pointent chacun sur une notion, un champ du modèle de données Power Pivot et en présente les valeurs. Mais contrairement à la notion de filtre propre aux tableaux et graphiques croisés dynamiques, les slicers peuvent interagir avec plusieurs de ces tableaux et graphiques, qu’ils soient ou non sur la même feuille Excel, à condition qu’ils y soient liés.