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.