Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Mondrian4 - OSGI - IvySE - Saiku

  1. #1
    Join Date
    Apr 2008
    Posts
    144

    Wink Mondrian4 - OSGI - IvySE - Saiku

    Greetings developers and community members,

    This thread is intended to spur interest in Mondrian 4, IvySE, OSGI and Saiku on both the Enterprise Edition and Community Edition of the Business Analytics servers released by Pentaho. I wanted to have my favorite tools available on EE and CE and was initially disappointed because they seemed to work in some measure on CE releases but not EE. I began reading Will's blog to understand what may be happening. http://www.willgorman.com/?p=58
    As of this writing January 11, 2015 our understanding of things is as follows.

    Running Saiku 3 on BA Server 5.2.1.0-EE

    --
    If you are running Pentaho 5.2.1-EE and would like to run Saiku and IvySE, you are in luck.
    To get Saiku running, one must delete the olap4j files contained within pentaho-solutions/system/saiku/lib. Also you must add some imports the Mondrian 4's jar's MANIFEST.MF file which is located within pentaho-solutions/system/osgi/bundles/mondrian-4.1.1.0-148.jar file. It is located within the jar file itself in a directory called META-INF. See: http://jira.pentaho.com/browse/BISERVER-12199

    All that is required is to replace line 63, the second Import line... with the following addition

    Code:
    Import-Package: org.pentaho.platform.web.servlet.messages.Messages;org.pentaho.platform.web.servlet.PentahoDataSourceResolver;org.olap4j.OlapConnection;org.olap4j.driver.xmla;resolution:=optional,org.olap4j.d
    That weird looking last part org.olap4j.d is to end the line such that when it wraps the rest of the word 'driver' is completed. Java does not seem to mind that our modification does not wrap lines the way the rest of the file does.

    That's all that is required. You can restart your EE server and enjoy Saiku the way you previously did, and more because it is constantly evolving.
    Saiku 3 should work on your 5.2.1-EE server now.


    IvySE and schema support for Mondrian 3 and Mondrian 4.
    If you have not tried the latest IvySE, you should. Available in the marketplace on Tuesday, January 12, 2015. It has been updated to reveal far more information about schemas than it did previously. In our business we have 60 cubes or so in many different schema files. Now they are easier to deal with. There are several features that I am thinking about implementing for yet another release.
    1. A download button so people can save the schema.xml files to disk and make easy backups.
    2. An import button to allow you to upload a schema. Saves you a trip to managing datasources.
    3. Supporting the creation of Mondrian 4 connections.
    4. Add a feature create a duplicate of an existing schema and convert it to Mondrian 4 format. See my request below for a function to capture the conversion that appears in Mondrian 4 logs.

    -----
    Where I need responses / learning / improvement.

    I see the file pentaho-solutions/system/olap4j.properties and know from Will's blog that this is the place to define Mondrian 4 connections, but do not know what one would look like for using a schema backed by MySQL, Postgres or Vertica. Would someone be able to provide an example connecting to Foodmart or steelwheels using those databases?

    What are the rules? Is is necessary to restart the server after modifying the properties file, or is there some way to edit the file and then get the server to re-read it and make the connections available?

    I trust the blog so far in understanding that datasources that use jdbc:mondrian4: will be able to issue MDX queries. If someone provides a datasource definition for something other than mongodb, I'll update everyone on how to interact with Mondrian 4.

    Tom Barber and I have been talking about what improvements Saiku needs to really take advantage of the new features of Mondrian 4. Measure groups come to mind. Next week Tom should have enhanced Saiku to allow IvySE to launch a new instance of Saiku opened directly on the schema and cube you are editing in IvySE. I wanted this mechanism in there to help us all rapidly, "Edit, save, view" cycle through developing Mondrian 4 schemas.

    Tom tells me that in Mondrian 4, if you up the debug level and open a Mondrian 3 style schema, that in the logging, a Mondrian 4 schema conversion is output.
    If one of the Mondrian developers would see fit, could we get a function call that I can capture the output of to make a sort of schema converter to jumpstart people into using Mondrian 4 without completely re-writing their schema files by hand?

    If some folks can teach me more about Mondrian 4 here on the forum, I will commit to getting features in IvySE and work with the community on Saiku to make it easier to explore and put into production Mondrian 4 schemas. You'll have a better schema editor and Saiku OLAP experience to boot.

    Feel free to create JIRA's in whatever way you know necessary to get results. At the moment, I have a vision for the end result, but lack the specific knowledge to write a JIRAs to cover what exactly is required.

    Thanks ahead of time for your help. With luck, bright minds like Diethard Steiner, Luc and Will will chime in.
    Last edited by Smoodo; 01-12-2015 at 12:15 PM.

  2. #2

    Default

    Alright chaps, me and Brandon have been working on getting Mondrian 4 working with Saiku from within the BI server. For me this is a reasonably important task as we look to add Mondrian 4 specific features and so on. We have a solution which I am working on to get rolled out with the next plugin version which is due pretty much now, so we'll get this sorted ASAP.

    I didn't know about olap4j.properties until Brandon mentioned it about 20 minutes ago and had implemented a change to the data source properties where by if you add the property Mondrian = 4 to an Analysis datasource it will try and use Mondrian 4 for the connection instead of Mondrian 3, to me this is an ideal solution for people wanting to test out their schema on 4(especially when 3 has some interesting performance quirks over the last 18 months).

    I agree with Brandon about the schema upgrade stuff, its nice to get a freebe in the logs to upgrade your schema from 3 to 4 but it would be cool to be able to prod Mondrian to export a V4 schema to do an inline upgrade, I looked at the source but couldn't figure out a way to do it.

    Breno is also working on a small tweak to Saiku to allow users to open a blank query by schema and cubename to help facilitate Brandons interest in having tighter integration with IvySE.

    Running Mondrian 4 on both CE and EE isn't as simple as open and run and behind the scenes we'll have to do a bit of work to make it as seamless as possible, but we believe if we can facilitate Mondrian 4 usage within the BI server then we'll help get Mondrian 4 integrated properly into the platform instead of being a poor OSGI cousin used solely for people who want to use MongOLAP.

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)


  3. #3
    wgorman Guest

    Default

    Hi guys,

    Thanks for pushing forward on this! I'm glad my blog helped out a bit (although it sounds like Tom didn't read it!!)

    Regarding the olap4j.properties file, unfortunately yes, you'll need to restart the server for changes to take effect. I've attached source for a prototype REST webservice that can be included in a plugin that will allow runtime access to olap4j datasources. Feel free to use the code for IvySE, it may eventually find its way into Pentaho's data-access plugin.

    The blog post left out the specifics of how I connected to foodmart within olap4j.properties, here are the details for that:

    Code:
    foodmart.name=FoodMart
    foodmart.className=org.pentaho.platform.plugin.services.connections.PentahoSystemDriver
    foodmart.connectString=jdbc:mondrian4:DataSource=Foodmart;Catalog=/public/Foodmart/Foodmart.mondrian.xml
    Note that I had also defined a Foodmart Database Connection in the Pentaho UI.

    Regarding auto-upgrading from Mondrian 3 to Mondrian 4, here's a link to the debug line, hopefully it can help give you guys ideas on calling the upgrader directly:

    https://github.com/pentaho/mondrian/...ader.java#L262

    Good luck!

    Will
    Attached Files Attached Files

  4. #4

    Default

    I did read it! But I'd already implemented most of it. It transpires libs Saiku ships aren't compatible because we get classpath clashes with the OSGI libs which didn't used to happen with libs in pentaho/WEB-INF/lib so thats currently not a blocker but could prove awkward futher down the line.

    Anyway, I've written a Mondrian 4 plugin that will install M4 that works with Saiku on both CE and EE we'll be testing it today and tomorrow and push it to the marketplace after that.

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)


  5. #5
    Join Date
    Apr 2008
    Posts
    144

    Default

    Tom's CM4 (Community Mondrian 4) plugin appears to work very well. It simply deploys an OSGI bundle to the right place in the platform.
    Saiku has internal code that pays attention to the Mondrian=4 parameter and alters the connection URL to try to connect to Mondrian 4.

    You can see the specific Saiku 3 code here:
    https://github.com/OSBI/saiku/blob/6...ction.java#L80

    After trolling around the forums, I stumbled upon a smart gentleman named Dimetry

    His blog post answered one of my questions about setting up Mondrian 4 with more traditional datasources.
    http://pdiby.blogspot.com/2014/09/mo...-suite-51.html

    Editing the file that Will Gorman's blog pointed out pentaho-solutions/system/olap4j.properties

    Code:
    connection1.name=Mondrian4Connectionconnection1.className=org.pentaho.platform.plugin.services.connections.PentahoSystemDriver
    connection1.connectString=jdbc:mondrian4:Catalog=solution:/public/myschema.xml;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart
    Enabling debugging output for this appears here on Dimetry's blog:
    3) Restart your BA server and create a new analyzer report. The cubes from the schema should appear in the list when analyzer starts. If the cubes don't appear, you can open up one of these log files. There's probably an error or a problem with your schema.

    tomcat/logs/osgi_pentaho.log
    tomcat/logs/osgi_mondrian.log
    tomcat/logs/osgi_mondrian_sql.log

    If the last two logs are not there, you might have to enable them first, then do a quick BA server restart. Open the file pentaho-solutions/system/osgi/log4j.propertiesand uncomment the part at the bottom for mondrian logs.
    The next steps for me an IvySE are to do the following tasks:
    1. Verify that I can set up a few different Mondrian 4 connections to other cubes.
    2. Test that MDX against Mondrian 4 can work from CDA
    3. Satisfy curiosity of Analyzer compatibility by properly setting up a connection and 'seeing what happens'.
    4. Verify that Mondrian 4 / OSGI stuff will output to separate logs from Mondrian 3. I will look to keep the two separate.
    5. Explore Will's java code for internally providing some REST API calls to interact with olap4j. Looks like there are powerful things beyond what I thought about initially within the file. The desired trick would be to get the code within a UDJC in PDI so I don't have to have something compiled and planted on the BI Server as a permanent fixture.

    6. Finally take all lessons learned and start making screens in IvySE to read in, and write out configuration files to enable the configuration of these data sources.
    7. As a bonus, maybe I can conjure up a way to shutdown the server and have it start itself back up again so all changes can take effect. I'll make it user aware so we won't bump the server accidentally if other people are connected.

  6. #6
    Join Date
    Oct 2014
    Posts
    12

    Default

    Hi Guys,

    I hope you might be able to help me on this one, cause I really feel like I'm missing just a small, but crucial bit. I want use Mondrian4 to create a report using BI Server.

    I have created a datasource I want to use using UI:
    datasource.jpg

    Added following lines to ola4j.properties:
    Code:
    lukaszds.name=lukaszds
    lukaszds.className=org.pentaho.platform.plugin.services.connections.PentahoSystemDriver
    lukaszds.connectString=jdbc:mondrian4:DataSource=AIS_DWH;Catalog=solution:/public/tsschema4.xml
    And after resetting the server I hit following error:
    Code:
    2015-01-19 11:43:33,896 ERROR [org.pentaho.platform.plugin.services.connections.mondrian.MDXOlap4jConnection] MDXConnection.ERROR_0002 - Invalid connection properties: driver=org.pentaho.platform.plugin.services.connections.PentahoSystemDriver;url=jdbc:mondrian4:DataSource=AIS_DWH; Catalog=solution:/public/tsschema4.xml
    java.lang.RuntimeException: java.lang.ClassNotFoundException: org.pentaho.platform.web.servlet.PentahoDataSourceResolver not found by mondrian [33]
    I work with: Pentaho Business Analytics Server Enterprise Edition: 5.2.0.0.209

    Please let me know where could I make a mistake.

    With best regards,
    Lukasz
    Last edited by lkjanicki; 01-19-2015 at 08:45 AM.

  7. #7
    Join Date
    Apr 2008
    Posts
    144

    Default Some progress, more roadblocks

    Progress so far is as follows.
    1. Using CDA to access Mondrian 4.2.0-R works on Pentaho 5.3-RC and 5.2.0-CE. What's interesting about this is that the CDA file can specify the connection to the database and the schema file that Mondrian will use. This completely bypasses the pentaho-solutions/system/olap4j.properties group of settings and also whatever is set up in the "Datasource Manager" within the platform.

    Initially when I populated foodmart4, and placed all the required jar files into /pentaho-solutions/system/osgi/bundles, the mondrian.cfg file enabled aggregates, which later failed with error messages about not being able to find a path to one of the aggregate tables listed, even though it existed within the physical database tables. After disabling the two aggregate related properties, and restarting the server, the MDX queries issued to Mondrian 4 through CDA worked. I then went back and re-enabled the properties, restarted, and subsequent attempts to run MDX also succeeded. (Head scratching).

    2. Nothing else seems to pick up on Mondrian 4 in the platform. Although I imported the Mondrian 4 format FoodMart.mondrian.xml file to the platform, and set the property "Mondrian=4" correctly, the datasource is not picked up by Saiku. Pivot4j crashes throwing an NPE because, although it detects the schema, it tries to use the regular mondrian connection URL, which results in fires (NPEs).

    It is unclear which method Lukasz was intending to use to reach Mondrian 4. I received no such error. For his benefit and everyone elses, my olap4j.properties file reads as follows.

    Code:
    foodmart.name=FoodMart
    foodmart.className=org.pentaho.platform.plugin.services.connections.PentahoSystemDriver
    foodmart.connectString=jdbc:mondrian4:DataSource=Foodmart;Catalog=/public/FoodMart/FoodMart.mondrian.xml

  8. #8
    Join Date
    Apr 2008
    Posts
    144

    Default Luc gave light to how Mondrian 4 works in OSGi currently 5.3-RC

    === Begin Luc's Response from the Mondrian Mailing List ===

    This parameter won't work. [I had tried to use a JNDI connection and its corresponding definition within the Datasource Manager in the Pentaho User Console]
    DataSource=Foodmart

    The reason for that is that OSGI won't allow the platform SPI to leak into the mondrian 4's bundle class loader. To do this properly we need an empty jar loaded by OSGI which injects the PentahoDataSourceResolver into the mondrian bundle. Either that or modify mondrian's MANIFEST.MF headers manually and force it in. ( http://jira.pentaho.com/browse/BISERVER-12199 )
    You can bypass that by using Jdbc, JdbcUser and JdbcPassword as parameters instead.

    For the catalog, I'm not quite sure that using "/public/FoodMart/..." directly will work. I think you need to use the VFS prefix: "solution:/public/FoodMart/...". I might be wrong.

    As for making them show on Saiku, they won't. In 5.2 we have created the iOlapService, a new service in the BA platform which consolidates the hosted catalogs (mondrian in local instances) with generic olap4j connections. Analyzer uses it, but as far as I know Saiku does not.


    === End Luc's Response ===

    Here are the contents of my new olap4j.properties file:
    Code:
    OLAP4JPropFileFoodmart.name=FoodMart
    OLAP4JPropFileFoodmart.className=org.pentaho.platform.plugin.services.connections.PentahoSystemDriver
    OLAP4JPropFileFoodmart.connectString=jdbc:mondrian4:Jdbc=jdbc:mysql://172.27.0.204/foodmart4;JdbcDrivers=com.mysql.jdbc.Driver;JdbcUser=sg_pentaho_user;JdbcPassword=sg_user_password1234;Catalog=solution:/public/FoodMart/FoodMart.mondrian.xml
    Tracing the platform code has not yielded results, probably due to lazy loading. I am modifying Saiku to use the iOlapService to populate a connection list and will see how to generate a connection from there to Mondrian 4. Kurtis Walker pointed out what may be the most important bit that "jdbc:mondrian4" has to be in the connection string, which is exactly what Saiku does right now, it just does not get it's schema list from the iOlapService yet. With luck, later today it will.

  9. #9
    Join Date
    Oct 2014
    Posts
    12

    Default

    Thanks for the reply Smoodo, your solution hit the spot. After changing the MANIFEST file I am able to use JNDI connection defined in tomcat.

    Previously to work around the problem I was using following connection string:

    Code:
    complex_ar.connectString=jdbc:mondrian4:Catalog=solution:/public/ComplexArMondrianSchema.xml;Jdbc=jdbc:oracle:thin:@aisbip;JdbcUser=aisdwh;JdbcPassword=password
    I am accessing Mondrian 4 through Analyzer.

  10. #10
    Join Date
    Apr 2008
    Posts
    144

    Default

    That's really good to know! Once you established a datasource in Olap4j.properties Analyzer was able to pick it up?

    --
    As far as updates from my side. I have an internal build of Saiku that is adapted to use the new iolapservice and it successfully connects to both Mondrian 3 and 4 cubes.
    I've been building out a sparkl application that is intended to possibly replace the datasource manager that is included within Pentaho both CE and EE versions to allow easier configuration of datasources and analysis files. Soon I will publish a link to a github repository where I have both Saiku and my plugin for the BA platform evolving. Once I've done sufficient testing I will offer it to Pentaho for placement on the Marketplace.

    For the adventurous they can go to my github repository and try out the sparkl plugins and see the code and approach to getting this micro-project in order.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.