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

Question

I want to implement an application that provides support for MDX queries. For this purpose I would like to use one of libraries from pentaho mondrian (an open source OLTP server with the MDX interface) that transforms MDX queries into SQL of underlining database (based on a xml description), unfortunately I can not find any information which libraries I need to include -- and how to use them -- in my project to have MDX to SQL mapping working.

Does anybody have some experience in reusing mondrian components in her/his application?

Untuck

Hi, Were you able to do this? I am stuck with the same problem as well. – Biliary

Answer 1

I recommend downloading the latest 3.2.0 build of Mondrian, it is distributed with all of its dependencies. Also included in the distribution is an ivy file which describes its dependencies.

Latest Release: 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 be found on Pentaho's Website as well. Good luck!

Spruce

Could you write me which libs should I include in my project to use the transformation mechanism? – Untuck

Answer 2

Olap4j is part of pentaho mondrian now . Perhaps you can find information into 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 accessing multi-dimensional data.

olap4j is designed to be a common API for any OLAP server, so you can write an application on one OLAP server and easily switch it to another. And built on that API, there will be a growing collection of tools and components.

Ternate

-1, I would like to include "mapping" functionality in my application. It is clearly stated in my question. – Untuck

Answer 3

It looks like mondrian.rolap.agg.AggregationManager has some code related to it but its not straight forward.

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 end up here

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

But I am not so sure if mondrian generates only one SQL for an mdx. I think through XMLA (which most tools use as interface), you can request a drill through on a single axis, in which the complete query is not rewritten.

Hope this answers.

Biliary

Answer 4

I’m using Mondrian backed by hsqldb so that developers work against in-memory dbs, but then switch to non-Mondrian OLAP implementation when we deploy to real environment. Means we can do some more nimble development, essentially just using Mondrian to convert MDX to sql.

I have created a SpringBoot/mvn test case and used this page to get started – copying the ROLAP ddl and Mondrian mapping schema. I used the MondrianDaoSupport template from one of the comments here. Same page also has some help on mvn dependencies required (nb: i had to use jflex 1.4.1 with mondrian 3.2.0 in order to get past a NPE issue). Using SpringBoot with hsqldb i create the ROLAP schema with data on test start up. Then I inject the schema file as a classpath resource, and Datasource is then the autowired hsqldb datasource. It’s working well so far – taking just ~second to run Junit test that bootstraps a fresh db with data and executes an MDX query. I expect some problems when trying to use more complex MDX queries – but let’s see.

Rech