Tip PENTAHO: comment modifier les traductions de l’interface Jpivot?

Voilà une astuce qui va être utile pour les développeurs et administrateurs de pentaho qui ont mis en place les pivots jpivot.
Vous avez surement remarqué   comme les traductions étaient des fois limites? Par exemple, la fonction dans la barre des outils de jpivot qui permet de supprimer les lignes vides a été traduite en « Opprimer les lignes blanches »:
Pas très compréhensible tout ça…
Et bien, il y a un moyen pour y remédier, c’est de faire sa propre traduction.
Pour cela, il va falloir modifier directement la librairie jpivot:
Allez dans biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib et choisissez le fichier jpivot-XXX.jar
Ouvrez-le  avec votre gestionnaire d’archives préféré , puis allez dans le répertoire
com/tonbeller/jpivot/toolbar/resources
Là dedans vous allez trouver l’ensemble des fichiers de ressource qui servent à  l’affichage des tooltips dans la barre des outils. Ouvrez le fichier resources_fr.properties
nano resources_fr.properties
puis modifiez la ligne suivante :
toolb.non.empty=Opprimer lignes blanches
par :
toolb.non.empty=Supprimer lignes vides
Il n’y a plus qu’à  enregistrer le fichier de propriétés dans le jar et redémarrer PENTAHO et le tour est joué.

PENTAHO/JPIVOT: erreur à l’export Excel ou PDF

Maintenant que votre pivot est affiché, vous avez peut-être envie de l’exporter sous Excel pour retravailler les données.
Pour cela vous utilisez le bouton bien connu :

Sauf qu’au lieu de récupérer l’export vous êtes confronté à une erreur   du style :

mai 25, 2005 12:26:18 PM org.apache.catalina.core.ApplicationContext log
SEVERE: StandardWrapper.Throwable
java.lang.NoClassDefFoundError: org/apache/fop/configuration/Configuration
at com.tonbeller.jpivot.print.PrintServlet.init(PrintServlet.java:71)
at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173)
at org.apache.catalina.core.StandardWrapper.allocate(StandardWrapper.java:809)
../..
Caused by: java.lang.ClassNotFoundException: org.apache.fop.configuration.Configuration
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1645)
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1491)
... 14 more
Tout cela est dû au problème de compatibilité descendante entre les dernières version de PENTAHO et jpivot.
Alors que PENTAHO intègre la dernière version de la librairie apache FOP, jpivot ( qui n’est plus maintenu depuis des lustres) utilise une version, disons-le, très ancienne.
Or, cette version ancienne version utilisait une classe (org.apache.fop.configuration.Configuration) qui n’existe plus dans la dernière version de FOP.
Il suffit alors de récupérer la version 0.20.5 sur un site ( maven par exemple)  et la copier dans
biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib/:
cd biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib/
wget https://mirrors.ibiblio.org/pub/mirrors/maven/fop/jars/fop-0.20.5.jar
Le bug a été référencé sur le JIRA de PENTAHO :
https://jira.pentaho.com/browse/BISERVER-7286
Ah oui, dernière chose,  n’oubliez pas de déplacer ou supprimer la librairie (fop-0.9XX.jar )fournie par PENTAHO.

Limitation pentaho/mondrian: ne pas utiliser explicitement les clefs (key) pour les membres de dimensions dans les requêtes MDX

Bon, tout est dans le titre… mais, quand même, ça nécessite quelques explications et surtout des solutions de contournement.

La problématique

Si vous venez, comme moi, du domaine décisionnel « non libre » ( Microsoft Essbase, etc.) , vous avez sûrement l’habitude d’utiliser les clefs de membres des dimensions   pour les sélectionner  directement  dans les requêtes MDX.
Ok on fait un récap:
Lorsque vous faites une requête MDX, vous pouvez sélectionner un membre de dimension soit par sa valeur affichée ( en général le nom du membre), soit par la clef utilisée par le moteur OLAP. En général, la clef  utilisée et une clef numérique donc plus facile à  manipuler.

Un exemple, vite !!

Ok, disons que vous voulez mettre en perspective  les ventes  deux de vos produits phares :

  • les [casseroles en métal argenté(c)]
  • et  les [poêles dorées à l’or fin(TM)].

La requête MDX ressemblerait à  peu près à  ça :

SELECT {[ProductHierarchy].[casseroles en métal argenté(c)], [ProductHierarchy].[poêles dorées à l'or fin(TM)]} on ROWS,
 {[Measures].[sales]} ON COLUMNS
 FROM [Commercial]

Jusque là  tout va bien, sauf que en fait la même requête doit servir pour deux pays différents. Et pour chaque pays, le nom des produits est différent.
Plus précisément, en Angleterre les deux produits s’appellent respectivement : [silver metal pan(c)] et [gilded with fine gold pans(TM)]
Et là on a un problème:  la requête précédemment crée ne fonctionne plus telle quelle à  condition que les dénominations soient remplacées par leur traduction respective!
Du coup, en Angleterre la requête qui fonctionne est celle là :

SELECT {[ProductHierarchy].[silver metal pan(c)], [ProductHierarchy].[gilded with fine gold pans(TM)]} on ROWS,
{[Measures].[sales]} ON COLUMNS
FROM [Commercial]

Alors, comment faire pour que la requête MDX fonctionne correctement quelque soit le pays (et donc les traductions) ?

L’implémentation

C’est là  qu’en tant que super spécialiste de votre moteur OLAP préféré, vous allez utiliser les fonctions différentiation par la clef .
Dans notre exemple la casserole en argent a un N° d’identifiant numérique égal  à 112 et la poêle en or fin le N° 113, quelque soit les langages.

On utilise le caractère & ( « et commercial » ou « ampersand » en anglais) pour utiliser la clef à  la place du nom.
La requête devient alors :

SELECT {[ProductHierarchy].&[112], [ProductHierarchy].&[113]} on ROWS,
{[Measures].[sales]} ON COLUMNS
FROM [Commercial]

Ok, on met tout ça en pratique.

On crée donc  la dimension ProductHierarchy dans le cube Mondrian à  partir d’une table de dimension dénommée DimProduct avec deux champs  ProductKey ( notre clef)  et ProductName:

Le fait d’avoir rempli la propriété « column » avec le nom du champ « ProductKey » va permettre un certain nombre de choses, dont l’optimisation des requêtes SQL qui sont effectuées par le moteur de Mondrian, mais aussi la possibilité de faire notre requête sur la base de la clef ProductKey.

Mondrian Error  !!

Vous exécutez la requête et là, patatras  voilà ce que l’interpréteur de requête de Mondrian nous dit :

Mondrian Error:MDX object ‘[ProductHierarchy.New Hierarchy 0].&[113]’ not found in cube ‘Commercial’

La raison en est simple, Julian Hyde (le créateur de Mondrian ) n’a tout simplement pas prévu cette fonction dans son moteur.
Voici la preuve: un ticket a été référencé dans le gestionnaire d’incident de Mondrian sous la description :Member key treated as member name in WHERE

Arggh, voilà  donc une mauvaise surprise !  Pour enfoncer le clou, ce bug a été ouvert en 2009…

Vous pourriez me dire: « tu n’as qu’à  mettre le champ ProductName dans la propriété ‘column’ et on en parle plus ! ».
Oui, en effet ça marche mais :

  1.  la problématique de l’internationalisation reste entière.
  2. De plus, il faut savoir que le moteur Mondrian va utiliser la propriété « column » pour créer ses jointures lors des requêtes SQL sous-jaçentes ( rappelons-nous que Mondrian est un moteur ROLAP-  donc basé sur le moteur de base de données !). Or en général , il vaut mieux faire les jointure sur des champs de tables qui sont indexés , ne serait-ce que pour des questions  de performance et faire un index sur un champ numériques a beaucoup plus d’efficacité que sur un champ de type caractère.

Quelle est donc la solution ?

On va donc utiliser une fonctionnalité  qui ,elle, a été implémentée:  les propriétés en conjonction avec les filtres.
Dans un premier temps on va créer une propriété basée sur le champ « ProductKey »:

Et enfin on va utiliser un filtre , basé sur cette propriété

SELECT {Filter([ProductHierarchy].[Product].Members,
([ProductHierarchy].CurrentMember.Properties(« key »)=113)
OR
([ProductHierarchy].CurrentMember.Properties(« key »)=111))
} ON ROWS,
{[Measures].[sales]} ON COLUMNS
FROM [Commercial]

Là -enfin-, vous pouvez utilisez cette requête, quelque soit les traductions des produits utilisées.

Si vous avez déjà  été dans cette situation n’hésitez pas à  commenter cet article ou à  demander des précisions !

Tip Pentaho/kettle: accélérer le chargement de spoon PDI 4.3 GA

J’ai été assez désappointé par la dernière version de PDI ( Pentaho data integration) délivrée par Matt Casters en version 4.3 GA.
En effet, spoon, qui est l’interface de création des ETL, se charge en plus de 3 minutes montre en main !!!
Inacceptable dans ce monde speedé!!
Moi qui pensait que PDI était le seul truc de bien dans pentaho ( avec peut-être Mondrian) et bien j’en suis resté baba…

Et là  je me suis aperçu que je n’étais pas le seul dans cette situation. Ce qui  est assez déprimant c’est que le bug recensé n’est pas attribué, donc ne risque pas d’être résolu. Heureusement, Matt Casters himself , nous donne le work-around:

  1. Aller dans le répertoire data-integration/plugins/spoon, 
  2. puis supprimer le répertoire agile-bi et tout ce qu’il contient.

Miracle, spoon prend 40 secondes  à  charger ( ce qui est encore trop long, mais bon…) !
Par contre, du coup, toutes les fonctionnalités agiles de PDI ne sont plus disponibles (mais comme c’est du pipeau, c’est pas bien grave)

Bon par contre, Matt nous dit dans le forum qu’il travaille à la résolution de ce bug alors que le ticket en cours n’est pas attribué.

Hou, le menteur!!

tip pentaho/déploiements: rafraîchir le repository

Le problème est très vite arrivé quand on veut déployer de nouveaux rapports sur plusieurs serveurs différents de façon automatique.
imaginons que ayez un nouveau rapport de type CDE ( kesako ?)que vous avez fabriqué sur votre serveur local.

Vous avez copié les fichiers physiques du rapports  sur le serveur de production grâce à  un script shell par exemple.
Si vous vous connectez sur votre console utilisateur (https://monserveur:8080/pentaho/Home), vous ne voyez pas le nouveau rapport !!
L’explication est simple, tant que vous n’avez pas rafraîchi la solution , les nouveaux fichiers ne sont pas pris en compte par le moteur Pentaho.
Sous le capot, Pentaho utilise un indexage des fichiers qu’il enregistre dans la base de données ( tables PRO_FILES  et PRO_ACLS_LIST dans la base de données hibernate).
Tant que l’on a pas commandé à  pentaho de rafraîchir le repository, les nouveaux objets qui ont été copiés n’ont pas été enregistrés dans l’index pentaho.
Comment on peut piloter ce rafraîchissement, par exemple par script shell ?
La seule façon que j’ai trouvé est de créer une page jsp que je vais installer dans le contexte d’exécution de pentaho.
Je vais l’appeler RefreshRepository.jsp et voici le code :

<%@ page language= »java »
import= »org.pentaho.platform.engine.core.system.PentahoSystem,
org.pentaho.platform.api.engine.IPentahoSession,
org.pentaho.platform.web.jsp.messages.Messages,
org.pentaho.platform.api.engine.IUITemplater,
org.pentaho.platform.web.http.WebTemplateHelper,
org.pentaho.platform.util.messages.LocaleHelper,
org.pentaho.platform.api.repository.ISolutionRepository,
org.pentaho.platform.repository.solution.dbbased.DbBasedSolutionRepository,
org.pentaho.platform.web.http.PentahoHttpSessionHelper » %>
<%
response.setCharacterEncoding(LocaleHelper.getSystemEncoding());
IPentahoSession userSession = PentahoHttpSessionHelper.getPentahoSession( request );
String content = «  »;
ISolutionRepository solutionRepository = PentahoSystem.get(ISolutionRepository.class, userSession);
solutionRepository.reloadSolutionRepository(userSession, userSession.getLoggingLevel());
content = « OK refresh »;
%>
<%= content %>

Je n’ai plus qu’à  déployer cette page dans le répertoire suivant :

biserver-ce/tomcat/webapps/pentaho/jsp/

Une fois fait, je peux maintenant piloter le refresh  en faisant  :

https://monserveur:8080/pentaho/RefreshRepository?userid=totologin&password=XXXX

Du coup je peux piloter le refresh  par script Shell :

 wget  –quiet  -O – « https://monserveur:8080/pentaho/RefreshRepository?userid=totologin&password=XXXX »

Pentaho:calcul du pipe commercial en MDX avec Mondrian – partie 2

Dans mon précédent post j’ai posé le problème du calcul par périodes du pipe commercial.


Maintenant que l’on a la solution en SQL, on pourra valider la requête MDX.

On voudrait retranscrire ce que l’on a fait en SQL c’est à dire sélectionner les devis dont la date de devis est inférieur ( ou égal) à la fin de la période et la date de commande ou d’expiration supérieure (ou égale) au début de la période

pour rappel la requête SQL est la suivante :

… where (datedevis <= "31/01/2011") and(datecommande >= « 01/01/2011 » );

Premier essai MDX: Modélisation du cube

Imaginons que l’on ai créé un cube Mondrian basé sur la table des devis en sachant

que la table contient deux dates ( date de devis et date de commande)

Il nous faut donc deux dimensions de temps liées ces deux dates

On a donc les dimensions suivantes [TimeStart] et [TimeEnd]

Ces dimensions ne sont que des dimensions virtuelles de la dimension Time qui contient une hiérarchie par défaut dont les descendants ordonnés sont : Année,Quarter,Mois,Date:

Voici donc le rendu dans SchemaWorkBench :

Dans cette image , on remarque que les dimensions TimeStart et TimeEnd sont des Dimensions « Usage » c’est à dire qu’elles ont été crées à partir d’une véritable dimension.

Premier essai MDX: la requête

maintenant que l’on a créé le cube, on peut concevoir la requête:

On doit faire un croisement entre

  1. la période qui va de janvier à l’infini dans la dimension TimeStart pour la date de commande ( rappelez-vous la condition SQL : datecommande >= « 01/01/2011 »)
  2. et la période qui va du début de l’existence de l’informatique (merci Turing! – je laisse Snail préciser les choses) à janvier dans la dimension TimeEnd pour la date de devis (condition SQL: datedevis <= "31/01/2011")
  3. et enfin le montant
Bon alors ça donne quoi ?
déjà comment savoir la date du début de l’informatique ? On va utiliser le membre Firstchid de la dimension TimeStart auquel on va adjoindre la fonction MDX Openingperiod.
Du coup la condition 1 se résume à

OpeningPeriod([TimeStart].[Month],[TimeStart].firstchild):[TimeStart].[2011].[Q1].[January]

De même, la condition 2 correspond à ça ( grâce à la fonction ClosingPeriod et au membre Lastchild):

[TimeEnd].[2011].[Q1].[January]:Closingperiod([TimeEnd].[Month],[TimeEnd].lastchild )

Une petite explication s’impose sur ces deux ensembles:
[TimeStart].Firstchild permet d’identifier le premier enfant de cette dimension c’est à dire la première année possible, LastChild étant la dernière année possible.
OpeningPeriod permet de connaitre le dernier enfant possible à un niveau spécifié( [TimeEnd].[Month]) pour un membre spécifique( [TimeEnd].lastchild).
Pour moi, la fin du monde étant positionné à décembre 2013, la formule correspondra à
[TimeEnd].[Q4].[December].
Je vous laisse imaginer l’explication pour ClosingPeriod qui est la contraposée parfaite de la première.
Il suffit maintenant de mettre tout ça dans un bocal, rajouter la mesure Montant et d’agiter très fort. Voilà ce que ça devrait donner :
with MEMBER Measures.x as ‘
SUM( NonEmptyCrossJoin(
OpeningPeriod([TimeStart].[Month],[TimeStart].firstchild):[TimeStart].[2011].[Q1].[January],
[TimeEnd].[2011].[Q1].[January]:Closingperiod([TimeEnd].[Month],[TimeEnd].lastchild ) )
, [Measures].[Montant] )
Select Measures.x on 0
from [Pipe]
Outre le fait que j’utilise un membre calculé, je fais appel à la fonction NonEmptyCrossJoin
qui permet de faire la jointure entre les parcours des deux dimensions ( en enlevant bien sûr les tuples vides) ainsi que la fonction SUM qui permet de faire la somme du montant sur le dataset créé par le crossjoin.
Premier essai MDX: Conclusions
Ok, on a le bon montant du pipe commercial en janvier 2011, mais peut avoir la même chose pour toutes les autres périodes à partir d’une seule requête ?
ça voudrait dire que l’on puisse décrire mois par mois ce qui revient à dire qu’il faut rajouter un axe supplémentaire dans la requête pour montrer les montants pour chaque mois.
Dans bien des cas, cela se résume à rajouter un axe colonne comme ceci :
select Measures.test on rows,
Descendants([Time]. [year],(Month) on columns
from [MonCube]
Fastoche ? Pas vraiment : je vous rappelle que l’on a deux dimensions de temps différentes TimeStart et TimeEnd et que l’on ne peut pas les aligner si facilement…
Comment ? parlez plus fort…
?rajouter une troisième dimension de temps ?
Ok, mais ça suffit pas…. il faut aussi lier les trois dimensions entre elles pour que lorsque l’on se positionne par exemple sur janvier , les deux autres prennent en compte cette période.
Heureusement M$ ( l’inventeur de MDX; respect à Mosha Pasumansky) a pensé à tout : la fonction LinkMember .


Arghh !! Mauvaise Limonade: Julian Hyde, le créateur de Mondrian, ne l’a pas implémenté… voir le ticket si ça dit à quelqu’un(e)..


Dans la troisième partie de cette série on verra comment résoudre le problème:
afficher à partir d’une seule requête MDX le montant du pipe commercial Mois par Mois

Pentaho:calcul du pipe commercial en MDX avec Mondrian – partie 1

Voilà donc un cas d’école de la vraie vie comme un consultant décisionnel aime à en voir: Comment calculer mois par mois un pipe commercial ?

Bon, le besoin est simple: une entreprise veut connaître mois par mois le montant global des propositions commerciales qui ont été envoyées à ses prospects.

En général l’entreprise a un système de force de ventes qui permet d’extraire ces données là.

Par exemple , un extract serait :

N° de devis, N° de client/prospect, date du devis , date d’expiration du devis (ou date de commande),Montant du devis, etc.

Imaginons que nous voulions savoir la liste de tous les devis actifs sur le mois de janvier.

On va donc devoir identifier les cas possibles; ça se résume à cette image :

La solution SQL:

Bon là c’est facile , il suffit de retranscrire les 4 cas possibles dans les conditions de filtre:

select sum(montant) from Pipe where (datedevis>= ’01/01/2011′ and datecommande<=’31/01/2011′ or () etc…

Fastidieux, non ?…

Heureusement, Il y a une façon plus élégante de regrouper les filtres:

select sum(montant) from Pipe  where (datedevis <= ’31/01/2011′) and (datecommande >= ’01/01/2011′ );

Dans le prochain article j’aborderais la solution MDX pour Mondrian.