Preload Mondrian Cache on Saiku analytics startup

Another strategy in Saiku/Mondrian to solve performance problems is to rely on its its cache. Mondrian library presents a cache layer in order to respond immediately if previous requests have built same agregations than asked; this cache is smarter than a simple SQL or MDX queries cache, it can detects whether same data is in its cache even if query to handle is not exactly the same as other previous requests.

Here we will improve a little bit this behaviour to avoid a common pitfall of cache usage; firsts users are always penalized because cache is empty. Because BI tools are accessed by a limited public, first users are often the only users, an already filled cache ready to use every morning can pleasure your mates as well as chocolate croissants.

Bad performance observation

As seen before there, preload cache has to be used only if it is necessary, take a look on this post to generate a situation where performances need to be improved.

You can also look in this previous post to follow a Saiku installation tutorial. I assume for the following that approximatively same installation.

Save slow queries 

Saiku Analytics provides a way to save queries. Once you find queries that are long a should need to be preloaded, save them by clicking on 'floppy disk' icon in main toolbar. This create a .saiku file in apache-tomcat-8.0.5/webapps/saiku/WEB-INF/classes/saiku-repository

Install and configure library to preload cache

No we will make saiku load this saved request on startup.

Download library saikuCachePreloader-1.0.jar , and place this jar into apache-tomcat-8.0.5/webapps/saiku/WEB-INF/lib.

Source code is available on https://github.com/GermainSIGETY/sauceDallas-saikuCachePreloader

To activate preload on startup, open apache-tomcat-8.0.5/webapps/saiku/WEB-INF/saiku-bean.xml and add theses lines (spring bean definitions) :

<!-- create another olapQueryBean that is not a 'session scoped' bean
(change the default behavior of initial olapQueryBean is a bad idea)-->
<bean id="olapQueryBean2" class="org.saiku.service.olap.OlapQueryService">
 <property name="olapDiscoverService" ref="olapDiscoverServiceBean" />
</bean>

<!-- Yet another clone of repositoryBean so that we can specify a different
path for saved queries to load at startup   -->
<bean id="repositoryBean2" class="org.saiku.web.rest.resources.BasicRepositoryResource">
 <property name="olapQueryService" ref="olapQueryBean2"/>
 <property name="path" value="res:saiku-repository" />
 <!-- <property name="path" value="/tmp"/> -->
</bean>

<bean id="mondrianCachePreloader" class="fr.saucedallas.MondrianCachePreloader">
 <property name="olapQueryService" ref="olapQueryBean2" />
 <property name="basicRepositoryResource" ref="repositoryBean2" />
</bean>

Important property here is 'path' for bean repositoryBean2: This indicates where to lookup requests to load on startup. Here it points in same repository as requests saved via Saiku user interface, which is WEB-INF/classes/saiku-repository. But you can specify another path, e.g  an absolute path on your os, and put saiku files on it.
Or a just a sub folder of WEB-INF/classes/saiku-repository.

See speedup

Restart Saiku, and perform a queries whose data should be loaded. Response time would be better.

Monitor preload

Add this appender in WEB-INF/classes/log4j.xml :

   <category name="fr.saucedallas">
     <priority value="DEBUG"/>
     <appender-ref ref="SAIKUCONSOLE"/>
   </category>

Requests preload will be logged in tomcat-dir/logs/saiku.log

Integrate with your data store update process.

Now you can organise a flow like this: your data store is updated every night, and just after this update, a restart of Saiku is scheduled, and then everything is ready to use for the day.