OGDI DataLab et l’extension Data Explorer pour Microsoft Excel 2013

Disponible depuis peu, Microsoft « Data Explorer » Preview pour Excel est un nouvel outil qui propose une expérience transparente pour la découverte de données, la transformation de données et un enrichissement pour les personnes en charge de gérer de l'information, les professionnels de la BI (Business Intelligence), etc. Cet aperçu donne une première idée des fonctionnalités à venir en permettant aux utilisateurs de facilement découvrir, de combiner et d'affiner les données pour une meilleure analyse dans Excel. (Comme avec la plupart des pré-versions, ces caractéristiques sont susceptibles d’apparaître différemment dans la version finale).

Data Explorer se présente sous la forme d’une extension (add-in) pour Microsoft Excel 2013 qui permet :

• D’identifier les données qui vous intéresse depuis vos sources préférées (flux OData, bases de données relationnelles, MS Excel, fichiers textes et XML, pages web, Hadoop HDFS, etc.).
• De découvrir des données pertinentes en utilisant la capacité de recherche intégrée à Excel.
• De combiner des données provenant de multiples sources (mashup) et les façonner afin de les préparer pour une analyse plus approfondie avec des outils comme Excel et PowerPivot pour la création de graphiques croisés dynamiques dont nous avons déjà eu l’occasion de parler dans ce blog.

Pour de plus amples informations sur Data Explorer, nous vous invitons à consulter le blog MSDN éponyme dédié : https://blogs.msdn.com/dataexplorer/.

Vous pouvez télécharger l’extension Data Explorer depuis le Centre de téléchargement Microsoft ici. Une fois installé, vous accédez aux fonctionnalités de Data Explorer au sein de Microsoft Excel 2013 depuis l’onglet DATA EXPLORER.

Les « présentations » étant ainsi faites, ce billet se propose de vous présenter un exemple d’utilisation de l’extension Data Explorer en combinant des données provenant d’un flux OData (via la plateforme de publication de données ouvertes OGDI DataLab) et des données complémentaires provenant directement d’Internet. Le thème de cet exemple sera « les communes françaises ». Nous allons donc rassembler et associer des informations sur les communes de France pour enfin produire des graphiques permettant l’analyse de ces données.

Etape 1 : Récupération des données sources depuis OGDI DataLab

Dans l’instance de test OGDI DataLab mise à disposition par Microsoft France, nous avons chargé les informations de toutes les communes françaises, il y en a plus de 36000 !

Une fois dans l’onglet DATA EXPLORER, cliquez sur From Other Sources puis sur From OData Feed.

Dans la fenêtre OData Feed qui s’ouvre, précisez l’URL de votre flux OData : https://ogdifrance.cloudapp.net:8080/v1/frOpenData/Communes.

Cliquez sur Apply. Une fenêtre s’ouvre et affiche une prévisualisation du résultat. C’est ici que vous allez pouvoir configurer le résultat souhaité en appliquant toute sorte de modification tel qu’insérer, masquer ou renommer des colonnes, filtrer ou grouper les résultats, appliquer des calculs, etc.

Vous allez masquer les colonnes qui ne contiennent pas d’informations utiles pour Cette illustration. Pour sélectionner plusieurs colonnes, commencez par sélectionner une colonne en cliquant sur son en-tête. Pour ajouter d’autres colonnes à la sélection, maintenez la touche CTRL puis cliquez sur les en-têtes des colonnes à ajouter.

Une fois les colonnes sélectionnées, masquez-les en faisant Clic droit > Hide Columns. Il se peut que l’opération soit longue car Excel va recharger tous les éléments.

Une fois que votre requête est prête, il vous reste plus qu’à cliquer sur le bouton Done pour charger les résultats dans Excel.

La fenêtre de prévisualisation se ferme et les données commencent à se charger dans Excel. Cette opération dure plusieurs minutes dans la mesure où il y a près de 70Mo de données à télécharger. Tout dépend du débit de votre connexion Internet. Vous pouvez suivre son avancement dans le panneau de droite. Il est important d’attendre la fin du téléchargement des données pour passer à la suite.

A l’issue de l’opération, vous devez avoir la liste de toutes les communes de France dans votre tableau Excel soit plus de 36000 lignes !

Etape 2 : Enrichissement des données avec une source différente

Dans l’état actuel des données rapatriées, vous disposez de la liste des communes française avec les codes INSEE associées mais vous n’avez aucune information sur le nom des départements.

Toujours dans l’onglet DATA EXPLORER, cliquez sur Online Search.

Un panneau s’ouvre à droite avec un champ de recherche. Nous recherchons « départements France » et parcourons les résultats pour choisir celui qui correspond le mieux à notre besoin. En passant sur les résultats, vous prévisualisez le contenu directement dans Excel.

Choisissez les données en provenance de Wikipédia qui contiennent à la fois le code INSEE - ce qui va vous permettre de réaliser la jointure avec vos données actuelles - et le nom du département, de la préfecture et de la région – ceci constitue la valeur ajoutée de nos données). Cliquez pour utiliser ces données.

Une nouvelle feuille de calcul s’ouvre et les données sont chargées.

Il convient maintenant de lier ces nouvelles données avec vos données initiales. Avant cela, un petit détail se doit d’être régler au niveau du format du code INSEE. Dans le résultat de votre requête OData, les départements de 1 à 9 ne contiennent pas de 0 alors que dans le résultat de la requête Web, le 0 est précisé. Il s’avère donc nécessaire de modifier la requête. Pour cela, fermez le panneau Online Search puis cliquez sur Filter & Shape dans le panneau de droite.

Il va vous falloir remplacer le code INSEE des neufs premiers départements à la main. Faites un clic droit sur la valeur puis cliquez sur Replace Values. Remplissez ensuite le champ Replace With avec la valeur du code INSEE sans le 0 puis enfin cliquez sur Apply.

Une fois les modifications ainsi effectuées, votre tableau doit ressembler à ceci :

Ces données doivent à présent être liées avec vos données initiales. Pour ce faire, cliquez sur le bouton Merge de l’onglet QUERY.

Une fenêtre Merge s’ouvre. C’est ici que vous allez spécifier la colonne de jointure, c’est-à-dire la colonne commune aux deux requêtes permettant d’associer chaque ligne. Sélectionnez votre requête OData comme primary table et votre requête Web sur les départements ensuite. La jointure va se réaliser sur le code INSEE du département ; il convient donc sélectionner la colonne dep de la requête OData et la colonne INSEE_code de la requête Web.

Une fois les deux colonnes choisies, Excel va évaluer votre sélection pour déterminer combien de lignes vont pouvoir être associées. Le but est d’avoir 100% de vos lignes associés.

L’évaluation prend un certain temps compte tenu du volume de données. Vous pouvez cliquer sur OK sans attendre la fin de l’évaluation.

Une fenêtre de prévisualisation s’ouvre. C’est ici que vous allez paramétrer votre requête finale. On retrouve bien tous les champs de votre requête OData ainsi qu’une nouvelle colonne NewColumn à droite. Cette colonne contient les données de la requête web sous forme de tableau.

Cliquez sur l’icône puis cochez Department, Prefecture et Region dans la fenêtre pop-up qui s’ouvre. Enfin cliquez sur OK.

Vous pouvez maintenant constater que pour chaque commune sont associés son département, sa préfecture et sa région. Vous pouvez changer l’ordre des colonnes (en glisser/déplacer) pour une meilleure organisation. Une fois votre requête prête, il ne vous reste plus qu’à cliquer sur Done.

Excel va recharger toutes les lignes. Un fois terminé, votre tableau contient la liste de toutes les communes françaises (récupéré sur l’instance de test de la plateforme de publication OGDI DataLab) enrichi avec des informations venant d’une page Web. C’est toute la puissance de Data Explorer !

Rien n’empêche de continuer à enrichir cet ensemble de données avec d’autres données venant de sources différentes comme par exemple un fichier (texte, XML, CSV, Excel, etc.), une base de données (SQL Server, SQL Azure, MySQL, Access, Oracle etc.), Facebook, Hadoop HDFS, Active Directory et beaucoup d’autres encore ! Vos besoins, votre imagination et l’existant constituent la limite.

Etape 3 : Analyse des données avec Power View

A présent que vos données sont fin prêtes, vous allez vous intéressez à leur analyse au travers de la capacité Power View de Microsoft Excel 2013. (Vous pouvez retrouver en détail les fonctionnalités de Microsoft Excel 2013 sur le billet de Jean-Pierre Riehl.)

Pour cela, cliquez sur Power View dans l’onglet INSERT.

Une fois sur la page Power View, commencez par supprimer le tableau présent par défaut en le sélectionnant puis en cliquant sur la touche Suppr de votre clavier. Pour sélectionner le tableau, cliquez sur la bordure de celui-ci.

Exemple 1 : Population des communes par département

Vous vous retrouvez avec une page vierge et un panneau à droite. Dans ce panneau de droite, cochez la case Department. Cliquez maintenantsur le bouton Tiles de l’onglet DESIGN.

Un nouvel élément s’affiche dans votre Power View, ajustez-le afin qu’il occupe toute la place disponible en laissant de la place pour un titre en haut. Votre titre sera « Population des communes par département ». Votre Power View doit maintenant ressembler à la capture suivante :

Sélectionnez le petit tableau du milieu puis, dans le panneau de droite, cochez nom et popu. Supprimez Department du champ FIELDS de sorte à avoir ceci :

Cliquez sur le bouton Bar Chart puis Stacked Bar de l’onglet DESIGN.

Ajustez votre tableau afin qu’il occupe tout l’espace disponible. Vous avez la possibilité de changer le paramètre de tri (nom ou popu) ainsi que l’ordre (croissant ou décroissant). Choisissez par exemple de trier par population décroissante. Vous disposez maintenant des communes, filtrées par département et triées par ordre de population décroissante. Vous pouvez ainsi parcourir les différents départements et découvrir qu’elles sont les communes les plus peuplées. A noter que l’unité de la population est en centaines d’habitants.

Exemple 2 : Carte des départements de France

Dans ce deuxième exemple, vous allez utiliser la fonction Map de Power View pour afficher les départements français sur une carte.

Retournez sur votre feuille de calcul contenant les données sources puis cliquez à nouveau sur Power View de l’onglet INSERT.

Si Excel vous le demande, choisissez de créer une nouvelle feuille Power View.

Une fois sur votre Power View, commencez par supprimer le tableau présent en le sélectionnant puis en cliquant sur la touche Suppr de votre clavier. Pour sélectionner le tableau, cliquez sur la bordure de celui-ci.

Cochez Department dans le panneau de droite puis cliquez sur Map dans l’onglet DESIGN.

Dans le panneau de droite, il faut mettre Department dans le champ LOCATIONS et le supprimer du champ COLOR. Vous remarquez que Power View géolocalise automatiquement les départements sur la carte de France.

Ajustez la carte à la taille de la page puis ajoutez un titre, par exemple « Carte des départements de France ». Vous devriez avoir ceci :

Vous allez encore améliorer cette carte. Pour ce faire, glissez le champ popu dans la case SIZE du panneau de droite. Ce champ doit être en mode Sum (en cliquant sur la petite flèche à droite), c’est-à-dire qu’il va calculer la somme de la population de chaque commune d’un département donné et ensuite ajuster la taille du point sur la carte en fonction du résultat.

On peut maintenant facilement déterminer quels sont les départements français les plus peuplés.

Encore une petite amélioration, glissez le champ Region dans la case COLOR. Les points se colorent selon leur région et une légende apparait à droite de la carte.

Profitez-en pour découvrir les multiples possibilités qu’offre Power View. Par exemple la fonction de filtrage, accessible en cliquant sur la petite icone en haut à droite de la carte, permet d’affiner vos analyses en vous concentrant sur certains départements ou sur une fourchette de population spécifique.

Power View permet la visualisation de données sous plusieurs formes telles que des cartes, des graphiques, des camemberts, des histogrammes etc. C’est un outil puissant qui vous sera utile pour toute analyse de données.

Ceci conclut ce billet à la découverte de Data Explorer. Nous vous joignons le fichier Excel correspondant si vous souhaitez directement visualiser le résultat.

Data_Explorer.xlsx