(Star or Snowflake) and Snowmelt?

Question

(Star or Snowflake) and Snowmelt?

Wesley Norton

SSCrazy

Points: 2827
More actions
March 4, 2010 at 1:55 pm

#219636

First - I have searched, and haven't come across an answer that makes sense to me, so far.
The Setup: I am trying to design a quick and dirty example of what a data warehouse might look like for my boss. I would undoubtedly be purchasing a few books on the subject and doing more before I get really going into it. I just wanted to show what the benefits would be.
Where I am now: Almost everything I have seen relates to products, what I have are employees. I started with a fact table that consisted of three keys: date_key, status_key, employee_key. Date key is pretty straight forward, the status key refers to an employee's status (Active, Terminated, Leave, Layoff, etc.) the employee key refers to a specific employee. So a new row is created each time an employee has a change in status. (Created being a loose term, this would be loaded once a day, with one large push at the beginning from historic tables.)
The Problem: I would like to also record other changes such as: location, company, employee type(staff,hourly, etc), possibly others.
The Question: Do I create additional fact tables (like the one mentioned above with the three keys) or do I branch out, and create a table that describes different types of changes with that table linking to the different dimensions?
I have not yet tried the second way, so I can only provide table creation scripts for the first idea. Let me know if those would be useful. Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 1

So from the sounds of it you might have three dimension tables and then a factless fact table? The date, status, and employee keys would be placed in a fact table that you would simply use to get row counts on.

You wouldn't insert a new employee key when the status changed in the employee table, but in the factless fact table you would.

It sounds like you then want to implement what is called a slowly changing dimension (type 2) on the employee dimension. Everytime one of the attributes of an employee changes you would insert a new record with the new information. This way you can track historical changes.

I would check out some of the Kimball Groups books on Amazon and the Kimball Group website for tips and advice. Another good site to check out in regards to methodology is SQLBI.com.

Good luck.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

Wesley Norton SSCrazy Points: 2827 More actions · Answer 2

Outstanding, that's exactly what I am looking for, thank you very much.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 3

I wouldn't rule out a transaction based fact table, as you've indicated a change would cause a row insertion. If it's your first cube, you've chosen a very challenging one.

Wesley Norton SSCrazy Points: 2827 More actions · Answer 4

Thanks for the idea, I will look into that as well. I was hoping that by working with data I had, and knew about, that it would be somewhat easier. Would you suggest a different approach, understanding that I deal with employees and companies? Not products, so much.