Employee compensation - Star or Snowflake?
2 posters
Page 1 of 1
Employee compensation - Star or Snowflake?
I'm designing a data mart for employee compensation. The measures will include things such as salary and various bonuses.
Most of the data marts I've designed so far have fallen rather gracefully into a star schema. For this one, however, I have dimensions for Currency, Date and Employee. These are straightforward. Beyond this, everything else that is a logical way of slicing the data is actually an attribute of Employee in some way. For example, the users would like to see compensation by Location (Office, City, State, Country). But location is derived from the employee's job history record. They would also like to see compensation by Organization (Team, Department, Subcompany), but this is an attribute of the employee's position, which again is derived from the job history record. For consistency with their other reporting tools, the users want Location and Organization to be separate dimensions. They only want to see demographic-type information in the Employee dimension (i.e. Name, DOB, etc.) They don't want to have to browse to the Employee dimension to find the Organization and Location hierarchies.
So, my question is, what is the best way to model this? Should I lump all of these attributes into the Employee dimension table? Should I snowflake Organization and Location tables off of Employee? Or should I find a way to add Location and Organization keys to my fact record so that I can have a star schema? In case it makes a difference, I'm tracking these changes over time, so some of these attributes will be Type 2.
Any thoughts or advice would be greatly appreciated!
Most of the data marts I've designed so far have fallen rather gracefully into a star schema. For this one, however, I have dimensions for Currency, Date and Employee. These are straightforward. Beyond this, everything else that is a logical way of slicing the data is actually an attribute of Employee in some way. For example, the users would like to see compensation by Location (Office, City, State, Country). But location is derived from the employee's job history record. They would also like to see compensation by Organization (Team, Department, Subcompany), but this is an attribute of the employee's position, which again is derived from the job history record. For consistency with their other reporting tools, the users want Location and Organization to be separate dimensions. They only want to see demographic-type information in the Employee dimension (i.e. Name, DOB, etc.) They don't want to have to browse to the Employee dimension to find the Organization and Location hierarchies.
So, my question is, what is the best way to model this? Should I lump all of these attributes into the Employee dimension table? Should I snowflake Organization and Location tables off of Employee? Or should I find a way to add Location and Organization keys to my fact record so that I can have a star schema? In case it makes a difference, I'm tracking these changes over time, so some of these attributes will be Type 2.
Any thoughts or advice would be greatly appreciated!
jkaszynski- Posts : 3
Join date : 2010-01-26
Location : Boston, MA
Re: Employee compensation - Star or Snowflake?
Just because an attribute is dependent on another attribute doesn't mean you can't split out to separate dimensions. Geography dimensions are almost as common as time dimensions. Break the dimensions out as needed to support simplified reporting. This is not a snowflake since you are not building relationships between the dimensions.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Star vs Snowflake with many different attributes
» Snowflake or Star Schema?
» Converting Snowflake to Star
» Customer addresses in a high volume retail environment
» Confusion - star, snowflake, bridge table
» Snowflake or Star Schema?
» Converting Snowflake to Star
» Customer addresses in a high volume retail environment
» Confusion - star, snowflake, bridge table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum