Hello, I have this MDX that takes too long for executing. I post 2 MDX: the
first that has a delay of 30 seconds one time, and the second that hasa a
delay o 30 minutes or more...

1ST MDX:
------------
iif(
isleaf([Date].CurrentMember),
iif(
[Line].CurrentMember is [Line].[All Line],
Sum([Line].CurrentMember.children,[Measures].[Remaining]),
iif(
[Line].CurrentMember is [Line].[Todas Linea].[Comercial],
Sum([Line].CurrentMember.children,[Measures].[Remaining]),
iif(
[Line].CurrentMember.Level is [Line].[Line],
iif(
[Measures].[Stock Sistem] < [Measures].[Stock Real],
null,
[Measures].[Stock Sistem] - [Measures].[Stock Real]
),
[Measures].[Stock Sistem] - [Measures].[Stock Real]
)
)
),
Sum([Time].CurrentMember.children)
)


2ND MDX
-------------

iif(
isleaf([Date].CurrentMember),
iif(
isleaf([Enterprise].CurrentMember),
iif(
[Line].CurrentMember is [Line].[All Line],
Sum([Line].CurrentMember.children,[Measures].[Remaining]),
iif(
[Line].CurrentMember is [Line].[Todas Linea].[Comercial],
Sum([Line].CurrentMember.children,[Measures].[Remaining]),
iif(
[Line].CurrentMember.Level is [Line].[Line],
iif(
[Measures].[Stock Sistem] < [Measures].[Stock Real],
null,
[Measures].[Stock Sistem] - [Measures].[Stock Real]
),
[Measures].[Stock Sistem] - [Measures].[Stock Real]
)
)
),
Sum([Enterprise].CurrentMember.children)
),
Sum([Time].CurrentMember.children)
)



MDX1 is a shorter version of MDX2. MDX2 has a TIME operation.

MDXs do:
At LINE dimension, when is LINE do an operation.
If is parent of LINE (Comercial) Sum all lines
if is grandparent of Line (All lines) Sum all parent of line (Sum Comercial)
As the data doesnt show correctly, except is DAY (Leaf TIME dimension) I
implement: if is Day then DO, else SUM.

All goes ok, but when in a day they are 2 enterprises have wrong results.

So I do the same trick and get MDX2 With this, if is LEAF (an Store) do this
MDX, if is parent (Enterprise) Sum stores.

The matter is that this consumes lot of time.

Can you help me?


--
Message posted via http://www.sqlmonster.com

does it a recursive measure?


"Andrew A via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

Hello Jéjé

This MDX do is not a recursive measure, but as it calcs the children level of
3 dimensions, takes too much time. The dimensions are Time, Enterprise and
Line.
Spend too much calculating this (maybe for including time dimension at level
of DAY)

Hope can give some advice with this MDX


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...-olap/200509/1

I solved this problem a bit.

I started with dimension LINE, then they´re 2 parents of LINE
They´re 25 stores in ENTERPRISE dimension
They´re approx 40 days in Time dimension

I suppose my MDX do the calc of LINE 25 * 50 = 1250 operations
Too much time.

Now I join in fact table days and now my LEAF node is MONTH
so I have 9 months.

25 * 9 = less time for me (approx 25 seconds for one step)

But the question is if this MDX can be optimized, maybe with isANCESTOR. So I
could have the DAYs as node back.

Thanks!


--
Message posted via http://www.sqlmonster.com