When you set up data connections from relational or other non-cube data sources, you have the possibility to add relations between original database tables in one data connection, so that they in Spotfire are joined to a single view (or data table).
Open the Views in Connection dialog.
Click Relations > New Relation....
Select the two data tables you want to connect from the Foreign key table and Primary key table drop-down lists.
Select the columns containing the identifiers from the Column drop-down lists.
You can specify a second pair of identifiers by selecting the check box Second column pair, and a third pair of identifiers by selecting the check box Third column pair.
If desired, change the Join method.
Comment: If you have knowledge of the tables in the data source, selection of a suitable join method may help increasing the performance of the data source. For example, if an inner join is sufficient, then a left outer join may cause unnecessary stress on the system.
Click OK when you have specified the necessary identifiers.
Response: A relation is created between the two tables. If you add the Foreign key table to the Views in connection list, then the columns in the Primary key table will be included automatically in the resulting view. To see which tables have a relation to a specific table, you can click on the arrow next to the table to expand the tree structure. Note that adding the Primary key table to the Views in connection list will not include the columns in the Foreign key table.
To delete a structural relation:
Note: Only structural relations defined in Spotfire (shown in blue color) can be deleted, not those set up by the database administrator. However, you can always add a database table to a connection view without including a related table by clearing the check box for the related table in the Views in connection list.
Open the Views in Connection dialog.
In the Available tables in database list, locate the table that is the foreign key table in the relation.
Expand the tree view by clicking on the arrow next to it.
Select the table in the expanded view.
Comment: This is the table that was set up as the Primary key table when the relation was created.
Click Relations > Delete Relation.
Response: The relation between the two tables is removed.
Comment: 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 Views in connection list.
Note: Only structural relations defined in Spotfire (shown in blue color) can be edited, not those set up by the database administrator.
Open the Views in Connection dialog.
In the Available tables in database list, locate the table that is the foreign key table in the relation.
Expand the tree view by clicking on the arrow next to it.
Select the table in the expanded view.
Comment: This is the table that was set up as the Primary key table when the relation was created.
Click Relations > Edit Relation....
Make the desired changes in the Edit Relation dialog and click OK .
Response: The relation is updated.
Note: You can also create relations between different data tables in Spotfire without actually joining them. This will form a looser connection between the tables but it can be used if you want to set up a details visualization using one of the data tables, limited by selections in the other. See Details on Manage Relations for more information.
See also:
Adding Data Connections to an Analysis