The Aggregation is one for the most important thing for improving query performance in SSAS. You can create aggregation manually in Aggregation Tab --Advanced View. An aggregation is to the result of an SQL SELECT statement with a GROUP BY
The Aggregation is one for the most important thing for improving query performance in SSAS. You can create aggregation manually in Aggregation Tab -->Advanced View.
An aggregation is to the result of an SQL SELECT statement with a GROUP BY clause.
An aggregation for [product]. [color]
is (T-SQL):
select catalog, SUM(VALUE) FROM TABLE
GROUP BY color
For example, in sample cube [Adventure Works] (you may need to download sample cube since you ask question here frequently)
If you run the below query:
select [Measures].[Internet Order Quantity] on 0
, [Product].[Color].[Color] on 1
from [Adventure Works]
where [Date].[Calendar Year].&[2003]
Create a trace, then you will find the query scan the partition not the aggragation:
Started reading data from the 'Internet_Sales_2003' partition.
So, how to create a aggregation for this query? You need to switch to Advanced View -> select the aggration you created from the c design wizard and then in the grid
A0 A1 A2……
attribute
…
…
color *
…
Each column (A0..AN) is an aggregation. In column A0, unselect all the attributes, and then select the attribute color. After that, process the partition 'Internet_Sales_2003'. In SSMS, clear the caching through:
ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
Object>
DatabaseID>Adventure Works DW 2008DatabaseID>
Object>
ClearCache>
Execute the query:
select [Measures].[Internet Order Quantity] on 0
, [Product].[Color].[Color] on 1
from [Adventure Works]
where [Date].[Calendar Year].&[2003]
In the trace you created, you will find this:
Started reading data from the 'Aggregation 1' aggregation.
To optimize one query, you just need to see the event ‘Query Subcube Verbose’ in the trace, to find everything other than a zero by an attribute, and then create aggregation for that. For example, in above query we used:
Dimension 3 [Product] (0 0 0 * 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0) … [Color]:*…
This means we can create aggregation for the attribute color.