Aggregation Design
-
Aggregations are precalculated summaries of data from cells.
-
Aggregations improve query response time by preparing answers before questions are asked.
-
Ex:-When a data warehouse 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 for the answer.
-
Aggregations are stored in the multi-dimensional 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 the cube.

-
Created when the cube is processed
-
Stored on disk
It makes queries fast because it means SSAS does not have to aggregate as much data at query time.
Drawbacks:
Aggregations created at processing time, building more aggregations takes more processing time.
Also, increases disk space used by the Cube.
Ex:
Subscribe to our youtube channel to get new updates..!
Database Explosion
10 20
30 40
10cell 20cell 30cell
30cell 40cell 70cell
40cell 60cell 100cell
5 more aggregations added even though we have 4 values
1) Aggregations are useful when the storage engine has to fetch 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 a group can have 0 or 1 aggregation design objects.
1) Run aggregation design wizard to build some aggregations
2) Perform usage-based optimization for at least a few weeks, regularly.
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.
Aggregations → advanced view →
Measure group: Text – Fact Aggregation Design: XXDe.
Storing: Range:
List of Related Microsoft Certification Courses:
SSIS | Power BI |
SSRS | SharePoint |
SSAS | SQL Server DBA |
SCCM | BizTalk Server |
Team Foundation Server | BizTalk Server Administrator |