DW design question - user/role integration to the data

View previous topic View next topic Go down

DW design question - user/role integration to the data

Post  bt.solidbee on Tue Jul 24, 2012 5:22 am

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

bt.solidbee

Posts : 1
Join date : 2012-07-24

View user profile

Back to top Go down

Re: DW design question - user/role integration to the data

Post  ngalemmo on Tue Jul 24, 2012 10:58 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

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