Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
We're a manufacturing company and last year we switched from an older ERP software package to a newer one. I've developed an app with dashboards for the sales team, but it's currently only working with the data from the new system. I'd like to import the data from the old system, just by concatenating tables and loading 'AS'.
This works fine for most of the data, but the issue is that the system for assigning customer numbers is completely different between old and the new, so I can't just concatenate. I put together a sort of 'translation table' to link the two, but I want to make sure my approach was sound. What I have is this (With made-up numbers, of course):
Customer Number - Old System | Customer Number - New System |
---|---|
14807 | AA579153 |
43123 | AB383495 |
10561 | |
FD407291 | |
EF254790 | |
10219 | |
15692 | YR581526 |
RR608605 | |
EW695550 | |
45990 | UG656444 |
BK347076 | |
12552 | |
TT668254 | |
13736 | |
RY412691 | |
39806 | HH424348 |
LK625613 | |
AS591949 | |
20203 | |
28512 | TA381031 |
28204 | GX388615 |
CN568137 | |
41079 | GH502448 |
44227 | |
53427 |
As you can see, some customers are new since the adoption of the new system, others are present in both, and some only exist in the old system (Should they become customers again in the future, I'm not sure how we'll handle it, but that's another question).
I'm looking for advice on how to use this table to link the customer tables from both databases. I know the null values will cause problems, but I'm not sure how to handle them in the cases where each entry only exists in one database.
Apologies if I haven't articulated the problem well enough; I suspect it's a case of lacking the vocabulary on some level since I'm relatively new to all this. Thanks so much for any help you might offer.
Jonathan
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;
Jonathan,
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
[MapCustomerNumberOldToNew]:
Mapping Load
[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
Concatenate(CustomerTableName)
Load
...
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!