How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)

View previous topic View next topic Go down

How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)

Post  thomaszhwang on Thu Aug 11, 2011 6:11 pm

I have a Project dimension table and a Client dimension table. The business rule is 1 Client can have 0+ Projects and 1 Project must belong to only 1 Client. In this case, for the Project dimension, I de-normalized the Client fields into the Project dimension table

Now I have an Invoice fact table. The business rule is the Invoice could be affiliated to a Project (so as to be affiliated to a Client) or just be affiliated to a Client without a Project.

The Invoice fact table looks like this.

FactInvoice
PK/FK?Field NameRequired?
PKInvoice_KeyYes
FK1Client_KeyYes
FK2Project_KeyNo
Invoice_AmountYes

Questions:
1. Is there any problem to design this way?
2. Does the Project dimension table in this case require to include all the Client fields (de-normalization)?

Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)

Post  hang on Thu Aug 11, 2011 7:55 pm

I would not de-normalize the Client attributes into Project dimension as they belong to a different base dimension called Client. You should only de-normalize attributes into the base dimension when the containing dimension is regarded as an aggregate dimension, in which case you should build your aggregate dimension based on the base dimension in your ETL process so that you donít end up with two versions of attributes in different places.

Instead, I would snowflake Project dimension by having an FK to the Client dimension. In your fact table, all you need is the Project_Key as the entry point for the Client-Project hierarchy.

hang

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

View user profile

Back to top Go down

Re: How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)

Post  thomaszhwang on Thu Aug 11, 2011 8:48 pm

Thank you hang. This is very helpful.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)

Post  VHF on Fri Aug 12, 2011 9:24 am

Can you have invoices that don't have a project associated with them? If not, go with what hang suggested.

If so, keep your original fact table design. However, don't use a nullable FK for Project_Key. FKs in a fact table should never be null. Instead, have a special 'not applicable' row in your project dimension table to point to. You can set all the attributes to 'NA' or whatever makes sense for you when there is no project.


Last edited by VHF on Fri Aug 12, 2011 10:18 am; edited 1 time in total (Reason for editing : typo)

VHF

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

View user profile

Back to top Go down

Re: How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)

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

Yes, for some Invoices, a Project is not required.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)

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