Mondrian, XML/A and the Olap4J datasource

When we started with Pentaho Reporting 3.5.0, one of our goals was to kick out as many hard-coded dependencies as possible. Coding against private APIs is ugly, every upgrade breaks something and you always chase your own tail. In short: It is not fun.

The OLAP4J datasources are among the more promising new APIs that emerge. Originally, I had the hope to completely kick out every Java source file that contained the words “import mondrian.”. So far I am still waiting to pull the plug from the Mondrian datasource to make OLAP4J the primary OLAP datasource. Hurry up, sticking with 0.x versions for years and years is soooooo lame. Where do you guys pick up such bad behaviour?

Right now, the only viable use case for using OLAP4J is to access XML/A Cubes. Sounds not that exciting, but with it the Pentaho Reporting System can talk to SAP BW, Palo and Microsoft’s Analysis server.

How do you do connect Pentaho Reporting to a XML/A Datasource?

  1. Create a OLAP4J datasource in PRD
  2. Create a new connection definition. The Database dialog that pops up is full of JDBC databases. Ignore all of that useless gibberish and select the “Generic database”.

Use the following entries in the fields:

Test the connection and once everything is fine, hit OK.

 You are now set up to run MDX queries and to use the result in a report.

But Thomas, there are so many OLAP4J datasources? Which one shall I use?

Pentaho Reporting comes with three flavours of OLAP datasources.

The Banded Datasource is the one that most reports should use these days. This datasource provides a flattened view over the cube much like the results you see in Analyzer and JPivot. You can easily print that resulting dataset using the classical banded reporting techniques, like relational groups and detail bands.

The Denormalized  Datasource provides a denormalized stream of data. It will be used for the crosstabbing in 4.0, but right now in the majority of cases, it is of not much use. It has its merrits if you need to do your own aggregation on that data.

The Legacy-Datasource behaves mostly like the Banded datasource. It exists to support PRD-3.0 and older reports. Ignore that, you are fresh and modern, after all. The Legacy-datasource names columns differently and cannot handle ragged hierarchies. Some ancient reports require that kind of weird behaviour to continue to work as before.

So use the Banded Datasource and you cannot go wrong.

Can I use OLAP4J datasources in the BI-Server?

Sure. Just do it. But stay clear from declaring them in JNDI, as connection pooling can easily fail. OLAP4J drivers cloak as JDBC drivers to try to make use of connection pools and JNDI systems. For code that was written for JDK 1.5 or earlier, “unwrapping” the driver does not work and then the pooled JDBC objects cannot be used to make a OLAP connection.

I read I can use Mondrian via OLAP4J as well. How?

Although it is technically possible to use Mondrian this way, I heavily recommend against it.

Mondrian encodes a system specific file name into its URL. When Mondrian tries to establish a connection, it tries to read this file. If the file name is a absolute file name, then you will run into troubles when you move your report onto the BI-Server. The referenced file will usually not be available there. If the file name is relative, then Mondrian will resolve the file using the application’s current working directory (for the BI-Server usually Tomcat’s ‘bin’ directory, for PRD either the PRD installation directory or your home directory).

So far I have not seen a safe way to specify the schema file so that it works both locally and when the report is published on a server.

Related posts:

  1. Filtering Mondrian roles
  2. How to work with Advanced SQL/MDX Datasources efficiently
  3. Don’t hardcode host names, use JNDI
This entry was posted in Advanced Topic, Tech-Tips on by .
Thomas

About Thomas

After working as all-hands guy and lead developer on Pentaho Reporting for over an decade, I have learned a thing or two about report generation, layouting and general BI practices. I have witnessed the remarkable growth of Pentaho Reporting from a small niche product to a enterprise class Business Intelligence product. This blog documents my own perspective on Pentaho Reporting's development process and our our steps towards upcoming releases.

6 thoughts on “Mondrian, XML/A and the Olap4J datasource”

  1. VolkerM

    Hi Thomas,

    I was trying to use your approach above to connect Spoon 4.2 to an MIS Alea 5.2 Olap server using XMLA. But to no avail. Whenever I click on “Test” the apps response will be “Connection to database [Alea Test] is OK” with an empty Hostanme and Database name and a port of 1521, even that I provided Hostname and DB name but no port. I always get the same response even when I use completely wrong server names.
    Any idea what I am doing wrong?

    thanks in advance,
    Volker

  2. Thomas Morgner

    I have no clue on whether that would work in Kettle. If anything, then you probably need something like an OLAP4J input step. OLAP4J is not SQL/JDBC, even though its API cloaks itself as JDBC-driver.

    So trying to treat it as SQL connection and invoking SQL statements will fail with an error.

    Also: Spoon only checks whether the driver returns a connection object. It relies on the JDBC driver to do some actual checking. If the OLAP4J or any other JDBC driver is programmed in a way where it does not validate the input until later, then Spoon would not be able to detect errors here.

  3. Julcik

    I’ve created datasource as described here. connection test passed, simple MDX query works. But when I press Ok button datasource creation dialog in Report Designer, I get exception

    java.lang.IllegalArgumentException: No enum const class org.olap4j.metadata.Property$StandardMemberProperty.BACKGROUND_COLOR
    at java.lang.Enum.valueOf(Enum.java:196)
    at org.olap4j.metadata.Property$StandardMemberProperty.valueOf(Property.java:143)
    at org.pentaho.reporting.engine.classic.extensions.datasources.olap4j.MDXMetaDataMemberAttributes.getMetaAttribute(MDXMetaDataMemberAttributes.java:128)
    at org.pentaho.reporting.engine.classic.extensions.datasources.olap4j.MDXMetaDataMemberAttributes.getMetaAttribute(MDXMetaDataMemberAttributes.java:94)
    at org.pentaho.reporting.engine.classic.core.wizard.DefaultDataAttributes.merge(DefaultDataAttributes.java:158)
    at org.pentaho.reporting.engine.classic.core.cache.CachableTableModel.(CachableTableModel.java:65)
    at org.pentaho.reporting.engine.classic.core.cache.EhCacheDataCache.put(EhCacheDataCache.java:103)
    ….

    I checked – there’s no BACKGROUND_COLOR in org.olap4j.metadata.Property$StandardMemberProperty.

    Is it possible to work with XMLA datasource in report designer? Please, help me!

  4. Thomas Morgner

    Sounds like a bug: We do not handle it gracefully when your datawarehouse produces attributes that are not part of the MDX specification from Microsoft. (BACKGROUND_COLOR is called BACK_COLOR there).

    File me a JIRA case at http://jira.pentaho.com/browse/PRD and we can easily fix that so that non-standard properties get ignored or mapped somewhere outside of the standard space where they cannot do any harm.

    (For your reference: A link to the MDX documentation about cell-properties)
    http://msdn.microsoft.com/en-us/library/ms145573.aspx

  6. Alan Leavy

    Hi Thomas,
    I have the same issue as Julcik had.
    I notice that you updated http://jira.pentaho.com/browse/PRD-3697
    reducing the severity and commenting “can be solved by fixing the query to follow Microsoft’s naming standard.”

    Could you give me some pointers on how such problematic queries can be fixed?
    My test query was:
    SELECT NON EMPTY { [Measures].[Reseller Sales-Sales Amount] } ON COLUMNS, NON EMPTY { [Customer].[Customer Geography].[Full Name].members } ON ROWS FROM [Analysis Services Tutorial]
    executed against Microsoft SSAS

Comments are closed.