How to handle Contact dimension in a Project dimension

View previous topic View next topic Go down

How to handle Contact dimension in a Project dimension

Post  thomaszhwang on Fri Aug 12, 2011 10:46 am

The business requirement is one Project should have one Contact Person for general affairs, one Contact Person for billing and one Contact Person for resources allocation. Those three Contact Person come from a Contacts table in the transaction system. Each Contact Person has a list of detail information (10+ fields: e.g. first name, last name, phone number, address and etc.)

Now I'm creating a Project dimension. Should I snowflake those Contact Person fields or should I de-normalize those fileds into the Project dimension?

Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: How to handle Contact dimension in a Project dimension

Post  VHF on Fri Aug 12, 2011 11:54 am

I would snowflake in this case... treat the Contact dimension as an outrigger of the Project dimension.

If you have any issues with query performance you could pull important contact fields (such as name) into the Product dimension, but I don't think you would want to denormalize 10+ fields for 3 contacts into the Project dimension.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: How to handle Contact dimension in a Project dimension

Post  thomaszhwang on Fri Aug 12, 2011 12:01 pm

OK, I see. Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: How to handle Contact dimension in a Project dimension

Post  hang on Fri Aug 12, 2011 5:26 pm

It seems a project relates to multiple people. So if you snowflake the project dimension, you would have to nominate a single person, say for general affairs, to link to the project. Then you need a bridge table to hold FKs to both dimensions if change history may be ignored, otherwise use periodical snapshot factless fact table which may be loaded at the same frequency as your invoice fact.

hang

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

View user profile

Back to top Go down

Re: How to handle Contact dimension in a Project dimension

Post  thomaszhwang on Fri Aug 12, 2011 6:03 pm

hang wrote:It seems a project relates to multiple people. So if you snowflake the project dimension, you would have to nominate a single person, say for general affairs, to link to the project. Then you need a bridge table to hold FKs to both dimensions if change history may be ignored, otherwise use periodical snapshot factless fact table which may be loaded at the same frequency as your invoice fact.

I don't really understand what you are saying, but what I did in this case is to create a Contact dimension table and to have the various Contact fields in the fact table linked to the aliased tables of the Contact dimension table.

Do you see any problem using this approach? Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: How to handle Contact dimension in a Project dimension

Post  hang on Fri Aug 12, 2011 7:31 pm

I see your point. Contact is a role playing dimension in the fact (invoice) or dimension table (project). I think it's better design than my proposed bridge or factless fact table as it is not an open-ended 1:m relationship between project and contact.

However when you snowflake the project dimension with contact, what are you going to do? Do you have three FKs in the dimension as well? You would possibly create the relationship in two different places, fact and dimension. To be consistent and ignoring the contact changes, you might be better off having only project FK in the fact table to provide a single entry to the Project-Contact relationship through snowflaked and role played project dimension.

If a project has changed the contact persons in its life cycle and you want to track that change, you need to make it clear that contact FKs in the fact table represent the the contacts at the point in time (PIT), whereas the FKs in the project are type 1 and always represent the current contacts. It's similar to Kimball's mini/outrigger dimension arrangement.

hang

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

View user profile

Back to top Go down

Re: How to handle Contact dimension in a Project dimension

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