Hi,

This works:

with member [measures].[gbp3] as
'
[measures].[portvalue adjusted]/[measures].[rategbp]
'
member [measures].[rategbp] as
'
ValidMeasure( ([measures].[rate],[Currency].[All Currency].[USD].[GBP]) )
'
select
{[Zone and Portfolio].[Zone Mnemonic].[e]} on 0,
{[measures].[gbp3]} on 1
from [aum multi]
where [date].[2005]

while this provokes infinite recursion:

with member [Currency].[gbp3] as
'
[measures].[portvalue adjusted]/[measures].[rategbp]
'
member [measures].[rategbp] as
'
ValidMeasure( ([measures].[rate],[Currency].[All Currency].[USD].[GBP]) )
'
select
{[Zone and Portfolio].[Zone Mnemonic].[e]} on 0,
{[Currency].[gbp3]} on 1
from [aum multi]
where [date].[2005]

(Using ValidMeasure() because virtual cube with my 'main' data in first cube and
exchange rates [and dates and currency] in the second).

All I've done (when I break the code) is move the gbp3 member from dimenion
[measures] to dimension [Currency].

I have other currencies (and measure combinations) to populate and I most
certainly don't want them in [measures]. So how do I get them into [Currency ]?

pps. 119-121 of MDX Solutions recommends looking at crossjoin(); which I did;
but it hasn't helped so far.

I 'believe' infinite recursion to be a matter of 'inifintely pinging
back-and-forth between axes'. So if this is correct, I'm doubling up a dimesion
on both axes (?).

thanx.

pat

Is the [portvalue adjusted] measure calculated in some fashion and, if
so, does it refer to the current member of the [Currency] dimension -
which would cause recursion?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hello Deepak,

No [portvalue adjusted] has its own column and values in the fact table (of the
main cube [this a virtual cube]).

It would be nice if there were some way of dumping out diagnostics as to the
offending dimesion. (Was I correct in assuming that infinite recursion is
caused by the same dimension appearing on both axes?).

I 'seem' to remember that one could get some kind of extra info by modifying the
connect string in MDX Sample App. But maybe not the information I need here ...
Still I'll look through my notes.

pat


In article <exZ9KcpWFHA.4036 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Deepak Puri says...

Applied SQL 2000 SP4. Didn't help - still have infinite recursion.


In article <exZ9KcpWFHA.4036 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Deepak Puri says...

Hi Pat,

Based on your clarification, I think that I've reproduced a similar
recursion error for the Foodmart [Warehouse and Sales] cube (assuming
that your Currency dimension doesn't directly apply to your 'main' cube
data):

'[Measures].[Warehouse Sales]'

Select {[Store].[All Stores].[USA].[CA]} on 0,
{[Customers].[TestRecursion]} on 1
from [Warehouse and Sales]
where [Time].[1997]
The recursion error occurs here because the [Customers] context member:
[TestRecursion] causes the measure: [Warehouse Sales] to be evaluated in
the current context. This causes [TestRecursion] to be invoked again,
and so on.

Here, the recursion can be avoided by wrapping [Warehouse Sales] in
ValidMeasure(). This eliminates the [Customers] dimension from its
evaluation, since the main [Warehouse] cube itself does not include
[Customers]:

'ValidMeasure([Measures].[Warehouse Sales])'
If the [Currency] dimension does directly apply to [portvalue adjusted],
then explicitly specify the desired context for [Currency] in
[Measures].[gbp3], like:

'([Measures].[portvalue adjusted],
[Currency].[All Currency])
/[Measures].[rategbp]'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanx Deepak. Yes that worked.

Now I'll have to meditate upon why.

pat

In article <eD921N2WFHA.3100 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, Deepak Puri says...