Snowflake issue

View previous topic View next topic Go down

Snowflake issue

Post  thomaszhwang on Mon Aug 22, 2011 2:56 pm

I have a Time Slip fact table to record the time usage of our employees. Each Time Slip should be associated with a record in the Project dimension table. Each Project should have a Client.

The problem now is should I have a Client dimension and have a Client_Key in the Time Slip table or should I denormalize the client information into the Project dimension table? In the requirement, I do have other fact tables should be associated with a Client dimension table and should not be associated with a Project dimension table.

Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: Snowflake issue

Post  mru22 on Mon Aug 22, 2011 5:31 pm

Your question sounds like a similar one I had for a system I am working on.

I think if you can keep the Client Dimension "Conformed" since it is used in other fact tables and then denormalize some of the client data stuff into the Project dimension you can remain flat and still meet your needs.

It took me a while to realize the benefits of keeping my model flat as possible and avoiding Outrigger or snowflaking as much as possible.

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Snowflake issue

Post  ngalemmo on Mon Aug 22, 2011 5:48 pm

Add a client key to the time slip fact table.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Snowflake issue

Post  thomaszhwang on Tue Aug 23, 2011 10:18 am

I suppose I should add a Client_Key/Client_ID in the Project dimension table too. Should I use the Client_Key or Client_ID? Should I enforce the FK constrain? Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: Snowflake issue

Post  ngalemmo on Tue Aug 23, 2011 11:53 am

A dimension table should always have a surrogate primary key. Always use the surrogate key as the FK in the fact table. Never use the natural key.

FK contraints are superfluous. They are not needed.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Snowflake issue

Post  thomaszhwang on Tue Aug 23, 2011 12:19 pm

The current situation is both the Client table and the Project table are dimension tables. They have a one-to-many relationship (one client has many projects). Do I need to reflect this relationship in the design?

Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: Snowflake issue

Post  ngalemmo on Tue Aug 23, 2011 1:11 pm

Depends. If there is already a fact that tracts a project from inception, one would expect client is a dimension of that fact. That should be more than enough to support reporting project/client relationships.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Snowflake issue

Post  thomaszhwang on Tue Aug 23, 2011 1:17 pm

What if I do want to keep this relationship between (client dimension and project dimension), just in case users would like to roll-up from Project dimension to Client dimension, should I use the Client_Key (surrogate key) or Client_ID (natural key) in the Project dimension?

Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: Snowflake issue

Post  hayrabedian on Mon Mar 04, 2013 6:56 am

thomaszhwang wrote:What if I do want to keep this relationship between (client dimension and project dimension), just in case users would like to roll-up from Project dimension to Client dimension, should I use the Client_Key (surrogate key) or Client_ID (natural key) in the Project dimension?

Thanks.

I would put the Client_Key (surrogate key) in the Project dimension, even more, I would make it SCD2. The only thing to bother is not to allow too many rows to be propagated due to a changed parent dimension's SCD2 attributes.

hayrabedian

Posts : 7
Join date : 2011-04-01

View user profile

Back to top Go down

Re: Snowflake issue

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