a quick question on dimension design...
We have a project detail page a asp.net based web page where admins enter the project details
There is a section in this page for Project Team where we can assign users to different role (like Project manager, Partner etc..)
There can me more than one user for the same role.
Right now we have total of 10 roles defined in the system.
Now the design question is while doing the dimension design do we need to create ten different dimensions? like DimPM, DimPart etc...
or there is any other way to do this by just creating one single dimension.
Basically from the cube we should be able to see who are the PM's, PART's etc for a project
I have two approaches right now in my mind...
a) Need ten dimensions for ten roles ex DimPM(PMKey, PMUserID, PMUserName). DimPart etc...
b) Create a Factless table with columns (FctLesskey, ProjectKey, PMKey, PRATKEY etc...)
c) Fact table - ProjectKey, FctLessKey, Amount etc...
Second approach -
a) Create a Role dimension (fill all the roles) and will have columns RoleKey, RoleName
b) Assuming there is a User dimension (Userkey, UserName etc...)
c) FactLess table - FactLessKey, UserKey, RoleKey
d) Fact Table - ProjectKey, FactLessKey, Amount etc...
Please let me which
View Complete Post