MDX problem in AS2005

When executing the following MDX statements I get an error with AS2005 although the query seems to be valid, at least AS2000 has no problems with it.

CREATE SET [Sales].[Y1] AS '{[Product].[All Products].[Drink]}'

CREATE MEMBER [Sales].[Product].[X] AS 'Sum([Sales].[Y1])'

SELECT Order({[Product].[X], [Y1]}, [Measures].[Unit Sales]) ON AXIS(0) FROM [Sales]

I create a set, a calculated member as the sum of this set, and then query both the calculated member and the set. The error is: "The dimension [Sales] was not found in the cube when the string, [Sales].[Y1], was parsed." It seems that [Y1] is not recognized as the formerly defined named set.

This query works if query scope is used (WITH SET, WITH MEMBER), or if you don't use the Order() function. It's always ok with AS2000.

The error occurs on every AS2005 cube I tested. The example above is the Foodmart 2000 database migrated to AS2005.

What's wrong?

Thanks. Guido.

Hi Guido,

I was able to reproduce this error in query scope with Adventure Works as follows:

>>

With Member [Product].[Product Categories].[X] as

Sum([Adventure Works].[Core Product Group],

[Measures].[Sales Amount])

select Order({[Product].[Product Categories].[X],

[Core Product Group]},

[Measures].[Sales Amount]) on 0

from [Adventure Works]

Query (2, 5) The dimension '[Adventure Works]' was not found in the cube when the string, [Adventure Works].[Core Product Group], was parsed.

>>

But this other version, without specifying the cube, works as expected:

>>

With Member [Product].[Product Categories].[X] as

Sum([Core Product Group],

[Measures].[Sales Amount])

select Order({[Product].[Product Categories].[X],

[Core Product Group]},

[Measures].[Sales Amount]) on 0

from [Adventure Works]

Accessories Clothing Components Bikes Touring Bikes Mountain Bikes Road Bikes X
$571,297.93 $1,777,840.84 $11,799,076.66 $66,302,381.56 $10,451,490.22 $26,492,684.38 $29,358,206.96 $204,429,800.41

>>

So I suspect the issue may be that AS 2005 is trying to cast a member to a set. But wouldn't this syntax for the calculated member work with both?

CREATE MEMBER [Sales].[Product].[X] AS 'Sum([Y1])'

|||

Thanks for your answer.

You are right. "Sum([V1])" solves this problem. I've included the cube name due to another problem:

CREATE SET [Sales].[Y1] AS '{}'
CREATE MEMBER [Sales].[Product].[X] AS 'Sum([Y1])'
DROP MEMBER [Sales].[Product].[X]
DROP SET [Sales].[Y1]

The last statement yields error message "Unable to drop the object 'Y1', because it is referenced by at least one another object."

No other calculations are involved, and the calculated member is dropped before the named set. If the calculated member is defined as "Sum([Sales].[Y1])" the named set is dropped correctly. There are no such problems with AS2000.

Any idea what's wrong?

Thanks

Guido

|||

Guido,

Unfortunately, I can't shed much light on this problem - I didn't work with CREATE/DROP in AS 2005, since I typically make changes to the MDX Script and redeploy. If you don't receive any response here, you could open an issue at:

http://lab.msdn.microsoft.com/ProductFeedback/.