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 2008</DatabaseID>
</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]:本文来源gao@daima#com搞(%代@#码网@*…
This means we can create aggregation for the attribute color.
转载请注明原文链接：对于aggregation 的A0,A1,A2
