Reference Tables

Reference tables store metadata in the Model repository. Reference tables can store column data in the reference data warehouse or in another database. 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 stores the reference data warehouse database connection. You can specify an IBM DB2 database, a Microsoft SQL Server database, or an Oracle database as a reference data warehouse.

When you import data to the reference data warehouse from another database, use a native connection or an ODBC connection to import the data. When you specify an unmanaged database as the data source for a reference table, use a native connection to connect to the database.

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.

Create a reference table with a single column 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 reference data to the transformation configuration.

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

To edit data in an unmanaged reference table, verify also that you configured the reference table object to permit edits.