Sql-server – MDX Intersection – how to

cubemdxsql serverssas

A similar question has been asked here, but I'll give a brief intro. In SQL Server there are 3 tables:

  1. Order (Id, Date);
  2. Product (Id, Name);
  3. OrderRefProduct (Id, OrderId, ProductId);

OrderRefProduct is a many-to-many table since one product can be in different orders and different products can be in one order.

All this is sticked into an OLAP Cube with Orders and Products as Dimensions and OrderRefProduct as Facts. We need to get results like the output of SQL query would provide:

SELECT ProductId, COUNT(*) as [Count]
FROM OrderRefProduct 
GROUP BY ProductId

the correspondind MDX query is fairly simple, given the Cube is built correctly:

SELECT 
NON EMPTY { [Measures].[Order Ref Product Count] } ON COLUMNS, 
NON EMPTY { ([Product].[Product ID].[Product ID].ALLMEMBERS ) } ON ROWS 
FROM [Sales]

And the result for both is:

ProductId   Count
1   7
2   7
3   5
4   6

Now here's the problem. I want to select count of orders that have BOTH products 3 AND 4. SQL:

select ProductId, Count(*) as [Count]
from OrderRefProduct
where [OrderId] in 
(SELECT S.OrderID FROM 
(select * from OrderRefProduct where ProductID=3) as S
INNER JOIN (select * from OrderRefProduct where ProductID=4) as T on T.OrderId = S.OrderID)
Group by ProductId

Result:

ProductId   Count
1   1
2   1
3   3
4   3

Can't reproduce this in MDX query. What I could achieve so far is FULL JOIN, while I need an INNER one. Any help would be appreciated. Test solution can be found here.

Best Answer

You just need to add an MDX where clause. Look at the Basic MDX syntax.

In that where you need to specify the SET to slice on. Have a look at Using Sets in MDX Queries

SELECT 
NON EMPTY { [Measures].[Order Ref Product Count] } ON COLUMNS, 
NON EMPTY { ([Product].[Product ID].[Product ID].ALLMEMBERS ) } ON ROWS 
FROM [Sales]
WHERE {[Product].[Product ID].[3],[Product].[Product ID].[4]}

In my personal opinion it's no use trying to relate MDX queries to SQL queries. Both are an entirely different beast.

You need to stop thinking in tables and joins, but in tuples and sets and members and measures.

One of the best resources in my opinion to get started is the Stairway to MDX series.

Related Question