Using translation tables to link two databases

[#1]

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

[swuehl]

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;

[Anonymous]

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!