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.
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
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.
Storing: Range:
SSIS | Power BI |
SSRS | SharePoint |
SSAS | SQL Server DBA |
SCCM | BizTalk Server |
Team Foundation Server | BizTalk Server Administrator |
Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!
Name | Dates | |
---|---|---|
MSBI Training | Sep 26 to Oct 11 | View Details |
MSBI Training | Sep 30 to Oct 15 | View Details |
MSBI Training | Oct 03 to Oct 18 | View Details |
MSBI Training | Oct 07 to Oct 22 | View Details |
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.
1 / 15
Copyright © 2013 - 2023 MindMajix Technologies