Reference Data in the Developer Tool > Reference Tables
  

Reference Tables

You add a reference table to a transformation in the Developer tool. You configure the transformation to find reference table values in input data and to write the corresponding valid values from the reference table as output.
To create a reference table in the Developer tool, use one of the following methods:

Reference Table Data Properties

You can view properties for reference table data and metadata in the Developer tool. The Developer tool displays the properties when you open the reference table from the Model repository.
A reference table displays general properties and column properties. You can view reference table properties in the Developer tool. You can view and edit reference table properties in the Analyst tool.
The following table describes the general properties of a reference table:
Property
Description
Name
Name of the reference table.
Description
Optional description of the reference table.
The following table describes the column properties of a reference table:
Property
Description
Valid
Identifies the column that contains the valid reference data.
Name
Name of each column.
Data Type
Data type of the data in each column.
Precision
Precision of each column.
Scale
Scale of each column.
Description
Description of the contents of the column. You can optionally add a description when you create the reference table.
Include a column for low-level descriptions
Indicates that the reference table contains a column for descriptions of column data.
Default value
Default value for the fields in the column. You can optionally add a default value when you create the reference table.
Connection Name
Name of the connection to the database that contains the reference table data values.

Creating a Reference Table Object

Choose this option when you want to create an empty reference table and add values by hand.
    1. Select File > New > Reference Table from the Developer tool menu.
    2. In the new table wizard, select Reference Table as Empty.
    3. Enter a name for the table.
    4. Select a project to store the table metadata.
    At the Location field, click Browse. The Select Location dialog box opens and displays the projects in the repository. Select the project you need.
    Click Next.
    5. Add two or more columns to the table. Click the New option to create a column.
    The following table describes the properties for each column:
    Property
    Default Value
    Name
    column
    Data Type
    string
    Precision
    10
    Scale
    0
    Description
    Empty. Optional property.
    6. Select the column that contains the valid values. You can change the order of the columns that you create.
    7. The following table describes optional properties:
    Property
    Default Value
    Include a column for row-level descriptions
    Cleared
    Audit note
    Empty
    Default value
    Empty
    Click Finish.
The reference table opens in the Developer tool workspace.

Creating a Reference Table from a Flat File

You can create a reference table from data stored in a flat file.
    1. Select File > New > Reference Table from the Developer tool menu.
    2. In the new table wizard, select Reference Table from a Flat File.
    3. Browse to the file you want to use as the data source for the table.
    4. Enter a name for the table.
    5. Select a project to store the table metadata.
    At the Location field, click Browse. The Select Location dialog box opens and displays the projects in the repository. Select the project you need.
    Click Next.
    6. Set UTF-8 as the code page.
    7. Specify the delimiter that the flat file uses.
    8. If the flat file contains column names, select the option to import column names from the first line of the file.
    9. The following table describes optional table properties:
    Property
    Default Value
    Text qualifier
    No quotation marks
    Start import at line
    Line 1
    Row Delimiter
    \012 LF (\n)
    Treat consecutive delimiters as one
    Cleared
    Escape character
    Empty
    Retain escape character in data
    Cleared
    Maximum rows to preview
    500
    Click Next.
    10. Select the column that contains the valid values.
    11. The following table describes optional properties:
    Property
    Default Value
    Include a column for row-level descriptions
    Cleared
    Audit note
    Empty
    Default value
    Empty
    Maximum rows to preview
    500
    Click Finish.
The reference table opens in the Developer tool workspace.

Creating a Reference Table from a Relational Source

You can use a database source to create a managed or unmanaged reference table.
Note: You can configure a database connection in the Connection Explorer. If the Developer tool does not show the Connection Explorer, select Window > Show View > Connection Explorer from the Developer tool menu.
    1. Select File > New > Reference Table from the Developer tool menu.
    2. In the new table wizard, select Reference Table from a Relational Source. Click Next.
    3. Select a database connection.
    At the Connection field, click Browse. The Choose Connection dialog box opens and displays the available database connections.
    To create a managed reference table, connect to the reference data warehouse. To create an unmanaged reference table, connect to a different database.
    Click OK when you select a connection.
    4. If the database connection you select does not specify the reference data warehouse, select Unmanaged table.
    If you want to perform edit operations on an unmanaged reference table, select the Editable option.
    5. Select a database table.
    At the Resource field, click Browse. The Select a Resource dialog box opens and displays the resources on the database connection. Explore the database and select a database table.
    6. Enter a name for the table.
    7. Select a project to store the reference table object.
    At the Location field, click Browse. The Select Location dialog box opens and displays the projects in the repository.
    Select a project and click Next.
    8. Select the column that contains the valid values.
    9. The following table describes optional properties you can edit:
    Property
    Default Value
    Include a column for row-level descriptions
    Cleared
    Audit note
    Empty
    Default value
    Empty
    Maximum rows to preview
    500
    Click Finish.