SQL Server chez les clients – La vie des rapports Excel au travers de différents environnements

Les projets décisionnels BI sont  très régulièrement architecturés autour d’un besoin de restitution Excel, outil d’analyse bien connu des utilisateurs et largement déployé sur la plupart des postes de travail.

Les besoins en termes d’utilisation d’un rapport Excel sont proches du cycle de vie d’un rapport Reporting Services et doivent être gérés :

-       Développement/Modification

-       Test

-       Migration

-       Montée d’environnement

-       Centralisation dans SharePoint (Excel Services) pour le partage

Pour répondre à ces besoins, Excel utilise les connections ODC (Office Data Connexion) et permet ainsi de changer la connexion vers le serveur de données de plusieurs fichiers Excel en une seul action.  Cependant, l’ODC ne peut couvrir l’ensemble du cycle de vie d’un rapport.

Cette article permet de découvrir une approche de modification des connexions Excel pour  faciliter la reconnexion en masse de fichiers Excel présents sur un serveur SharePoint ou sur un système de fichiers.

Problématique

• Permettre le changement d’environnement de rapports Excel (ex : Dev vers Recette)
• Garantir la reconfiguration des accès aux données lors de migration ou de mutualisation de site SharePoint, ce qui engendre généralement de nouvelles urls de connexion et donc l’inutilisation de liens ODC.
• Maintenir les connexions Excel et faciliter le passage à un ODC si celui-ci n’avait pas était prévu en amont ou pour maintenir les connexions des rapports Excel créés et déposées par des utilisateurs sur SharePoint

Bénéfices

• Rendre la gestion de connexion Excel massivement configurable
• Offre une approche des connexions Excel non dépendante d’un environnement
• Industrialiser les processus de changement d’environnement sur les rapports Excel

La configuration d'un environnement, une étape incontournable

Avant d’expliquer l’approche de l’outil, il est nécessaire de comprendre le comportement d’Excel vis-à-vis des connexions de données afin de configurer au mieux un environnement et faciliter l’ajout d’un fichier Excel dessus.

Excel peut contenir des connexions « embedded » ou « linked ». Une connexion embedded (intégrée) est physiquement stockée dans le fichier Excel alors qu’une connexion linked (liée) est stockée à l’extérieur dans un fichier de connexion (ODC) et est référencée dans le fichier Excel.

Pour une connexion donnée, un classeur Excel peut être pourvu à la fois d’une connexion intégrée et d’une connexion liée. Dans ce cas, l’option Always use an external connection file est important car elle va définir le comportement de la connexion pour rafraichir ces données.

Un des avantages de l’ODC est de centraliser la chaine de connexion vers la source de données pour plusieurs fichiers Excel et ainsi permettre sa modification (déplacement de la base SSAS par exemple) sans devoir retoucher tous les fichiers Excel.

De la même façon qu’Excel, Excel Services (SharePoint) peut utiliser les connexions intégrées et liées (ODC). Pour des raisons de sécurité d’accès aux données, Excel Services peut être configuré pour n’autoriser que les connexions à partir de fichiers ODC localisés dans des librairies identifiées.

SharePoint possède un type de librairie (Data Connexion Library) conçu pour stocker les fichiers de connections. Ces librairies offrent à l’utilisateur la possibilité de gérer de manière centralisée, de sécuriser, de stocker et de réutiliser les fichiers de connexion de données :

-       Réutiliser : En centralisant les fichiers de connexions, les utilisateurs peuvent y accéder en les identifiant via leur nom et description et réutiliser ces fichiers qui ont été mis à disposition par d’autres utilisateurs ayant le droit d’éditer cette librairie. En d’autres termes, un utilisateur peut effectuer un rapport ad-hoc à partir d’une connexion mise à sa disposition, sans nécessairement connaître et comprendre le détail technique de la connexion (Pilote, nom du serveur, nom de l’instance/Port, authentification,…). De plus les emplacements des data connexion library peuvent être directement publiés sur son client Office https://technet.microsoft.com/fr-FR/library/cc262899(office.12).aspx#subsection9

-       Gérer : Comme les fichiers Excel ne possèdent qu’un lien vers les fichiers de connexions, si un paramètre de la connexion évolue (nom du serveur ou SSO application ID par exemple), seul le fichier de connexion devra être mis à jour sans avoir à impacter tous les fichiers Excel qui l’utilisent.

-       Sécuriser : Cette librairie supporte les permissions SharePoint incluant la sécurité sur les dossiers et sur les fichiers. La création et la modification sont contrôlées, il suffit de données accès aux utilisateurs en lecture seule pour qu’ils puissent utiliser ces fichiers.

Excel Services dans SharePoint permet l’ouverture de fichier Excel à partir d’emplacement initialement approuvé. Un emplacement approuvé possède des caractéristiques contrôlant l’accès aux données externe, il est possible d’autoriser l’affichage d’un fichier Excel dans Excel Services avec comme option :

-       Pas de connexion aux données externes autorisées

-       Accès aux données externes uniquement à travers une librairie « data connection »

-       Accès via une connexion intégrée en plus de la connexion liée

Pour configurer un environnement Excel Service dans SharePoint de manière sécurisée et pour faciliter le cycle de vie d’un rapport Excel, il est nécessaire de prévoir dans son architecture la configuration d’Excel Services suivante :

1)     Création d’une data connection librairy : Nécessaire pour stocker les fichiers ODC autorisés dans SharePoint

2)     Déployer les fichiers ODC dans cette librairie : Utiliser Excel pour générer les fichiers ODC nécessaires pour accéder à chaque source de données utile aux utilisateurs 

3)     Autoriser les data providers : Installer et autoriser les pilotes d’accès aux données

4)     Autoriser l’utilisation de Data Connection Librairies : Autoriser Excel à utiliser les fichiers ODC disponibles dans la librairie créer à l’étape 1)

 5)    Autoriser l’emplacement de fichiers Excel : Spécifiez l’emplacement des fichiers Excel autorisés à s’ouvrir dans Excel Services

La gestion des connexions Excel

La problématique qui n’est pas gérée via l’utilisation de fichiers liés de connexion, comme défini en préambule, relève de la migration et la promotion d’environnement Excel.

Dans le cas d’un changement d’environnement, exemple incontournable du cycle de vie d’un rapport Excel, il est nécessaire d’exporter un fichier Excel testé et validé sur un environnement pour le mettre à disposition  vers un second environnement et de mettre à jour la définition de sa connexion (redirection vers le fichier ODC de l’environnement cible).

Cette étape est facilement réalisable lorsqu’il s’agit de quelques fichiers Excel mais quand il s’agit de déployer une arborescence de plusieurs fichiers Excel, cela devient vite contraignant.

L’approche utilisée est d’effectuer la modification directement dans SharePoint (possibilité de post configurer des fichiers déplacés par des utilisateurs sans nécessiter leur téléchargement avec gestion du check-in/check_out) via la conception d’un outil type IT en ligne de commande afin d’identifier et de modifier des connexions Excel spécifiques sans avoir besoin d’Excel.

La manipulation des connexions dans Excel (fichiers XLSx et XLTx) se fait via la librairie OpenXML. Un fichier Excel est organisé en tant que conteneur d’information au format XML. Un fichier Excel renommé avec l’extension zip peut être ouvert comme suit :

La partie intéressante dans notre cas de figure se trouve dans le dossier xl, il s’agit du fichier connections.xml

La librairie OpenXml permet d’ouvrir le fichier Excel et d’accéder à la partie des connexions de manière pratique :

Et ainsi exploiter le contenu XML de cette partie via les fonctionnalités .Net XML native au Framework :

En s’appuyant sur ce jeu de fonctionnalités, il est alors relativement aisé de gérer les paramètres des connexions Excel en fonction des besoins opérationnels.

Définir la connexion à modifier :

L’expertise Microsoft Consulting Services au service de ses clients

La démarche qui est décrite dans cet article est une offre de service MCS qui peut tout aussi bien être mise en œuvre dans le cas d’un projet géré par Microsoft Services, que dans le cadre d’une assistance technique sur certains aspects du projet (architecture, validation…).

Cet outil spécifique permettant d'industrialiser la reconfiguration des rapports Excel est un des nombreux exemples de solutions permettant d’accélérer la mise en œuvre d'applications, l’alignement des configurations et d’améliorer la maintenance au quotidien. Cet outillage est utilisé par de nombreux clients Microsoft Consulting Services, dont voici des exemples de domaines d’activité :

-       Retail

-       Distribution d’énergie

-       Télécommunication

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

Antoine Richet, Consultant BI/SQL, Microsoft Consulting Services J’interviens dans les projets SQL Server pour les aspects d’architectures, optimisations et intégration de données décisionnelles.