The Many to Many Relationship

September 1, 2007

So today’s post will be about the usage of the many to many relationship that can reside between two dimensions. Quoting from the definition of Dimension Relationships, I would note that “Dimension usage defines the relationships between cube dimensions and the measure groups in a dimension…The many-to-many dimension relationship defines an association between a dimension and a measure group by specifying an intermediate fact table that is joined to the dimension table”.

Now I know the usual example for this relationship is with bank accounts and people, as in: each person can have more than one bank account and each bank account can have more than one owner. But I thought that instead of that I would give you an example from my own business world of Human Resource as I found it adds an extra twist to it all.

In the HR world I’ve defined in my cube, I have people and jobs. Now a job can have two people occupying it (if the job is defined as having 100% of work and each person is occupying it only in 50%). A person is also capable of occupying two jobs (I work half time as a system analyst and half time as a developer). The added twist? Well a job can also exist without anyone on it. That is, a job can be totally vacant and ready to be occupied.

I had a fact table for the jobs stating how much work is defined per job (the “Fact Table” in the above drawing). The Job dimension (the “Regular Dimension” in the above drawing) is linked to this fact table. In another fact table, I had how much work is defined per worker occupying a job (the “Intermediate Fact Table” in the above drawing). Both the Worker dimension (the “M2M Dimension” in the above drawing) and the Job dimension were linked to this fact table. When I tried  in the Cube > Dimension Usage to connect the Worker Dimension to the Jobs Fact table via the Worker on a Job Fact table, I found that the rows for all the jobs that were completely vacant disappeared from my Job Fact table. The Analysis Services gave me no notice of this while processing the new connection to the Measure Group. I found it out only through checking the details through the Cube Browser. It seems that as the intermediate fact table did not have rows for vacant jobs (but only for actual workers on a job) it’s connection to the jobs fact table had dropped those rows from it as well.

So what was my solution? I created a named query on the Worker on a Job Fact table, creating in that query also a row with a non – existent worker on the vacant jobs that were in that period of time. I hid the non – existent worker in the Worker Dimension, giving him a very large ID which will make him very hard to find (unless you’re specifically looking for him). I turned that named query into a fact table including a measure of Count Rows. I hid that measure (as I never really needed that table as a fact table in reality, as much as I needed it as and intermediate fact table) and I connected the Worker Dimension to the Job Fact table through this new Intermediate Fact Table. Thus, I had the correct connection (including all workers and all of the jobs in the intermediate fact table), and this was also invisible to the End – User as the only measure in this new fact table was invisible (meaning, the End – User could never know the table or the measure ever existed).

And so, when you use the Many-to-Many relationship in the Dimension Usage tab of the cube I would advise you (as always) to check very carefully the consequences of this new relationship. Did it drop you lines from the fact table? If it did, try working around it with the steps I’ve described.