How to handle Contact dimension in a Project dimension

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.

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.

OK, I see. Thanks.

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

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.