SQL Server chez les clients – Enrichir le décisionnel d’entreprise avec la BI Self-Service
Les équipes métiers expriment régulièrement le besoin d’enrichir une application décisionnelle opérationnelle, avec des données externes (référentiels ou indicateurs complémentaires…) et cela de manière agile, sans recourir à une phase de développement en mode projet.
C’est aujourd’hui possible grâce aux outils de « Self-Service BI » de la suite Power BI de Microsoft, qui donnent la possibilité aux utilisateurs de construire leur propre application décisionnelle.
Problématique
- Répondre à un besoin de Reporting ad-hoc urgent.
- Accélerer les phases de développement et de test du mode projet classique.
Bénéfices
- Plus d’autonomie pour les équipes métiers.
- Une réduction des demandes de Reporting spécifique pour les équipes de développement qui peuvent se concentrer sur l’enrichissement de la plateforme décisionnelle.
- Remplacer de manières plus simples et efficaces certaines opérations et traitements effectués en VBA.
L’objectif de ce billet est d’illustrer comment exploiter la valeur des informations disponibles sur Internet, sur d’autres sources de données, afin d’étendre un système décisionnel existant.
Dans ce contexte, la suite Power BI composée des outils Power Query, Power Pivot et Power View, sera mise en œuvre afin de traiter un thème d’actualité : le découpage des régions.
Dans l'exemple qui sera présenté, le besoin opérationnel consistera à fournir à court terme des rapports qui présentent les indicateurs de l’entreprise selon une répartition qui reflète les données des régions courantes avant le redécoupage, alors que l’application actuelle fourni uniquement les données au niveau du département.
L’exemple présenté ci-dessous s’appuiera sur la base de données exemple « Adventure Works ».
Récupérer et traiter des données externes
Afin de récupérer des données provenant de sources de données externes, nous allons utiliser Power Query, le requêteur de la suite « Self-Service » BI de Microsoft.
Cet outil, qui pour certaines opérations peut être une bonne alternative aux macros, est un complément Excel permettant de récupérer facilement les données depuis différentes sources de données (multiples éditeurs de bases de données, fichiers textes, XML, JSON, listes SharePoint, Facebook, Exchange, etc…).
Le connecteur utilisé ici permet de se connecter à internet, parcourir des pages HTML et récupérer depuis le web et Wikipédia (https://fr.wikipedia.org/wiki/Liste_des_d%C3%A9partements_fran%C3%A7ais), la liste des régions et des départements.
Pour récupérer ces données, ouvrez l’onglet « Power Query » depuis Excel :
Une fois l’Url renseignée, la liste des tableaux HTML trouvés sur la page web sont affichés et un aperçu de ces derniers vous est proposé afin de faciliter votre choix :
Nous allons ensuite commencer les transformations pour ne garder que les éléments qui nous intéressent.
Pour cet exemple, nous allons simplement supprimer les colonnes qui nous sont inutiles, mais un certain nombre de transformations, souvent complexes à mettre en œuvre tels que le basculement de lignes en colonnes ou inversement sont disponibles et réalisables simplement en quelques clics.
Toutes ces transformations sont écrites dans le langage M.
La transformation par l’interface graphique Power Query peut générer beaucoup d’opérations superflues.
Le script peut être allégé en utilisant l’éditeur avancé dans lequel on peut voir et modifier l’intégralité des opérations effectuées.
Une fois les transformations terminées, vous pouvez choisir de charger vos données directement dans votre modèle analytique ou dans une feuille Excel.
L’avantage d’Excel est que vous pouvez effectuer divers traitements sur le tableau (utilisation dans des macros, modifications de valeurs…) mais le tableau n’est pas chargé dans un modèle Power Pivot.
Or puisque nous allons avoir besoin de ce dernier, nous allons donc choisir de charger le tableau dans le modèle de donnée.
Lorsque vous décidez de charger vos données dans le modèle, vos données ne sont visibles quand dans l’add-in Power Query en cliquant sur le bouton « Classeur » dans la partie « Gérer les requêtes » :
Ou dans l’onglet Power Pivot :
Récupérer les données propres à votre système décisionnel
Cette partie traite de la récupération des données présentes dans un système décisionnel (ici une base de données Multidimensionnelle SQL Server Analysis Services).
Nous allons utiliser Power Pivot car il va nous permettre de facilement construire un modèle de données faisant le lien entre les données interne et externe.
Nous allons construire notre requête en cliquant sur « Design ».
Il faut ensuite ajouter les éléments nécessaires à sa requête selon le même principe que les tableaux croisés dynamiques Excel en glissant et déposant des éléments.
Une fois la requête construite, nous allons ajouter une colonne calculée à cette requête avec une formule en DAX (langage très proche des formules Excel) : =LEFT([GeographyPostalCodePostal Code],2) .
Grâce à cette formule qui permet d’extraire le numéro du département à partir du code postal, nous avons maintenant les moyens de lier les données internes aux données que nous avons récupéré précédemment, et ce grâce à un simple « glisser déposer » entre les deux tables dans Power Pivot.
Visualiser les données
Une fois les données présentes dans notre modèle de données, il ne nous reste plus qu’à passer à la création d’un rapport l’outil Power View, qui offre une nouvelle expérience de visualisation et de présentation interactive.
Dans l’onglet, « Insérer » d’Excel, il suffit de cliquer sur le bouton « Power View » pour ajouter un rapport.
La liste des tables et des champs disponibles apparait sur la droite. Il suffit de cocher les éléments désirés pour les ajouter au rapport sous la forme d’un tableau (comportement par défaut).
Nous allons ensuite transformer ce rapport, en transposant cette représentation matricielle en une représentation cartographique grâce au composant de visualisation géographique natif dans Power View.
Dans l’onglet « Design » de Power View, changeons le mode de visualisation en « Carte » :
Afin que Power View puisse placer les points correspondants aux régions sur la carte, il faut ajouter l’entité « Région » au niveau du panneau de configuration « Emplacement ».
Grâce aux fonctionnalités de résolution de Bing Maps, les régions sont alors automatiquement localisées sur la carte.
Les données géographiques peuvent parfois être ambiguës, par exemple, la ville de Paris existe à la fois en France…ou au Texas.
C’est la raison pour laquelle, dans certains cas, Bing peut avoir des difficultés à choisir entre deux localisations ambiguës et c’est ce que nous voyons dans notre exemple avec la région Centre, ici située aux Etats-Unis.
Nous allons voir comment travailler au niveau du modèle de données pour fournir à Bing plus d’éléments lui permettant de résoudre de manière plus précise un point.
Intégrer des coordonnées géographiques dans Power BI
La première approche, très simple, consiste à créer une nouvelle colonne en concaténant le nom de la région et le nom du pays. Ainsi Bing saura que dans notre contexte, la région Centre doit être placée en France.
Mais le composant de visualisation cartographique dans Power View permet également de travailler avec les coordonnées géographiques (latitude et longitude du point).
Le composant de visualisation cartographique dans Power View permet de travailler également à partir de coordonnés géographiques (latitude et longitude du point).
Nous allons décrire de bout en bout comment mettre en place ce type d’application Power BI en s’appuyant notamment sur Power Query, afin d’effectuer des appels à un Web Service (Bing Maps) renvoyant du JSON et récupérer les coordonnées géographiques des régions.
Petit aparté, cette technique peut aussi servir à nettoyer des données géographiques telles que des adresses.
Sur l’URL suivante, la ville de Paris a été renseignée pour une adresse se trouvant à Issy-Les-Moulineaux. Le résultat montre que l’API corrige elle-même et renvoi l’adresse correcte avec la ville situé du 92 !
Vous pouvez donc utiliser l’API Bing Maps couplée avec Power Query pour faire du nettoyage de données de références pour les adresses de vos clients, fournisseurs, etc…
Nous utiliserons le même principe et interpréterons ensuite le JSON retourné par le Web Service pour ne récupérer que les coordonnées géographiques, et cette fois ci en renseignant le nom de nos régions et en précisant le pays.
Nous allons une nouvelle fois, spécifier que nous voulonsrécupérer les données depuis le web :
Il faudra ensuite, dans la fenêtre Power Query, descendre au niveau des coordonnées géographiques :
Le code pour arriver jusqu’au tableau des coordonnées doit ensuite être factorisé pour finalement n’être réduit qu’à une seule et unique transformation qui sera utilisée comme formule pour les nouvelles colonnes, latitude et longitude, de notre modèle.
Pour intégrer ces éléments au modèle de donnée que nous avons construit préalablement, il suffit de modifier la requête Power Query permettant de récupérer la liste des régions afin d’y ajouter deux nouvelles colonnes :
Et d’y insérer comme formule de calcul, l’expression factorisée précédemment soit celle-ci pour la latitude :
=Json.Document(Web.Contents("https://dev.virtualearth.net/REST/v1/Locations/"&[Région]&",France?output=json&key=%20AmVoAsOUH9QHTL4-Zc7qF7MjU8tm7zR9rDdXsA5QRsgudEwRJmz_a_NkGMmTUn3I "))[resourceSets]{0}[resources]{0}[geocodePoints]{0}[coordinates]{0}
Pour la première, représentant la latitude et la suivante pour la longitude:
=Json.Document(Web.Contents("https://dev.virtualearth.net/REST/v1/Locations/"&[Région]&",France?output=json&key=%20AmVoAsOUH9QHTL4-Zc7qF7MjU8tm7zR9rDdXsA5QRsgudEwRJmz_a_NkGMmTUn3I "))[resourceSets]{0}[resources]{0}[geocodePoints]{0}[coordinates]{1}
Les coordonnées géographiques de chaque région sont bien chargées dans la fenêtre Power Query, qui peut être fermée une fois cette opération terminée.
En exploitant ces coordonnées géographiques à travers les propriétés Latitude et Longitude du composant de cartographie, nous retrouvons les points, mais avec la région Centre située cette fois-ci en France.
Visualisez plus rapidement vos données
Pour embellir le rapport et faire en sorte que les éléments soient reconnaissables graphiquement et donc identifiables plus rapidement, les drapeaux des régions vont être utilisés comme éléments de filtres.
Pour cela, il suffit d’ajouter une colonne dans Power Pivot (comme on le ferait dans un tableau Excel) contenant l’URL d’une image correspondant à chacune des régions de la table.
Afin que cette URL soit identifiée comme étant une image il faut le spécifier dans l’onglet « Advanced » de Power Pivot et dans la partie « Reporting Properties ».
Ce champ va être ensuite identifié comme étant une image et va pouvoir être utilisé de plusieurs manière, soit pour affiché en tant que « Carte » :
Soit pour filtrer les données en fonction de la représentation de cette image, en l’occurrence, la région Ile-de-France dans l’image ci-dessous :
Conclusion
Cet article donne un aperçu de la puissance et de la facilité d'utilisation des outils de self-service BI Microsoft qui mettent à la disposition de tous, le Reporting personnalisé.
L’adoption de ces outils permet aux métiers d’être plus indépendants en évitant de solliciter les équipes de développement avec des demandes de rapports ad-hoc, tandis que celles-ci se concentreront sur la continuité du système décisionnelle déjà en place tout en continuant à recueillir les besoins des métiers, avec une pression atténuée.
Pour en savoir plus sur les outils Power BI de Microsoft :
https://office.microsoft.com/fr-fr/excel-help/guide-de-mise-en-route-de-power-bi-HA104103589.aspx
L’expertise Microsoft Consulting Services au service de ses clients
MCS France propose une offre de service packagée pour accompagner les clients sur la prise en main et l’implémentation des outils de Business Intelligence « Self-Service » dans des environnements Office 365 et/ou sur leurs propres plateformes.
Les consultants Microsoft Consulting Services interviennent régulièrement chez des clients grands comptes de différents secteurs d'activité, tels que la finance, le retail ou l'assurance, pour les accompagner dans la transformation de leurs usages décisionnels.
Cet accompagnement peut être proposé dans le cadre d’un projet géré par Microsoft Services, ou d’une assistance technique sur certains aspects du projet (architecture, validation, formation…).
Pour plus d’informations sur les offres packagées Microsoft Consulting Services, rendez-vous sur https://www.microsoft.com/france/services
Plus d’informations sur les blogs « SQL Server chez les clients ».
|
Marc Mineo, Consultant Data Insights, Microsoft Consulting Services Issu du graduate program de Microsoft (programme MACH), permettant d’intégrer des jeunes diplômés tout en leur offrant une formation riche pour une meilleur intégration dans l’entreprise, je participe à des missions en tant qu’expert sur les outils de Business Intelligence, avec un certain attrait pour la visualisation des données et la conception de bases de données multidimensionnelles. |
