Lookups

Contents

1. What are Lookups?

2. Getting Started

What are Lookups?

Performing a lookup typically involves searching for specific 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.

Getting Started

Scenario

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?

Features

• Datasets

• Pipelines and/or Uploader

Inputs

Two datasets are required.

1. 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

2. 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.

Build the Dataset

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

Configuring the Lookup (non-foreign key-based)

Pipeline Configuration

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:

1. Select the Dataset called Provider Information.

2. Select the Table called Customer Master.

3. Select the Lookup Field called Customer Name

4. Select the Matcher Field called Customer ID

5. Hit Submit

The result in the Destination Column will be the customer name.

Uploader Configuration

In a Uploader 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:

1. Select the Dataset called Provider Information.

2. Select the Table called Customer Master.

3. Select the Lookup Field called Customer Name

4. Select the Matcher Field called Customer ID

5. Hit Submit

The result in the Destination Column will be the customer name.