Methode-import-sigmah
publicité
Christophe Paquette - [email protected] 12/01/2012 METHODE POUR UNE MISE A JOUR AUTOMATIQUE DE TABLEAUX DE BORD EXCEL A PARTIR DES DONNEES SIGMAH. Ce document présente une méthode permettant d'actualiser automatiquement un tableau de bord sous Excel à partir des données de Sigmah. L'intérêt de cette méth ode est qu'elle ne nécessite aucune programmation ni de Macros : les services administratifs et financiers maîtrisent généralement bien Excel et devraient pouvoir l'appliquer sans difficultés majeures. Les pré-requis pour créer le tableau de bord automatisé sont donc : être familiarisé avec les notions de base de Sigmah et bien maîtriser Excel, (en particulier les Tableaux croisés dynamiques, mais aucune Macro n’est requise). Quelques notions en matière de bases de données sont souhaitables 1. Pour l’utilisation du tableau de bord, aucun pré-requis : une fois qu’il est créé, les mises à jour sont automatiquement faites à l’ouverture du classeur Excel. Autrement dit, il y a un peu de boulot pour mettre au point l’outil et ensuite tout doit être automatique (Inch Allah). N'étant pas moi-même informaticien, excusez mes maladresses et approximations de vocabulaire technique. Je précise que notre organisation est en train d’évaluer la possibilité d’utiliser Sigmah : ce système et le tableau de bord ne sont pas actuellement en utilisation opérationnelle mais seulement installés dans une configuration de test. Dans notre association, notre tableau de bord des projets s’appelle « le tableau A3 » : ce nom est utilisé dans l’exemple ci-joint. Si des collègues d’autres ONG souhaitent me contacter pour des précisions, je leur répondrais avec plaisir (dans la limite de ma disponibilité de temps !) : [email protected] Problématique et principe de résolution. Notre objectif est de disposer d’un tableau Excel qui comporte une ligne par projet et une colonne par rubrique (par exemple : Nom du projet, Responsable, Zone géographique, nom du partenaire, Date de Début, Date de Fin, Budget, Sources de financement, m ontant des frais administratifs). Il est assez facile d'extraire à partir d'Excel les informations d'une base de données, au moyen d'une « requête SQL » définie dans l'onglet "Données" d'Excel. C'est d'ailleurs ainsi que seront extraites les "données de base" d'un projet (nom du projet, dates de début et de fin, budget prévu, etc.), qui se trouvent dans des tables de structure fixe (une colonne "numéro du projet", une colonne "nom du projet", une colonne « Date de Début », etc.). La difficulté provient de la structure "flexible" de Sigmah. En effet, au moyen des "modèles de projets", chaque organisation peut définir les informations particulières qu'elle souhaite enregistrer dans le système. Ces informations se situent dans différentes tables liées entre elles par des numéros de référence (c’est le principe des bases de données relationnelles) . Par exemple, si nous créons un champ "frais administratifs", une première table (la table des libellés, appelée dans Sigmah « table Flexible_element ») comporte dans une colonne le libellé "Frais administratifs" et dans un autre son numéro de référence (par exemple 3824). Une autre table (la table des valeurs, appelée dans Sigmah « table Value ») comportera dans une colonne le numéro de référence (3824) et dans une autre la valeur (5 480,00 €, par exemple). La solution consiste à extraire l'information "brute" dans une feuille de calcul et à l'organiser dans une autre feuille au moyen de Tableaux croisés dynamiques et de la fonction "RecherchV". Je suis parti sans aucune connaissance des requêtes SQL et toute ma science provient d’un site web très basique : http://sql.1keydata.com/fr/ 1 Christophe Paquette - [email protected] 12/01/2012 Etapes. 1. Dans Sigmah, identifier les données que vous souhaitez importer dans Excel . Pour éviter d'importer dans Excel la totalité des informations "flexibles" (de nombreuses informations ne nous intéressent pas pour le Tableau de Bord), l'astuce consiste à intégrer, lors de la préparation d'un modèle de projet dans Sigmah, un signe distinctif dans le libellé des champs qui nous intéressent. Par exemple, au lieu de définir un champ "Frais Administratifs" nous pouvons l'appeler "Frais Administratifs:" (remarquer les deux points). Nous aurons donc plusieurs champs ainsi "marqués" dans le modèle de projet ("Frais Administra tifs:", "Secteur:", "Partenaire :", etc.). Une autre solution consisterait à faire des requêtes SQL plus longues (les requêtes SQL permettent d’extraire des informations d’une base de données, comme expliqué ci-dessous), cette astuce permet de simplifier. 2. Dans Excel, préparer et enregistrer une requête SQL pour importer les données qui nous intéressent dans une première feuille. C'est l'objet de la feuille « Données-flexibles » du classeur Excel d’exemple ci-joint. 2.1. Vérifiez si vous avez dans Excel le « fournisseur de données » pour PostgreSQL. Dans le ruban « Données », bouton « A partir d’Autres Sources », choisir « Assistant de connexion de données » puis « Autres/Avancées » et cliquer sur « suivant ». Dans l’image ci-dessous, le bon fournisseur est surligné. Il n’était pas présent par défaut dans ma version d’Excel 2007, je l’ai téléchargé sur http://pgfoundry.org/projects/oledb/ . Christophe Paquette - [email protected] 12/01/2012 2.2. Indiquez les paramètres de connexion à votre base de données. Ces informations sont à demander à l’administrateur système qui a installé Sigmah dans votre organisation. Dans mon cas, il s’agit d’une installation de test « locale » (mon ordinateur est à la fois serveur et client) et les paramètres sont présentés dans l’image ci-contre. Cliquez sur « tester la connexion » pour vérifier. La connexion étant testée, cliquons sur « annuler », nous reviendrons plus tard sur cette étape. 2.3. Préparer la requête SQL. Nous avons besoin d’une requête qui importe dans une feuille de calcul des informations qui se trouvent dans plusieurs tables de la Base de Données Sigmah : Flexible_element, Value et User_database et nous souhaitons importer quatre informations pour chaque projet : le nom du projet, le numéro de référence des « champs flexibles » qui nous intéressent (et donc les champs dont le libellé comporte « : »), ce libellé et la valeur. Cette requête est la suivante : Select public.userdatabase.name, public.flexible_element.label, public.value.id_value, public.value.value, public.userdatabase.fullname From public.userdatabase Inner Join public.value On public.userdatabase.databaseid = public.value.id_project Inner Join public.flexible_element On public.value.id_flexible_element = public.flexible_element.id_flexible_element Where public.flexible_element.label Like '%:%' Un informaticien l’aurait certainement rédigée directement, pour ma part j’ai utilise un outil graphique (un complément à Excel) appelé “SQL Drill” et construit ma requête comme indiqué ci-dessous. Christophe Paquette - [email protected] 12/01/2012 Copier la requête SQL, qui sera ensuite (voir ci-dessous) collée à l’endroit approprié dans Excel. Si vous n’êtes pas intéressés par personnaliser votre requête (par exemple pour importer les informations dont le libellé comporterait « ; » au lieu de « : »), vous pouvez simplement copier la requête SQL encadrée ci-dessus. 2.4. Introduire la requête SQL dans Excel. Retournons dans la première feuille de notre classeur Excel (la feuille appelée « Données-flexibles » du classeur Excel d’exemple ci-joint) et établissons une connexion avec la base de données comme expliqué aux points 2.1 et 2.2 cidessus. Cliquer sur OK, puis décocher la case « Connexion à une table spécifique », puis cliquer sur Suivant. Donner un nom au fichier (par exemple « MonTableau.odc » et cocher la case « Enregistrer le mot de passe dans le fichier » et choisir « Oui » en réponse au message qui apparait (« le mot de passe sera enregistré sans chiffrement, etc ».), puis cliquer sur « Terminer ». Dans la fenêtre qui apparaît, choisir n’importe quelle table et cliquer sur OK. Dans la fenêtre « Importation de données » qui apparaît, cliquer sur le bouton « Propriétés… ». Choisir l’onglet « Définition ». Cocher la case « Toujours utiliser le fichier de connexion et, dans le menu déroulant « type de commande », l’option « SQL ». Dans la case « texte de la commande » : effacer le contenu et y coller la requête SQL que nous avons copiée dans l’étape 2.3 ci-dessus. Puis : OK. Le plus compliqué est fait! Nous avons à présent une feuille Excel qui extrait les informations de la base de données Sigmah. Si vous modifiez une donnée dans Sigmah (par exemple les « Frais administratifs » augmentent à 6 300 €), vous constaterez qu’il suffit de cliquer sur le bouton « actualiser tout » dans l’onglet « Données d’Excel » pour que l’information soit mise à jour dans cette feuille. Il ne reste plus qu’à ordonner l’information. 3. Dans Excel, créer un Tableau Croisé Dynamique pour regrouper les informations de type « flexible ». La difficulté avec les TCD est qu’ils ne peuvent comporter dans la zone « Valeur » que des chiffres, pas du texte, alors que nous souhaitons avoir dans notre tableau de bord des informations telles que le nom du partenaire. Pour contourner la difficulté, nous allons regrouper dans le TCD non pas la valeur mais le numéro d’identifiant de cette valeur (id_flexible). Christophe Paquette - [email protected] 12/01/2012 Nous créons donc ce TCD dans une deuxième feuille du classeur (TCD-Flexible, dans l’exemple ci-joint), qui va rechercher les informations dans la première feuille (Donnéesflexible) et les ordonner. Nous obtenons un TCD qui comporte une ligne par projet, les libellés (Nom du projet, nom du partenaire, frais administratifs, etc.) en entêtes de colonnes et des numéros de référence en valeurs (le contenu du tableau). Il ne reste plus qu’à remplacer ces numéros de références par les valeurs, ce qui est l’objet de l’étape suivante. 4. Dans Excel, créer un tableau qui présente les valeurs. Dans une nouvelle feuille du classeur (dans l’exemple, la feuille « A3 Brut »), nous utilisons la formule « RechercheV » d’Excel, qui va rechercher dans chaque cellule du TCD le numéro de référence, puis va rechercher dans la première feuille ce numéro et le remplacer par la valeur. La formule de base pour la Cellule B3 de notre nouvelle feuille est : RECHERCHEV('TCDFlexible'!B3;'Donnees-flexible'!$C:$D;2;FAUX). Autrement dit, identifier dans la feuille TCDFlexible le chiffre qui se trouve dans la Cellule B3 puis rechercher dans les colonnes C et D de la feuille Donnes-flexibles ce chiffre et rapporter la valeur de la deuxième colonne (la colonne D, c'est-à-dire « value »). Dans l’exemple ci-joint la formule est un peu plus compliquée simplement pour des raisons esthétiques : si la donnée recherchée n’existe pas, le résultat est un truc du genre #NA. C’est moche, et la formule présentée dans le classeur d’exemple permet une présentation plus satisfaisante. =SI(ESTNA(RECHERCHEV('TCD-Flexible'!B3;'Donnees-flexible'!$C:$D;2;FAUX));"";RECHERCHEV('TCD-Flexible'!B3;'Donneesflexible'!$C:$D;2;FAUX)) Autre petite subtilité : dans la base de donnée de Sigmah, la séparation des décimales est enregistrée sous forme de virgule dans certaines tables, mais sous forme de points dans d’autres tables. Nous devons donc allonger un peu notre formule pour remplacer les points par des virgules, au moyen de la fonction SUBSTITUTE. La formule finale devient donc : =SI(ESTNA(RECHERCHEV('TCD-Flexible'!B3;'Donnees-flexible'!$C:$F;2;FAUX));"";SUBSTITUE(RECHERCHEV('TCDFlexible'!B3;'Donnees-flexible'!$C:$F;2;FAUX);".";",")) 5. Ajouter les informations qui nous intéressent mais qui proviennent d’autres tables de Sigmah. . Voila notre tableau de bord «brut », qui regroupe les informations « flexibles » de Sigmah. Cependant, d’autres informations nous intéressent, qui se trouvent dans d’autres tables. Nous allons à présent créer de nouvelles feuilles, qui comporteront les requètes SQL permettant d’importer ces informations dans notre classeur, puis introduire dans notre feuille « A3 Brut » les formules appropriées pour aller piocher l’information ainsi récupérée. 5.1 Le nom complet du projet Il se trouve déjà dans la table « données flexibles » créée à l’étape 2.4. Il suffit d’aller chercher l’information au moyen de la formule =SI(ESTNA(RECHERCHEV(A3;'Donnees-flexible'!$A:$F;5;FAUX));"";RECHERCHEV(A3;'Donnees-flexible'!$A:$F;5;FAUX)) 5.2 Les informations de base du projet. Les informations de base se trouvent dans des tables « fixes » et non pas « flexibles » ; en particulier la table Project. Donc, pas besoin de TCD. Il suffit, dans une nouvelle feuille du Christophe Paquette - [email protected] 12/01/2012 classeur, de préparer la requête SQL appropriée. Pour voir cette requête, allez sur la feuille « Project » du classeur exemple et, dans le ruban « Données » cliquez sur « connexions » puis sur « propriétés » de la connexion « project ». Vous verrez la requète : Select public.userdatabase.name, public.userdatabase.startdate, public.project.close_date, public.project.end_date, public.project.planned_budget, public.project.received_budget, public.project.spend_budget From public.userdatabase Inner Join public.project On public.userdatabase.databaseid = public.project.databaseid Nous n’avons qu’à ajouter des colonnes à notre tableau de bord (la feuille « A3 Brut »), qui irons « piocher » l’information voulue dans cette feuille au moyen de la fonction RechercheV. 5.3 Le nom du site. Une démarche similaire est nécessaire pour faire figurer dans le tableau le nom du site, qui se trouve dans une autre table (la table « Partner »). Select public.userdatabase.name, public.partner.name, public.partner.fullname From public.userdatabase Inner Join public.partnerindatabase On public.userdatabase.databaseid = public.partnerindatabase.databaseid Inner Join public.partner On public.partnerindatabase.partnerid = public.partner.partnerid 5.4 Le informations flexibles, mais qui proviennent d’une « liste de choix ». Pour les informations de type « liste de choix » de Sigmah, nous devons ajouter une petite étape supplémentaire. Par exemple, dans notre association, nous avons 4 catégories de projets : Enfance, Tourisme, Economie et Autre. L’utilisateur de Sigmah ne doit pas saisir de texte, mais choisir une de ces options dans un menu déroulant. La valeur de cette information dans la table « Value » de la base de données n’est pas « Economie », mais…. un nouveau numéro d’identification, qui fait référence à une autre table de Sigmah, la table « Question-Choice ». Nous devons donc préparer une nouvelle feuille avec une requête SQL qui va importer cette table. Select public.question_choice_element.id_choice, public.question_choice_element.label From public.question_choice_element Dans notre tableau de bord, la formule permettant de piocher l’information « Economie » et en faire une belle présentation est donc un peu plus sophistiquée : =SI(ESTERREUR(RECHERCHEV(CNUM(SI(ESTNA(RECHERCHEV('TCD-Flexible'!N3;'Donneesflexible'!$C:$D;2;FAUX));"";RECHERCHEV('TCD-Flexible'!N3;'Donnees-flexible'!$C:$D;2;FAUX)));'Question Choice'!$A:$B;2;FAUX));"";RECHERCHEV(CNUM(SI(ESTNA(RECHERCHEV('TCD-Flexible'!N3;'Donneesflexible'!$C:$D;2;FAUX));"";RECHERCHEV('TCD-Flexible'!N3;'Donnees-flexible'!$C:$D;2;FAUX)));'Question Choice'!$A:$B;2;FAUX)) 5.5 Le nom du responsable du projet Là encore, il faut commencer par créer une nouvelle feuille de calcul, qui ira chercher le nom du responsable au moyen de la requête : Christophe Paquette - [email protected] 12/01/2012 Select public.userdatabase.name, public.userlogin.firstname || ' ' || public.userlogin.name From public.userdatabase Inner Join public.project On public.userdatabase.databaseid = public.project.databaseid Inner Join public.userlogin On public.project.id_manager = public.userlogin.userid Il y a une petite subtilité dans cette requête car nous souhaitons concaténer le prénom et le nom. Il est donc utilisé une fonction de SQL pour cela : la double barre verticale (sur le clavier, par alt gr 6). Dans notre feuille « A3 Brut » nous allons donc piocher l’information au moyen de la formule : =SI(ESTNA(RECHERCHEV($A3;Tableau_Users;2;FAUX));"";RECHERCHEV($A3;Tableau_Users;2;FAUX)) 6. Dernière étape : dans Excel, préparer le tableau de bord final. Il ne reste plus qu’à ouvrir une dernière feuille dans ce classeur pour présenter le tableau de bord de manière esthétique, avec les colonnes dans l’ordre voulu. Pour faire plus propre, toutes les autres feuilles peuvent être masquées : tout est à présent automatique, aucune donnée n’est à saisir dans Excel et seule cette feuille est utilisée pour consulter l’information. Dans l’exemple cijoint, c’est la feuille « A3 ». Attention : si vous modifiez les modèles de projet dans Sigmah, il faudra réajuster le tableau de bord. Il est donc préférable de mettre au point le tableau de bord une fois que l’organisation a arrêté définitivement ses modèles de projet.
