Unwinding MDX Flattening Semantics with DMX

When issuing an MDX statement in code, you have the option of requesting either a multidimensional set or a relational rowset as the result set.  If you are familiar with MDX and multidimensional concepts, the multidimensional result is the obvious choice.  However, if your application is more suited to dealing with tabular data, such as Reporting Services, it may be more convenient to request the rowset result.  One issue is that it's not always obvious how the MDX flattening semantics will translate the multidimensional set into a flattened rowset.

You can see exactly how MDX flattens results right inside SQL Server Management Studio by exploiting a little trick using DMX.

For example, take the simple Adventure Works query below:

SELECT
{Measures.[Measures].[Reseller Sales Amount] *
[Product].[Category].MEMBERS} ON Columns,
{[Date].[Month]} ON ROWS
FROM [Adventure Works]
WHERE [Date].[FY 2002]

Which produces a result like this:

How many columns do you think the flattened result will have?  It would seem that you could represent the results in the six columns that of the multidimensional result.  However, the result set actually contains the entire hierarchy for the month.  You can exploit DMX's ability to accept raw MDX statements as input to extract the rowset result.  Since DMX always returns a rowset, if you select the input columns, you will retrieve the flattened result of the MDX statement. 

The next question, of course, is how to you create a model such that you can access the MDX?  The answer, you don't really have to.  You can use any mining model that happens to be there, or just create any arbitrary mining model for the purpose.  This trick takes advantage of the NATURAL PREDICTION JOIN construct with matches input columns to model columns by name and simply ignores any columns that don't match up - in this case, likely all of them.

Therefore, the DMX statement

SELECT t.* FROM [Customer Tree]
NATURAL PREDICTION JOIN
(SELECT {Measures.[Measures].[Reseller Sales Amount] *
[Product].[Category].MEMBERS} ON COLUMNS,
{[Date].[Month]} ON ROWS
FROM [Adventure Works]) AS t

Essentially ignores the model and simple returns the flattened result of the MDX query, which in this case returns nine columns, like this:

This trick can be used against any dimensionality MDX query to extract the flattened results.  From a data mining perspective, this is particularly useful to determine the column layout of an MDX query in order to train mining models with INSERT INTO.