how to design deminsion for multiple mergers

[colon832]

Hi,

how do you design a dim table to allocate for multiple mergers.

Example:
if company ABC Inc. purchases CBA Inc. now CBA no longer exists. and
what about if later on xyz buys ABC? how do we reperesent nested mergers.

thanks

[BoxesAndLines]

What usually happens is one company is acquired. That company's data is integrated into the acquiring company. Data will then flow through seamlessly via existing ETL processes. It maybe that you want to flag the new data as a separate operating company which would require enhancing an existing dimension or simply adding a new one.

[colon832]

This doesn’t allow reporting with mergers that occurred in between the “original” and “current” structures. If A merges into B, and B then merges into C; you would not be able to report the A records as they were when they were merged into B.
do you design this as an SCD2 or SCD4 approuch?

DIM_INSTITUTION
DIM_KEY
CURR_INST_KEY
MRGR_EFF_DT
START_DT
END_DT
INST_MRGR_FLG
Historic_name
Current_name
Version

[Sponsored content]