1. Background
The purpose of this document is to provide details on importance of aggregation, how effectively we can design aggregations, performance monitoring & usage based aggregation.
2. What is Aggregation and why build aggregation
The aggregations are the high level summaries of data. It is analogous to calculated value by GROUP BY statements of T-SQL with some attributes. SQL Server pre-aggregates the measures with respect to dimension attributes at some extent instead of executing the query runtime. Pre-aggregation improve the query performance.
Following are the reasons to build the aggregation:
I. Performance is the top priority of all the OLAP system and having aggregated data will helpful to easily retrieve the data. e.g. if we aggregate the yearly data based on days in a year. Cube has to aggregate a large no. of columns. In place of days wise, if we have aggregated data up to months or quarter, we will get yearly aggregated data more quickly.
II. Aggregations improve query performance by limiting the amount of partition data that needs to be scanned to answer a query. Instead of querying the fact level data of the partition, SSAS will query the aggregation and because the amount of data in the aggregation is smaller, the query can be answered more succinctly
3. How to do logging and Performance monitoring
1. To enable the logging, go to analysis service instance where your cube exists, Right click on the server èProperties
By default, Analysis Services does not log queries into the query log table. To log queries, you need to set Analysis Services properties. Change the below properties to enable logging:
I. Create QueryLog table è True will create a table named OLAPqueryLog in DB based on connection string in QueryLogConnectionstring.
II. QueryLogConnectionstring will contain the ConnectionString e.g Server name & DB name to create log table
III. QueryLog sampling: The default value for this property is 10, meaning that 1 out of every 10 server queries is logged. I set to 1 to log all the queries hit to cube.
2. To Check whether table is created or not, connect the OLTP server & DB based on ConnectionString provided and check whether table is available or not:
Table is created.
3. Browse the cube and check whether query information is logging into table or not. Start the SQL server profiler also to see the query performance:
Checking the logging information:
I have browsed the cube for End Customer revenue fact and logging information with fact name & query execution duration is properly logged in the OlapQueryLog table.
By using OLAPQueryLog table, we can get information about facts on which users are mostly interested and frequently called facts
4. Check the profiler for the logged information:
4. How to design aggregation
Based on the above statistical data, if we feel that query is taking time and performance of the query can be improved. In this case, we can go for the designing aggregation for Fact EndCustomerRevenue.
I. For designing the aggregation, open the cube solution and go to Aggregation tabè End Customer Revenue fact. Here it is showing ‘unassigned aggregation design’, it means there is explicitly aggregation designed for this fact.
II. Select ‘Design Aggregations’ to design aggregation. It will start the aggregation design wizard
Click next
III. It will open Review Aggregation Usage window. Here if we want to exclude any dimension attributes from aggregation, we can remove it to avoid unnecessary aggregation.
The options are Default, Full, None, and Unrestricted. Full means that the attribute must be included in every aggregation, while None means that the attribute will not be in any aggregation. Default means that the SSAS engine will examine the attribute and associated relationships and try to determine whether or not to include that attribute in aggregations using a set of internal rules. These rules are very restrictive and a lot of attributes get excluded from consideration. Unrestricted means that the attribute will be considered without regard to the internal set of rules. For the first creation of aggregations, I just leave all attributes set to Default. We will keep analyze our cube and accordingly will change the usage configuration of the aggregation. Click next
IV. It will open the object count window, either we can enter the estimated count value or wizard will automatically count the row. Entering count values manually will be good in case of bulk amount of data because wizard may take lot of time to count. While specifying the value manually, make sure value should be nearer to exact count because the Aggregation Design Wizard uses the object counts to estimate storage requirements.
Click on count to get the estimated count
V. Next is Aggregation option. It will provide an option to design aggregation with respect to storage space or performance point of view.
In designing our cube, you will have to balance the storage needs of the aggregation tables against the speed and performance of the queries. The three approaches to achieving this balance are as follows:
· Set the storage size and let Microsoft® SQL Server Analysis Services determine which aggregations to store. This approach works well when you have limited storage space.
· Set the percentage of performance gain and let the necessary aggregation tables take as much storage space as they need.
· I click Stop: Manually determine the best balance by watching the progress of the Performance vs. Size graph.
I am selecting Performance gain because for many applications, performance is more important as compared to storage space and Cube processing time.
VI. Click start to design aggregation
It creates 274 aggregations with occupying 446.5MB disk space. Save the aggregation name. We can either deploy the changes now or we can save only. Click finish.
VII. In the Cube aggregation tab, we can see the designed aggregation:
5. Monitor the performance of the cube after aggregation design
Deploy the designed aggregation and check the query performance by using profiler:
Fact End Customer Revenue:
Fig showing query execution duration before designing aggregation- 7952 Secs
Fig showing query execution duration after designing aggregation- 4000 Secs
Now the same query that we executed in section 3.4 taking only 4000 seconds
Net New Quota
Fig showing the query execution duration before designing aggregation- 1484 secs
Fig showing the query execution duration after designing aggregation- 1484 secs
True up
Fig showing the query execution duration before designing aggregation- 1344
Fig showing the query execution duration after designing aggregation- 953
6. Usage based optimization
The problem with the Aggregation Design Wizard is that it assumes all queries are equallylikely. Therefore, some of the aggregations it designs are likely to be useful, but some will not be. More importantly, there will be aggregations you need that are not created by the Aggregation design wizard. To create those aggregations that are required but aggregation wizard does not created, we can use usage based optimization.
Click on the advance view under aggregation view:
It will display the aggregation designed with respect to dimension attributes:
Based on the logging information, if we found that we need some more aggregation then we can check the box against the associated dimension attributes. If we feel some of the aggregations are not required and wizard is creating unnecessary, in this case, we can uncheck it. Removing unnecessary aggregations will helpful to manage disk space for the partitions as well as it reduce the cube process time.
7. Disadvantages of more no. of aggregations
i. Storage space required for the aggregated table is more.
ii. Processing time for the cube is more.
8. Conclusion
By using the above steps, we can understand how to design aggregation to improve cube query performance and based on user’s action, we can do optimize the aggregation to provide best performance and adequate disk storage to the cube.
----------------------------------------End of Document---------------------------------------------
