Multiselect Slicing MDX Query Question

Hello,

I am trying to understand MDX multiselect / slicing / axis restriction. And there may be some attribute hierarchy vs normal hierarchy input as well. Any thoughts would be very helpful

I've written a few queries from the Adventure Works cube.

Query 1: Multiselect slicing on the Date attribute hierarchy; Displaying the Fiscal hierarchy, Date level on the axis. Normal, expected results.

Code Snippet

select [Date].[Fiscal].[Date].Members on 0
from [Adventure Works]
where ({[Date].[Date].&[1000], [Date].[Date].&[1100]}, [Measures].[Internet Sales Amount])

Results:

March 26, 2004 July 4, 2004
$43,703.84 $1,301.33

Query 2: Multiselect slicing on the Date attribute hierarchy; Displaying the Fiscal hierarchy, Month level on the axis. I believe I am getting different results because the visual total is overwritten. Essentially, when I specify the Month on the axis, I get rid of my initial slice of two days, so I get the entire month's values. Is this correct?

Code Snippet

select [Date].[Fiscal].[Month].Members on 0
from [Adventure Works]
where ({[Date].[Date].&[1000], [Date].[Date].&[1100]}, [Measures].[Internet Sales Amount])

Results:

March 2004 July 2004
$1,480,905.18 $50,840.63

Query 3: Multiselect slicing on the Fiscal hierarchy, Date level; Displaying the [Month of year] attribute hierarchy on the avis. I have absolutely no idea why I am not getting anything back for July. Can anyone explain this to me?

Code Snippet

select [Date].[Month of Year].Members on 0
from [Adventure Works]
where ({[Date].[Fiscal].[Date].&[1000], [Date].[Fiscal].[Date].&[1100]}, [Measures].[Internet Sales Amount])

Results:

All Periods March July
$43,703.84 $43,703.84 (null)

I'm running SP2.

Thanks!
Jessica

If Query 3 is re-written using a subselect, data is returned for July:

Code Snippet

select [Date].[Month of Year].Members on 0

from (select

{[Date].[Fiscal].[Date].&[1000], [Date].[Fiscal].[Date].&[1100]} on 0

from [Adventure Works])

where ([Measures].[Internet Sales Amount])

All Periods March July
$45,005.17 $43,703.84 $1,301.33

Also, if the order of the selected dates is reversed, no data is returned for March instead:

Code Snippet

select [Date].[Month of Year].Members on 0

from [Adventure Works]

where ({[Date].[Fiscal].[Date].&[1100], [Date].[Fiscal].[Date].&[1000]},

[Measures].[Internet Sales Amount])

All Periods March July
$1,301.33 (null) $1,301.33

So, at first glance, this seems like a bug to me - but maybe someone else can explain the results differently?

multi-select MDX issue from a client tool

Hi

This is the sample query which was getting generated by the client tool :
with member [Destination].[Destination].[Sum] as 'Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]})'
select [Measures].[Value] on columns,
nonempty([Time].[Calendar].allmembers) on rows
from [Consolidation Prototype]
where ([Destination].[Destination].[Sum]

The above query was failing in SSAS. It was failing because of the implicit use of current member somwhere.

The error was

The MDX function CURRENTMEMBER failed because the coordinate for the attribute contains a set.

Mosha has written a few blogs over the network explaining the reason for the problem. My guess is that, although, I am not using the currentmember across Destination dimension explicitly in the above query, it is getting used in the MDX script while evaluating the values of the measure [Measures].[Value] .

So while evaluating the currentmember for Destination dimension, its finding 2 current values and fails, because SSAS cannot handle this scenario.

Now the interesting part :

If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember)

Would anyone be able to explain to me the reason for this kind of behaviour?

ZA

"If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember) "

Just a guess, but the above tweaks may be enough to stop the replacement that Mosha describes in his blog :

Writing multiselect friendly MDX calculations

...

AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

|||I thought so. Thanks anyways, Deepak.

multi-select MDX issue from a client tool

Hi

This is the sample query which was getting generated by the client tool :
with member [Destination].[Destination].[Sum] as 'Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]})'
select [Measures].[Value] on columns,
nonempty([Time].[Calendar].allmembers) on rows
from [Consolidation Prototype]
where ([Destination].[Destination].[Sum]

The above query was failing in SSAS. It was failing because of the implicit use of current member somwhere.

The error was

The MDX function CURRENTMEMBER failed because the coordinate for the attribute contains a set.

Mosha has written a few blogs over the network explaining the reason for the problem. My guess is that, although, I am not using the currentmember across Destination dimension explicitly in the above query, it is getting used in the MDX script while evaluating the values of the measure [Measures].[Value] .

So while evaluating the currentmember for Destination dimension, its finding 2 current values and fails, because SSAS cannot handle this scenario.

Now the interesting part :

If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember)

Would anyone be able to explain to me the reason for this kind of behaviour?

ZA

"If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember) "

Just a guess, but the above tweaks may be enough to stop the replacement that Mosha describes in his blog :

Writing multiselect friendly MDX calculations

...

AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

|||I thought so. Thanks anyways, Deepak.sql

multi-select MDX issue from a client tool

Hi

This is the sample query which was getting generated by the client tool :
with member [Destination].[Destination].[Sum] as 'Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]})'
select [Measures].[Value] on columns,
nonempty([Time].[Calendar].allmembers) on rows
from [Consolidation Prototype]
where ([Destination].[Destination].[Sum]

The above query was failing in SSAS. It was failing because of the implicit use of current member somwhere.

The error was

The MDX function CURRENTMEMBER failed because the coordinate for the attribute contains a set.

Mosha has written a few blogs over the network explaining the reason for the problem. My guess is that, although, I am not using the currentmember across Destination dimension explicitly in the above query, it is getting used in the MDX script while evaluating the values of the measure [Measures].[Value] .

So while evaluating the currentmember for Destination dimension, its finding 2 current values and fails, because SSAS cannot handle this scenario.

Now the interesting part :

If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember)

Would anyone be able to explain to me the reason for this kind of behaviour?

ZA

"If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember) "

Just a guess, but the above tweaks may be enough to stop the replacement that Mosha describes in his blog :

Writing multiselect friendly MDX calculations

...

AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

|||I thought so. Thanks anyways, Deepak.

multiple subcubes in one MDX query

Is it possible to use more than one subcube in one MDX statement? Putting a subcube in the FROM clause is great, but sometimes you need more than one.

One subcube in the from clause is nice, but sometimes you need to use one subcube to limit the totals for one column, but then you need a different subcube to limit the totals of another column. For instance, if users can dynamically pick a list of stores and you want the total sales for the stores they pick, a subcube in the from clause will do that. But then if you want a second column to show the totals of peer stores (which are not in the subcube), then you're in trouble.

I think I know the answer to this question already, but I thought I'd ask just to make sure.

The silence is deafening ;-)

In SQL you can use multiple subqueries such as:

select column1
,column2 = (select x from y where a=b)
,column3 = (select x from z where c=d)
from aaa

In MDX, I think you can only have a subcube in the FROM clause, so you can't do more than one subcube per query. Is that correct?

|||

No - you can only use subcubes in the from clause, if you have more than one, they have to be nested - effectively narrowing the scope, which would not help in your example.

In the example you have given I would consider approaching it from the other way. Create a subcube in the from clause that picks all the peer stores, then use a calculated measure or a tuple to pick out the total for the particular store that the user selected.

Multiple Selections and MDX

I am creating a report that the user wants to have mulitple parameters
but I am querying an OLAP Cube. Is there a way of building a query on
the fly depending on users selection. Fopr example in SQL you could say
"IN (Parameters!MyParam.Value)" is it possible to do something like
this in MDX or do I just have to query everything and filder using the
Report Parameter
Thanks in advance
DenverYes, you can do this pretty much the way you described. You're
building a string which is the mdx query and adding the parameters as
you do this. Can't remember the exact syntax I used, but it amounts
to:
"mdx_part1" + Parameters!MyParam.Value + "mdx_part2"|||How will this work for multiple selections?