vrijdag 1 juli 2011

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:

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 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.