Hi

I have a rather interesting problem with MDX queries and want to know if
there is any way that this can be resolved or is it a known issue. If it is
can anyone point me to any documentation about it?

I am generating a member in one dimension based on another dimensions
property.
MEMBER [DIM 1].[Attr. Hier 1].[Mem Name] AS
STRTOMEMBER("[DIM 1].[Attr. Hier 1].[" +
[DIM 2].CurrentMember.Properties("Attribute
Property")
+ "]")

The problem I have is that this generated member will only return valid data
if it is first generated as a set.
Either
SET [X] AS StrToMember...
and then
MEMBER .. AS [X].Item(0)

Or I need to query it directly in the Rows / Columns. So
SELECT ... ON COLUMNS,
CROSSJOIN(STRTOMEMBER.....
,{[Dim 3]....}) On ROWS
FROM....
WHERE ....

Any other attempt to use the generated member returns no results in the query.

Thanks

Hi Mike,

I'm not sure I understand exactly what's going on here (can you provide an
example with Adventure Works?) but is it the following?

If I run the following query on Adventure Works:

WITH
MEMBER [Date].[Day Name].TEST AS
STRTOMEMBER("[Geography].[Country].&[Australia]")
SELECT
[Date].[Day Name].TEST
ON 0
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])

I get a null value. However, if I modify it as follows:

WITH
MEMBER [Date].[Day Name].TEST AS
STRTOTUPLE("
([Geography].[Country].&[Australia],[Date].[Day Name].[All Periods] )
")
SELECT
[Date].[Day Name].TEST
ON 0
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])

I get the value I expect. What I've done is instead of use STRTOMEMBER, I've
used STRTOTUPLE and included the All Member from the attribute hierarchy I've
created the calculated member on.

Is this your problem?

Chris


--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Mike" wrote:

Hi Chris

Thanks for your response. I have managed to find the problem with what I was
trying to do and as yet have not been able to solve it other than through the
use of a set operation. In short I was looking for some sort of precedence
that queries are run in.

What I was trying to do in my example - which is a bit difficult in
Adventure Works - is trying to get a member in one dimension based on the key
in another. So the closest I could think of in AW is trying to get the DATE
key based on the Shipping or Order date key. This is a bit short of what we
are trying to do as these dimensions are simply alias's of the one dimension
so keys are all the same.

eg: WITH MEMBER [Date].[ShipDate] AS STRTOMEMBER("[Date].[Day].[" + [Ship
Date].[Day].CurrentMember.Properties("Name") + "]")

SELECT [Date].[ShipDate] ON 0,
[Measures].[Internet sales amount] ON 1
FROM [Adventure works]
WHERE([Order Date].[Day].&[20050201])

I haven't' tested this but I assume it would work. The issue I have is that
later I am trying to use the another measure across a set of what in this
case is the [Order Date] so the member gets re-evaluated at each point.

ie in Above Add: MEMBER [Measures].[Month sales] AS
SUM({[Order Date].[Day].&[20050201],
[Order Date].[Day].&[20050202],
[Order Date].[Day].&[20050203],
[Order Date].[Day].&[20050204],
[Order Date].[Day].&[20050205]}, [Measures].[Internet sales amount])

Place this measure on columns, The countries on Rows and then the calculated
[DATE] member in the Where clause and you get nothing:

ie

WITH MEMBER [Date].[ShipDate] AS (See Above)

MEMBER [Measures].[Month sales] AS (See Above)

SELECT [Measures].[Month sales] ON 0,
[Geography].[Country].Members ON 1
FROM [Adventure Works]
WHERE ([Date].[ShipDate])

Will return null.

However if we changed the syntax for [Date].[Shipdate] to the following

WITH SET [shipping] AS STRTOMEMBER("[Date].[Day].[" + [Ship
Date].[Day].CurrentMember.Properties("Name") + "]")

MEMBER [Date].[ShipDate] AS [shipping].ITEM(0)

The query will return what we want. This all comes out of the fact that the
set is defined once at the start of the query and the member is evaluated for
EVERY step of the calculation.

All that said my problem remains that I have to create a member that is only
defined once and I will let you know how I get to that result as I need to
use the calculations in an UPDATE statement which for some reason doesn't
like the WITH syntax to occur before it.

Thanks again for your help
Mike


"Chris Webb" wrote:

There's certainly something very strange going on here...

Although I still don't understand completely what's going on in the set of
queries in my last post, I have come up with a working example of what I
think you want to do in Adventure Works:

WITH
MEMBER [Ship Date].[Day Name].TEST AS
STRTOMEMBER("[Ship Date].[Day Name].&[" + [Date].[Day
Name].CURRENTMEMBER.PROPERTIES("KEY") + "]")
MEMBER MEASURES.MONTHSALES AS
SUM(
{[Date].[Month of Year].&[1], [Date].[Month of Year].&[2]}
, [Measures].[Internet Sales Amount]
)

SELECT {MEASURES.MONTHSALES} ON 0,
[Date].[Day Name].[Day Name].MEMBERS ON 1
FROM
[Adventure Works]
WHERE([Ship Date].[Day Name].TEST)

Interestingly, if you change the hierarchy you put the first calculated
member on, you get an internal error:

WITH
MEMBER [Ship Date].[Date].TEST AS
STRTOMEMBER("[Ship Date].[Day Name].&[" + [Date].[Day
Name].CURRENTMEMBER.PROPERTIES("KEY") + "]")
MEMBER MEASURES.MONTHSALES AS
SUM(
{[Date].[Month of Year].&[1], [Date].[Month of Year].&[2]}
, [Measures].[Internet Sales Amount]
)

SELECT {MEASURES.MONTHSALES} ON 0,
[Date].[Day Name].[Day Name].MEMBERS ON 1
FROM
[Adventure Works]
WHERE([Ship Date].[Date].TEST)

My feeling is that your problem is either going to be related to the exact
mix of hierarchies (where you put the calculated member, which one you're
linking from, which one you're linking to) involved in your query; the
behaviour I noted in my last post, where adding the All Member to the tuple
cured the problem, plus the fact that putting the member in a set first also
cured it, makes me think this is the area to investigate. Can you post up the
exact MDX you're running against your cube?

Regards,

Chris


--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Mike" wrote:

Hi Chris

The original query is quite a long and substantial file - if you want that
it may be easier to try and get it to you. I have managed to get around my
problem but it would be quite nice to know if there is a more efficient
solution. Problem is with Member being recalculated on every pass.

I have also tested the STRTOTUPLE on the query and the result is the same.
The problem is basically to do with the member being calculated at every turn
as a member when you want it to be fixed by the first evaluation as in the
set creation.

It seems really difficult to try and explain my solution and the fact that
is was working in one instance and not the other which is why I was looking
for assistance.

Basically I have MEMBER 1 on Dimension 1 Depends on Dimension 2 currentmember.

Member 2 is the SUM of a set of Dimension 2's members, which would overwrite
the selected current MEMBER that is used in MEMBER 1, whilst I want MEMBER 1
to be fixed.
When you generate the member in a set first the set is only evaluated once
and then never again so the member is fixed.
In the SELECT clause I mention MEMBER 1 is also essentially a set function
so only evaluated once as well as being that it doesn't then affect the
calculation of MEMBER 2.

That is where it gets blurry so if you want the actual query I can try and
post it but it is rather a long query.

Regards

"Chris Webb" wrote:

Hi Mike,

Sorry, I got a bit distracted by the strange behaviour. If you want to send
me the original query, then use the email address that I'm posting with here.

What you've noticed is normal behaviour, ie calculated members are always
evaluated dynamically whereas the contents of sets remain static from the
first time you access them onwards. I still don't know why you were getting
nulls when you didn't use a set though, and I'd like to investigate that
further if only for my own satisfaction. You might be able to implement the
calculation more elegantly in some other way (perhaps with MDX Scripts), but
if you're happy with the results you're getting and the time it takes to get
them then I'd stick with your current approach.

Regards,

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Mike" wrote: