Note: Which connectors you have access to is determined by the licenses set up by your Spotfire administrator. Some connectors also require that a driver is installed on the machine running Spotfire. See the system requirements at http://support.spotfire.com/sr.asp for details.
If data connections have been set up in advance you can add them directly from the library.
To add a predefined connection from the library:
Select File > Add Data Tables....
Response: The Add Data Tables dialog is displayed.
Click Add > Connection To > Shared Connection in Library.
Response: The Select Data Connection dialog is opened.
Browse to the connection of interest and click OK when it has been selected.
Click OK in the Add Data Tables dialog.
To add a data connection based on an existing data source from the library:
Select File > Add Data Tables....
Response: The Add Data Tables dialog is displayed.
Click Add > Connection To > Data Source in Library.
Response: The Select Data Source dialog is opened.
Browse to the connection data source of interest and click OK when it has been selected.
Response: If the selected data source is a relational database you will get the option to specify the data tables to use in the Data Tables in Connection dialog. Make your choice and click OK. If the selected data source is a cube, then the data table will be directly added to the Add Data Tables dialog.
Click OK in the Add Data Tables dialog.
Below are some examples of how certain types of connections are set up. Other connector types are set up in a similar fashion, but it may include fewer or additional steps.
Select File > Add Data Tables....
Response: The Add Data Tables dialog is displayed.
Click Add > Connection To > Microsoft SQL Server.
Response: The Microsoft SQL Server Connection dialog is opened.
Specify the Server you want to connect to.
Select Authentication method.
If you selected SQL Server authentication, specify Username and Password.
Click Connect.
Response: Spotfire will connect to the specified server, and the databases that are available on the server will be listed in the Database drop-down list.
Select the Database of interest.
Click OK.
Response: If the database you connect to contains a large number of tables, you will reach the Select Database Tables dialog which lets you limit the number of tables to work with, see step 9. Otherwise you will reach the Data Tables in Connection dialog, see step 13.
In the Available tables list, select one or more tables you that want to be able to work with in Spotfire.
Click Add >.
Response: The tables are moved from the Available tables list to the Selected tables list.
Repeat step 9 and 10 until all the tables of interest have been moved to the Selected tables list.
Comment: Retrieving the tables and their schemas from the database may take some time if you add a large number of tables. Therefore, it is recommended that you add only the tables you need to work with.
Tip: Let Spotfire locate related tables for you. Select one or more interesting tables in the Selected tables list and then click on the Add Related Tables button. All tables that have a relation in the database to the selected tables will then be added to the list.
Click OK.
Response: The Data Tables in Connection dialog is opened.
In the Available tables in database list, double-click on the tables you want to work with in Spotfire.
Response: The tables are moved to the Data tables in Connection list. If you add a table with relations to other tables (a table with relations to other tables is indicated by an arrow to the left of its name), all related tables will automatically be included and the resulting data table in Spotfire will be a joined virtual table with columns from all the related tables.
Comment: Click on a table in the Data tables in connection list to view the columns in the table.
Enter a descriptive Data connection name.
Click OK.
Response: The connection with the selected tables is added to the Data tables list in the Add Data Tables dialog.
Click OK.
Response: A connection to Microsoft SQL Server has now been added to the analysis. A default visualization is opened in Spotfire, and the selected data tables are ready to be used.
Select File > Add Data Tables....
Response: The Add Data Tables dialog is displayed.
Click Add > Connection To > Teradata.
Response: The Teradata Connection dialog is opened.
Specify the Server you want to connect to.
If desired, select Use data encryption.
Select Authentication method.
If you selected Teradata authentication, specify Username and Password.
Click Connect.
Response: Spotfire will connect to the specified server, and the databases that are available on the server will be listed in the Database drop-down list.
Select the Database of interest.
Click OK.
Response: If the database you connect to contains a large number of tables, you will reach the Select Database Tables dialog which lets you limit the number of tables to work with, see step 10. Otherwise you will reach the Data Tables in Connection dialog, see step 14.
In the Available tables list, select one or more tables that you want to be able to work with in Spotfire.
Click Add >.
Response: The tables are moved from the Available tables list to the Selected tables list.
Repeat step 10 and 11 until all the tables of interest have been moved to the Selected tables list.
Comment: Retrieving the tables and their schemas from the database may take some time if you add a large number of tables. Therefore, it is recommended that you add only the tables you need to work with.
Tip: Let Spotfire locate related tables for you. Select one or more interesting tables in the Selected tables list and then click on the Add Related Tables button. All tables that have a relation in the database to the selected tables will then be added to the list.
Click OK.
Response: The Data Tables in Connection dialog is opened.
In the Available tables in database list, double-click on the tables you want to work with in Spotfire.
Response: The tables are moved to the Data tables in Connection list. If you add a table with relations to other tables (a table with relations to other tables is indicated by an arrow to the left of its name), all related tables will automatically be included and the resulting data table in Spotfire will be a joined virtual table with columns from all the related tables.
Comment: Click on a table in the Data tables in connection list to view the columns in the table.
Enter a descriptive Data connection name.
Click OK.
Response: The connection with the selected tables is added to the Data tables list in the Add Data Tables dialog.
Click OK.
Response: A connection to Teradata has now been added to the analysis. A default visualization is opened in Spotfire, and the selected data tables are ready to be used.
Select File > Add Data Tables....
Response: The Add Data Tables dialog is displayed.
Click Add > Connection To > Oracle.
Response: The Oracle Connection dialog is opened.
Specify the Server you want to connect to.
Select whether to Connect using: SID or Service name.
Select Authentication method.
If you selected Oracle authentication, specify Username and Password.
Click OK.
Response: If the database you connect to contains a large number of tables, you will reach the Select Database Tables dialog which lets you limit the number of tables to work with, see step 8. Otherwise you will reach the Data Tables in Connection dialog, see step 12.
In the Available tables list, select one or more tables that you want to be able to work with in Spotfire.
Click Add >.
Response: The tables are moved from the Available tables list to the Selected tables list.
Repeat step 8 and 9 until all the tables of interest have been moved to the Selected tables list.
Comment: Retrieving the tables and their schemas from the database may take some time if you add a large number of tables. Therefore, it is recommended that you add only the tables you need to work with.
Tip: Let Spotfire locate related tables for you. Select one or more interesting tables in the Selected tables list and then click on the Add Related Tables button. All tables that have a relation in the database to the selected tables will then be added to the list..
Click OK.
Response: The Data Tables in Connection dialog is opened.
In the Available tables in database list, double-click on the tables you want to work with in Spotfire.
Response: The tables are moved to the Data tables in Connection list. If you add a table with relations to other tables (a table with relations to other tables is indicated by an arrow to the left of its name), all related tables will automatically be included and the resulting data table in Spotfire will be a joined virtual table with columns from all the related tables.
Comment: Click on a table in the Data tables in connection list to view the columns in the table.
Enter a descriptive Data connection name.
Click OK.
Response: The connection with the selected tables is added to the Data tables list in the Add Data Tables dialog.
Click OK.
Response: A connection to Oracle has now been added to the analysis. A default visualization is opened in Spotfire, and the selected data tables are ready to be used.
Select File > Add Data Tables....
Response: The Add Data Tables dialog is displayed.
Click Add > Connection To > Microsoft SQL Server Analysis Services.
Response: The Microsoft SQL Server Analysis Services Connection dialog is opened.
Specify the Server you want to connect to.
Click Connect.
Response: Spotfire will connect to the specified server, and the databases that are available on the server will be listed in the Database name drop-down list.
Select the Database of interest.
Select the Cube of interest.
Click OK.
Response: The connection with the selected cube is added to the Data tables list in the Add Data Tables dialog.
Specify a descriptive Name for the connection.
Click OK.
Response: A connection to Microsoft SQL Server Analysis Services has now been added to the analysis, and a default visualization is opened in Spotfire.
This instruction is only applicable to relational in-db data sources. Cubes are always treated as a single data table.
Open the Data Tables in Connection dialog.
Under Relations, click New....
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.
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 Data tables in connection list, then the columns in the Primary key table will be included automatically. 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 Data tables 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 can be deleted, not those set up by the database administrator. However, you can always add a database table to a connection without including a related table by clearing the check box for the related table in the Data tables in connection list.
Open the Data Tables 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.
Response: The Delete button is enabled.
Click on the Delete button.
Response: The relation between the two tables is removed.
Comment: Note that relations may include other relations so that deleting a relation for a one table may also affect the resulting number of columns in other virtual tables in the Data tables in connection list.
Note: Only structural relations defined in Spotfire can be edited, not those set up by the database administrator.
Open the Data Tables 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.
Response: The Edit... button is enabled.
Click on the Edit... button.
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: