OSBI.FR - Open Source Business Intelligence

Vos exports Excel à la demande en web avec Kettle (TechTip)

Pentaho Data Integration (Kettle) est souvent très utile pour générer et délivrer de façon simple des fichiers Excel à destination d’utilisateurs finaux. On peut en effet joindre un fichier « au résultat » d’une transformation afin de l’envoyer ensuite par mail aux personnes souhaitées via l’étape « Envoi Courriel » (cliquer si besoin sur les images pour les agrandir) :

Cependant, comment faire pour permettre à un utilisateur final de télécharger à la demande le fichier Excel via un navigateur web, en filtrant éventuellement les données à exporter au travers de paramètres présentés via un formulaire Html (listes déroulantes, cases à cocher…) ?

Réponse: avec un serveur Pentaho !

Ci-dessous la recette complète pour faire tout ceci… (Oui je sais, c’est encore un peu les fêtes, on peut encore parler gastronomie, non ?…)

Le plat à préparer :

Permettre à un utilisateur de télécharger via la console d’utilisation Pentaho un fichier Excel dont le contenu dépend des paramètres sélectionnées dans l’interface web.

Le fichier XLS est généré par Kettle, ce qui permet d’effectuer des traitements très évolués avec toute la palette des briques proposées par PDI (jointures complexes sur des sources hétérogènes, dénormalisation, etc etc…) :

Les ingrédients :

  • Pentaho Data Integration 4.1. On utilisera un référentiel Kettle de type « base de données » pour le stockage des traitements (c’est mieux, notamment pour le partage et l’échange entre plusieurs développeurs)
  • Un serveur Pentaho 3.7 (qui doit être démarré of course !)
  • La lecture attentive de ce qui suit…

Les étapes pour la création d’un référentiel Kettle de type SGBD :

1. Créer une base de données vierge encodée en UTF-8 dans le SGBD de votre choix (PostgreSQL, Oracle, MySql, SQLServer). Pour moi ce sera du PostgreSQL avec une base nommée « kettle4_rep »

2. Lancer Kettle, puis aller dans le menu « Outils\Référentiel\Se connecter au référentiel ».

La boite de dialogue des connexions aux référentiels s’ouvre.

Cliquer sur le bouton (+) pour notifier à Kettle que l’on souhaite travailler avec un nouveau référentiel :

3.  Dans la boite de dialogue suivante, sélectionner « Kettle Database Repository » puis cliquer sur OK :

4. Dans la boite de dialogue suivante, cliquer sur « Nouveau » pour définir les propriétés de connexion du référentiel.

Il s’agit ensuite de renseigner toutes les informations nécessaires pour l’établissement de la connexion JDBC avec la base de données créée (kettle4_rep)

Saisir le nom du serveur (host name), le nom de la base, le type de SGBD, ainsi que le nom d’utilisateur et le mot de passe.

Vérifier le bon fonctionnement de la connexion avec le bouton « Test »…

5. Dans la boite de dialogue suivante, saisir un ID et un Nom pour le référentiel, en sélectionnant la connexion préalablement créée (kettle4_rep).

Attention, ne pas saisir un ID ou un nom de référentiel comportant des lettres accentuées. Ces informations sont en effet stockées dans un fichier XML encodé en UTF-8 qui ne gère pas la langue de Molière et ses accents d’humeur. Ce fichier « repositories.xml »,   situé dans le répertoire .kettle dans le home utilisateur, peut toutefois être encodé en ISO-8859-15 pour autoriser des lettres accentuées.

6. Cliquer sur « Créer ou Mettre à niveau ».

Répondre « Oui » à la question « Souhaitez-vous créer le référentiel sur la connexion spécifiée ? » et « Non » à « Souhaitez-vous évaluer le script SQL ? »

7. Une fois le référentiel créé, celui-ci apparaît dans la liste.

Le sélectionner, puis se connecter avec l’identifiant par défaut « admin » , mot de passe « admin »

Le traitement Kettle à mettre en place :

Il s’agit d’une extraction (très) simple de données à partir de la base de démo SampleData qui tourne automatiquement lorsque le serveur Pentaho tourne.

Voici donc la transformation « export data » :La requête SQL de l’étape « Extraction depuis Table » permet de filtrer dynamiquement les données via les variables d’environnement ${varClient} et ${varProduit} :

Une 3ème variable d’environnement ${varCheminFichier} permet de définir le répertoire de stockage du fichier Excel sur le serveur Pentaho

Comme le serveur Pentaho ne peut envoyer que des « arguments » à un traitement Kettle (c’est finalement une méthode main() en Java) , il va falloir faire en sorte que Kettle convertisse ces « arguments » en variables d’environnement.

Pour cela, une tâche  « job Export XLS » doit être mise en place.

Rappelons nous en effet que dans Kettle, les variables d’environnement ne peuvent pas être utilisées dans la transformation dans laquelle elles ont été créés (voir mon article sur les boucles dans PDI) :

Il est donc nécessaire de créer une transformation « init variables » pour instancier préalablement les 3 variables d’environnement

Mise en place de l’Xaction

Pour que le traitement Kettle soit disponible via l’interface web, il faut créer une Xaction avec Pentaho Design Studio, véritable boite à outils (indispensable) pour profiter au maximum des capacités de Pentaho. (et Dieu sait qu’on peut en faire des choses…)

Ci-dessous, une vidéo montrant la configuration de Pentaho Design Studio (vous aurez bien sûr au préalable dézippé l’archive « pds-ce-win-32-3.7.0-stable.zip » (ici pour Windows 32Bit)

L’Xaction comporte 4 paramètres d’entrée :

  • choixClient (pas de valeur par défaut)
  • choixProduit (pas de valeur par défaut)
  • localisation_fichier, avec la valeur par défaut « C:\Pentaho-3.7.0\biserver-ce\tomcat\webapps\pentaho\export.xls »  (chemin absolu de dépôt du fichier, à adapter selon votre répertoire d’installation de Pentaho)
  • url_redirection, avec la valeur par défaut « /pentaho/export.xls » (chemin relatif du fichier pour le download via le serveur web)

L’Xaction se compose de 4 séquences d’actions :

  • Requête SQL « Liste Clients » pour retourner la liste des clients
  • Requête SQL « Liste Produits » pour retourner la liste des produits
  • Prompt/secure Filter pour présenter les listes déroulantes à l’utilisateur afin de sélectionner le client et le produit
  • Pentaho Data Integration Job pour invoquer le job Kettle :

On notera les points suivants :

  • Les arguments à passer au job sont à placer dans la fenêtre « Job Inputs »: attention, l’ordre doit être identique à celui de réception dans le traitement Kettle !
  • Le job Kettle est chargé depuis notre référentiel de type base de données (la case « Use Kettle Repository » est donc ici cochée). Il suffit ensuite de spécifier le nom du job dans « Job File » et le cas échéant le répertoire dans « Directory ».

Le fichier de configuration \biserver-ce\pentaho-solutions\system\kettle\settings.xml est à modifier comme indiqué ci-dessous pour qu’on puisse travailler avec un référentiel de type SGBDR (rdbms in english). Le redémarrage du serveur est obligatoire après cette modification ! Attention, la balise <repository.name>doit renfermer l’ID du référentiel  (et pas son Nom) !

  

  
       
	
	

	rdbms

	
	DEV repository
	
	
	admin
	
	
	admin
		

Enfin, pour que le fichier Excel puisse être téléchargé, il faut spécifier une url de redirection.

C’est ce qui est fait dans la partie « Process Outputs » de l’Xaction en spécifiant un paramètre « redirect » pour la réponse http :

Les sources de l’exemple

Vous souhaitez tester tout ça ?
Aucun problème, il vous suffit pour cela de télécharger le fichier zip ci-dessous :

Puis :

  • Copier le répertoire « ExportKettle » avec son tout son contenu dans un répertoire du serveur \pentaho-solutions\steel-wheels\ (par exemple). Dans l’Xaction « ExportXLS.xaction » adapter la valeur par défaut du paramètre localisation_fichier en fonction de votre installation, puis faire un refresh de la console
  • Ne pas oublier de configurer l’accès à un référentiel Kettle depuis un serveur Pentaho comme indiqué ci-dessus (modification du fichier « settings.xml »). ATTENTION: si votre référentiel Kettle est hébergé dans un SGBD d’un autre type que MySQL, ne pas oublier de rajouter le driver JDBC dans le répertoire suivant: \biserver-ce\tomcat\lib (en effet, seul le driver pour MySQL est présent par défaut dans Pentaho CE)
  • Dans Kettle, importer le fichier XML « kettle_repository.xml » depuis le menu « Outil\Référentiel\Importer dans le référentiel… »

Conclusion

Ce premier Post de 2011 me permet bien sûr de vous souhaiter à tous une excellente année 2011 et de remercier les (déjà) plus de 3000 lecteurs mensuels de ce blog.

Avec cet article, je souhaite également donner « la tonalité » pour 2011. En effet, une (très) grande place sera faite cette année à des articles techniques de ce genre, afin d’aider le maximum d’entre vous à se familiariser et à appréhender les capacités des solutions open source décisionnelles.

En gros: moins de blabla philosophique genre « qui est le meilleur ? qui à la plus grosse communauté ?… » (vous trouverez ça ailleurs) mais beaucoup plus de technique et d’exemples de mise en oeuvre pratique. Car finalement, la pratique, c’est toujours mieux que la théorie non ? 😉

PS (PUB): ce TechTip est extrait de la cinquantaine de travaux pratiques issus de la Formation Pentaho 3.7 Community Edition donnée par Atol CD chaque mois.

6 Comments

  1. Bonne année Sylvain et meilleurs voeux,
    est ce qu’il y’a du nouveau sur le plugin Metadata de birt?
    j’attend avec impatience.
    cdt,

  2. Bonne année également Bahafid !
    Pas de nouveau pour l’instant en ce qui concerne le plugin BIRT (on a plein de projets autour de Pentaho en ce moment et c’est dur de trouver du temps…)
    J’ai quand même pu coder en fin d’année un plugin Kettle pour faire la même chose : une étape d’extraction de données depuis Pentaho Metadata. Ca marche plutôt pas mal et c’est très pratique 😉
    Vais bientôt publier ça !
    A+

  3. Bonjour Sylvain et bonne année aussi.
    Très intéressant cette possibilité de pouvoir diffuser des fichiers excel au travers de pentaho. Avant de me lancer en détail dans tes explications, est ce qu’on peut faire la même chose avec Talend ?

  4. Bonjour Nathalie,

    C’est possible (surement) aussi avec Talend Open Studio : dans ce cas il va falloir déployer un job Talend sous forme de Web Service AXIS et ensuite le déployer sur un serveur d’application Java (Tomcat par exemple). Il serait sympa de faire le même exemple avec Talend… je vais essayer d’y songer ! (si jamais un autre lecteur du blog l’a déjà fait, qu’il n’hésite pas à indiquer un lien web)
    Bonne année à vous également !

Les commentaires sont fermés.