I want to improve the performance of a calculated member since it
takes about 20 to be opened by Excel. My calculated member is code as

Sum(
Crossjoin(Descendants([Allocation Method],[Allocation
Method].[Allocation Code]) , Descendants([Product].[Size by
Channel],[Product].[Size by Channel].[SKU])),


(

([Measures].[Amount],[Customer].[Channel Channel].[All
Customer],[Customer].[Channel SD].[All Customer])

*
iif((ancestor([Allocation Method],[Allocation Method].[Allocation
Type]).name <> "H" and
ancestor([Allocation Method],[Allocation Method].[Allocation
Type]).name <> "K") ,0,
([Measures].[Percent Allocate],[Function].[All Function])
)
))

If I use other OLAP tools, will they provide a better performanace? Or
what should I do ti improve it.

Thanks,

Anote

Hi Anote

The following may run a little faster (and you should check it returns the correct results, because I don't know enough about your cube to say that it will)

Sum
NonEmptyCrossjoin
{Descendants([Allocation Method].CURRENTMEMBER,[Allocation Method].[Allocation Code])}
, {Descendants([Product].[Size by Channel].CURRENTMEMBER,[Product].[Size by Channel].[SKU])}
,{[Customer].[Channel Channel].[All Customer]
,{[Customer].[Channel SD].[All Customer]
,2
, ([Measures].[Amount],[Customer].[Channel Channel].[All Customer],[Customer].[Channel SD].[All Customer]

iif
/*I assume that the relevant member names are [Allocation Method].[H] and [Allocation Method].[K] here, otherwise you will need to change them*
NOT(ancestor([Allocation Method].CURRENTMEMBER,[Allocation Method].[Allocation Type]) IS [Allocation Method].[H])
an
NOT(ancestor([Allocation Method].CURRENTMEMBER,[Allocation Method].[Allocation Type]) IS [Allocation Method].[K])
,0
([Measures].[Percent Allocate],[Function].[All Function]

)

The big thing to notice is that using NONEMPTYCROSSJOIN to remove empty tuples from the set you're summing up may drastically reduce the amount of summing you need to do, and therefore speed things up. If you give us some more details about the structure of your dimensions, then further optimisations may be possible..

You will also find that with this kind of calculation, having the right aggregations built could do wonders. To understand how to do this I would recommend you read the Analysis Services Performance Guide a
http://www.microsoft.com/technet/pro...n/ansvcspg.msp
From the above MDX it's clear that any aggregations built above the levels [Allocation Method].[Allocation Code] and [Product].[Size by Channel].[SKU] will be useless for this calculation, and depending on the structure of your dimensions it might be that Analysis Manager isn't actually building any useful aggregations for you. If you haven't already done so, try Usage Based Optimisation; otherwise you might have to build some aggregations manually with Partition Manager. Again, the more you tell us about your cube the more help we can be here

Regards

Chri

----- Anote Sangkunakupt wrote: ----

I want to improve the performance of a calculated member since i
takes about 20 to be opened by Excel. My calculated member is code a

Sum
Crossjoin(Descendants([Allocation Method],[Allocatio
Method].[Allocation Code]) , Descendants([Product].[Size b
Channel],[Product].[Size by Channel].[SKU]))




([Measures].[Amount],[Customer].[Channel Channel].[Al
Customer],[Customer].[Channel SD].[All Customer]


iif((ancestor([Allocation Method],[Allocation Method].[Allocatio
Type]).name <> "H" an
ancestor([Allocation Method],[Allocation Method].[Allocatio
Type]).name <> "K") ,0
([Measures].[Percent Allocate],[Function].[All Function]

)

If I use other OLAP tools, will they provide a better performanace? O
what should I do ti improve it

Thanks

Anot