Unknown number of relationships from dimension to fact until fact loaded

[DHS Solutions]

Hi,
I have an interesting twist to the much discussed ' multi-valued' dimension discussion. I have a fact table related to a person dimension which contains a role code (director, planner, etc.). So in theory, a person can play different roles at the same time.
The population of the person dimension occurs as part of the fact load (i.e., first pass of fact done to pull dimension information out). What gets extracted from the first pass of the fact is a person and the role they are playing for that particular fact. (This is having to be done because the source system does not track people separately, but as part of the actual event).
While profiling the data it was discovered that multiple people could be assigned to the same role. So I could have more than one person assigned for example to the director role for that fact. So the number of relationships from the person dimension to the fact table isn't known.
I'm sure someone has come across this, or something similar to it in the past. I'm trying to figure out if a bridge table is truly all that is needed. So person dimension relates to a person role bridge table which in turn relates to the fact. The only twist is that I don't know how many people will be assigned to the role until I start processing the fact.
Thanks for any clarity you can provide!

[Jeff Smith]

What questions is the fact table being used to answer?

You could create a measure column that is 1/(the number of the person appears in the fact table). If Person A is doing 2 jobs, and is in the table 2 times, then each row has a measure of 1/2.

Or, you could create an aggregate of the Person fact that has each person listed twice. Because your Person Fact isn't really at the Pesron Level. It's at the Person-Job level.

[BoxesAndLines]

Sounds like a bridge table to me. The other option is to break the roles out to separate dimension. But you will still need bridge tables where multiple people participate in the same role.

[hang]

Yep, bridge table. Normally a role may be played by multiple persons at different point in time and you may have unknown number of roles for one event in which case a person-role bridge would help to resolve m-m relationship.

However if a role is associated to more than one person at the same time, you need a person group bridge that relates many persons to the same group key and put the group key as FK in place of the multi-person role column in the fact table. In this structure, there is virtually a group dimension, which could be physical as well, connecting between person dimension and fact table.

[Sponsored content]