Stored Procedure Select

Contents[Hide]

The Stored Procedure Select transform lets you retrieve data using a relational stored procedure. This transform is created automatically when you drag a stored procedure from a data connector in Explore to the Data Cube Designer 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

First, create a new data cube from the main menu using the Blank option.

Go to Explore, expand your data connector, expand the Programmatic Structures folder, and then locate the stored procedure you want.

Drag the stored procedure to the Data Cube Designer canvas.

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

A Stored Procedure transform appears on the canvas and is connected to a Process Result transform.

Stored Procedure transform is added
Stored Procedure transform is added

Note
The icon at the top right of the stored procedure node indicates that this transform includes a parameter.

3. Configure the transform

Right-click (or long-tap) on the Stored Procedure transform and select Configure from the menu.

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

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

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

Click Define parameters
Click Define parameters

In the Transform Parameterss dialog, you will see that a parameter was automatically detected and added to the list. Click the Edit button for the parameter.

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

Edit a parameter
Edit a parameter

Tip
Make the stored procedure parameter public in order to attach a dashboard filter to it later on. See the references at the end of this article for the steps.

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: 7am-6pm, ET, Mon-Fri