Stored Procedure Select
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.
The Stored Procedure Select transform extracts data via a stored procedure such as uspGetSalesForDateRange from the AdventureWorks 2012 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.
A Stored Procedure transform appears on the canvas and is connected to a Process Result transform.
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.
Click Define parameters to configure the stored procedure's 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.
Set a value for the remaining parameters as well.
Select the Process Result transform on the canvas and then go to Data Preview to see the output from the stored procedure.