Unknown number of relationships from dimension to fact until fact loaded

View previous topic View next topic Go down

Unknown number of relationships from dimension to fact until fact loaded

Post  DHS Solutions on Fri Feb 10, 2012 11:10 am

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!

DHS Solutions

Posts : 1
Join date : 2012-02-10

View user profile

Back to top Go down

Re: Unknown number of relationships from dimension to fact until fact loaded

Post  Jeff Smith on Fri Feb 10, 2012 2:36 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Unknown number of relationships from dimension to fact until fact loaded

Post  BoxesAndLines on Fri Feb 10, 2012 3:33 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Unknown number of relationships from dimension to fact until fact loaded

Post  hang on Fri Feb 10, 2012 5:52 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Unknown number of relationships from dimension to fact until fact loaded

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum