MS SQL Server OLAP - How to optimize Descendants(Dimension,,Leaves) ?

Author

How to optimize Descendants(Dimension,,Leaves) ?

Mahesh Shrestha

2005-11-22, 8:24 pm

Hi,

The MDX which includes the dimension having dynamic number of leves is
very very slow. For example, Sum(Descendants(Prod
ucts,,Leaves),
Measure.Cost) is taking about a minute to process. Is there any way to
optimize this query?

If a MDX has five Descendants(Products
,,Leaves) in it, is there any way
to store this set of leaf products for the first occurence and use it in
remaining four occurences?

Any suggestions or comments are greatly appreciated.

Thanks
Mahesh

*** Sent via Developersdex http://www.droptable.com ***

Deepak Puri

2005-11-22, 8:24 pm

Hi Mahesh,

In some situations, you can use inline set aliases to "cache" a set,
like for the Dead Stock logic:
[color=darkred]
Except(Descendants(& #91;Product],,LEAVES
) as StoredLeaves,
NonEmptyCrossJoin(St
oredLeaves,
{[Time].Lag(30):[Time].PrevMember}, 1))[color=darkred]

But when you say that " Sum(Descendants(Prod
ucts,,Leaves),
Measure.Cost) is taking about a minute to process", this may be due to
the time it takes to sum over that many leaves, rather than the time to
compute the set of leaves. So how many leaves are there, and can you
measure the time to simply list them, like with this query:
[color=darkred]
Select {} on columns,
Descendants(& #91;Product],,LEAVES
) on rows
from & #91;TheCube][color=d
arkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Mahesh Shrestha

2005-11-23, 9:24 am

Hi Deepak,

Thank you very much for all the suggestions.

As you said, Sum(Descendants(Prod
ucts,,Leaves), Measure.TotalQuantity))
was taking long, because of time taken to calculate
Measure.TotalQuantity and sum it up rather than finding all the leaf
products.

In my cube, each activity affecting inventory is recorded as - qunatity
if it is out and + quantity if it is in. If i need to find out my
current stock (as of last day in teh cube), i can simply get the
Measure.Quantity with Date.All. It is fast.

However, if i need to find out the stock for a particular date, then i
have to sum of my quantity from the Day 1 in my cube which is 1/Jan/2000
to that date. This is very slow. I have used calculated measure
Measure.TotalQuanity for this and it is calculated as

'SUM(PeriodsToDate([Date].[(All)],[Date].CurrentMember),[Measures].[Quan
tity])'

If i get the Measure.Quantity by giving only Date.CurrentMember, it will
give me the sum of quanitities of activities performed in that day
instead of total available stock.

Is there any way to optimize this method? Any help will be greatly
appreciated.

Thanks and Regards
Mahesh

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links