Learn how to use SQL Server , from beginner basics to advanaced techniques taught by industry Experts Enroll for free SQL Server Training Demo
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
[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.
Aggregations created at processing time, building more aggregations takes more processing time.
Also, increases disk space used by the Cube.
10cell 20cell 30cell
30cell 40cell 70cell
40cell 60cell 100cell
5 more aggregations added even though we have 4 values
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.
Aggregations → advanced view →
Measure group: Text – Fact Aggregation Design: XXDe.
|SSAS||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
Ravindra Savaram is a Content 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.