Why should I use aggregate tables? - Microstrategy

MicroStrategy uses optimized SQL to query the relational database directly to answer users’ questions. Users can therefore ask any question that is supported by the data in their warehouse and then analyze the results until they find their precise answer. The disadvantage to this relational OLAP (ROLAP) methodology is that accessing huge fact tables can be potentially time-consuming. Multidimensional OLAP (MOLAP) was considered by some to be the answer to this problem. However, it is not scalable for large projects because of the difficulty of maintaining every possible combination of aggregates as the number of attributes and the amount of data increases. MicroStrategy’s solution is the use of aggregate tables to provide quicker access to frequently-asked data while still retaining the power to answer any user query.

Some advantages of aggregate tables are as follows:

  • reduce input/output, CPU, RAM, and swapping requirements
  • eliminate the need to perform dynamic calculations
  • decrease the number of physical disk reads and the number of records that must be read to satisfy a query
  • minimize of the amount of data that must be aggregated and sorted at run time
  • move time-intensive calculations with complicated logic or significant computations into a batch routine from dynamic SQL executed at report run time

In summary, the MicroStrategy SQL Engine in combination with aggregate tables and caching can produce results at about the same speed as MOLAP. But the ability to answer questions on the fly is still retained, and the solution is scalable for large databases, unlike MOLAP.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Microstrategy Topics