A lookup table contains predefined data that is used to look up values and find matching patterns for data. This enables data elements to be automatically classified through data classification.
Lookup tables usually contain finite non-overlapping sets of data that help you define a standard set of values for data elements. In Metadata Command Center, you can import and publish CSV files as lookup tables that you can use in data classification. When you choose to look up values of a column in the lookup table in your data classification inclusion rule, the rule uses the column data in the lookup table to classify data.
For example, let us assume that your organization's data set contains company specific non-overlapping product IDs that follow a particular pattern. You can copy these product IDs in a CSV file, and upload the CSV file as a lookup table in Metadata Command Center. You can then create a data classification rule using this lookup table to classify the columns in the specified data set as Product ID.
You must be a Catalog Administrator user to upload and publish lookup tables in Metadata Command Center. For information about uploading lookup tables, see Importing and publishing a lookup table.
Importing and publishing a lookup table
Import and publish multiple lookup tables in Metadata Command Center that you can use to look up values with matching data patterns while classifying data.
To import and publish a lookup table, ensure that you define appropriate roles and select the Manage Reference Data feature for that role when configuring privileges for the Metadata Command Center service in Informatica Intelligent Cloud Services Administrator. For more information about feature privileges that the organization administrator can configure for user roles, see the Introduction and Getting Started help.
1Click New.
2Select Lookup Table from the list of asset types.
3Click Create.
The New Lookup Table page opens.
4Enter a name for the lookup table. The name can contain only letters, digits, underscores, and spaces.
The name that you enter is converted into a unique identifier that you can use in classification expressions while creating a data classification rule.
5Optionally, enter a description.
6In the Code Page for Delimited Files, select a code page to match the encoding of the lookup table file that you upload. Use this option to ensure that lookup table files with non-English characters are displayed correctly when published. Default is UTF-8.
Select one of the following options:
- UTF-8. Select if the lookup file contains Unicode and non-Unicode characters.
- Shift-JIS. Select if the lookup file contains double-byte characters.
7Click Browse to import a CSV file. Read the following rules and guidelines before you upload a CSV file:
- The size of the CSV file must not exceed 5 MB.
- The file must contain headers, use a comma as the delimiter, and can contain up to 5 columns. Each column is considered as a string data type.
- The first line in the file must be a header based on which column names are identified.
- Column names can contain letters, digits, and underscores. They cannot contain special characters. ASCII punctuation symbols such as @ # & ? and ~ are not allowed.
- The column size must not exceed 150 characters.
- The row length must not exceed 750 characters.
8Click Save to publish the lookup table.
A Lookup Table Import job is initiated. You can click View Status to monitor the status of the job on the Overview page for that import job. If the publishing is successful, the job status changes to Published and the last job status changes to Completed. You can see the top 20 sample values of the lookup table when the publishing is complete.
After successfully publishing a lookup table, you can perform any of the following actions:
Example: Use a lookup table in a data classification expression
After you import and publish lookup tables, Metadata Command Center allows you to use the look up tables in data classification expressions to create inclusion rules. You can look up any attribute, such as the name, inferred data type and frequent values on a column in a lookup table.
Let us construct an expression that looks up city names from the lookup table named 'cities' that we have imported and published. The expression checks if all the frequently occurring values in the columns of the data set are names of cities that are present in the lookup table named 'cities'. Depending on the matches, the expression classifies the columns in the data set as 'city'. We can construct the expression in the following way:
NAME LIKE "%City%" AND FORALL(FREQUENT_VALUES,v->v IN cities.name)
We use attributes (NAME, FREQUENT_VALUES), operators (AND, IN), and built-in functions (FORALL) to construct the above expression. Let us simplify the expression to understand the function that each phrase performs:
•NAME LIKE "%City%": This phrase evaluates the expression for column names that contain the word 'City'.
•FORALL: This function is used with frequent_values to evaluate the expression for all the frequently occurring records in the specified column of the lookup table.
•FREQUENT_VALUES,v->v IN cities.name: This phrase evaluates the items in the array for the most frequently occurring values in the column named 'name' of the lookup table called 'cities'.
Editing a lookup table
You can edit an existing lookup table to change the description or overwrite the last imported CSV file.
To edit an existing lookup table, perform the following steps:
1In Metadata Command Center, go to the Explore page.
2Click the arrow () icon on the top of the page and select Lookup Tables.
On this page, you can browse through all published and unpublished look up tables and view basic information about each table.
3Hover your mouse over the lookup table that you want to edit, and click the Action menu on the far right.
4Select Edit from the menu.
The overview page of the selected lookup table appears. The overview page displays the name, description, the name of the CSV file that you had last imported, and the top 20 sample values from the lookup table.
5Update the description of the lookup table, or click Browse to overwrite the last imported CSV file with another one.
Note: You cannot change the attributes of the last imported CSV file. If you attempt to overwrite the last imported CSV file with another CSV file that contains different attributes, an error message appears.
6Click Save to publish the lookup table.
If the publishing is successful, the status of the job changes to Published and the last job status changes to Completed. You must refresh or reload the page for your changes to reflect.
) icon on the top of the page and select