The ability to return nested results is a great feature of DMX and it makes browsing query results very intuitive if you are using the right result viewer.  On occasion though, you'll find a flattened result more easy to manipulate in your application, especially if you're doing client-side computation and filtering. In such cases, it is helpful to have an understanding of how flattening works in DMX so you know what you're going to get if you issue a "SELECT FLATTENED" query with multiple nested tables.

 

Let’s look at an example:

 

SELECT

    t.[ID],

    PredictHistogram(dmm.Gender) AS H1,

    PredictHistogram(dmm.HairColor) AS H2

FROM dmm PREDICTION JOIN

    OPENQUERY(ds, 'SELECT * FROM NewCustomer') AS t

ON  dmm.Age = t.Age

AND dmm.CarColor= t.CarColor

 

This DMX query will return three columns: t.ID, H1, and H2, where H1 and H2 are nested table columns.

Suppose the query returns the following hierarchical rowset:

 

t.ID	H1	H2
1	Gender Support Prob. AdjProb. Variance missing 30 0.3 0.4 0 Male 30 0.3 0.4 0 Female 40 0.4 0.5 0	HairColor Support Prob. AdjProb. Variance missing 20 0.2 0.2 0 Black 30 0.3 0.2 0 Brown 40 0.4 0.3 0 Gray 10 0.1 0.1 0
2	Gender Support Prob. AdjProb. Variance missing 20 0.2 0.3 0 Male 20 0.2 0.3 0 Female 60 0.6 0.7 0	HairColor Support Prob. AdjProb. Variance missing 10 0.1 0.2 0 Black 20 0.2 0.2 0 Brown 30 0.3 0.3 0 Gray 40 0.4 0.4 0

 

Now, let us add the FLATTENED keyword to the above query so it looks like this:

 

SELECT FLATTENED

    t.[ID],

    PredictHistogram(dmm.Gender) AS H1,

    PredictHistogram(dmm.HairColor) AS H2

FROM dmm PREDICTION JOIN

    OPENQUERY(ds, 'SELECT * FROM NewCustomer') AS t

ON  dmm.Age = t.Age

AND dmm.CarColor= t.CarColor

 

The flattened result returned by the above query will be as follows:

 

t.ID	Gender	Support	Prob.	AdjProb.	Variance	HairColor	Support	Prob.	AdjProb.	Variance
1	missing	30	0.3	0.4	0
1	Male	30	0.3	0.4	0
1	Female	40	0.4	0.5	0
1						missing	20	0.2	0.2	0
1						Black	30	0.3	0.2	0
1						Brown	40	0.4	0.3	0
1						Gray	10	0.1	0.1	0
2	missing	20	0.2	0.3	0
2	Male	20	0.2	0.3	0
2	Female	60	0.6	0.7	0
2						missing	10	0.1	0.2	0
2						Black	20	0.2	0.2	0
2						Brown	30	0.3	0.3	0
2						Gray	40	0.4	0.4	0

 

Note that the flattening algorithm does not produce a Cartesian product of rows from independent nested table columns. This means that we may end up with a lot of NULL values when we have more than one nested table column as shown in the above example.