Thursday, December 10, 2009

Patching JPivot to filter by multiple members belonging to the same dimension

A very annoying problem with the release of JPivot shipped with the actual release of Pentaho is that you can only select one member from the same cube dimension at a time. But frequently we would like to select more than one member at a time. So what a good opportunity to build a patched release of JPivot and make that happens!

The first thing I made was try to test that by writing a sample MDX query on the SteelWheels cube that had, as a filter, a tuple made up by a set of members belonging to the same dimension. It worked. Infact, starting from Mondrian 3.1.2.13008, the OLAP engine supported compound slicers that makes this possible. After this very brief verification I was convinced to continue in my adventure and the next step was to find out where was the latest JPivot sources. As stated by Will Gorman in the post #7 of this thread the latest and good sources are in the obvious place... the JPivot CVS Repository of the JPivot project on SourceForge. So I get it and started my work.

The patch wasn't so difficult to be done and I reached easily my goal. It was a good opportunity to go into the internals of JPivot and learn about it. The new PAT is not round the corner so my opinion is that JPivot will be the production OLAP viewer of choice for Pentaho Community Edition again for a few months. So, I think, this exercise is not a waste of time.

If you want to try it, you can find the patched JPivot jar file here. The setup takes just one minute. Move your original JPivot library from <biserver_home>/tomcat/webapps/WEB-INF/lib in a different directory (just to be safe). Unzip the archive you just downloaded and copy the extracted jar file to the location specified before. Restart Pentaho BI Server and then... here we go!!

10 comments:

  1. I'd just like to stick my big nose in and say this is a very useful patch and a most welcome tweak by a community member.

    Tom

    ReplyDelete
  2. Hi,
    I just tried to rename the zip to jar and put it in the lib folder, but as soon as i start an analysis view i get a JPivot error. I'm running Pentaho CE 3.5. Do you have an idea about what I'm doing wrong?

    Thanks,
    Andrea

    ReplyDelete
  3. I don't know with so little informations about the error. Send the log file with the exception at info@serasoft.it and I'll have a look

    ReplyDelete
  4. I'm very sorry but Andrea experience a strange problem of file corruption after the download. If someone else will experience some problems with the downloaded file please email me at info@serasoft.it and I'll be very happy to send back the fully working jar file.

    Apologize me for this inconvenience.

    Sergio

    ReplyDelete
  5. Ok, the .zip contains a .jar, and everything works properly now.
    Thanks again for the patch Sergio.

    Andrea

    ReplyDelete
  6. Hi Sergio,

    Thanks for this great patch! This is the first time I'm setting up a Pentaho Environment and it's strange this isn't standard functionality.. Anyways great work and many thanks!

    ReplyDelete
  7. The patch has been merged in JPivot CVS and will be available in the next release of Pentaho.

    ReplyDelete
  8. I think this patch doesn´t support filtering by many members from same cube dimension and from other member from another cube dimension at the same time.
    Something like:
    WHERE ( {member1_dim1, member2_dim1, member3_dim1}, member1_dim2 )

    Does Mondrian support that?

    ReplyDelete
  9. Perfect work ! Thank you for your patch !
    Works properly on Pentaho 3.6 integrated on JBoss Portal 2.6.4
    Thanks again !

    ReplyDelete
  10. really good..
    I want some of the slicer fixed at the starting of the table and dont want to show in hierarchy navigator.
    I tried a lot but cant get it.Please advice me....

    ReplyDelete