Hello, I have one MDX that uses 4 nested SUM. It´s very slow and need to have
an advice for implement a better version of this:

The Dimensions:
TIME: ALL-YEAR-QUARTER-MONTH-DAY
ENTERPRISE ALL-ENTERPRISE-STORE
LINE ALL-COMERCIAL-LINE-SUBLINE-ARTICLE

The CORE:
At Line level, Day Level and Store Level must calc:
iif(
[Measures].[System Stock] < [Measures].[Real Stock ],
null,
[Measures].[System Stock] - [Measures].[Real Stock]
),

If is lower than LINE level
[Measures].[System Stock] - [Measures].[Real Stock]

If its higher than LINE level:
SUM


So I implement this MDX called [Measures].[Remaining]
----------------------------------------------------------------------------
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].[System Stock] < [Measures].[Real Stock ],
null,
[Measures].[System Stock] - [Measures].[Real Stock]
),
[Measures].[System Stock] - [Measures].[Real Stock]
)
)
),
Sum([Enterprise].CurrentMember.children)
),
Sum([Time].CurrentMember.children)
)


It´s VERY SLOW, but works. So please, need some advice of implement a better
version of this.
Thanks


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

if you don't specify a measure, the formula is a recursive one.

can you explain what do you want exactly?

its slow because you have a lot of comparisons to do!!!
for each date you compare if the member is the leaf one, if not you "drill
down" then test it again.
when you reach the lower level you test if the enterprise is the leaf
member, if not you "drill down" then test the date then the enterprise
again!!!!
there is too many tests, thousand of IIF comparisons... (365 days, 100
stores = 365 * 100 = 36 500 comparisons minimum!!!)

first try: go as quickly as possible at the lower level by using the
descendants function:
you are not at the DAY level and the STORE level, then go directly to these
levels by using the crossjoin + descendants functions.

try this: (look at the crossjoin, maybe a nonemptycrossjoin could help you
by eliminating empty cells in the calculation)
iif(
isleaf([Date].CurrentMember) and isleaf([Enterprise].CurrentMember),
descendants([Time].CurrentMember,, leaf )))
)

with nonemptycrossjoin:
Sum(nonemptycrossjoin(descendants([Enterprise].CurrentMember,, leaf ),
descendants([Time].CurrentMember,, leaf ), measures.[Real Stock],2),
measures.[Remaining])

second try, using internediate measures to minimize again the number of IIF
comparisons
if you have 1000 IIF(ISLEAF(...)) to evaluate its much slower then only 10
comparisons...

REMAINING1 = (recursive measure only for commercial linea)
iif(
[Line].CurrentMember is [Line].[Todas Linea].[Comercial],
Sum([Line].CurrentMember.children,[Measures].[Remaining1]),
iif(
[Line].CurrentMember.Level is [Line].[Line],
iif(
[Measures].[System Stock] < [Measures].[Real Stock ],
null,
[Measures].[System Stock] - [Measures].[Real Stock]
),
[Measures].[System Stock] - [Measures].[Real Stock]
)
)

REMAINING =
Sum(nonemptycrossjoin(descendants([Enterprise].CurrentMember,, leaf ),
descendants([Time].CurrentMember,, leaf ), {measures.[Real Stock]},2),
iif(
[Line].CurrentMember is [Line].[All Line],
Sum([Line].CurrentMember.children,[Measures].[Remaining1]),
[Measures].[Remaining1]
)
)

testing the leaf level is not required because you'll insure to go at the
leaf level!
nonemptycrossjoin insure that you'll sum only cells with values, empty cells
are not tested.
the recursive query is isolated, so ou no longer test the leaf levels of the
date & enterprise dimensions.
Hide the "Remaining1" measure, because the user can't use it.

Remember to aggregate your cube at the day, store & line levels.


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

EXCELLENT!

If I had a Hat, surely I would take off !!!

I had the same result with a delay of normal cubes. Neither 30 seconds(my
second try), or the worst 30 minutes (first try) NonemptyCrossJoin is very
useful as combinates filter, crossjoin and NonEmpty.

About the MDX you send, I have a little questions:

Remaining MDX
-----------------------
Sum(
NonEmptyCrossjoin(
descendants([Enterprise].CurrentMember,,leaves),
descendants([Time].CurrentMember,,leaves),
{[Measures].[Real Stock]},2
),
[Measures].[Remaining1]
)

in
descendants([Time].CurrentMember,,leaves),
when I use double Comma ,, I mean that is all Descendants? Only the leaf
level of descendants?

in
{[Measures].[Real Stock]},2
Why only Real stock, and not [System Stock] Only one meassure is needed? And
this number 2 means that they´re descendantsTime and descendatEnterprise?

Thanks you Jéjé! You are very good.


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

I recommand to use the [Measures].[Real Stock] is the nonemptycrossjoin to
eliminate Real stocks empty cells from the calculation.
the ",2" in the nonemptycrossjoin mean that I need to extract only the 2
first sets has the result of the nonemptycrossjoin.
In this case, the result is the combination of all days / stores where a
real stock value exists. If I replace ",2" by ",1" only the first set is
returned, so only the stores are returned.

descendants ,,LEAF (I'm not sure for the syntax because I don't have the BOL
in front of me) return the lowest level.
not just 2 "," but the leaf keyword is required, or you could use:
descendants(dimension.currentmember, dimension.lowestlevel)

but I found your calculation too heavy.
could you explain what is your target?

have you try to create a preaggregated cube?
imagine you create an SQL statement which return the day / store / line
information with the comparison & calculation (system vs real stock)
allready done.
Then you'll have a cube with everything calculated and you no longer need a
formula.
and the response time go under 1 sec.


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

Well, this MDX is quite heavy. Now with this modifies is lite.

The target of my cube was to Calculate the Remaining in an Inventory process.
An inventory Process needs to calc the remaining (at Line level) of products
(articles) As the hierarchy is
All - Commercial - Line - Subline - Article,

if in a store the SystemStock says must be 30 Adidas shoes, and I found 35, I
have then +5 in remaining
The same case of 20 Nike in SystemStock, and found 10 in RealStock (inventory)
I have -10 in reamining

At Line level (Shoes) I CAN SUM, so
Adidas (+5) + Nike (-10) = Shoes -5

If the difference is positive at line level, I don´t need it:
So T-Shirt (+4) transforms in T-Shirt (0)

When I see at Commercial Level, all Remainings for all lines must SUM
Shoes(-5) + coat(-4) = (-9) remaining

Why this? Because if I have (-10) coats, and have (+25) socks, then without
this opperation I´ll have that I have (+15) articles in this store. And the
Management for the Boss of a Store could show wrong information.

The other thing that I observed, was that as the formula is SystemStock (SS) -
RealStock (RS), when I consult at Enterprise level, it MIX all SS-RS values
in stores, so if I have

SS RS Remainig (Store1)
100 80 20

SS RS Remainig (Store2)
60 75 0

SS RS Remainig Enterprise (all stores)
160 155 5

And need the 20 + 0 = 20 remaining articles. The same case for Day lavel,
that Mix all days.


I have a final Calculation, that not posted name INDICATOR (IIC) that calcs:
Remaining / SystemStock
When you see data in Excel, you can select to show only one month (July for
example) and you see a sumarized column like AllYear (2005) and Allyears
(2004+2005)
I have then:

------------------July------- 2005
SystemStk 25894 25894
RealStock 25548 25548
Remaining 415 2385 <----Nottice this?
IIC(Indicator) 1.60% 9.21%

This is because the MDX of remaining always calcs for 2005, all remaining for
all childs (From January to September, including July) This is a feature
thata I never solved, in all my cubes. I don´t know if I must Alter
SystemStk to show for 2005 the ressult of the Sum of all months, or Try to
recalculate the remaining MDX to show only this 415 value. Don´t know if you
had the same problem using MsExcel of course.

Well, this is a not little explanation of what I did in my cube.
I don´t know too much about preaggregated Cubes. For that 3 things, I suppose
* I use Analysis Services Standard Version
* I have in Fact table 2 columns: Possitive and Negattive, that stores SS-RS
and RS-SS, when 0 then 0 in both cases, maybe can help this
* I have SQL and Business Intelligence 2005 June CTP, studio. So Maybe I can
work with precalculated there.

Thanks a lot Jéjé for your time. And thanks for contributing me for my
growing in Business Intelligence.


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

generally, in a stock system, the "year" display the last non empty value,
which is september in your case.
you can't display the July value (or the selected month value) for the year
2005 dynamically. (its not an Excel issue)

I think there is a lot of way to optimize this formula. but by a newsgroup
its difficult to see the overall expected results and all the cases.


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

About Stocks, maybe this is a translation problem, I speak Spanish. When
saying SystemStock I mean really: "The System tolds me how many units must be
in a store" The RealStock means "How much an inventory proccess really found
in a store"
The calcs in this business for a global indicator are: Sum(all Remainings) /
Sum(all SystemStock) so the SS_2005_total is needed, in stead of value of
September.

I have another cubes that use closing and opening periods, I understand what
you say.
The matter is that in this '?' cell I could put the remaining for selected
cells (like 415) or if i 2005 can put the sum of All_SS_stocks (400,000) so
the Indicator will not be distorsioned (must be 0.54%)

------------------July------- 2005
SystemStk 25894 25894
RealStock 25548 25548
Remaining 415 2385 <---- ?
IIC(Indicator) 1.60% 9.21%


Thanks a lot Jéjé, I´ll try this next step.


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