SSAS: Joining issue with MDXing of M2M dimension (part III)

Hi,

At this moment i'm developing a M2M (or MM) dimension for locations and departments. If you haven't seen my former posts (part I, part II) about this subject, i would recommend reading these posts. One issue i have with the M2M dimensions is getting the right information in the reports (who doesn't;-)) and it took me a while to understand how to handle this. The problem is how to show the locations which don't have a fact for a specific department.

Okay, this is the situation: there are departments and locations and they are n:m related to each other. Therefore i've developed a bridge table for this purpose. In this bridge table i've added a count field. In contrast with my former post i've added an extra record for simulating the scenario i have. So the records in the different tables looks like this:

Query :

SELECT
Location_Description,
Department_Description,
DT.Date_Year,
TestFactCount
FROM dbo.Location L
LEFT OUTER JOIN dbo.TestFact F ON L.Location_Dim_key = F.FK_Location_Key
LEFT OUTER JOIN dbo.DimDate DT ON F.FK_Date_Key = DT.Date_dim_key
INNER JOIN dbo.Bridge_Location_Department B ON B.FK_Location_Key = L.Location_Dim_key
INNER JOIN dbo.Department D ON D.Department_Dim_key = B.FK_Department_Key


Resulting in:

So when the user selects department C two locations should be shown: location 3 and 4. Location 3 has factvalues and location 4 does not have any fact values.

Option 1

Okay the first attempt for building a MDX query for this is:

SELECT [Measures].[Test Fact Count] ON 0,
[Location].[Location Description].[Location Description].MEMBERS ON 1
FROM ( SELECT (STRTOSET('[Department].[Department Description].&[ Department C]', CONSTRAINED) ) ON COLUMNS
       FROM [MultiValueDimension])

As you can see all locations are shown (even the one that has no relation with department C) . Not a desired situation

Option 2:
The next thing i tried is the following query:

SELECT [Measures].[Test Fact Count] ON 0,NONEMPTY([Location].[Location Description].[Location Description].MEMBERS) ON 1FROM
( SELECT (STRTOSET('[Department].[Department Description].&[ Department C]', CONSTRAINED) ) ON COLUMNS FROM [MultiValueDimension])


Resulting in :

Also not a desirable situation here...

Option 3
Someone on the MSDN forum pointed me to a extra parameter of the nonempty() function. The nonempty() function has the following syntax:  NONEMPTY(set_expression1 [,set_expression2]) and set_expression2 is a sort of limitation of the first set_expression. So the function returns a set of tuples that are not empty, based on the cross product of the second set.

So the final solution for getting the right information is this:

SELECT [Measures].[Test Fact Count] ON 0,NONEMPTY([Location].[Location Description].[Location Description].MEMBERS, [Measures].[Bridge Location Department Count]) ON 1FROM
( SELECT (STRTOSET('[Department].[Department Description].&[ Department C]', CONSTRAINED) ) ON COLUMNS FROM [MultiValueDimension]) 

So i'm using the bridge count of the bridge table for getting the right information. When this value is nonempty it does show me the locations and facts that are related. With this MDX query i can produce the following information:

Conclusion
With this solution you can simulate joining in SQL for getting the right information.

Greetz,
Hennie