Today, I'l talk about what Aggregate Tables are and identify some effective use cases. Ultimately, the use of Aggregate Tables is a balance between pushing the calculations from Run Time to ETL, and their structures and quantity will depend on your scenarios and platforms. One thing I can promise you though is that if you aren't using Aggregate Tables today, you're missing out on huge performance gains.
MicroStrategy is aggregate aware which means that it will always choose the best table to run the report from. The tricky part is that the definition of "the best table" isn't really intuitive. MicroStrategy uses a ranking algorithm to score every table in the system. This algorithm actually has nothing to do with the number of rows in the table, and instead is based on the sum of the cardinality of each attribute in the table. The cardinality is calculated by taking each attribute's place in the hierarchy, where the top level attribute is 1 and the bottom level attribute is X (where X is the number of attributes between itself and the top).
Year -> Month -> Day
1 -> 2 -> 3
A table that contains Year, Revenue would have a value of 1.
A table that contains Month, Revenue would have a value of 2.
A table that contains Day, Revenue would have a value of 3.
MicroStrategy will choose the table with the lowest value that can resolve the query.The total count of all Attributes (Facts don't count) is called the Logical Size of the Table. At run time, MicroStrategy will look for tables that can answer the query. If more than one are discovered, the one with the lowest Logical Size is chosen.
Say you have the following schema:
Your Detail_Fact_Table may be pretty large, so you may want to introduce a City/Revenue aggregate table. All you need to do is add that table to your database:
Add it to your project (via Warehouse Catalog or Architect tool) and map the City attribute to it. Now if you run a report with City and Revenue, it'll come out of the City_Agg_Table. If you drill down to Zip_Code, it will now come out of Detail_Fact_Table. MicroStrategy will always choose the best table to use. The best part is that you can introduce Aggregate Tables when you need them. If you didn't need or consider them at the start of the project, you can simply add this new table to the schema and everything you've ever built can now use it!
There may be situations where you'll want to manually set the order that tables should be used. For this, you can override the Logical Size yourself by editing the table and changing the value at the bottom. Be sure to check the box "Preserve Logical Size" because the Logical Size is recalculated every time you update the schema. You may want to do this if your Aggregate Table isn't a true aggregate. For example, maybe it only contains a subset of data. MicroStrategy always assumes Aggregate Tables contain complete roll ups of each other. So if you have 10yrs of data in your Detial and 2yrs of data in your Aggregate, then MicroStrategy isn't going to know that it needs to run a 5yr old query out of the Detail table instead of the Aggregate, so structure your tables carefully. (By the way, to accomplish that last scenario, you would want to use Metadata Partitioning, which I'll talk about in a future post).