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 uspGetSalesForDateRange from the AdventureWorks 2012 sample database.

Stored procedure in SQL Server Management Studio
Stored procedure in SQL Server Management Studio

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

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 Parameters dialog, you'll see that parameters have been automatically detected and added to the list. Click the Edit button for the first parameter.

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

Edit a parameter
Edit a parameter

Set a value for the remaining parameters as well.

Tip
Make a 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