Lookup Group Mapper
- Juan Brea (Deactivated)
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.
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.
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.
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.
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.