Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

History Preservation

2 posters

Go down

History Preservation Empty History Preservation

Post  OrionPax Fri May 16, 2014 3:57 am


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.

- 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


Posts : 2
Join date : 2014-05-16

Back to top Go down

History Preservation Empty Re: History Preservation

Post  nick_white 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


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

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum