Home  >  Blog  >   MSBI

SQL Server Analysis Services Aggregation Designs

Rating: 5
  
 
4925
  1. Share:
MSBI Articles

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 of 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.

Learn how to use SQL Server , from beginner basics to advanaced techniques taught by industry Experts Enroll for free SQL Server Training Demo

However, a response can be almost immediate if the summarization data to answer this queer has been pre-calculated.
  • 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.

 

aggregation design wizard

The need for 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 reduced

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 a statement in SQL Query]

It makes queries fast because it means SSAS does not have to aggregate as much data at query time.

 MindMajix YouTube Channel

Drawbacks

Aggregations created at processing time, building more aggregations takes more processing time.

Also, increases disk space used by the Cube.

Ex:

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

 
When aggregations are used:
  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.
Aggregated Designs:
  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.
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.

Navigations:

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:                    Range:

 
Note: If there are no aggregations, default aggregation is “Grouping” 
 
Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

List of Related Microsoft Certification Courses:

 SSIS Power BI
 SSRS SharePoint
 SSAS SQL Server DBA
 SCCM BizTalk Server
 Team Foundation Server BizTalk Server Administrator

 

 

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
MSBI TrainingMar 23 to Apr 07View Details
MSBI TrainingMar 26 to Apr 10View Details
MSBI TrainingMar 30 to Apr 14View Details
MSBI TrainingApr 02 to Apr 17View Details
Last updated: 03 Apr 2023
About Author

Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

read more
Recommended Courses

1 / 15