Defining structural relations in a data connection

Structural relations are used to combine different tables from a data source into a single view in Spotfire, which in turn can be used to create a data table.

Before you begin

You are using the installed client and you are adding relations from the Select data flyout (or Views in Connection dialog).

Procedure

Click on a view and select Add related tables > Define a new relation.
The New Relation dialog opens.
In the Primary key table drop-down menu, select which primary key table you want to use with the foreign key table.
The foreign key table is already pre-filled when defining a new relation. This is the table for which you wish to define a relation.
The Column drop-down menu lists all columns available in the foreign key table. Select the column to be used in the matching of rows.
Select the Primary key table to specify which table you wish to relate to the foreign key table.
Select Column to see all columns available in the primary key table and choose which will be used in matching the rows.
If you want to use a second pair of columns to match the tables, select the Second column pair check box.
If you want to use a third pair of columns to match the tables, select the Third column pair check box.
Specify the Join method to use in the relation. By choosing a suitable join type you might improve the performance of the data source.

Note: Not all join methods are supported by all data connectors. The default join type is Left outer. See below for some examples of different join methods and how they behave.
Click OK.

Results

The relation is saved.

Note: If you wish to edit or delete a relation you've defined, click on the resulting view in the Selected tables list, and then click on Edit relation or Remove. Relations created by a database administrator cannot be edited or deleted. Note that relations may include other relations so that deleting a relation for one table may also affect the resulting number of columns in other views in the Select data flyout.

Join methods

About this task

All of the examples below assume a join configured using the A columns. By default, another A column is created with the joined values, but you can always clear the check box for the duplicate column in the Views in Connection dialog.

Inner join

Data will be kept (and columns might be added depending on how data is modeled) only for rows that are available in both the foreign key table and the primary key table. If the foreign key table contains fewer rows than the primary key table, rows will be removed from the primary key table after this operation. If there are duplicates of the identifiers then there will be one row for each combination of values.

Left outer join

Data will be kept (and columns might be added depending on how data is modeled) only for rows that are available in the foreign key table. If additional rows exist in the primary key table, they will not be added to the foreign key table. If there are duplicates of the identifiers then there will be one row for each combination of values.

Right outer join

Data will be kept (and columns might be added depending on how data is modeled) only for rows that are available in the foreign key table. If the foreign key table contains fewer rows than the primary key table, rows will be removed from the primary key table after this operation. If there are duplicates of the identifiers then there will be one row for each combination of values.

Full outer join

Data will be kept (and columns might be added depending on how data is modeled) for all rows available in any of the tables. If additional rows exist in the foreign key table, they will be added to the primary key table. If there are duplicates of the identifiers then there will be one row for each combination of values.