Connecting to stored procedures using data connections

Stored procedures are predefined queries that can be saved and executed by some relational database systems. The procedures often return a set of result columns which can be used to populate a data connection view and, hence, can result in a data table in Spotfire.

Things to consider

Not all connectors support stored procedures. To find out if a certain connector supports stored procedures, read the section titled 'Supported Functions' for the connector of interest.
If you are connecting to a Teradata database that contains macros, Spotfire handles macros the same way as stored procedures.
Stored procedures that do not result in a data table output will not be visible in the Views in Connection dialog for some connectors. For Oracle stored procedures, some additional requirements must be met. See Supported Functions for Oracle for details.
The schema of the stored procedure must not change from time to time (for example, the use of parameters must not change the schema).

To set up a data connection with stored procedures:

About this task

Note: The steps below show how to add procedures to a Microsoft SQL Server connection that is going to be saved in the library. You can also use steps 4-7 to add procedures to an embedded data connection in an analysis ((after following the first steps in To add a connection to Microsoft SQL Server until you reach the Views in Connection dialog ).

Procedure

On the menu bar, select Data > Manage Data Connections.
Select Add New > Data Connection > Microsoft SQL Server.

You can also add a new Connection from Data Source in Library. If this is the case, you will select a data source, log in, and then continue from step 4.
In the Microsoft SQL Server Connection dialog, specify the required settings and click Connect.
In the Views in Connection dialog (or Select data flyout), select the procedures you want to work with in Spotfire in the Available tables in database list, then click Add > .

The Stored Procedures dialog opens.
In the Stored Procedures dialog, define the result columns to retrieve for each of the stored procedures.

If you want to retrieve all result columns that have been defined for a stored procedure, then it is easiest to click Run or Run All and automatically include all result columns. However, you can also manually define which result columns to include in the view.

Named parameters included in the procedure will not be shown in the Stored Procedures dialog. Instead, standard values are used.
When all stored procedures show the status Automatic or User-defined, click OK.
If desired, add more views to the connection, or define prompting for parameters in the view based on a procedure.
When you are finished with defining the data connection, click OK.
Click Save and specify where you want to save the data connection in the library.

Known Limitations

When a view is based on a stored procedure it forces the load method for that data table to be Import data (in-memory analysis).
Prompting and on-demand loading of data from stored procedures is only supported for parameters, not for columns.