Reference Tables
A reference table contains the standard versions of a set of data values and alternative versions of the values that might occur in business data.
You can create and edit reference tables in the Developer tool. When you create a reference table, the Model repository stores the table metadata as a repository object.
You add a reference table to a transformation in the Developer tool. You use the transformation to find source data in the reference table and to write the alternative values in the output data.
To create a reference table in the Developer tool, use one of the following methods:
- •Create an empty reference table and enter the data values.
- •Create a reference table from data in a flat file.
- •Create a reference table from data in another database table.
Reference Table Structure
Most reference tables contain at least two columns. One column contains the correct or required versions of the data values. Other columns contain different versions of the values, including alternative versions that may appear in the source data.
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.
Reference Tables and the Parser Transformation
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.
Reference Table Properties
You can view and edit reference table properties in the Analyst tool. A reference table displays general properties that describe the repository object and column properties that describe the column data.
To view the properties, open the reference table and select the Properties panel.
To edit the properties, open the reference table and click Actions > Edit Table.
Reference Table Column Properties
The column properties contain information about the column metadata.
The following table describes the column properties:
Property | Description |
|---|
Name | Name of each column. |
Data Type | The datatype for the data in each column. You can select one of the following datatypes: - - bigint
- - date/time
- - decimal
- - double
- - integer
- - string
You cannot select a double data type when you create an empty reference table or create a reference table from a flat file. |
Precision | Precision for each column. Precision is the maximum number of digits or the maximum number of characters that the column can accommodate. The precision values you configure depend on the data type. |
Scale | Scale for each column. Scale is the maximum number of digits that a column can accommodate to the right of the decimal point. Applies to decimal columns. The scale values you configure depend on the data type. |
Description | Optional description for each column. |
Nullable | Indicates if the column can contain null values. |
Managed and Unmanaged 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 uses the database connection for the reference data warehouse.
To maintain reference tables, you must have the following privileges in the Content Management Service:
- •Create Reference Tables
- •Edit Reference Table Data
- •Edit Reference Table Metadata
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.
Note: If you edit the metadata for an unmanaged reference table in a database application, use the Analyst tool to synchronize the Model repository with the database table. You must synchronize the Model repository and the database table before you use the unmanaged reference table in the Developer tool.