Which pentaho mondrian library to include in a Java application to map MDX to SQL

Question

I want to implement an application that provides MDX query support. For this, I would like to use one of the libraries from pentaho mondrian (an open source OLTP server with MDX interface), which converts MDX queries to SQL (based on xml description), unfortunately I can not find any information, which libraries that I need to enable — and how to use them — in my project to work with MDX to SQL.

Does anyone have experience reusing Mondrian components in his / her application?

Answer 1

I recommend downloading the latest version 3.2.0 from Mondrian, it is distributed with all its dependencies. The distribution also includes an ivy file that describes its dependencies.

Latest version: http://forums.pentaho.com/showthread.php?77035-Mondrian-3.2-GA-Schema-Workbench-and-Agg-Designer-stable-available-on-SourceForge&p=239443#poststop

Documentation can also be found on the Pentaho website. Good luck

Answer 2

Olap4j is now part of the Pentagon Mondry. Perhaps you can find the information in the olap4j API? http://www.olap4j.org/

On this page I see:

org.olap4j.mdx.parser : Parser for the MDX query language.

olap4j is an open Java API for OLAP.

Think of it like JDBC, but for access to multidimensional data.

olap4j is designed as a regular API for any OLAP server, so you can write an application on one OLAP server and easily switch it to another. And built on this API will grow the collection of tools and components.

Answer 3

It seems that mondrian.rolap.agg.AggregationManager has some code associated with it, but not direct.

parseTree = this.olap4jConnection.getMondrianConnection().parseStatement(mdx); DrillThrough plan1 = (DrillThrough)parseTree; Query query = plan1.getQuery(); query.setResultStyle(ResultStyle.LIST); this.setQuery(query); CellSet cellSet = this.executeOlapQueryInternal( query, (MondrianOlap4jCellSetMetaData) null ); List coords = Collections.nCopies(cellSet.getAxes().size(), Integer.valueOf(0)); MondrianOlap4jCell cell = (MondrianOlap4jCell) cellSet.getCell(coords); ResultSet resultSet = cell.drillThroughInternal( plan1.getMaxRowCount(), plan1.getFirstRowOrdinal(), plan1.getReturnList(), true, (Logger)null, rowCountSlot ); 

If you follow the last line,

cell.drillThroughInternal (...)

you will get here

String sql = this.getDrillThroughSQL (fields, extendedContext);

But I'm not sure if mondrian generates only one SQL for mdx. I think that through XMLA (which most tools use as an interface) you can request drilling on one axis in which the full request is not rewritten.

Hope this answers.

Answer 4

Im using Mondrian, supported by hsqldb, for developers to work against dbs in memory, but then switch to a non-Mondrian OLAP implementation when deploying in a real environment. This means that we can do more agile development, essentially just using Mondrian to convert MDX to sql.

I created a test case for SpringBoot / mvn and started using this page to copy the ROLAP ddl to Mondrian mapping scheme. I used the MondrianDaoSupport template from one of the comments here . There is also help on the same page regarding the required mvn dependencies (nb: I had to use jflex 1.4.1 with mondrian 3.2.0 to get past the NPE problem). Using SpringBoot with hsqldb, I create a ROLAP schema with data when the test runs. Then I insert the schema file as a class path resource, and Datasource is an automatically allocated hsqldb data source. So far, it has worked well - just a second to run the Junit test, which loads a fresh db with data and executes an MDX request. I expect some problems when trying to use more complex MDX queries, but let's see.