Measures in MDX subquery : Arbitrary shape error

Question

Hi

I am trying to remove a tuple from a larger set using sub-queries. This works perfectly until the tuple involves a measure. Then we get the dreaded "#Error An arbitrary shape of the sets is not allowed in the current context." It only happens if the measure is part of a tuple. If its on its own axis then it works just fine.

Use this MDX to see what I mean. The first statement works. The second one throws the error. If I add the "NON VISUAL" keywords in, the query works again. But the goal here is to produce visual totals in the uter query that excludes the tuples in the sub-select.

==========================================================================
select

 {measures.members} on 0,
{   [Product Categories].[Category].members } on 1

from
(
select
except({measures.members}, {[Measures].[Customer Count]})  on 0

from   [Adventure Works]
)

=======================================================================

select

{ [Customer].[Customer Geography].[Country].members} *{measures.members} on 0,
{   [Product Categories].[Category].members } on 1

from
(
select
except(
{ [Customer].[Customer Geography].[Country].members} *{measures.members},
 {[Customer].[Customer Geography].[Country].&[United States]} *  {[Measures].[Customer Count]}
 )  on 0

from   [Adventure Works]
)

====================================================================

Any ideas on the cause? Any ideas on a work around. Sub-queries are a requirement for other reasons. So I am not looking for alternative solutions that do not include the sub-query approach (which there are many).

thanks in advance

SNAP

Answer 1

hi,

The only solution i can think of for the moment is not really elegant and might still collide with the requirements that goes along with the reasons you want a sub query approach:

You could make a copy of the Measure customer count (in the probabale case you will still need the original one), scope it to zero for the United States. You might also consider using NON EMPTY if you want the column to disappear along with the other non-empties

Philip,

Answer 2

Interesting... I don't think there is a workaround, so you may need to treat measures as a special case and not use subselects to filter them, only using subselects for non-measures dimensions (it won't have any impact on your ability to return visual totals if you do this).

I'll ask higher authority if they know of any way of getting around this.

Chris

---

Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

Answer 3

Actually - the goal of the sub-query is to deliver visual total functionality TOGETHER with the ability to remove tuples from the underlying axis set. By not eliminating the tuple combination through a sub-query, there is no other real way of creating the proper visual totals across a ragged selection. And it works fine until measures are part of the equation.

The only real alternative is what Philip described above - a fairly ridiculous work around if you ask me - overwriting cell values to null them from the total. And I'm not sure if that works flawlessly either.

I believe this is an engine bug - because there is no reason why it shouldn't be able to work with measures as part of a tuple.

Maybe someone in the SSAS team can shed more light on the conundrum.

thanks

SNAP

Answer 4

Well, actually I'd argue that the goal of a subquery in MDX is purely to deliver visual totals functionality. It may remove tuples from an axis set as a side effect, but if you really need to do this you should alter your axis set directly. A lot of people have run into trouble over the years precisely because of this misunderstanding...

What I'm suggesting is that you make measures a special case and construct your queries as follows:

select

{ [Customer].[Customer Geography].[Country].members} *

except({measures.members}, {[Measures].[Customer Count]}) on 0,
{   [Product Categories].[Category].members } on 1

from
(
select
except(
{ [Customer].[Customer Geography].[Country].members},
 {[Customer].[Customer Geography].[Country].&[United States]} )  on 0

from   [Adventure Works]
)

Because there isn't an All Member you don't need to worry about visual totals on Measures, and so long as you use subselects for your other filtering you should be fine.

I've already had a quick response from the dev team, and it was that there probably isn't a workaround here. I'll let you know if I hear any more.

Chris

---

Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

Answer 5

Chris

Thanks for the suggestion and thanks for forwarding to the dev team. Unfortunately, your alternative does not satisfy the goal.

The dev team may be interested in seeing the outcome. In the grid below, we want to eliminate USA/customer count (red box) from the grid and then have the totals (blue box) reflect the "visual total". Because the MDX VisualTotals funciton only works on a single dimension, the only way to re-evaluate the totals is with the sub-query which allows us to eliminate tuples and then evaluate visual totals from there (as you pointed out).

As you have already noted there probably isn't a work around. And I have just tested some basic cell calculations which also don't resolve the issue without redefining the aggregation of the parent. A mess to be sure.

However, it would be great if this could be addressed in near future (service pack x) !

Thanks

SNAP

Answer 6

Sorry, yes, I see what you're after now and why my query won't work. I think the only thing you can do will be to open an item on Connect and keep hoping...

Chris

---

Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

Answer 7

hi,

it seems like the request has already been commented

http://cwebbbi.wordpress.com/2008/04/30/things-id-like-to-see-in-analysis-services-2011/

Philip,

Answer 8

That Connect is on a slightly different topic - arbitrary shapes in scoped assignment definitions. But I agree, it would help we never had to see that error message anywhere.

Chris

---

Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

Answer 9

So to summarize:

• this is a problem. The requirement does point to a flaw in the sub-query engine of SSAS and makes visual totalling through this mechanism a little limited in complex scenarios.
• using calculated measures/members/cells is a suggestion, but it does not truly solve the issue. In fact, resolving it with these techniques is going to be almost impossible anyway.
• the previous Arbitary Shape submissions to Connect do not specifically cover this exact problem.

The item has been submitted to Connect. Hopefully MSFT will address it in the future.

SNAP