How to find the usage of aggregates in Reports
This blog will help us to find the number of times use of aggregates in BI reports.
The objective of aggregates is to reduce the volume of data per query being read. In fact aggregates are a new separate, transparent Info Cube which holds aggregated data. The user gets the improved performance without any intervention or knowledge if aggregates are being defined and created.
Aggregates can be created on navigational attributes, hierarchy levels and dimension characteristics. Aggregate tables cannot be created on hierarchies where the structure is time-dependent, and time-dependent navigational attributes of time-dependent data. Best results are gained for aggregates for external hierarchies and navigational attributes.
You can define a lot of aggregates for an Info Cube. However, when you create aggregates, you must always maintain a balance between cost and use. On the one hand, aggregates improve query performance, but on the other hand, they have a negative effect on the loading times due to roll-up and change runs. Optimal aggregate structure depends on how often a query is executed, how important performance is to the execution of a query, and how often transaction data arrives in the system. The questions of how up-to-date the master data and hierarchies have to be, and how often the change run is carried out (daily, weekly, monthly,) are also important.
Optimization of an Info Cube consists of several steps. Create the first aggregates after you have created the Info Cube and the queries, to ensure adequate performance for the first test. Continue to check whether additional aggregates are needed, or whether aggregates that you have created are no longer used at all. Delete aggregates that are no longer used to minimize maintenance costs.
Rules for Efficient Aggregates
- An aggregate must be considerably smaller than its source, meaning the Info Cube or the aggregate from which it was built. Aggregates that are not often affected by a change run have to be 10 times smaller than their source. Other aggregates have to be even smaller. The number of records contained in a filled aggregate is found in the “Records” column in the aggregates maintenance. The “Summarized Records (Mean Value)” column tells you how many records on average have to be read from the source, to create a record in the aggregate. Since the aggregate should be ten times smaller than its source, this number should be greater than ten.
- Delete aggregates that are no longer used, or that have not been used for a long time. The last time the aggregate was used is in the “Last Call” column, and the frequency of the calls is in the “Number of Calls” column. Do not delete the basic aggregates that you created to speed up the change run. Do not forget that particular aggregates might only not be used at particular times.
Based on this option we can estimate the unnecessary aggregates and can delete the un-used and less number of usage aggregates if required. So that it improves the query performance. We can find this option by “USAGE” in maintain aggregates screen or by using the t-code RSDDV by maintaining the aggregates.
The below screen shot will explain about the usage of aggregates before.
The below screen shot is after maintenance of the aggregates. The usage option is explaining the number of times use for this aggregate that is “1” in this case. Based on the usage we can decide the aggregates required or not?
