MicroStrategy - to cache or not to cache, that is the question

MicroStrategy is a Business Intelligence tool used by a range of companies in order to facilitate better business decisions through reporting, analysis and monitoring. One of our clients to whom we provide data analysis services uses MicroStrategy. Because of this, we were required to use MicroStrategy in order to deliver the reports and business insight. Having had first-hand experience of using MicroStrategy, we would like to share information on related business intelligence topics which will hopefully serve as a useful resource for interested readers. Today, we look at caching.

Caching in general is often deemed to be a good thing; it saves time and is less work for the system. However, within the context of business intelligence, is this always the case? The answer is it depends. The following table aims to offer some very high level guidance:

Data changes often

It is important to ensure your report is referring to valid data. One way you can ensure this is the case is by refreshing your caches either manually or by schedule.

Data inserted into the DB throughout the day, especially during working hours

If data is inserted throughout the day then a cached report reading from this data will not be up to date. Refreshing a cache during working hours can interrupt other users who may be executing the same report or performing related activities. Furthermore, if data is inserted regularly, it may not be practical refreshing the cache frequently. As a result, it is recommended that these reports are not cached.

Reports are run frequently

If reports are run frequently, caching saves repeated visits to the database allowing users to retrieve report results quicker.

Reports take a long time to run

Related to the last point, if a report is heavy and taking a long time to run, caching will speed up the report execution time considerably.

In summary, caching must be used with caution especially in environments where data is being updated on a regular basis. However, if used correctly, caching can serve as a great way to improve user productivity and user experience.