Selecting data for your connection
Once you have created a data connection and connected to your database, you must select which data you will use in Spotfire. This is done in the Select data flyout (or Views in Connection dialog).
You can choose to bring database tables directly into Spotfire, but you can also expand your data by creating custom queries, running stored procedures, joining related tables, and so on. While combining and expanding your data, and before it materializes into a data table in Spotfire, you are dealing with "views". This is essentially a data table in progress. All of this is done in the Select data flyout (or Views in Connection dialog).
If you are using the installed client using a connector that does not allow authoring connections on the web, then the dialog for selecting data will look like the screenshot below.
1. Available tables
Available tables lists all the tables
and stored procedures
that are available in the
database. When you initially connect, you will see the database schemas. Click
on a schema to see the included tables and any stored procedures.
2. Selected tables
The
Selected tables list contains all the selected
views that can become data tables in Spotfire. Here you can also view any
created custom queries
, stored procedures that
have been run, and related tables.
You can add related tables using the Details section of a chosen view. If a source table with structural relations to other tables is added, then all related tables will be included in the list, so that a virtual, joined, view is produced.
An arrow to the left of a view indicates that the table has been defined with one or more structural relations to other tables in the database. To see the structure of the relation, click on the arrow to expand the view.
The expanded view above shows that the table Sales and Cost is related to the table Customer Information.
3. Details section
The Details section shows up when you have clicked on a view in the Selected tables list. You can see the name of the view at the top, and directly below are available actions you can take, depending on the type of view you have selected.
Stored procedures, custom queries, and manual relations can be edited. Click on Edit to do this.
Rename
Click Rename to edit the 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.
Preview
The Preview tab shows a preview of the selected view, unless it is based on parameters. Parameter values cannot be shown in Preview because they have not been set by the end user in this step.
Columns
The Columns tab lists the columns that the selected view contains.
In the list, you can see the column name, the Spotfire data type, the original data type, whether it is a primary key, and whether prompts have been defined and any prompt order.
To edit a column name or custom primary key, hover over the text and
click on the icon
that appears.
Click Use as primary key to confirm that the selected column or columns should be used as the primary key for the selected view.
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.
Clear a check box to exclude a column from the resulting view in Spotfire.
The Information tab includes information such as the original name of the view, its type, and any related constraints.
4. Custom queries
Click on Add custom query to open the custom query dialog and create your own custom database query. The added custom query will show up in the Selected tables list and can be used to define views as any other database table.
5. Add related tables and set load methods
You can use the relations that have been defined in the database to join database tables into a single view in Spotfire.
- To add database tables that are directly related to the selected table (one level down), click .
- To add all database tables that are in some way related to the selected table (all levels), click . .
You can also create your own relations by clicking on a view and then selecting . These manual relations are indicated by a pale blue background in the Selected tables list.
You can specify that a view should be available as external data only, or as imported data only, in the Details section. Click on a view to open the Details section and select Allowed load methods and one of the options Allow any load method, External only (in-database), or Import only (in-memory).
6. Define prompting
Prompting lets you define that the end users opening this data connection should be prompted for input before data is retrieved. The data to analyze is then limited based on these inputs.
To add or edit prompts for the selected view, click Define prompting.