OSBI.FR - Open Source Business Intelligence

Vos tableaux croisés dans Pentaho Reporting… grâce à PDI !

Au mois d’août dernier, j’expliquais dans un article ce qu’était un tableau croisé et surtout la méthodologie de mise en place dans les moteurs de reporting open source. Alors que chez Jasper et BIRT, cette fonctionnalité est disponible en standard et utilisable au travers de « wizards » (assistants de création), chez Pentaho elle manque encore cruellement en ce début 2011.

Il semble que la prochaine version de Pentaho Reporting (3.8) n’inclue encore pas cette fonctionnalité, elle se consacre en effet à l’amélioration des performances, ce qui est une très bonne chose.  Thomas Morgner détaille d’ailleurs sur son blog le mécanisme de cache mémoire mis en place dans Pentaho Reporting en vue de réduire le temps d’exécution des rapports.

Kettle (PDI) au secours de Pentaho Reporting ?

Pentaho Data Integration (PDI), l’ETL de la suite Pentaho, démontre une excellente intégration avec la plate-forme BI.

Par exemple, PDI permet de publier des « cubes » directement sur le serveur afin de les exploiter avec JPivot ou le Web AD Hoc Query Reporting (WAQR). Pour cela on utilisera le plugin kettle « Agile BI » et son requêteur OLAP embarqué, Pentaho Analyzer.

Une autre possibilitéexiste, utiliser Kettle comme source de données dans Pentaho Report Designer (au lieu d’une source SQL). Nous allons mettre cette fonctionnalité en oeuvre pour créer nos tableaux croisés…

Données normalisées et dénormalisées

PDI dispose depuis très longtemps d’étapes de normalisation et dénormalisation de données.

Un flux normalisé comme ci-dessous peut être dénormalisé sur la colonne année afin de produire un tableau croisé

Flux dénormalisé avec pivotement sur le champ « ANNEE » :

Vous constatez que le tableau croisé est plus facile à lire et qu’il permet d’effectuer de manière plus commode des comparatifs entre les différentes années.

Dénormalisation de données dans Kettle

La transformation ci-dessous permet de procéder à la dénormalisation des données présentées ci-dessus :

La requête SQL à paramétrer dans la brique d’extraction est la suivante (votre serveur Pentaho local doit tourner…) :

SELECT
"CUSTOMERS"."CUSTOMERNAME",
"PRODUCTS"."PRODUCTLINE",
YEAR("ORDERS"."ORDERDATE") AS annee,
SUM ("ORDERDETAILS"."QUANTITYORDERED" * "ORDERDETAILS"."PRICEEACH") AS montant
FROM
"ORDERS" INNER JOIN "ORDERDETAILS" ON "ORDERS"."ORDERNUMBER" = "ORDERDETAILS"."ORDERNUMBER"
INNER JOIN "CUSTOMERS" ON "ORDERS"."CUSTOMERNUMBER" = "CUSTOMERS"."CUSTOMERNUMBER"
INNER JOIN "PRODUCTS" ON "ORDERDETAILS"."PRODUCTCODE" = "PRODUCTS"."PRODUCTCODE"
WHERE customername= '${filtre_client}'
GROUP BY customername, productline , annee
ORDER BY customername, productline , annee

A noter que la clause « where » prend comme paramètre ${filtre_client} pour le champ « customername » dans la transformation. Cela permettra d’implémenter également un filtre sur le client dans Pentaho Reporting

La configuration de l’étape « Dénormalisation » ressemble à ceci :

Création d’un tableau croisé dans Pentaho Report Designer à partir de la transformation PDI

La vidéo suivante illustre l’utilisation de la transformation Kettle pour la création du tableau croisé

 

Télécharger l’exemple

Vous pouvez télécharger les sources (transformation kettle et définition du rapport) ici :

14 Comments

  1. Ne pas oublier d’ajouter les paramètres à la transformation avec valeur par défaut sinon pas de preview et des heures à s’arracher les cheveux…(dans ma transformation c’est une requête MDX)
    Super tuto…

  2. Excellent ce tuto ! Je cherchais ça depuis qq jours surtout pour ce qui concerne le passage de paramètre dans la requête SQL dans PDI ! Pas mal d’arrachage de cheveux de mon côté aussi !
    Par contre, comment pourrait_on faire pour faire passer plusieurs valeurs de paramètres à Customer? Je cherche maintenant un filtre de type « where customername IN (‘${filtre_client}’) » mais je sais pas comment le gérer dans PDI
    Qq1 a une idée de génie ??

  3. Bonjour
    Il y a une technique toute simple pour passer plusieurs paramètres à la requête dans Kettle
    => Dans Pentaho Report Designer, il suffit de faire une liste de type « multi-valuée » puis avant de la passer à PDI, de concaténer les paramètres sélectionnés en les séparant par une virgule: ceux-ci pourront alors être utilisés directement dans la clause WHERE IN en SQL !

    Bon courage, content en tout cas que cet article vous aie apporté de l’aide 😉

  4. Bonjour !! Merci pour votre réponse !Mais je n’y arrive toujours pas 🙁
    Que voulez-vous dire par « avant de la passer à PDI, de concaténer les paramètres sélectionnés en les séparant par une virgule » ?
    J’imagine qu’il faut passer le paramètre à PDI sous forme de string sur ce format: ‘Alpha Cognac’,’AV Stores, Co’,….
    C’est le Post-Processing Formula dans l’écran de config du paramètre dans PRD qui peut faire ça?
    N’hésitez pas à me renvoyer sur de la doc que je n’aurais pas trouvé ou pas bien lu….
    Merci !!

  5. Bonjour.
    Content que vous ayez trouvé la réponse sur le forum Pentaho, c’est exactement la méthode à laquelle je pensais.

    Bonne continuation 😉

  6. Arrgh… je m’arrache encore un peu les cheveux.
    J’ai défini un paramètre de type Object avec mes clients, un paramètre caché de type string qui transforme le premier en CSV avec la fonction =CSVTEXT([mselect], TRUE()). Le paramètre caché est passé à ma requête dans ma step « Table Input » dans PDI. Je n’ai pas utilisé l’autre fonction =CSVARRAY([param], TRUE()).
    Ca marche parfaitement bien dans le preview du report designer mais après publication, j’ai mon paramètre entouré en rouge et Pentaho me dit « This prompt value is of an invalid value ». Impossible de poursuivre.
    Je ne comprends vraiment pas…. As-tu déja rencontré ce problème ? Pas trouvé d’info sur les forums.

  7. Oui et ça fait qqc d’assez étrange.
    Dans mon paramètre, j’affiche le nom du client en clair (customer.code) mais j’envoie l’ID comme paramètre pour filtrer sur customer.id (ça me paraissait plus sain).
    En décochant « Validate Values », une fois publié, dès que je clique sur une valeur de mon client (je suis en Multi Selection Button), il me rajoute un bouton avec l’ID du client.
    Par exemple, si j’ai deux client (Client1 et Client2), je vais avoir 2 boutons « Client1 » et « Client2 » dans ma barre de paramètre.
    Dès que je vais cliquer sur « Client2 », il m’ajoute un bouton « 23 » (qui est l’ID interne de ce client).

    par contre, je n’ai pu de message « This Prompt Value … »

    Je me dis qu’il y a une erreur de type mais je ne trouve pas. Dans le lien que j’ai trouvé dans mon post précédent, il est indiqué qu’il faut retransformer le paramètre dans PDI avec la fonction CSVARRAY (dans une step FORMULA) mais la fonction est inconnue dans PDI. Le pb pourrait venir de là.

    Je suis sec de chez sec.

  8. Hello ! Merci beaucoup pour ton temps !
    Je suis désolé, ça ne marche pas mieux… Résultat impeccable dans le preview de Report Designer. Une fois publié dans Pentaho Solutions, il m’affiche une petite boite à la place du rapport indiquant ‘Report validation failed’. J’ai adapté mon rapport avec ta fonction (SEQUENCEQUOTER) mais j’ai exactement les mêmes erreurs : « This prompt value is of an invalid value ». J’en ai maaaare.

  9. Bonjour
    Attention mon rapport est développé sous PRD 3.9.1 et publié sous BIServer 4.8 CE
    Peut-être est-ce la raison du pb si tu n’est pas dans la même version..

  10. En effet, je suis sur PRD 3.9.0 et BI 4.5.0. Je suis en train d’installer un serveur en production avec les mêmes versions que toi. Je te tiens au jus si le pb vient de là.
    Désolé pour le dérangement et un très grand merci pour ton temps.

Les commentaires sont fermés.