Usenet.com

www.Usenet.com

Group Index

Comp Thread Archive from Usenet.com

<-- __Chronological__ --> <-- __Thread__ -->

MDX question with roles on top



Dear all you OLAP/MDX gurus:

A very good morning to you all.

I have a very interesting situation here:

I have a Virtual Cube with 3 dimensions - Product, Geography and
Period.

My Geo dimension has 6 levels (inclduing All).

I have an MDX on the Period dimension called : 

[Mese attuale] that gives me the latest month and is defined as : 

SUM(TAIL([PERIOD].Levels([PERIOD].Levels.count - 1).Members, 1))

With a solve order 0

I have a MDX on Measures  called : 

[Sales Unit Market Share] that is defined as : 

iif(
        [PRODUCT].currentmember.level.Ordinal = 1, 100,
        IIf(
IsEmpty(([Measures].[SALES UNIT], ANCESTOR([PRODUCT].CURRENTMEMBER,
[PRODUCT].Levels(1)))) Or
               ([Measures].[SALES UNIT], ANCESTOR([PRODUCT].CURRENTMEMBER,
[PRODUCT].Levels(1))) = 0,
               0,
               ([Measures].[SALES UNIT] / ([Measures].[SALES UNIT],
ANCESTOR([PRODUCT].CURRENTMEMBER, [PRODUCT].Levels(1)))) * 100
)
)

With a solve order 102

I have a MDX on Measures  called : 

[Ratio MS national] that is defined as : 

iif(
        isempty ((Ancestor([GEOGRAPHY].CurrentMember,
[GEOGRAPHY].Levels(1)),[Measures].[Sales Unit Market Share])) or
        (Ancestor([GEOGRAPHY].CurrentMember,
[GEOGRAPHY].Levels(1)),[Measures].[Sales Unit Market Share]) = 0,
        0,
        [Measures].[Sales Unit Market Share] * 100 /
(Ancestor([GEOGRAPHY].CurrentMember,
[GEOGRAPHY].Levels(1)),[Measures].[Sales Unit Market Share])
)

With a solve order 230


I then run a query, selecting :

any product
[Mese attuale] on Period
[Ratio MS national] on Measures
and any member at level 4 on geo


Now when i set a role at the highest level of geography, the query
runs fine.

However, when I set a role at a lower level in geography, it sometimes
returns values and at some others (for that same combination) it
returns 'Infinite recursion error'.

So any views, comments, questions on that ?

Thanks in advance.

Suranjan.



<-- __Chronological__ --> <-- __Thread__ -->


Usenet.com



Please check out one of the premium Usenet Newsgroup Service Providers below for access to Usenet.