Aggregations in SSAS
Anyone who is developing an SSAS solution for the first time may expect to see a lightning fast query response once the solution is deployed. When a cube is built, by default it does not contain any aggregation defined on measure groups. The query requests are satisfied by the partition data, and rhw data is aggregated based on the dimension attribute hierarchy or user hierarchy; query requests can take a while since no aggregation is in place.
Factors to consider during Designing Aggregations
Before the aggregation's design, the database has to be processed and partitions and dimensions should have data. Using aggregation design wizard aggregations can be created. Enter the correct member count in the aggregation's design wizard, even though wizard populates the count, cross verify the value since we can have multiple partitions in a measure group count can be incorrect. The correct count helps to find the aggregation cost. By default aggregation usage property is set to default, choice of correct aggregation usage property value is very important.
Default: Designer will decide the rule based on the dimension attribute
Full: Designer must include the dimension attribute
None: No aggregations for the dimension attribute
Unrestricted: Designer does not impose any restriction but dimension attribute must be evaluated to decide whether to include the attribute or not.
Monitoring Aggregations
Clear the SSAS database cache since the data requests can also be satisfied by the cache, to clear cache to use the below code:
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID> Adventure Works</DatabaseID>
</Object>
</ClearCache>
Connect to the SQL Server Management Studio (SSMS) ->Tools -> SQL Server Profiler. Select the default trace option and click on Run. Fire the MDX by opening the new query window so that aggregation can be hit by the query, Observe query processing and query events in the SQL Server Profiler, this would show data being extracted from the aggregation and the data request is satisfied within no time, in some cases aggregations may take some time where in designer might have created more aggregations. Before building the aggregations, using the SQL Server Profiler capture the statistics so that is can be compared later against the aggregation's statistics.
Why Aggregations?
When no aggregations are in place and MDX is fired, all the records in the partition are scanned to give the output. When aggregation is designed the data is not stored at the very detail level, say we have 100000 records in the partition, which may result in 1000 aggregations. If the aggregations store data for the in-between level in the hierarchy then the query requests are satisfied by aggregating the value for the higher levels. In this case, response using the aggregations is faster since it has to scan fewer records compare to partitions.
Can Anything and Everything be part of Aggregations?
Aggregations improve performance at a cost of the disk space and extra processing time. One should be cautious while deciding the aggregation strategy. Exclude unused attributes or rarely used attributes and set the aggregation usage property to None in the aggregation design wizard.
Usage Based Aggregations
SSAS has another option to create aggregations, that is Usage Based aggregations. It may happen that we want to build aggregations for the most used query requests then usage based aggregation is the best option, only useful aggregations can be created.
Threads answered on aggregations:
Aggregation Design Minimum Record Count
Usage Based Aggregations