MQT vs Aggregate Tables vs Cubes
3 posters
Page 1 of 1
MQT vs Aggregate Tables vs Cubes
I want to move some aggregation out of reports. Which would be a better fit? I wanted to understand why i should go for one option as compared with the other.
dwcurious- Posts : 20
Join date : 2011-04-14
Re: MQT vs Aggregate Tables vs Cubes
They serve different purposes. MQT and aggregates are purely for performance and need proper strategy for maintenance in ETL. Cubes give you performance, consistency and rich features, but using MDX and trying to get aggregates out of cubes is not for the faint-of-heart. If you like challenge, go for cubes.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: MQT vs Aggregate Tables vs Cubes
I would use an MQT or aggregate table to improve performance of a report.
Aggregate tables are good when the table has history that can take a long time to recreate in an MQT and if the aggregations are built by removing dimension keys from the detailed table.
The MQT is better if it can be run in a relatively short time and if you need to roll up data to the middle of a hierarchy.
If the MQT takes to long to refresh from the source and it is rolling up the data to the middle of a hierachy, consider doing both. Create an aggregate table that is created by removing dimension keys and summing up the data and an MQT that uses the aggregate table and further aggregates the data to the middle of a hierachy.
Aggregate tables are good when the table has history that can take a long time to recreate in an MQT and if the aggregations are built by removing dimension keys from the detailed table.
The MQT is better if it can be run in a relatively short time and if you need to roll up data to the middle of a hierarchy.
If the MQT takes to long to refresh from the source and it is rolling up the data to the middle of a hierachy, consider doing both. Create an aggregate table that is created by removing dimension keys and summing up the data and an MQT that uses the aggregate table and further aggregates the data to the middle of a hierachy.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Similar topics
» Difference between Aggregate Fact Tables, Rollup Tables and OLAP Cubes ?
» Multiple Fact tables, Aggregate tables or a different approach
» Control Checks between Sql Tables (Facts and Dims) and SSAS Cubes?
» Too many aggregate tables ?!
» Same grain different aggregate tables
» Multiple Fact tables, Aggregate tables or a different approach
» Control Checks between Sql Tables (Facts and Dims) and SSAS Cubes?
» Too many aggregate tables ?!
» Same grain different aggregate tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum