Imagine you have a fact table joined to the helper table. The helper table is joined to the dimension table, but also includes a weighting factor column (such that fact rollups are accurate). This is used to handle many-to-many relationships in the dimension.
Summarized nicely at:
http://www.dbmsmag.com/9808d05.html
Imagine I have two transactional records:
1,one,$100
2,two,$500.
I also have the following records relating site to market:
100,1,10
101,1,20
102,2,10
Site 1 is part of market 10 & 20. Site 2 is just part of market 10.
So you can see there is a many-to-many relationship between site and market.
Ideally, I need a fact table built that would contain the following:
fact key, join to helper table key, $50
fact key, join to helper table key, $50
fact key, join to helper table key, $500
This way the sum of the revenue is correct.
This is where the dimensional helper table comes in. It would look like the following:
helper key, site id, market id, weighting
200,1,10,0.5
201,1,20,0.5
202,2,10,1.0
There must be a way to build the helper table dynamically. (i.e. weights are dependent on the number of many to many relationships to market for a given site).
New Feature
Open
Severe
Problem when counts don't divide equally, e.g. when there is a rest of division (need to add the rest to one of the parts).