Real Time BI - Possible with Mondrian?

[Heisenberg]

Hi folks,

we have the following requirement:

Changes done in a productive system should be mirrored to the analytic system asap. For example: A user creates a report for last month and figures out some data is missing. He looks up the data in the productive system and recognizes that some of the data has been added to the wrong month. He corrects the data and reruns the report. Of course he would expect to have the corrected data inside the report now. But usually ETL-Jobs are scheduled in nightly, maybe hourly manner.

So how to solve the problem?

1. Schedule the ETL-Job for every minute.
2. Make the Job lightweight => Use timestamps in the source-system to detect changes. Instead of rebuilding the whole fact table only update changed rows
3. Tell the user it takes the maximum of one minute for a change to be represented in the analytic system

The Problem?
The Cache of course. The mondrian cache can be flushed easily completely but when it comes to fine grained flushing it get's complicated*
I see two possible solutions:

1. Create a Servlet, which takes parameters from a HTTP-Request coming from the ETL-Job and executes updates on the Cube
2. Create some wicked Talend Component which does that job.

My questions?

1. Isn't there any wrapper-library for Mondrian Cache Control API?
2. Someone has experience with fine grained cache flushing and likes to share it?
3. What about all the other caches? Reporting / CDA / Saiku?

* =
http://mondrian.pentaho.com/api/mondrian/olap/CacheControl.html
http://wiki.pentaho.com/display/COM/...+the+BI+Server

[real_mym]

Hi, I have similar case. I would like to run Mondrian on top of constantly changing database.
In my case I can either switch the cache off (as my db is supposed to be very fast, one of those in-memory dbs) or I do not mind to write a bit of code and manage the cache via the Cache Control API.
But the other problem to consider is transaction handling. In order to produce correct results Mondrian needs to execute all calculations (db queries) in one transaction.
I think Julian Hyde has mentioned that issue once, but at that time he supposed that 95% of OLAP systems were read-only. At the same time he suggested to make transnational behavior configurable.
But that was long time ago, so it would be nice to know whether single-transaction logic is feasible in recent versions of Mondrian.

[real_mym]

Hi, I have a similar case. I would like to run Mondrian on a constantly changing database.
Cache control is one but you also need to consider transaction handling. Mondrian issues multiple SQL queries while calculating MDX results. To produce consistent results , all such queries have to be in be executed in one transaction.
Julian Hyde mentioned that problem once. But he also supposed that 95% of OLAP systems are "read-only". So he suggested to make transaction support optional and configurable.
It would be nice to know whether transaction handling is feasible in recent versions of Mondrian.

[Heisenberg]

Hi real_mym,

sadly I can't help you with the Transaction problem. Fo rme it looks like Mondrian just isn't the right tool to do analytics on a constantly changing database...
However, to handle the Cache in a more comfortable way I put some effort on Community Distributed Cache Plugin: https://github.com/webdetails/cdc/co...de1d10a28f6ba8, see method clearCellRegion().
Now it is possible to flush a specific Cache Region of Mondrian by providing two Dimension Members as parameters in a simple HTTP Request. The Crossjoin-Region of this two members will then get invalidated so it's not needed to rebuild the whole cache.

Kind Regards
Norman