History Preservation

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.

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