Hi Community!

We are starting the week by exploring best practices for effectively working with Lookup Items and integrating them into your Data Quality (DQ) rules. Lookups are essential in ONE Web Application as they provide a list of predefined values for attributes, enhancing data validation and detection capabilities.

If you remember we have previously covered how to create Lookup files in rules in our Working with DQ Rules series, today we will expand on the lookup items feature. 

Overview of Lookup Items

Lookup Files in ONE Web Application enable you to use reference data, offering a catalog of predefined values for an attribute. You can utilize Lookup Files in both DQ Evaluation rules and Detection rules. To find Lookup Files, go to Data Quality → Lookup Items.
 

Uploading and Creating Lookup Files

Creating Lookup Files

To create a Lookup File, follow these steps:

1. In Knowledge Catalog, navigate to Data Quality.
2. Select Lookup Items and click Create.
3. Provide the required details, such as Name, Description (optional), Source catalog item, Key attribute, and Duplicities handling.

   

    

4. Choose matching options like Remove Accents, Remove Repeated Characters, Squeeze Whitespaces, and Upper-Case if needed.
5. Save and Publish the changes.

You can also directly create Lookup Files from Catalog Items in the ONE Web Application. Simply choose the desired Catalog Item and select either Create new Lookup item from the Data tab or the Profile & DQ insights tab.
 

Creating Lookup Files in Rules

Lookups can be built as a result of rule configuration by selecting the is from catalog item option when defining rule logic. System-created lookups, created automatically during rule implementation, will also be listed on the lookups page. Take caution while editing them to avoid breaking the rule.
 

Uploading Lookup Files

To bulk upload Lookup Items, navigate to Data Quality → Lookup Items → Upload. Drag and drop the files or use the file explorer to search for lookup files (.lkp).
 

Uploading a New Version of the Lookup Item

To upload an updated version of a lookup item, select the required item, choose Upload new version, and upload the new file. Once uploaded, the lookup will be in draft state, and you can publish it to complete the update.
 

Restoring Previous Lookup Versions

You can restore a previous version of the lookup by going to the History tab and selecting Restore this version on the desired version.

Using Lookup Items in Detection Rules

Detection Rules automatically assign terms to attributes based on lookup values. To use Lookup Items in Detection Rules, follow these steps:

1. In Data Quality, go to Rules and select Create.
2. Provide the rule details, including Name, Description, Data source, Owner, and Steward.
3. Define the rule logic as Detection.
4. Use isInFile to reference lookup files in the Advanced Expression rule builder.
    

Using Lookup Items in DQ Rules

Data Quality rules are used for DQ evaluation to validate data based on specific dimensions like validity, uniqueness, completeness, and accuracy. To utilize Lookup Items in DQ rules, follow these steps:

1. In Data Quality, go to Rules and select Create.

   

    

2. Provide the rule details, including Name, Description, Data source, Owner, and Steward.
3. Define the rule logic based on the DQ dimension that it should validate, such as Validity, Uniqueness, Completeness, or Accuracy.
4. Use isInFile and the name of the lookup in the format lookup:<name> or <lookupItemId> to reference the lookup files.
    

By following these best practices, you can seamlessly work with Lookup Items and leverage their power in your Data Quality processes.

If you have any questions or best practices to share with the community let us know in the comments below