MDX query equivalent to SQL subquery?

Question

What is the best way to replicate a SQL sub query in MDX?

Say for example, I want to know for customers who made a purchase in January, what purchases did they make in the rest of the year?

In SQL I could do this quite easily with a subquery, something along the lines of

*select * from sales where customerid in (select customerid from sales where salesmonth = January2016) and salesyear=2016*

However I can't figure out how to do something similar in MDX. I have a working query that gives me the customers with sales in January, but can't work out how to pass the results of this query to another query.

Can anyone help?

whytheq whytheq · Accepted Answer · 2017-03-23T09:46:46

You would use a WITH clause - so this is pseudo-code

WITH
 SET [CustomerinJan] NonEmpty(Customers, SalesMthJan)
SELECT
  [Measures].[Purchases] ON COLUMNS,
  [CustomerinJan] ON ROWS
WHERE (SalesYear2016);

MDX and OLAP is centered around set theory so aslong as you create the correct dimensions and hierarchies within the cube then all the possibilities of set theory are available to you.

MDX query equivalent to SQL subquery?

1 Answers