Is there a proper way to use a snowflake design?

Hello,

I am trying to develop a cube that includes employee counts. Looking at Ralph Kimball's article about Human Resource data
http://www.dbmsmag.com/9802d05.html it looks like I should have an Employee Transaction Dimension table of all the employees and all their actions over time. I also should have a fact table which "is a snapshot that contains monthly numeric summaries that are difficult to calculate from the underlying transactions." That all works for me.

My question is, what is the next step with this? Specifically, I need to have dimensions on the Employee Transaction Dimension table such as their Department, Location, Rating, Gender, etc. Do I have those dimensions (Department, Location, Ratings, etc.) as a dimension on the Employee Dimension table or do I put them directly on the Fact table?

Also, will Analysis Services be able to deal with that? Meaning will I still be able to browse the cube correctly?

Thanks for the help.

-Gumbatman

Hi Gumbatman,

It will be hard to give you details answer of this question. You need to go over data model books.

Quick answer is as you might know in fact table you keep only numeric measures and key fields to your dimensions.

Looks you need dimensions employee, department, location, ratings, gender and keep transactions in fact table.

-Ashok

sql