对于aggregation 的A0,A1,A2

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.