Reference Tables

To create a reference table in the Developer tool, use one of the following methods:

The column that contains the correct or required values is called the valid column. When a transformation reads a reference table in a mapping, the transformation looks for values in the non-valid columns. When the transformation finds a non-valid value, it returns the corresponding value from the valid column. You can also configure a transformation to return a single common value instead of the valid values.

The valid column can contain data that is formally correct, such as ZIP codes. It can contain data that is relevant to a project, such as stock keeping unit (SKU) numbers that are unique to an organization. You can also create a valid column from bad data, such as values that contain known data errors that you want to search for.

For example, you create a reference table that contains a list of valid SKU numbers in a retail organization. You add the reference table to a Labeler transformation and create a mapping with the transformation. You run the mapping with a product database table. When the mapping runs, the Labeler creates a column that identifies the product records that do not contain valid SKU numbers.

You create a reference table with a single column when you want to use the table data in a pattern-based parsing operation. You configure the Parser transformation to perform pattern-based parsing, and you import the data to the transformation configuration.

When the reference data warehouse stores the column data, the Informatica services identify the table as a managed reference table. When another database stores the column data, the Informatica services identify the table as an unmanaged reference table. The Content Management Service uses the database connection for the reference data warehouse.

To maintain reference tables, you must have the following privileges in the Content Management Service:

Use the Security options in the Administrator tool to review or update the service privileges. To edit data in an unmanaged reference table, verify also that you configured the reference table object to permit edits.