Tutoriel : une solution de BI (Business Intelligence) avec ODAF et PowerPivot en 20 minutes chrono !
Comme nous l’avons vu dans un précédent billet, le kit de démarrage ODAF (Open Data Application Framework) est un Framework permettant aux Administrations et Collectivités Territoriales d’exposer leurs données ouvertes géo-localisées au format (Keyhole Markup Language) sur une carte Bing Cartes. Sur cette fondation, la solution ODAF donne la possibilité aux citoyens d’émettre des avis sur les données ainsi exposées, sous la forme de commentaires ou de notations qui sont ensuite stockés dans la base de données d’ODAF comme SQL Azure de la plateforme de services Windows Azure dans le Cloud.
Le fait que ces contributions de la part des citoyens soient stockées dans une base de données consultable constitue un atout important de la solution et fait partie de l’essence même du mouvement Open Data : cela permet aux citoyens de se rapprocher des Administrations et Collectivités Territoriales, et de soumettre leurs avis et contribution(s). Ensuite, charge aux Administrations et Collectivités Territoriales de prendre en compte ces considérations et de réagir au mieux aux réactions des citoyens dans le but d’améliorer la qualité des services délivrés.
Pour ce troisième tutoriel, nous allons donc prendre en compte le scénario d’une administration locale qui a récemment investi dans le secteur de l’éducation en ouvrant une nouvelle école maternelle et une nouvelle école élémentaire à la demande de ses administrés. Elle voudrait par conséquent recueillir les réactions de ses administrés vis-à-vis de ses nouvelles infrastructures pour pouvoir réagir en conséquence et donc améliorer la qualité de vie des citoyens dans la ville considérée. Cette administration locale a fait le choix :
- Du kit de démarrage OGDI (Open Government Data Initiative) pour stocker et exposer ses données publiques et ainsi donner la possibilité d’accéder à ses données via le protocole KML (et OData) ;
- D’une solution fondée sur le kit de démarrage ODAF (Open Data Application Framework) pour se connecter aux flux de données KML exposés par l’instance OGDI et donc permettre à ses administrés d’apporter leurs avis et contributions qui seront stockés dans une base de données SQL Azure pour la solution ODAF.
Pour faire de l’analyse de données sur une base de données SQL Azure, plusieurs possibilités s’offrent à l’administration locale :
- SQL Server 2008 R2 Reporting Services qui fournit une gamme d’outils complète pour créer des rapports personnalisés.
- SQL Azure Reporting pour l’instant en version limitée CTP, un service de reporting en mode Cloud Computing. Une telle approche apporte de nombreux bénéfices : approvisionnement rapide, haute scalabilité, efforts réduits en termes de gestion, accès sécurisé, etc.
- L’extension PowerPivot pour Microsoft Excel 2010 dont nous avons déjà parlé précédemment dans ce blog, qui fournit des outils permettant d’analyser toutes sortes de sources de données hétérogènes, d’établir des relations entre ces sources de données, de réaliser des « mash-ups » de données dans la finalité de générer des rapports sous forme de tableaux ou graphiques dynamiques.
Pour ce tutoriel, la troisième possibilité, à savoir l’extension PowerPivot pour Microsoft Excel 2010, est celle que nous utilisons pour nous connecter à la base de données de la solution ODAF hébergée dans SQL Azure. Nous allons extraire les données concernant les commentaires et générer ensuite le rapport correspondant.
Il convient d’abord d’analyser le schéma de base de données ODAF afin de déterminer quelles données extraire.
Les tables qui nous intéressent sont PointDataSummary et PointDataComment. Dans une moindre mesure, il convient également de regarder dans la table PointDataLayer le champ Guid qui correspond à la source de données KML exposant la liste des établissements scolaires.
Ainsi, lorsque nous intégrerons les données dans PowerPivot, nous allons filtrer les données de la table PointDataSummary (qui contient tous les points de données sur lesquels figurent des commentaires, des notations ou des tags) pour n’obtenir que les points de données qui sont liés aux établissements scolaires (on filtrera sur le champ LayerId).
Tout d’abord, pour accéder à la base de données SQL Azure de l’administration locale, les agents concernés doivent disposer d’un accès explicitement ouvert configuré dans le pare-feu SQL Azure. Pour configurer le pare-feu SQL Azure, connectez-vous sur le portail SQL Azure et configurez votre pare-feu en autorisant explicitement la plage d’adresses adéquate.
Parmi les bonnes pratiques en termes de sécurité pour SQL Azure, il convient d’avoir un login par agent. Pour PowerPivot, les agents concernés n’ont besoin que de l’accès en lecture car ils ne feront que du reporting, pas d’insertions, de modifications ou de suppressions de données. Le billet Adding Users to Your SQL Azure Database sur le blog de l’équipe en charge du développement de SQL Azure offre un pas-à-pas détaillé à travers le processus de création d’utilisateurs en lecture seule pour une base de données SQL Azure. Vous pouvez vous y reporter si besoin.
Nous allons maintenant nous intéresser à PowerPivot en lui-même ! Tout d’abord, il s’agit d’une extension gratuite à Microsoft Excel 2010 téléchargeable sur le Centre de téléchargement Microsoft ici.
Une fois l’installation de PowerPivot terminée, ouvrez Microsoft Excel 2010. A son lancement, vous remarquez qu’un nouvel onglet, intitulé en toute simplicité PowerPivot, est venu s’ajouter au Ruban de Microsoft Excel. Sélectionnez-le et cliquez sur le bouton Fenêtre PowerPivot pour créer la connexion à vos sources de données.
Une fois que la fenêtre PowerPivot ouverte, cliquez sur le bouton Obtenir des données externes à partir d’autres sources. (Il s’agit du petit bouton sur fond jaune sur la capture d’écran suivante).
Sélectionnez Microsoft SQL Azure dans la liste déroulante proposée. Cliquez sur Suivant.
Sur la fenêtre suivante, vous devez préciser le nom de votre serveur, le login et le mot de passe de l’utilisateur et sélectionner enfin la base de données à laquelle vous souhaitez avoir accès.
Cliquez sur Suivant. Sélectionnez ensuite l’option Sélectionner les données à importer dans une liste de tables et de vues.
Cliquez sur Suivant. Les choses sérieuses peuvent maintenant commencer ! Vous disposez de la liste des tables de la base de données ODAF qui s’affiche. Cochez PointDataComment et PointDataSummary.
Sélectionnez ensuite PointDataSummaryet cliquez sur le bouton Afficher un aperçu et filtrer.
Tout d’abord, filtrez la colonne LayerId pour ne disposer que les données des établissements scolaires (rappelez-vous que la valeur qui vous intéresse se trouve dans la table PointDataLayer). Ensuite, filtrez la colonne CommentCount pour n’obtenir que les enregistrements dont le nombre de commentaires est supérieur à 0. Nous vous conseillons aussi de filtrer au maximum les colonnes (en fait, seuls les champs Id, Name et CommentCount nous intéressent dans le cas présent).
Pour la table PointDataComment, seuls les champs Textet SummaryIdnous intéressent.
Cliquez ensuite sur le bouton Terminer pour commencer l’importation de données. Une fois les données importées, cliquez sur Fermer.
Vous pouvez voir dans la fenêtre PowerPivot que 2 onglets ont été créés, contenant les données des deux tables que nous venons d’importer. Il faut maintenant reproduire la relation « clé primaire – clé étrangère » entre le champ Id de la table PointDataSummary et le champ SummaryId de la table PointDataComment.
Pour reproduire cette relation, sélectionnez l’onglet Conception puis cliquez sur le bouton Créer une relation.
Choisissez ensuite tout simplement vos tables et vos colonnes et validez la relation en cliquant sur le bouton Créer.
Maintenant retournez à l’onglet accueil et cliquez sur le bouton Tableau croisé dynamique.
Vous allez être redirigé vers Microsoft Excel où vous allez créer votre tableau croisé dynamique. Choisissez l’option Feuille de calcul existante dans le pop-up qui va s’ouvrir. Vous allez maintenant créer de toute pièce votre tableau croisé dynamique.
Procédez de la façon suivante. Faites glisser le champ Namedans la zone Découpage verticaux. Vous obtiendrez ainsi une source de données de laquelle vous pourrez filtrer les commentaires (grâce à la relation créée à l’étape précédente).
Faites glisser le champ Text dans la zone Etiquettes de ligne, faites ensuite glisser le champ SummaryId dans la zone Valeurs puis modifier l’opération qui est appliquée sur ce champ en cliquant sur le petit triangle noir qui lui est associé. Choisissez alors Modifier la mesure dans le menu contextuel, puis l’option Somme. Comme vous pouvez le voir, maintenant à chaque fois que vous changez d’école, vous disposez de la liste des commentaires associés qui s’affichent.
Voilà, c’en est terminé de ce tutoriel ! Il ne vous reste plus qu’à sauvegarder le classeur Excel pour une utilisation future.
Nous espérons qu’à travers cet exemple de cas d’utilisation, vous avez :
- Pu considérer à nouveau l’intérêt que peuvent revêtir des solutions prêtes à l’usage comme les kits de démarrage OGDI et ODAF pour une administration locale en terme de rapprochement avec ses administrés. Pour rappel, les kits de démarrage OGDI et ODAF sont disponibles sous licence libre (Microsoft Public License (Ms-PL) sur le Centre de téléchargement Microsoft ici et ici.
- Et noté la facilité d’intégration de ces données avec des applications tierces (l’extension PowerPivot pour Microsoft Exel 2010 dans le cas présent).
Fort de ces éléments, nous vous laissons à présent imaginer les applications et services innovants basées sur le kit de démarrage OGDI, le kit de démarrage ODAF, ou les deux J, avec ou sans PowerPivot, que vous pourriez proposer.