Problem with various Mondrian Caches, Sharing, Cache Control and Security

(See the post on the Pentaho Forum as well)

I (https://plus.google.com/u/0/100586999394126629414/posts, http://www.linkedin.com/profile/view...55&trk=tab_pro) have a schema with several cubes and virtual cubes defined. I have multiple roles defined in the schema that grant access to either all or part of the schema and various cubes, including, having a Role per Market area (about 1,000+ roles - which may or may not be a good idea). I have the cube being hit through both JPivot and through the XMLA servlet. I have ETL jobs that are updating new fact data every 15 minutes. The ETL job invokes an Xaction, via the Rest URL, to use the Cache control API from a JS step to attempt to clear the measure cache. I have found the following problems:

1. JPivot and the XMLA Servlet DO NOT share the same cache
2. The cache that gets accessed from the JS step using the cache control API in the XAction DOES NOT affect either the same cache that JPivot is using, nor the Cache that the XMLA Servlet is using
3. (Different users with different sets of roles using same or different caches? Roles assigned to the user do not appear to cause it to use separate caches. Is this right?)
4. Initial hit (after a complete initialization using the Packages.org.pentaho.platform.plugin.action.mondrian.catalog.MondrianCatalogHelper.getInstance().reInit(null); in the admin/clean_mondrian_schemacache.xaction) lto any of the above caches causes Mondrian to spend about 10 minutes doing a single SQL query for each an every Market Area member being granted in one or more of the Market Area roles. This happens whether or not the user logging in has any of those roles. And it happens regardless of whether the user is even attempting to do queries against the cube that those roles grant member access to.

Now, what I need to be able to do is to control refresh of the cache every 15 minutes without a complete "reinit" as in 4 above. Here is my cube definition:

(see attachment: RTC_GlobablIB_Sales.xml)


And here is the Xaction I am using to try to control the cache:

(see attachment: RefreshOEOS.xaction)

If I run a JPivot against the cube, mondrian spends about 10 minutes checking all the members for the roles. Since I have ~ there is 900 roles and 1,000+ members to check (this is probably not what I should be doing, but, there doesn't seem to be a much better way that doesn't involve udpating the cube schema mapping every time the market area assignments change). Then, if I hit the cube via a query to the XMLA servlet, it does another, independent cache intialization.

Now, when I run the Xaction to use the cache control API to cleare the measure cache, it again spends about 10 minutes initializing yet a third independent cache. It then politely does the measure clean-up on that, and runs the MDX included in the xaction to prime the cache. It has absolutely no effect on the cache that JPivot is accessing nor on the cache that the XMLA servlet is using.

If I run the clean-up Xaction again, it does not do the initialization again, but, shows that the cache is populated as I desired by the priming MDX from the previous run. The JS step executes and clears the cache and shows the cache cleared afterward. The prime MDX runs and shows the cache as primed. None of this affects the JPivot nor the XMLA cache.

In order to try to fix this, I did as suggested by Paul Stoellberger (of Saiku fame - https://plus.google.com/108993219027337899162/posts - thanks Paul) and tried to override MDXConnection with a created SharedCacheMDXConnection class (see attachments: SharedCacheMDXConnection.java and pentahoObjects.spring.xml). Though I was able to make Pentaho use this class, it did not seem to change anything with the above. JPivot, XMLA, and Pentaho Shared OLAP Connection (that the clear xaction uses) all seem to be different independent caches.

I've also, as suggested by Paul, installed the latest/greatest Saiku (as of this writing 2.4-SNAPSHOT available at, http://ci.analytical-labs.com/view/S.../saiku-plugin/) and after installing it (simply extract into the pentaho-solutions/system folder as saiku - make sure Pentaho BI Server is shutdown first) ran the "saiku-shareMondrian.sh" script (NOTE: If you are on Windows, you will need to have cygwin installed for this and run from a cygwin bash prompt). This made Saiku and the XMLA Servlet share the same cache (as far as I can tell), but, still JPivot and the Pentaho Sharedl OLAP Connection are independent caches.

So, after working with Paul, it seems that there is definitely an issue in Pentaho 4.5 CE where JPivot, XMLA, and Pentaho Shared OLAP Connection all use independent caches. Paul has agreed to take on the task of fixing this (http://jira.pentaho.com/browse/ANALYZER-726) . Also, Paul is fixing the issue that Saiku, when configured for cache sharing, using the "saiku-shareMondrian.sh", does not work correctly if the user has more than one mondrian role (union roles). Great!

Looking forward to seeing this fixed in Pentaho/Saiku! This should be great!