Snowflaking or not of Employee Transaction Dimension

View previous topic View next topic Go down

Snowflaking or not of Employee Transaction Dimension

Post  WissemHabboub on Wed Jul 24, 2013 10:54 pm

Hi All,

I have a question regarding the design of Employee Transaction Dimension which is itself related to 10 dimensions (Grade, Position, Job....). This dimension (Employee transaction) is common for almost all the HR Model (FACT HR Snapshot, FACT Attendance, FACT Promotion....). My Question is :
Shall we keep the schema in snowflake so each fact table will include only Employee Transaction Surrogate Key or we should add the Surrogate Keys of the 10 dimensions related to Employee transaction dimension in all our HR Fact Tables. what is the best practice. Below is the schema describing the design:

WissemHabboub

Posts : 1
Join date : 2013-07-24

View user profile

Back to top Go down

Re: Snowflaking or not of Employee Transaction Dimension

Post  VHF on Thu Aug 08, 2013 3:37 pm

Kimball-method best practice would be to stick with a star-schema by adding the attributes for Grade, Position, Job, etc. to the Employee Transaction dimension and just having a single FK in the fact table.

That said, I am becoming more partial to snowflake schemas.  This is influenced somewhat by our use of SAP Business Objects BI tools and the need to easily let the users do "drill-across" quieries involving fact tables at different grains.

If you will have other fact tables with a granularity at the Grade, Position, or Job level then you would be a candidate to keep the snowflake schema you have shown.

Kimball Group has said it is OK to snowflake if (1.) your query performance is still fast and (2.) it is still easy to use for the users.  In my case, the Business Objects universe hides the complexity of the snowflake schema from the users--to them it still looks like a star.  When I tested with Microsoft SQL Server 2008, I found that on average queries against the snowflake schema DW were about 30% slower than against the star schema, but still acceptibally fast with a small-to-medium size DW.

VHF

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

View user profile

Back to top Go down

Re: Snowflaking or not of Employee Transaction Dimension

Post  Jeff Smith on Thu Aug 15, 2013 1:32 pm

I think the Employee Transaction Dimension should go away. Either put the Grade, Position, and Job info on the Member Dimension or on the Fact HR Snapshot. The Benefit of putting Grade, Position, and Job on the Member Dimension is if you put the Member Dimension on any other fact, you will have the Grade, Position, and Job info as well without making the file bigger.

And if you chose to leave it as a snowflake, the member dimension should join to HR Fact through the Employee Transaction Dimension and not directly to the Fact.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Snowflaking or not of Employee Transaction 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