Creating query relationships

A relationship defines the dependency each source has on another. Typically, there is a master source and its related child sources. You can specify the type of relationship between the sources.

To create a query relationship:

  1. Open the integration that contains the ODBC or text sources you’re creating a relationship for.
  2. In the Integration window, double-click Query Relationships.

The Relationships window opens.

  1. Examine the queries you are using for the integration. Decide which query is considered the “root” and which queries are considered child queries. Remember that each query must be directly or indirectly connected to the root. No query or group of queries can remain unconnected.
  2. Integration Manager uses a graphical representation to show the relationships among the queries used for an integration. To add the query relationship, draw a line by dragging your mouse pointer between the corresponding column or columns in the queries. The query you draw the line from is called the master. The query you draw the line to is called the child. When Integration Manager reads a record from the master query, it also reads the appropriate number of records from the child queries.

For example, two queries are used to retrieve bank transaction information. One query retrieves basic information, including checkbook ID, number, and amount. The other retrieves line detail information. To allow Integration Manager to work with both queries, a relationship must be created between them. In this case, the BR TRX Header query would be considered the master. Each time Integration Manager reads a record from this query, the corresponding record should be read from the BR TRX Line query.

To set up this relationship, you need to draw a line between the corresponding columns in the two queries. In this case, the corresponding column is Index Number. You can resize the window to show more of the query information.

To improve the performance of your integration, we recommend that you sort the queries based on the columns used for the query relationship. To sort the queries, open the Properties window for the source, and choose the Sorting tab.

  1. To set the relationship type, right-click the line connecting the two queries and choose Properties.

The Select Relationship Type window opens.

The relationship type indicates how many records exist in the child query for each record in the master. The four types of query relationships are summarized in the following table.

[spacer]

Type

Description

1

There are zero records or one record in the child for each record in the master.

2

There is exactly one record in the child for each record in the master.

3

There can be zero or more records in the child for each record in the master. This is the default relationship type.

4

There must be at least one record in the child for each record in the master.

The default relationship type is 3, indicating there are zero or more records in the child query for each record in the master.

  1. Select the appropriate relationship type and click OK.
  2. Click Close to close the Relationships window. When you close the Relationships window, all of the relationships you created are verified. If any problems exist, a message appears. Reopen the Relationships window and correct any problems.

Documentation Feedback