Developer Tool Guide > Physical Data Objects > Relational Data Objects
  

Relational Data Objects

A relational data object is a physical data object that has a relational table, view, or synonym as a source. You can include a relational data object in a mapping, mapplet, or profile.
The relational data object describes a resource in a database. When you add a relational data object to the repository, you import it from a source database. You can change the relational data object definition after you import it to the repository. You can add and remove ports, define primary keys, and configure relationships between multiple relational data objects in the repository. You can change or parameterize the default connection, the database owner, and the resource name.
When you add the relational data object to a mapping, mapplet, or profile, you indicate whether the you want to create a read or a write object. You can configure different run-time properties based on whether the object is a source, a target, or a lookup table.
The following figure shows a sample relational data object in the editor:
The Overview view shows the name, description, and columns of the relational data object. A relational data object that is open in the editor also has Keys, Relationships, and Advanced views.
You can create primary key-foreign key relationships between relational data objects whether or not the relationships exist in the source database.
You can add multiple relational data objects to a mapping or mapplet as sources. When you add multiple relational data objects at the same time, the Developer tool prompts you to add the objects in either of the following ways:
You can import the following types of relational data objects:

Importing a Relational Data Object

Import a relational data object to add to a mapping, mapplet, or profile.
Before you import a relational data object, you must configure a connection to the database.
    1. Select a project or folder in the Object Explorer view.
    2. Click File > New > Data Object.
    The New dialog box appears.
    3. Select Relational Data Object and click Next.
    The New Relational Data Object dialog box appears.
    4. Click Browse next to the Connection option and select a connection to the database.
    5. Click Create data object from existing resource.
    6. Click Browse next to the Resource option.
    The Select a Resource dialog box appears.
    7. Select the table, view, or synonym that you want to import.
    8. To filter data objects, enter a name in the Filter section and click Search.
    Enclose the name in double quotes ("") to search for case-sensitive object names.
    9. Enter a name for the physical data object.
    10. Click Browse next to the Location option and select the project where you want to import the relational data object.
    11. Click Finish.
    The data object appears under Physical Data Objects in the project or folder in the Object Explorer view.

Key Relationships

You can create key relationships between relational data objects. Key relationships allow you to join relational data objects when you use them as sources in a customized data object or as read transformations in a mapping or mapplet.
When you import relational data objects, the Developer tool retains the primary key information defined in the database. When you import related relational data objects at the same time, the Developer tool also retains foreign keys and key relationships. However, if you import related relational data objects separately, you must re-create the key relationships after you import the objects.
To create key relationships between relational data objects, first create a primary key in the referenced object. Then create the relationship in the relational data object that contains the foreign key.
The key relationships that you create exist in the relational data object metadata. You do not need to alter the source relational resources.

Creating Keys in a Relational Data Object

Create key columns to identify each row in a relational data object. You can create one primary key in each relational data object.
    1. Open the relational data object.
    2. Select the Keys view.
    The following figure shows the Keys view for a sample relational data object that is open in the editor:
    The Keys view shows a key defined for the relational data object. The view also shows the name, description, and available and selected columns for the key.
    3. Click Add.
    The New Key dialog box appears.
    4. Enter a key name.
    5. If the key is a primary key, select Primary Key.
    6. Select the key columns.
    7. Click OK.
    8. Save the relational data object.

Creating Relationships between Relational Data Objects

You can create key relationships between relational data objects. You cannot create key relationships between a relational data object and a customized data object.
The relational data object that you reference must have a primary key.
    1. Open the relational data object where you want to create a foreign key.
    2. Select the Relationships view.
    3. Click Add.
    The New Relationship dialog box appears.
    4. Enter a name for the foreign key.
    5. Select a primary key from the referenced relational data object.
    6. Click OK.
    7. In the Relationships properties, select the foreign key columns.
    8. Save the relational data object.