Intersection in MDX

Question

I recently ran into a problem in our SQL Server 2008 Analysis Services Cube. Imagine you have a simple sales data warehouse with orders and products. Each order can be associated with several products, and each product can be contained in several orders. So the data warehouse consists out of at least 3 tables: One for the Products, one for the Orders and one for the reference table, modelling the n:n relationship between both. The question I want our cube to answer is: How many orders are there which contain both product x and product y? In SQL, this is easy:

select orderid from dbo.OrderRefProduct
where ProductID = 1
intersect
select orderid from dbo.OrderRefProduct
where ProductID = 3

Since I am fairly proficient in SQL, but a newbie in MDX, I have been unable to implement that in MDX. I have tried using distinct count measures, the MDX-functions intersect and nonempty and subcubes. I also tried duplicating the dimensions logically (by adding the dimension to the cube twice) as well as physically (by duplicating the data source table and the dimension).

On http://www.zeitz.net/thts/intersection.zip, you can download a zip file of 25kB size which contains an SQL script with some test data and the Analysis Services Solution using the tables.

We are using SQL Server 2008 R2 and its Analysis Services counterpart. Performance considerations are not that important, as the data volume is rather low (millions of rows) compared to the other measure groups included in that cube (billions of rows).

The ultimate goal would be to be able to use the desired functionality in standard OLAP (custom calculated measures are ok), since Excel is our primary frontend, and our customers would like to choose their Products from the dimension list and get the correct result in the cube measures. But even a working standalone MDX-Query would greatly help.

Thank you!

Edit March 12th Did I miss something or can't this be solved somehow?

If it helps to build the mdx, here is another way to get the results in sql, using subquerys. It can be further nested.

select distinct b.orderid from
(
select distinct orderid from dbo.OrderRefProduct
where ProductID = 1
) a
join dbo.OrderRefProduct b on (a.orderid = b.orderid)
where ProductID = 3

I tried something like this with subcubes in mdx, but didn't manage to succeed.

Answer 1

an other way to deal with this in SQL (I know it works, but I didn't test this query) is to use double negation

select distinct orderid 
from X 
where TK NOT in (
   select TK 
   from X x_alias 
   where productid NOT in (id1,id2)
)

I'm pretty sure you can do the same in MDX.

Answer 2

I've had a go - you can download my solution from here:

http://sdrv.ms/YWtMod

I've added a copy of your Fact table as a "Cross Reference", Aliased the Product1 dimension as a "Cross Reference", set the Dimension references to Product independently from your existing relationships, and specified the Many-to-Many relationships.

It is returning the right answer in Excel (sample attached).

You could extend that pattern as many times as you need.

Good luck! Mike