Member Hierarchy with Multiple Parents / Many to Many Dimensional Modeling

Over the course of one of my projects this unique issue posed itself that needed to be solved. I needed to build a reporting analysis tool that allowed me to analyze data across various dimensions such as Time / Geography / Product etc. One of the parameters on which analysis was need was on Type. Now Type unfortunately wasn't a one is to one mapping or a many to one mapping.

For example a single store could be Hair Focus outlet , it could also be a Skin focus outlet, a Food focus etc. Also possible that the it could just be one of the above.

Now if we go ahead and build a normal hierarchy with this kind of mapping of outlets to their respective types as defined below it runs into various problems of incorrect totaling.

Outlet Type
1 Skin Focus
1 Food Focus
2 Skin Focus
2 Hair Focus
3 Hair Focus
..
..
..


The problem being if we try and report say sale based on Type it will double count outlets 1 and outlet 2 and the resulting total would be incorrect.

Another typical example of this based on my reading is when in a bank individual have single accounts as well as joint accounts and the same needs to modeled ensuring that we don't end up double / triple counting the same record while reporting balance or any other parameter.

I came across this fantastic white paper, that describes this problem and more importantly the solution to the same.

http://www.sqlbi.eu/Projects/Manytomanydimensionalmodeling/tabid/80/language/en-US/Default.aspx

The issue is treated as a many to many dimension modeling and has a very straight forward solution using SQL Server 2005 and something known as a factless dimension. More importantly it gives insight into how one may be able to model the same and the fundamental principle could be attempted to be used in other schemas as well.

The capability solved my problem of allowing me to create a dimension on the type which allows for navigational / selection and ensuring that no double counting takes place at the total levels.

If you have found this page useful, do drop me a comment.