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 (142 downloads)

A bientôt ;-)

6 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 !

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=""> <strike> <strong>