During our 10+ years of work with MicroStrategy we often encountered overlapping cube and cache questions and what are the best practices in order to manage them. We decided to gather them in one placing using our experience and MicroStrategy materials.

Cache management best practices

Caching is the retention of a recently used result set in memory for improving query response time in future requests. Caching enables users to retrieve results from memory/files rather than executing queries against a data source.

• Cache generation best practices
  • Disable caching for real-time reports

Real-time reports need to show the latest data, with cache, they would show outdated data. This can be set on the report level

• • Enable caching for frequently used reports

This will improve time response and reduce database workload. If most of the reports are frequently used reports then it is worth to set this option on the project level and if needed disable it on the specific report level.

• • Use subscriptions to generate caches for frequently used reports

This is especially useful for report used by a lot of users or C-level executives. This will prevent first execution user from waiting the database retrieval.

• • Enable dossier/ document caches

This will improve response time for dossier/documents. It can be done on the project level with Developer and MicroStategy Workstation.

• • Allocate memory for report and document caches properly

This setting needs to be at least the size of the largest cache file, or the largest report/document caches will not be used

• • Enable cache per database login when using database authentication

This will ensure that users who execute their reports using different database login IDs cannot use the same cache. This is specially important if there are different databases for, for example, different countries and the user should not be able to see caches from different countries.

After making these changes, please remember to Monitor the storage space for result cache files. At least 10% of the drive should always be free. This prevents Intelligence Sever machine from running out of disk space.

• Cache cleanup best practices
  • Define cache duration period according to project characteristics

This will remove old caches and after this new caches can be generated.

• • Schedule invalidate caches task to be executed after warehouse data loads

This will prevent reports from showing outdated data. The cache cleanup event should be triggered from the DB and the it should trigger MSTR schedule, which will invalidate caches.

• • Schedule purge element caches task for attributes that change frequently

This will prevent element prompts from missing recent added elements.

• • Set cache lookup cleanup frequency to once a day

This reduces the amount of memory that the CacheLkUp.idx file consumes (cache matching information) and the time that it takes to back up the lookup table to disk. Default value is 0 (at server shutdown).

Cube management best practices

Intelligent Cubes allow multiple reports to retrieve data from a single shared in-memory set of data for faster response times. Intelligent Cubes are fully scalable, limiting excessive data consumption and redundant data by allowing you to build only the sets of data you require. There are two types of cubes, OLAP and MTDI (Multi-table Data Import). Both types of cubes leverage the partitioning functionality that allows loading more data to memory and querying this data in a parallel fashion leveraging multi-core hardware architectures.

• OLAP cubes best practices OLAP cubes, or the traditional Intelligent Cubes have existed since 9.x releases. OLAP cubes are
  authored through Developer and are tightly coupled to a MicroStrategy project schema. Because
  of this, OLAP cubes are traditionally built and maintained by advanced BI developers or
  architects.
  • Check the number of CPU cores when using cube partitions

The number of partitions should be less than or equal to the number of processor cores on your Intelligence Server machine.

• • Create and schedule incremental reports when the cube can be partially updated based on attributes

This reduces the time and system resources necessary to update the cube periodically.

• • Use automated tasks when the whole cube should be refreshed periodically

It will benefit in automation of administrative tasks and better use of system resources. They can be scheduled using Administrative tasks, command manager or event can be triggered from the DB when the ETL is done so the cube is always up to date. Big cubes (that take a lot of time and resources to publish) should be scheduled during the off peak hours.

• • Check VLDB data population options when dealing with large cubes and limited time window for publishing them

The way the data is normalized in cubes affects memory usage and processing time. Check which option best fits resources usage.

• • Use Cube Advisor tool to leverage the use of dynamic sourcing feature

Yes! This tool, even if “archaic” and not update is still useful! Dynamic sourcing can improve performance by enabling reports to automatically hit an available cube if it contains all the required information. Cube Advisor checks if existing reports can use dynamic sourcing.

• • Check the features that prevent reports from using dynamic sourcing

Reports with consolidations, custom groups and other features cannot benefit from dynamic sourcing. Design reports accordingly.

• MTDI cubes best practices

MTDI (Multi-Table Data Import) cubes have a “local” self-contained schema, containing a set of
tables. The corresponding attributes and metrics in this local schema only represent the columns
in these tables. Cube publication for MTDI consists of loading data from multiple tables in
parallel.

• • Define number of low priority connections appropriately

Data Import cube publishing jobs are initiated as low priority job.

• • Define maximum quota for data import per user for each project and custom quota for specific users if needed

Improvement of resources management.

• • Set maximum parallel queries per report appropriately

This VLDB setting can improve the speed of large MTDI cubes publication.

• • Define attribute and number of partitions when editing large MTDI cubes in MicroStrategy Web

This can improve publishing performance. The number of partitions should be less than or equal to the number of processor cores.

To write this article we used our experience and supported it with some MSTR materials.