Stored Procedure Select


The Stored Procedure Select transform lets you retrieve data using a relational database stored procedure. This transform is created automatically when you drag a stored procedure from a data connector in Explore to the data cube canvas.

1. Input

The Stored Procedure Select transform extracts data via a stored procedure such as uspGetManagerEmployees from the AdventureWorks 2014 sample database.

2. Add the transform

When editing a data cube, find your data connector in the Data Connectors folder in the Explore window. Expand it and its Programmatic Structures folder, then drag a stored procedure onto the canvas.

Drag the stored procedure to the canvas
Drag the stored procedure to the canvas

The stored procedure must return data in order to use it in a data cube.

A Stored Procedure transform appears on the canvas and is connected to a Process Result transform. The icon at the top-right of the Stored Procedure transform indicates that it includes a parameter.

Stored Procedure transform is added
Stored Procedure transform is added

The Stored Procedure transform has a default timeout of 30 seconds. If your stored procedure command takes longer to execute, the transform will appear red and will not work. You can configure the transform and increase the timeout.

3. Configure the transform

Double-click the Stored Procedure transform, or select it and then choose Configure in the toolbar.

In the configuration dialog, the names and data types of the stored procedure's input columns are listed.

Configuration dialog for the Stored Procedure transform
Configuration dialog for the Stored Procedure transform

The Timeout determines how long in seconds to allow execution to take before terminating it.

In version 5.0.1 and above, the Default Query Timeout application configuration setting determines the initial timeout setting.

Click Define parameters to configure the stored procedure's parameters.

Define parameters
Define parameters

In the Transform Parameters dialog, you will see that parameters are automatically detected and added to the list. Click the Edit button for a parameter to configure it.

In the Define Transform Parameter dialog, use the Value field to indicate a default value for the parameter.

Edit a parameter
Edit a parameter

To allow the parameter to be set or attached to filters in metric sets, dashboards, etc., check Public.

4. Output

Select the Process Result transform on the canvas and then go to Data Preview to see the output from the stored procedure.

Preview the output
Preview the output

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri