Done

For the record, I resolved the issue and the answer can be found here: http://dba.stackexchange.com/questions/127213/cube-with-2-measure-groups-with-different-relationships-to-dimensions-returns

Yes, I get the batch dimension members if I remove Units.

The cube is MD, not tabular.

Thanks for the extra solutions. Got me reading about relational division: https://www.simple-talk.com/sql/learn-sql-server/high-performance-relational-division-in-sql-server

Both solutions worked, thanks very much. For some reason Luis's is much faster on my large data set (millions of rows).

You are correct about the dimension logic - to be "inline", cells must have all coordinate values except 1 the same as the given cell. In other words, there is...

No, I was testing using my example data. mickey's query has a relatively simple plan with mostly index seeks. Running both mickey's and dwain's resulted in 19% for the former...

dwain.c - nope, I have nothing against dynamic SQL!

I have to award the prize to mickeyT's solution - it is the fastest of all the contributions.

Thanks everyone ... I was...

mickeyT - your solution returns too many rows unfortunately.

dwain.c - your solution returns the right rows for 3 dimensions (X,Y,Z) and the query plan is by far the best, but...

I now have the result I need. I combined your approach with someone else's help on StackOverflow and came up with this:

declare @CellID int = 1

;with SelectedCell as

(

select CellID, AxisCode,...

Wow! That is pretty close. However, what I needed was this result set:

+----------+---------+

| AxisCode | CellID |

+----------+---------+

| X | 1 |

| X | 2 |

| Y | 1 |

| Y...

For a solution see here: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d5f87abc-e303-484a-b40f-5639dfbe2efa/[/url]

Hi Grant,

One question about the book:

You refer a couple of times to indexed views that "only refresh once per day". My understanding was that indexed views are updated instantly when...

In case anyone needs to know how to resolve this (I did), this is the answer:

BUG: You may receive an "Access is denied" error message when a query calls an...

Thanks, I worked that out eventually!

This article is good for understanding indexed views, plus it has a section on "Using Indexed Views On Non Enterprise Editions of SQL Server":