Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Employee compensation - Star or Snowflake?

2 posters

Go down

Employee compensation - Star or Snowflake? Empty Employee compensation - Star or Snowflake?

Post  jkaszynski 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

Back to top Go down

Employee compensation - Star or Snowflake? Empty Re: Employee compensation - Star or Snowflake?

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

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

Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum