2 Replies Latest reply: Oct 29, 2015 6:07 PM by Nate Untiedt RSS

    Using translation tables to link two databases

      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 SystemCustomer 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

        • Re: Using translation tables to link two databases
          Stefan Wühl

          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;
          
          • Re: Using translation tables to link two databases
            Nate Untiedt

            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!