Hi everyone,
I have a question about how to handle different Business Key structures in multiple source systems.
In case of a single column BK with different data types I would use the same
hub with a datatype which is capable to hold the BK from different sourcesystems.
But how to handle a BK (for example Customer-No) consisting of one column in sourcesystem A and 2 (or more) columns in sourcesystem B?
I am thinking of three approaches:
Approach 1: Split the
hub into multiple
hubs by sourcesystem or structure-equivalent sourcesystems.
-
Hub_Customer_A (
HUBKEY, BK_COL1, SOURCESYSTEM, ...)
-
Hub_Customer_B (
HUBKEY, BK_COL1, BK_COL2, SOURCESYSTEM, ...)
=> any
satellites would attached either to
Hub_A or
Hub_B depending on sourcesystem.
=> any consolidation could be solved in a same-as-
link.
Approach 2: Modell a single
Hub with a common mix of columns
-
Hub_Customer (
HUBKEY, BK_COL1, BK_COL2, SOURCESYSTEM, ...)
=> in case of sourcesystem A, BK_COL2 would be empty and the Hashkey would calculated on the concatenation of BK_COL1 and BK_COL2 (regular for composite keys)
Approach 3: Modell a single
Hub with a common mix of Columns but with additional composite key column
-
Hub_Customer (
HUBKEY, BK_COMPOSITE, BK_COL1, BK_COL2, SOURCESYSTEM, ...)
=> in case of sourcesystem A, BK_COL2 would be empty. BK_COMPOSITE is a copy of BK_COL1 and the Hashkey would calculated on BK_COMPOSITE.
=> in case of sourcesystem B, BK_COMPOSITE is a concatenation of BK_COL1 and BK_COL2 and the Hashkey would calculated on BK_COMPOSITE.
What would be the best solution?
kind regards,
Christoph