DW design question - user/role integration to the data
2 posters
Page 1 of 1
DW design question - user/role integration to the data
I am looking to build an SSAs cube . Straight to the point, here is the source table structure I have,
Table : User (UserID, Username)
Table : Role ( RoleID)
Table: Client(ClientID, ClinetName)
Table :UserRoleLink (UserID,RoleID) , One user will have only one role, one role can go to multiple users
Table: Transaction (ClientID, TransDate, TransReference, Amount)
Table: TransRoleLink(ClientID,RoleID) , A role can have multiple ClientID, A clientID can go to multiple RoleID)
I identified 2 options with me now, Just wanted to know if there is any 3rd option available or which is the best option.
Option 1
DimUser(skUserID,UserID,UserName)
DimClient(skClientID,ClientID,ClientName)
FactTrans (skClientID, TransDate, TransReference, Amount)
FactRole(skUserID,skClientID) This fact has a link to the previous Fact , which i am not sure is correct.Also taking out the role concept and lining user to client here.
Option 2
DimClientUser(skClientUser,ClientID,ClientName,UserID)
FactTrans (skClientUser, TransDate, TransReference, Amount) Simply duplicate the rows to accomodate every combination clientUser.
Thanks in advance
Table : User (UserID, Username)
Table : Role ( RoleID)
Table: Client(ClientID, ClinetName)
Table :UserRoleLink (UserID,RoleID) , One user will have only one role, one role can go to multiple users
Table: Transaction (ClientID, TransDate, TransReference, Amount)
Table: TransRoleLink(ClientID,RoleID) , A role can have multiple ClientID, A clientID can go to multiple RoleID)
I identified 2 options with me now, Just wanted to know if there is any 3rd option available or which is the best option.
Option 1
DimUser(skUserID,UserID,UserName)
DimClient(skClientID,ClientID,ClientName)
FactTrans (skClientID, TransDate, TransReference, Amount)
FactRole(skUserID,skClientID) This fact has a link to the previous Fact , which i am not sure is correct.Also taking out the role concept and lining user to client here.
Option 2
DimClientUser(skClientUser,ClientID,ClientName,UserID)
FactTrans (skClientUser, TransDate, TransReference, Amount) Simply duplicate the rows to accomodate every combination clientUser.
Thanks in advance
bt.solidbee- Posts : 1
Join date : 2012-07-24
Re: DW design question - user/role integration to the data
How many roles may a single transaction row have in relation to client/user? Can only one role, as it relates to the client, be assigned to a single transaction? Then just treat everything as individual dimensions and have FK's to client, user and role in the transaction fact and get rid of the 'link' tables. If a single row can play many roles you need to either allocate measures in the fact to correspond with the role, or you build a bridge table to link this row to a list of roles that applied at the time of the transaction.
Similar topics
» Data Integration and ETL is Shifting to the End-User
» Data mart Design Question
» Design Question on Clinical trial data model
» User fields design issue
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Data mart Design Question
» Design Question on Clinical trial data model
» User fields design issue
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum