(v23) Stored Procedure Select

Stored Procedure Select

This applies to: Managed Dashboards, Managed Reports

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.

Input

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

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.

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

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

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.

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

Note: The Default Query Timeout application configuration setting determines the initial timeout setting.

Click Define parameters to configure the stored procedure's 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.

Note: Like in other transform parameters, you can use the menu to the right of the Value field to choose to pass the value of an attribute or custom attribute associated with each user.

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

Output

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