Performing Lookups
Last updated
Last updated
Performing a lookup typically involves searching for a specific piece of information within a larger set of data or databases. In Osmos, once you have created your Dataset(s) and have added at least one Table, you are ready to perform a look-up.
A Lookup is created in the data transformation phase and is managed through a QuickFix.
Customer ABC is ingesting customer data from various providers. Both the customer ID and customer name are required fields for their destination system. Unfortunately, the customer name is not included in the majority of the customer source data but all of the source data includes the customer ID. How are they able to populate the destination system with the customer name?
Datasets
Pipelines and/or Uploader
Two Dataset Tables are required.
Customer List, in this scenario, we will call it Customer Master, and it includes the two required fields, customer ID and customer Name. Other fields can be included but won't be used for this scenario
The ongoing data that will be uploaded and/or run through the pipeline will include at least one field to reference the data in the look-up. For this scenario, the Source Data will be called ABC Co Provider Monthly Detail. This data includes fields such as customer ID.
Step 1: Create the Dataset. Within that Dataset create a table for your Customer Master.
Step 2: Upload the Customer list into the Customer Master Table
For this scenario, the Dataset name is called Provider Information and the Table is called Customer Master.
For detailed steps, go here.
Foreign key lookups are the safest way to ensure referential integrity, but note that lookups can be made based on any values within tables
In a Pipeline configuration, the lookup is configured in the column mapping. The lookup is configured by way of a QuickFix.
Step 1: Map the Source, Customer ID field, to the Customer Name Destination Column
The source field is also called the match field in the QuickFix lookup.
In the column mapping, map the Source field, match field, to the desired look-up Destination Column field. The match field is required in the source field to trigger the Datasets Lookup QuickFix
Step 2: Create the Lookup
Once the column is mapped, go to QuickFixes, and select Datasets Lookup.
Configure the Lookup:
Select the Dataset called Provider Information.
Select the Table called Customer Master.
Select the Lookup Field called Customer Name
Select the Matcher Field called Customer ID
Hit Submit
The result in the Destination Column will be the customer name.
In a Pipeline configuration, the lookup is configured in the column mapping. The lookup is configured by way of a QuickFix.
Step 1: Map the Source, Customer ID field, to the Customer Name Destination Column
The source field is also called the match field in the QuickFix lookup.
In the column mapping, map the Source field, match field, to the desired look-up Destination Column field. The match field is required in the source field to trigger the Datasets Lookup QuickFix
Step 2: Create the Lookup
Once the column is mapped, go to QuickFixes, and select Datasets Lookup.
Configure the Lookup:
Select the Dataset called Provider Information.
Select the Table called Customer Master.
Select the Lookup Field called Customer Name
Select the Matcher Field called Customer ID
Hit Submit
The result in the Destination Column will be the customer name.
