Employee Dimensions

View previous topic View next topic Go down

Employee Dimensions

Post  juliang on Wed Aug 14, 2013 6:14 am

I currently have the below dimensions for my employees Fig.1, Users belong to a Team, a Team can have many Functions and the TeamFunction combo belongs to a BusinessUnit.

Have I followed the correct kimball convention? I feel like I have moved from a star to snowflake schema. Should I create one table with User, Team and flatten the multiple functions and BusinessUnits, this would be good when referencing Users as it would be one table I could join to and get the relevant team, functions and businessUnit. How would this work as a hierachy though, I would want the ability to drill through each layer.

Regards,
Julian

Fig.1

UserDim
UserKey (PK)
UserID (Natural key)
UserType
UserName
Forename
Surname
TeamKey (FK)
TelephoneWork
MobileWork
EmailWork

TeamDim
TeamKey (PK)
TeamID (Natural key)
TeamName

FunctionDim
FunctionKey (PK)
FunctionID (Natural Key)
FunctionName

TeamFunctionDim
TeamFunctionKey (PK)
TeamKey (FK)
FunctionKey (FK)
BusinessUnitKey (FK)

BusinessUnitDim
BusinessUnitKey (PK)
BusinessUnitID (Natural key)
BusinessUnitName



juliang

Posts : 4
Join date : 2013-08-13

View user profile

Back to top Go down

Re: Employee Dimensions

Post  Samsingh on Thu Aug 15, 2013 11:24 am

In my view either Star or Snowflake will work for your hierarchies however depending upon the BI / reporting tool you are going to use you should really decide on perfect star or snowflake or hybrid (where you may just add the parent keys to all flakes that way you may reduce the multiple join to go from one level to the other). If it MicroStrategy or BO i would go with combination of Star and Snowflake i.e. hybrid in which each flake will store all parent key field at least. so if you have User level fact and business user want to skip the fields and jump directly to TeamDim that can happen easily with just joining one more table BusinessunitDim -> TeamDim then going from Business unit Dim -> TeamfunctionDim -> FunctionDim -> TeamDim. Hope this make sense.

Thanks

Samsingh

Posts : 4
Join date : 2013-06-11

View user profile

Back to top Go down

Re: Employee Dimensions

Post  juliang on Thu Aug 15, 2013 12:12 pm

I will be building a cube on top with SSAS, thanks for the advice

juliang

Posts : 4
Join date : 2013-08-13

View user profile

Back to top Go down

Re: Employee Dimensions

Post  Sponsored content


Sponsored content


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