A common problem in MicroStrategy that users face is the performance of reports. But with simple tuning techniques that the product provides this can be fixed in no time.
I would like to share with you the techniques that I have played around with.
First I would like to talk about the caching.
Caching allows for improved performance in response to report queries. As the cache gets stored in the memory, when repeated queries are made against the same reports the data is fetched faster.
Caching comes in different forms - Project/Report Level Caching and Template Caching
1. Caching can be enabled/disabled at project or report level. However the report level setting will override the project level setting. Using Project Configuration we can enable the caching else in report – using caching options we can enable or disable the caching. This type of caching is very useful only in cases where the data is preloaded in the database and there is no incremental refreshing of the database at any time of the user report run. For a data warehouse caching would help as against a OLTP ( Online Transaction Processing) system where data is frequently refreshed.
2. Templates can also be used for caching. Using a common template for developing common reports will help to fetch report data faster.
In projects I have worked on, we have implemented template level caching where in one template is built with common set of attributes and metrics and the reports are built by using the templates as shortcuts and in turn we cache the templates. Hence when one of the reports built from the template is run the entire data of template is run and gets cached in the memory and when other subsequent reports are run the report hits the cache and does not hit the database thus increasing the performance.
Next I would like to talk about intelligent cubes. This is a form of In-Memory cache.
Rather than returning data from the data warehouse for a single report, you can return sets of data from your data warehouse and save them directly to Intelligence Server memory. These sets of data can be shared as a single in-memory copy, to be used by many different reports created by multiple users.
These are gaining huge importance as they have several advantage over report cache as report caches expire or become invalid in certain circumstances.
A few instances are:
1. When there are changes made to the objects in the data warehouse, the existing caches may be configured so that they are no longer valid when hitting certain warehouse tables. Any further report execution will not hit the cache.
2. When the definition of an application object changes (such as report definition, report, report template, metric definition) the related report cache is marked invalid.
3. When there is need to control the growth of caches in the intelligence server memory, old caches need to be expired automatically.
Intelligent cubes can be refreshed on daily, weekly, quarterly monthly or yearly. In my past experience where I was working with a financial firm, we maintained cubes for different regions - Asia, EMEA and Tokyo. Also we developed cubes for daily and monthly data. Hitting against the database caused performance issues and having a single cube to store all data had its problems.
Also with recent versions of the tool, a new concept called incremental refreshing was introduced where in a cube loaded with 1 lakh records need not be refreshed just to insert or update a few row of records. With this new concept it was just sufficient to build a incremental refresh report with the required criteria to refresh the cube. For eg: If data was loaded for Region A at 9:00 AM from the database into the cube and data for Region B was available only at 12:00 PM then a incremental refresh report can be created on top of the cube with only one filter condition like Region = B and with the insert records option so that it does not override the existing data.