Hitachi Consulting BI Blog

MDX Slicers and Subqueries

One of the big conceptual differences between MDX and SQL query statements is in the way the WHERE clause works. In SQL, the WHERE clause restricts the source that goes into the query, and thus affects the rows (group by items) as well as the data values. In MDX, the WHERE clause qualifies the tuple for each cell of the query, but doesn’t affect the axis sets. For example, in SQL, if you group by Country and put North America in the WHERE clause, you’ll only see rows for US, Canada, and Mexico. But in MDX, if you put Country.Members on rows and put North America in the WHERE clause, you see all the countries, but the cells for most of the countries of the world are empty.

In Yukon, they didn’t change the meaning of the WHERE clause, but they added a new structure that gives the same effect as the SQL WHERE clause—to filter the source: You can now use a subquery in the FROM clause. So, rather than putting North America in the WHERE clause, if you put (SELECT ([North America],*) on 0 FROM [Sales]) in the FROM clause, you get the SQL-like result that includes only US, Canada, and Mexico.

While there’s still a place for the data-slice behavior of the MDX WHERE clause, the new approach behaves in a way that feels more intuitive to both SQL developers and end users.

Reed