RE: MDX Query Problem
Tech-Archive recommends: Speed Up your PC by fixing your registry
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
>
> 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:
>
> > 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
> > >
> > > 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
.
Relevant Pages
- Re: Conditional MDX Staement
... What do you want the calculation to show when you're at the all member in ... the product dimension? ... The way the calculation is set up, ... > The products dimension has 3 levels. ...
(microsoft.public.sqlserver.olap) - Re: Disable dimension multi-select?
... multiple values in calculation? ... There will be no hierarchies defined for this dimension. ... can disable the "All" member, ...
(microsoft.public.sqlserver.olap) - Re: Allowed Member Set with 2 Attributes
... Based on my understanding of your dimension data, ... attributes in the Date dimension of Adventure Works. ... allowed member set will not restrict the parent year, ... simultaneously select both Company and Location Division. ...
(microsoft.public.sqlserver.olap) - Re: too many calculated members when calculating last year figures
... Maybe a Tima Analysis utility dimension will help you: ... calculation dimension with a YTD member: ...
(microsoft.public.sqlserver.olap) - Re: MDX Percentage Calculation
... Product Categories hierarchy in the Adventure Works DW database. ... WITH MEMBER measures.pcntOfParent as (.[Product ... Categories].CurrentMember, Measures.[Internet Sales Amount]) ...
(microsoft.public.sqlserver.olap) |
|
