Details on Views in Connection


This dialog is automatically shown when you are setting up data connections to relational data sources. It is used to select which views should be included in the connection. If tables are related, they can be joined into a single view, or virtual table. When a connection is added to an analysis using Add Data Tables, it is possible to select which of the views should actually result in a data table in the analysis.

  1. Select Tools > Manage Data Connections.

  2. Click on the connection of interest and click Edit.

  3. On the General tab, click Edit .

  1. Select Edit > Data Connection Properties.

  2. In the list of Connections, select the connection with the data tables of interest.

  3. Click on Settings....

  4. On the General tab, click on Edit....

  5. Comment: You can only edit data tables for data connections that are embedded in the current analysis by using these steps. For shared library connections it is recommended to use the Manage Data Connections tool as described above instead.

data_views_in_connection_d.png

Option

Description

Available tables in database

Lists the tables from the database for which the schema is saved in the analysis. The tables shown here may be all the existing tables in the database, but it could also be only a selection of tables. See Edit Tables... below. An arrow next to a table indicates that the table has been set up with one or more structural relations to other tables in the database:
data_data_tables_in_connection_collapsed_view.png

To see the structure of the relation, click on the arrow to expand the view:

data_data_tables_in_connection_expanded_view.png

The expanded view shows that the table Sales and Cost is related to the tables Customer Information and Region. Customer Information is in turn related to the table Buyer, and so on.

You can use the relations that have been set up in the database to join database tables into a single view in Spotfire by adding a table at the top level of a relation.

You can also create your own relations by clicking on a table and then selecting Relations > New Relations.... These manual relations are indicated by a blue table icon and they are the only ones that can be edited or deleted from within Spotfire.

Note: If the database contains a large number of tables, then this list shows only the tables that have been selected in the Select Database Tables dialog.

Custom queries are collected under a separate folder in the list.

[Type to search tables]

Type a search string to limit the number of items in the Available columns list. It is possible to use the wildcard character * in the search. See Searching in TIBCO Spotfire for more information.

Relations

 

   New Relation...

Opens the New Relation dialog where you can set up a structural relation between two tables.

   Edit Relation...

Opens the Edit Relation dialog where you can edit a structural relation that already exists between the selected table and another table.
Note: Only structural relations defined in Spotfire can be edited, not those set up by the database administrator.

   Delete Relation

Removes the relation.

Note: Only structural relations defined in Spotfire can be deleted, not those set up by the database administrator.

Custom Query

 

   New Custom Query

Opens the Custom Query dialog where you can create your own custom database query. The added custom query will show up in the Available tables in database list and can be used to set up views as any other database table.

   Edit Custom Query

Opens the Custom Query dialog where you can edit the selected custom query.

   Delete Custom Query

Deletes the selected custom query.

Edit Tables...

Opens the Select Database Tables dialog where you can specify the schema to save in the analysis. This determines which tables should be available in the Available tables in database list.

Note: The permissions set in the database decides whether or not you are allowed to view the tables in the database when opening the Select Database Tables dialog.

Add >

Adds the tables selected in the Available tables in database list to the Views  in connection list.

< Remove

Removes the selected views from the Views in connection list and sends them back to the Available tables in database list.

Views in connection

Lists the views that you have added from the Available tables in database list. The views listed in bold letters here are those that will become data tables in Spotfire. If a source table with structural relations to other tables is selected, then all related tables will be included in the list, so that a virtual, joined, view is produced.

If you do not want to include related source tables in the view, you can clear the check box next to the table you want to exclude.

data_data_tables_in_connection_excluding_tables.png

Click on a bold table name to edit the resulting view  name. This name will be the default name for a data table added from the view in the analysis, but it can be changed later.

Tip: Hover the mouse-pointer over a view for information about the original name.

If a data table in the database has been renamed or removed, the table name will become red after a schema refresh. In this case, you can right-click on the broken table and select Remove or Repair, to update the connection.

Columns in selected view

Lists the columns that the selected view in the Views in connection list contains.

A parenthesis after a column name indicates that the column is included in a table that is the primary key table in the relation with the selected table. The name in the parenthesis is the name of the column that was used as the foreign key column when joining the tables together.

data_data_tables_in_connection_columns.png

Clear a check box to exclude a column from the resulting view in Spotfire.

Click on a column name to edit it.

Tip: Hover the mouse-pointer over a column to see which data type it contains.

See also:

Details on Select Database Tables