Employee compensation - Star or Snowflake?

View previous topic View next topic Go down

Employee compensation - Star or Snowflake?

Post  jkaszynski on Fri Jan 21, 2011 1:25 pm

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!

jkaszynski

Posts : 3
Join date : 2010-01-26
Location : Boston, MA

View user profile

Back to top Go down

Re: Employee compensation - Star or Snowflake?

Post  BoxesAndLines on Sat Jan 22, 2011 11:24 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

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