History Preservation

View previous topic View next topic Go down

History Preservation

Post  OrionPax on Fri May 16, 2014 3:57 am

Hi,

I have a question regarding a modelling problem I have.

We currently take a daily snapshot of account balances per account. Each account has a consultant assigned to it at a particular point in time. Below is the Model structure and this works. The consultant is a separate dimension as it is used with other non-account related facts.

FactAccountSnapshot
- AccountSnapshotID (Surrogate Key)
- SnapshotDate (DimCalendar)
- AccountID (DimAccountID)
- ConsultantID (DimConsultant)
- ClosingBalance (Actual Closing Balance)
- MovementCYTD (Account Movement Calendar YTD)
- MovementFYTD (Account Movement Financial YTD)
- FeesCYTD (Fees Accumulated Calendar YTD)
- FeesFYTD (Fees Accumulated Financial YTD)
- FeesMTD (Fees Accumulated MTD)

Business has come with a requirement that they would like to be able to report based on the currently assigned consultant. If the consultant was related to the fact then this would be a type 6(hybrid) slowly changing dimension, but this is not the case.

Is an outrigger dimension recommended where the account dimension stores the surrogate keys between the account dimension and current consultant respectively, or create an additional dimension DimAccountCurrentConsultant that stores a single record for the current consult assigned to the account, and then add this surrogate key to the fact table?

I'm leaning towords the outrigger approach and then adding the attributes of the current consultant to the account dimension. This will seperate the current information for the account with the possibility of adding current consultant information for other dimensions (e.g. business process) at a later stage in the same manner.

Your help would be greatly appreciated.


Last edited by OrionPax on Fri May 16, 2014 4:16 am; edited 2 times in total

OrionPax

Posts : 2
Join date : 2014-05-16

View user profile

Back to top Go down

Re: History Preservation

Post  nick_white on Fri May 16, 2014 4:15 am

I'd do it as an outrigger for your Account Dim. Add the Consultant SK to the Account Dim and then treat it as an SCD 1 or 2 attribute of the Account depending on whether you want to hold the history of the Account-Consultant relationship or not

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

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