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:

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.

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com