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.