/
Lookup Group Mapper

Lookup Group Mapper

This article is a hybrid between Technical Documentation for a Solution and Workflow Documentation. Once each part has been elaborated on and defined enough to make sense on its own, it should be broken down appropriately.

 

This document assumes the reader is familiar with Benchmark Lookups and the Conversion staging standard. We’ve documented the most common lookup groups and the tables they relate to in the following document Field Table to Lookup Table Relationships.xlsx

 

The Lookup Group Mapper is a collection of tools that aim to ensure the Imported customer data that relates to lookup groups is properly configured in the Lookup table (tblLookup) in order to provide an acceptable user experience. It consists of a LookupGroup Mapping file (CSV), staging table(s) (i.e. PTG_ImportStaging_LookupGroupMap), table-specific mapping procs (i.e. dbo.spLookupMapping_ImportStagingTblParty)

 

Field Table to Lookup Table Relationships

Below is a preview of the Lookup Groups that are referencing data on the Benchmark tables tblPartyAddress and tblPartyName as documented on the Field Table to Lookup Table Relationships document.

1. Field Table to Lookup Table Relationships.xlsx using this documentation we can build the logic used in the LookupGroup Mapper.

 

 

LookUpGroup Mapping file

A.K.A. the CASE statement killer. No more mapping data through obscure CASE statements in SQL!, instead this mapping is to be populated by a joint effort between the Conversion Developers, System Engineers and the Customer, so that any value mapping is captured and documented in this sheet, and is able to be updated with no logic update required.

  • Column A: Contains the LookUp Group in Benchmark that we’re looking to set the code for. The logic will look for the code value in Column B within this lookup Group.

  • Column B: Benchmark LookupGroup Value. This is the value we’re mapping the source data to, it should already exist in Benchmark.

  • Column C: The Source LookupGroup Value. This is the value in the source data that we’re replacing with the Benchmark value in Column B.

2. A partially populated mapping file, missing some records on rows 5 and 6 for Column B

 

After staging the data into the staging tables, the Conversion Engineer checks which Lookups need to be maintained by looking at the Field Table to Lookup Table Relationships.xlsx for each of the tables staged.

i.e. image 1 shows the lookup groups for tblPartyName and tblPartyAddress.

Taking those examples, we’ll need to check that the data stored in [tblPartyName staging table].SuffixName exists in the PartySuffix Lookup Group.

 

  • Column C, the Conversion Engineer will populate with the staged values by running something like:

SELECT DISTINCT SuffixName FROM ImportStaging_tblPartyName

  • Column A, is just the name of the Benchmark Lookup Group to be populated/validated, in this case:

PartySuffix

  • Column B, is the Lookup Group Code value in the that we wish to change the source value to. Configuration Engineers would work with the customer to define any mapping conflicts.

3. The PartySuffix Lookup Group codes, as set up in a given Benchmark implementation.

 

Looking at Image 2, we have some source values (Column C) with no corresponding Benchmark values (Column B ), because they don’t exist on Benchmark (Image 3). So those rows would require some collaboration between the Configuration Engineers and the customer to determine how those would be configured in Benchmark. Upon determining that, the sheet can just be updated and reloaded with no logic update required!.

 

 

LookupGroup Mapper SSIS Package Step

The LookupGroup Mapping steps should happen after the relevant data has been staged on the staging tables. Below we have a screenshot of the LookupGroup Mapper steps in a package.

 

The typical SSIS Package layout for the LookupGroup Mapper, implementation may vary by project but intent would be the same

 

Load LookUpGroup Mapping File: The first step will import the mapping file into a staging table

Map LookupGroups File to Benchmark: This step will check that the values in Column B exist in Benchmark.

Map Benchmark LookupGroups to Staged data: This step will replace the staged data values with the mapped values in Column B.

i.e. Image 2, Import_staging.tblPartyName.SuffixName value ‘1' will be replaced with 'I’

 

Lookup Validation

After the lookups have been mapped, we’ll run the lookup validation steps which will check for each staged table that the lookups have been properly set up on Benchmark.

 

These procs should insert an exception row into the exception’s table for each data point that’s not configured.

 

We’ll need to determine how to output these exceptions to be analyzed by the team. We can put them in a txt file every time there’s exceptions resulting from a package execution.

 

 

After these steps, there will be more steps to load the data into the final Benchmark tables depending on Exceptions.

Related content