Adding Data Connections to an Analysis


Note: Which connectors you have access to is determined by the licenses set up by your Spotfire administrator. Most 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. You can also view Getting Started with Connectors to learn more about getting access to connectors in Spotfire.

If data connections have been set up in advance you can add them directly from the library.

  1. Select File > Add Data Tables....

  2. Response: The Add Data Tables dialog is displayed.

  3. Click Add > Connection To > Shared Connection in Library.

  4. Tip: If you know the name of the connection you can type the name in the search field of the Add menu and it will show up directly in the list, under the header Shared Connection in Library.

    Response: The Select Data Connection dialog is opened.

  5. Browse to the connection of interest and click OK when it has been selected.

  6. Select which of the views in the data connection you want to add as new data tables by clearing or selecting the check boxes and then click OK in the Add Data Tables dialog.

  1. Select File > Add Data Tables....

  2. Response: The Add Data Tables dialog is displayed.

  3. Click Add > Connection To > Data Source in Library.

  4. Tip: If you know the name of the data source, you can type the name in the search field of the Add menu, and it will show up directly in the list, under the header Connection Data Source in Library.

    Response: The Select Data Source dialog is opened.

  5. Browse to the connection data source of interest and click OK when it has been selected.

  6. Response: If the selected data source is a relational database you will get the option to specify the views to include in the Views in Connection dialog. Make your choice and click OK.

  7. Select which of the views in the data connection you want to add as new data tables by clearing or selecting the check boxes and then click OK in the Add Data Tables dialog.

Embedded Data Sources and Connections

You can also add data connections that are embedded in the analysis file instead of using shared sources. 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.

In this example, the data connection is based on Microsoft SQL Server data, but the process for adding data connections from other cube data sources is quite similar.

  1. Select File > Add Data Tables....

  2. Response: The Add Data Tables dialog is displayed.

  3. Click Add > Connection To > Microsoft SQL Server.

  4. Response: The Microsoft SQL Server Connection dialog is opened.

  5. Specify the Server you want to connect to.

  6. Select Authentication method.

  7. If you selected SQL Server authentication, specify Username and Password.

  8. Click Connect.

  9. 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.

  10. Select the Database of interest.

  11. Click OK.

  12. 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 Views in Connection dialog, see step 13.

  13. In the Available tables list, select one or more tables that you want to be able to work with in Spotfire.

  14. Click Add >.

  15. Response: The tables are moved from the Available tables list to the Selected tables list.

  16. Repeat step 9 and 10 until all the tables of interest have been moved to the Selected tables list.

  17. 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.

  18. Click OK.

  19. Response: The Views in Connection dialog is opened.

  20. In the Available tables in database list, double-click on the tables you want to be able to work with in Spotfire.

  21. Response: The tables are moved to the Views 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 view in Spotfire will be a joined virtual table with columns from all the related tables. See also Structural Relations.

    Comment: Click on a item in the Views in connection list to see the columns in the selected view.

  22. If desired, you can specify that a certain view should be available as external data only, or as imported data only. Right-click on the bold view name and select Load Method and the desired option. Repeat for other views.

  23. Click OK.

  24. Response: The connection with the selected views is added to the Data tables list in the Add Data Tables dialog.

  25. Select which of the available views should result in a new data table in the analysis by clearing or selecting the check boxes under Data tables and then click OK.

  26. Response: A connection to Microsoft SQL Server has now been added to the analysis and the selected data tables are ready to be used.

In this example, the data connection is based on a Microsoft SQL Server Analysis Services cube, but the process for adding connections from Essbase or SAP BW cubes is quite similar.

  1. Select File > Add Data Tables....

  2. Response: The Add Data Tables dialog is displayed.

  3. Click Add > Connection To > Microsoft SQL Server Analysis Services.

  4. Response: The Microsoft SQL Server Analysis Services Connection dialog is opened.

  5. Specify the Server you want to connect to.

  6. Click Connect.

  7. 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.

  8. Select the Database of interest.

  9. Select the Cube of interest.

  10. Click OK.

  11. Response: The Data Selection in Connection dialog is displayed.

  12. Click to select data of interest in the panel to the left.

  13. Comment: Click on the top level to select the entire cube. This is generally not recommended when you intend to import data.

    Comment: For Microsoft SQL Server Analysis Services, you can limit what is shown in the panel by selecting a single measure group, or by typing a search term in the search field.

  14. Click Add > to move the selected data to the Data selection in connection field.

  15. If desired, you can limit the selected data further by clicking on a dimension and, in the Settings pane, clicking Limit....

  16. Comment: This way, you can filter out certain values of the dimension to only include values of interest. For example, you can select data from a single country only. Note that it is not possible to limit measures, only dimensions, because the cube calculates the measures in the context of the selected dimensions.

  17. If desired, you can specify that the data in this connection should be available as external data only, or as imported data only. Right-click on the cube name and select Load Method and the desired option.

  18. When you are done with the data selection, click OK.

  19. Response: The connection with the selected cube is added to the Data tables list in the Add Data Tables dialog.

  20. Specify a descriptive Name for the connection.

  21. Click OK.

  22. Response: A connection to Microsoft SQL Server Analysis Services has now been added to the analysis, and a default visualization is opened in Spotfire.

Tip: In the Data Selection in Connection dialog, you can double-click on a collapsed item to expand it, or you can double-click on a leaf node to directly send it to the Data selection in connection list.

See also:

Data Overview

Adding Prompts to Data Connections

Connecting to Stored Procedures Using Data Connections

Importing Data from Cubes