Le requêtage SQL dynamique dans Pentaho CDE

Il y a bientôt 3 ans, j’évoquais ici-même la mise en place de requêtes SQL dynamiques dans les moteurs de reporting open source Pentaho, JasperSoft et BIRT.

Une requête dynamique – pour faire simple – c’est la possibilité de passer un paramètre dans un ordre SQL ailleurs que dans la clause WHERE : par exemple pour passer en paramètre la (les) colonnes que l’on souhaite retourner.

Prenons un exemple tout simple basé sur la base SampleData de Pentaho afin de mettre en place le dashboard suivant avec Pentaho CDE :

dynamic_sql_cde-01

Ce tableau de bord comporte 2 paramètres « Pays » et « Mesure », dont le second permet de sélectionner la colonne où aller chercher l’information (TOTALPRICE ou QUANTITYORDERED).

On imagine donc la requête SQL correspondante à créer dans une DataSource CDE de type sqlJndi :

SELECT
CUSTOMERS.COUNTRY AS PAYS,
PRODUCTS.PRODUCTLINE AS GAMME_PRODUIT,
ORDERFACT.YEAR_ID AS ANNEE,
SUM(ORDERFACT.${param_mesure}) AS MESURE
FROM
PRODUCTS
INNER JOIN ORDERFACT ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
INNER JOIN CUSTOMERS ON ORDERFACT.CUSTOMERNUMBER = CUSTOMERS.CUSTOMERNUMBER
WHERE CUSTOMERS.COUNTRY = '${param_pays}'
GROUP BY PAYS, GAMME_PRODUIT, ANNEE

Manque de chance, ça ne fonctionne pas, comme le confirme Pedro Vale (WebDetails) dans cette entrée de forum : « Parametrized SQL clause in CDE » :-(

Alors comment faire ?

=> Comme bien souvent, c’est Kettle qui va nous sauver la mise grâce à sa capacité de gestion des requêtes SQL dynamiques

1. Création d’une transformation Kettle paramétrée :

dynamic_sql_cde-02

A noter que si la connexion définie dans la transformation Kettle s’appuie sur une source JNDI, elle sera utilisée côté serveur Pentaho (à condition qu’elle existe !)

2. Création d’une datasource de type « Kettle » dans le tableau de bord :

Sélectionner la transformation Kettle puis l’étape « output » chargée de la récupération des données :

dynamic_sql_cde-03

Bien renseigner les paramètres « param_mesure » et « param_pays » dans les propriétés « Variables » ET « Parameters ».

Vous pouvez ensuite tester le fonctionnement de la requête CDA en renseignant les paramètres dans la zone à cet effet :

dynamic_sql_cde-04

3. Utilisation de la requête dans le tableau :

Il suffit alors de définir la requête kettle en tant que datasource du composant « tableau »,  en n’oubliant pas de spécifier les Listeners et les Parameters :

dynamic_sql_cde-05

Testez- par vous-même :

Comme habituellement, vous pouvez télécharger cet exemple :
Dynamic SQL avec Pentaho CDE (258 downloads)

A bientôt 😉

13 comments to Le requêtage SQL dynamique dans Pentaho CDE

  • Patience A.

    Bonjour,

    Je tiens tout d’abord à te remercier pour ce tuto très simple et très claire.
    Cependant j’ai un soucis avec CDE. J’ai crée mes dashboards, j’ai ajouté les paramètres et cela marche très bien.
    Je souhaiterais ajouter un autre paramètre qui est le login de l’utilisateur connecté pour qu’il accès qu’aux données le concernant… J’ai utilisé le paramètre ${env::username} comme on peut le faire dans Pentaho Report Disign mais cela ne marche pas. As-tu une idée de la solution?

    Merci d’avance!

  • Bonjour,

    Pour récupérer le username, utiliser la variable javascript « Dashboards.context.user »
    (pour la liste des rôles: « Dashboards.context.roles » )

    PS: désolé pour le retard de la réponse

  • Patience A.

    Merci pour la réponse!Mais comment pourrais-je exactement. Je veux dire, je dois créer un un simple paramètre comme mes autres paramètres, USER_ID et lui donner comme valeur Dashboards.context.user. Dans ce cas, ma condition sera WHERE user = ${USER_ID}. Ou placer directement Dashboards.context.user dans la condition comme suit : WHERE user = ${Dashboards.context.user}. Merci de m’éclairer!

    PS : J’ai oublié de dire que je suis encore débutante sur pentaho. :)

  • Voici la méthode :

    1/ Dans les « Components » créer un « Simple Parameter » et le nommer « p_user_search » (par exemple)

    2/ Dans le composant (select ou autre) sur lequel vous souhaitez filtrer les données en fonction de l’utilisateur connecté, ajouter la fonction javascript ci-dessous dans « Pre-Execution » :

    function f() {
    var user = Dashboards.context[« user »];
    console.log(Dashboards.context[« user »]);
    Dashboards.setParameter(‘p_user_search’,user);
    }

    3/ Votre datasource (query SQL) peut ensuite être filtrée dans la clause WHERE comme cela :
    WHERE user = ${p_user_search}

    (N’oubliez pas de spécifier les paramètres et listeners)

    Normalement ça marche très bien 😉

  • Pat

    Bonjour,

    Je tiens à vous l’ensemble de vos tutos, qui m’ont beaucoup aidé durant mon stage de fin d’étude.
    Si je puis me permettre, j’ai crée la première version des mes tableaux de bords sur une seule page, vu qu’il y en a beaucoup, C’est pas vraiment pratique à consulter. Je souhaite dans la deuxième version Créer les onglets, ou même une sorte de menu de navigation qui permettra aux utilisateurs de consulter les tableaux de bords d’onglet à onglet.
    J’ai testé la composante « Navigation Menu Component » mais j’obtiens l’erreur suivante « Error processing component ».
    Voici les propriétés que que j’ai défini HtmlObject : Row1 , Execute at start : true, Include Solutions: True, Name : Menu.

    Avez vous une idée de comment je peux résoudre cette erreur?
    Ou avez une idée de comment je peux ajouter des onglets ou bar de navigation? Si vous avez quelques tuto à me conseiller n’hésiter pas.

    Merci d’avance

    Cordialement

  • Bonjour
    Avez-vous consulté la doc disponible sur les Core Component de CDF (c’est en standard, dans l’explorareur)
    vous devriez avoir pas mal d’exemples sur ce que vous souhaitez faire…
    bon courage !

  • niebo26

    Bonjour,
    Oui c’est un vieux post mais Pentaho est toujours d’actualité non ?
    En espérant avoir une réponse :
    Est-il possible de faire autrement qu’avec Kettle ? J’ai une base de données MySQL et je dois me baser dessus… Par exemple, de vraies requêtes SQL dynamiques ne fonctionnent pas ? (arguments à passer, arguments dynamiques @, etc)
    Merci d’avance de votre réponse !
    (Et super site qui m’a sauvé de nombreuses fois !)

  • Bonjour
    hé non, je crois malheureusement qu’on ne peut toujours pas passer de requêtes SQL dynamiques (donc un param ailleurs que dans la clause where)
    C’est d’ailleurs pour cela que j’avais rédigé ce post

    Mais au cas où posez la question sur forums.pentaho.com 😉

  • niebo26

    Je vais essayer oui (la dernière, ma question a coulé sans réponses ^^;)
    Merci beaucoup de la réponse rapide !

  • niebo26

    Au cas où vous avez la réponse, je demande quand même ^^
    En fait, ce que je voulais faire, c’est afficher un graphique qui s’actualise (et donc dépend) en fonction de paramètres (année, mois, date, pays, etc). Grâce à ce site et à plein d’autres, j’ai réussi à faire des checkbox (avec un paramètre « all » fonctionnel) pour les mois et lorsque je fais dépendre mon chart de ce paramètre UNIQUEMENT, ça fonctionne. Par contre, quand je veux rajouter une checkbox pour l’année (ou une liste pour le pays par exemple) et que je fais dépendre mon chart de ce paramètre avec l’autre, ça ne marche plus…

    Voilà la barchart_query (ne faîtes pas gaffe aux autres tables ^^) :
    SELECT pginit.Famille, SUM(commande.AmountEUR) AS VENTES
    FROM pginit INNER JOIN statg ON pginit.PG = statg.PGInit INNER JOIN commande ON statg.StatGroup = commande.StatGroup
    WHERE (MONTH(commande.TransactionDate) IN (${month}) OR « All » IN (${month}) OR ${month} IS NULL) AND
    //////// Prendre le même pattern qu’au dessus pour un autre paramètre \\\\\\\
    GROUP BY pginit.Famille
    ORDER BY VENTES;

    (${month} est un paramètre de CDE au fait)

    Une idée ? En fait je voulais utiliser des clauses CASE comme Pedro le suggérait mais avec le cas IS NULL je ne vois pas comment faire…
    Merci d’avance de votre réponse !

  • J’ai pas d’idée (si quelqu’un en a une : qu’il poste un commentaire !)

  • niebo26

    Des pistes pour adapter le problème que j’ai se trouveront ici 😀

    http://forums.pentaho.com/showthread.php?189483-Dynamic-SQL-query&p=411826&posted=1#post411826

    Voilà, plus et mieux documenter Pentaho aidera de nombreux utilisateurs par la suite et rendra ce logiciel bien plus populaire :)

  • Parfait merci !
    (On notera tout de même la réactivité du forum Pentaho…)
    Bonne continuation avec #Pentaho

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>