SQL Server Analysis Services Aggregation Designs
- Aggregations are pre calculated summaries of data from cells.
- Aggregations improve query response time by preparing answers before questions are asked.
- Ex:-When a data ware house fact table contains millions of vows, a query requisition the weekly sales totals for a particular product line can take a long time to answer if all the rows in the fact table have to be scanned and summed at query time to compete the answer.
However, response can be almost immediate if the summarization data to answer this queers has been pre calculated.
- Aggregations are stored in the multidimensional structure is cells at coordinates specified by the dimensions.
- Depends on storage mode [MOLAP, ROLAP, HOLAP–] and percentage of calculations.
- If 0% aggregations, storage less but query does full scanning and aggregations performed at cube.
Need of aggregations:
Fact table will more rows, to benefit from long running queries and instead of doing scanning at leaf levels, aggregations create some level of data, so query processing time will be reduce
What is an aggregation:
It’s a copy of data in your fact table, pre aggregate to a certain level
- Created when the cube is processed
- Stored on disk
[This result is similar to Group by statement in SQL Query]
- It makes queries fast because it means SSAS does not have to aggregate as much data at query time.
Aggregations created at processing time, building more aggregations takes more processing time.
Also increases disk space used by the Cube.
5 more aggregations added even though we have 4 values
When aggregations are used:
1) Aggregations are useful when the storage engine has to fetches the data from the disk.
2) Aggregations will not be used if the data is in the storage engine cache.
1) Each Measure group can have 0 or more aggregation design objects.
2) Each partition in group can have 0 or 1 aggregation design objects.
Aggregation Design methodology:
1) Run aggregation design wizard to build some aggregations
2) Perform usage based optimization for at least a few weeks, regularly.
Usage based optimization
Usage based optimization test you adjust the aggregation design for a measure group by analyzing the queries that have been submitted by client applications.
Aggregations tab → Design Aggregations → Next → Exclude (or) include required objects → Next → click count (Or) , manually enter the object counts →
Design aggregations until
Estimated storage reaches 20 MB
Name:-XX – design
Click start → Next → ʘ Deploy and process Now → Finish.
To see aggregations designed:
Aggregations → advanced view →
Measure group: Text – Fact Aggregation Design : XXDe.
Storing:<specify if required> Range: <Specify if required>
<Observe the aggregations created for each table>
Note: If there are no aggregations, default aggregation is “Grouping”
Enroll for Live Instructor Led Online MSBI Training