« Back

Embedded Analytics

General Blogs October 31, 2016 By Fabian Larroca

Build Around MDX

I cannot make the case to encourage you to write MDX. It's a complex query language, underutilized, supported by major vendors*, but after 20 years of existence still struggle to get traction, much like SQL.

But a query language adds an abstraction layer needed for embedded analytics, avoiding APIs nightmares, closed doors and impossible iframes.

MDX was originally used by Excel Pivot Tables to access remote SQL Server data. Getting support later from IBM Cognos, Oracle Hyperion, SAS, Microstrategy, SAP, and others, to reach a wider Excel user base. Now most of this vendors use their own portals for analytics. 

Pentaho's Mondrian project  is one of few open source OLAP Server alternatives to run MDX queries. Exploratory web applications like JPivot, Saiku and Pivot4j are great for advanced users, who can keep an eye on the underlying MDX. But a bunch of isolated charts and tables are not enough to convince users to explore, validate and take action.

Liferay can be a better platform for MDX. I will try to show the case in a modular and open source environment with no dependencies and minimum requirements, to respond last mile business questions.

On Liferay Marketplace

I already embed a Mondrian OLAP Server in a Liferay portlet, run MDX queries against XMLA servers using Olap4j, show results using JSON, DataTables, Select2, C3 charts and D3 visualizations, with drilldown, filter and search across, including an ACE editor with MDX highlighting and metadata autocomplete. But it's not enough.

See the app in the marketplace.

Who What Where When

Multidimensional models are built to respond questions about specific facts using measures, dimensions, hierarchies and levels. They have limitations, but can easily respond a wide range of when, what, who, where type of questions. For other questions, like how and why, the user have to make an additional effort to infer responses.

MDX queries are executed against multidimensional models to respond this type of questions. I can show results from the old and still awesome FoodMart model.


Last Mile Analysis

This portlets are focused on navigation and simple interactions, to communicate and bring data analysis exploration to a wider user base. They just present the results of MDX queries, using portal preferences and public render parameters to enable exploration.

But someone have to write and configure MDX queries. The ACE editor portlet is just a helper for advanced users to write MDX queries. Also someone has to configure and provide data sources.

And the rest of the journey is out of scope.

Big Path?

Mondrian is a JDBC wrapper and depends on SQL. Simple models are currently working with Apache Calcite Avatica for MongoDB. Apache Kylin already proved some parts for Hadoop.

Mondrian 4 OSGi Bundle?

The latest version of Mondrian already supports OSGi, but new features broke compatibility with previous models and has low adoption. Perhaps OSGI could support multiple versions and forks like GeoMondrian. But OSGi is also new to Liferay.

Mondrian cache compete with Liferay in the same virtual machine. For large data sets or complex environments Mondrian can be configured independent as remote XMLA server, without OSGi dependencies.


This is a work in progress, please request access to the private repository.

Threaded Replies Author Date
Great post Fabian! Can you please grant me... Kanwar Yuvraj Singh March 15, 2017 10:13 AM
What a great work Fabian, I like to ask you,... Harvey Tamayo April 18, 2017 2:41 PM

Great post Fabian!

Can you please grant me access on github - yuvraj11, I want to explore this more.
Posted on 3/15/17 10:13 AM.
What a great work Fabian,

I like to ask you, can you grant me acces on github? my user name is hntamayoc.

Thank you in advance.
Posted on 4/18/17 2:41 PM.