Dealing with Mergers

We are provider for lot if financial institutions and have an asp mode.

Problem statement

Lot of times the FI's merge together either to form a new fi in or system or customers are bought from merging FI to the merged FI.
This leads to update of customer number and Fi number in our system.
We are trying to have a solution which will preserve the history as well us enable Fi's to look at historical transactions.
For example we may want to combine results of merging and merged fi for some instances or have individual reports for other instances.


Proposed solution

I am thinking of using the TYPE 3 change to track it. I will add a column of customer and fi which has the finalmergedfi number and finalmergedcustomernumber. This column will be used where we have to combine the results of merged and merging fi.

The potential drawback is that we can track only one level of change.

The other solution was to use to concept of Bridge Table.


Let me know your thoughts.

If you're looking to do "what-if" type of analysis, then I say TYPE 3 is the way to go. In this way you can view a row in different states without fussing with a bridge table. If you really need to go more than one level deep, and you do not want to add additional columns to the main dimension, then you may want to consider using a type 3 mini dimension as a companion to the main customer dimension. In the mini dimension, for example, you'll have the surrogate key, finalmergedcustomernumber, origcustomernumber1, origcustomernumber2, origcustomernumbern. This mini dimension can be used along side the main dimension.

Hopefully I understood your situation ok!

Thanks for the reply,

I have been also thinking of an hybrid approach. Use Type 3 and then use in conjunction with Mini Dimension or bridge . Mini dimension as you suggested sounds better. Most of the reporting for one level deep will go thru the dimension. For compliance /auditing purpose or to track the exact details, the mini dimension will come in picture.