Self referencing dimension - How to store Parent Key/Id as type II change

View previous topic View next topic Go down

Self referencing dimension - How to store Parent Key/Id as type II change

Post  ehudzik on Fri Jun 29, 2012 10:05 am

Hi all,

I am currently trying to model a situation, similar to an employee and manager model, where each 'Associate' has a 'sponsoring associate', and can themselves sponsor other associates. The depth of the hierarchy is, in theory, infinite, and ragged. Sometimes an associates sponsor can change, and this history needs to be tracked. The Business Key is Associate Id. So within DimAssociate, I currently have the Sponsoring Associate Name, and AssociateId (Business key) as Type II changes. When an Associates sponsor changes, a new type II record is created for that Associate Id, containing the new Sponsoring Associate Id and Name. The PrimaryKey of DImAssociate is the surrogate key, AssociateKey

What are everyone's thoughts on this method of tracking this type of history? I have wondered if I should be storing the current associate Key of the sponsoring associate instead of the Associate ID. My reasons for storing the Associate Id of the sponsoring associate is that I don't want to overly complicate things by storing the AssociateKey, and then having to be concerned about updating it to the current AssociateKey etc. However, I am not trying to use this dimension in a cube in SSAS and want to set it up as a parent child hierarchy, but in order to create the self reference, the Usage and Parent IDs must be the PK of the table. As currently designed, the PK is the AssociateKey, not the AssociateID that is used as the self referencing column.

I hope my explanation is clear.



Posts : 1
Join date : 2012-06-29

View user profile

Back to top Go down

Re: Self referencing dimension - How to store Parent Key/Id as type II change

Post  sgudavalli on Fri Jul 20, 2012 5:36 am

how about creating a bridge to store the associations that do comprise of the natural key's instead of surrgoate key's.

dimassociate -> AssociateId, Associatekey, associatename, associateage, startdate, enddate, currflag
BridgeAssociation -> AssociationId, Associatekey, sub-associatekey, startdate, enddate, currflag


Posts : 29
Join date : 2010-06-10
Age : 33
Location : Pune, India

View user profile

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