Create a Reference Table from a Database Table
When you create a reference table from a database table, you create a metadata object in the Model repository. You optionally import the table data to the reference data warehouse.
When you create a managed reference table, you import the column data to the reference data warehouse. When you create an unmanaged reference table, you identify the database table that stores the column data. You can create a managed reference table from an OBDC connection or a native connection. You can create an unmanaged reference table from a native connection.
Before you create the reference table, verify that the Informatica domain contains a connection to the database that contains the reference data. If the domain does not contain a connection to the database, you can define one in the Analyst tool.
To define a database connection, click Manage > Connections.
Creating a Reference Table from a Database Table
To create the reference table, connect to a database and select the table that contains the reference data.
1. Select Design > Reference Table in the Analyst tool.
The New Reference Table wizard appears.
2. Select the option to Connect to a relational table.
To create a reference table that does not store data in the reference data warehouse, select Unmanaged Table.
To enable users to edit an unmanaged reference table, select the Editable option.
Click Next.
3. Select the database connection from the list of connections.
Click Next.
4. On the Tables panel, select a table.
5. Verify the table properties in the Properties panel.
Optionally, click Data Preview to view the table data.
Click Next.
6. On the Column Attributes panel, select the Valid column.
Optionally, specify an audit comment to write to the audit trail when a user updates the reference table.
If you create a managed reference table, you can perform the following actions on the Column Attributes panel:
- - Edit the reference table column names.
- - Add a metadata column for row-level descriptions.
7. Click Next.
8. Enter a name for the reference table. Optionally, enter a description for the reference table.
9. In the Folders panel, select a project or folder to store the reference table.
The Reference Table panel lists the reference tables in the location that you select.
10. Click Finish.