You can use your translation table as MAPPING table in a MAPPING approach.
Basically concatenate your fact tables, but map the old value to the new value where a translation exists, if not, keep the old value as customer key.
Link the fact table to a dimension table that is also built of the two existing dimension tables.
Something along these lines:
DimOld: LOAD CustomerIDOld, Name INLINE [ CustomerIDOld, Name 1, Thomas 2, Jan ]; DimNew: LOAD CustomerIDNew, Name INLINE [ CustomerIDNew, Name A, Jan B, Peter ]; FactOld: LOAD CustomerIDOld, Value INLINE [ CustomerIDOld, Value 1, 10 2, 20 ]; FactNew: LOAD CustomerIDNew, Value INLINE [ CustomerIDNew, Value A, 30 B, 40 ]; TranslationMap: Mapping LOAD * INLINE [ CustomerIDOld, CustomerIDNew 2,A ]; FACT: LOAD CustomerIDNew as CustomerID, Value Resident FactNew; LOAD ApplyMap('TranslationMap',CustomerIDOld, CustomerIDOld) as CustomerID, Value Resident FactOld; DIM: LOAD CustomerIDNew as CustomerID, Name Resident DimNew; LOAD CustomerIDOld as CustomerID, Name Resident DimOld WHERE isnull(ApplyMap('TranslationMap',CustomerIDOld,NULL())); DROP TABLES DimOld, DimNew, FactOld, FactNew;
comm189794.qvw 145.2 K
You could use the ApplyMap function here to leverage the table you have created.
1. Load the example table you show here into a Mapping table
[Customer Number - Old System],
[Customer Number - New System],
From <where ever your table is>
where NOT IsNull([Customer Number - New System]); //Get rid of the nulls to make sure to only pull in values we have mapped to new.
2. When concatenating your old to your new, use the map created in step 1 and use the old customer number as the default fallback.
//Loading old data
ApplyMap('MapCustomerNumberOldToNew', [CustomerNumber],[CustomerNumber])) as [CustomerNumber],
As old customers get assigned new values you can add them to your translation table and they will be updated on the next load.
Hope that helps!