On my SQL server, I have added a Store Procedure to my Stage database. The Stored Procedure is called dbo.numbers:
The simple sample script for the Stored Procedure is this:
You can copy/paste the script from here, if you like:
/****** Object: StoredProcedure [dbo].[numbers] Script Date: 19-05-2022 12:04:17 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
ALTER PROCEDURE [dbo].[numbers]
-- Add the parameters for the stored procedure here
@count int = 0
@init int = 1
WHILE @init <= @count
SET @Init = @Init + 1
What the script does:
- It declares a parameter, @count, with an initial value of 0. This @count parameter is the one receiving input from another external data source - e.g., a CSV file. In this case, the external input should be an integer value.
- If the input value is 3, the WHILE loop will run three times (until @init <= 3).
- For each loop, the Return value will be incremented by 2 (due to the SELECT 2 statement). So, for an input value of 3, the Return value will become 6.
Data Discovery - SQL Stored Procedure data source
In Data Discovery, you can select the SQL Stored Procedure data source type:
Fill in the provider's properties:
- Name: Any name you would lik for this data source.
- Server: Type in the name of the server holding the SQL server with the Stored Procedure.
- Database: In case of Windows Authentication, you will be able to look up the correct database.
- Stored procedure: Select the proper Stored procedure from this server.
- Parameter sample (optional): You can enter a parameter samle, e.g., '3', to try to autodetect the Metadata.
- Metadata: Or, alternatively, you can switch to 'Manual' mode for Metadata. This will give you an option to type in your own desired name for the Return value. In my example below, I have simply typed in 'ReturnValue' and hit the '+' button.
Data Discovery - input data
The Stored procedure will need some input data. In my example, it will need some integer values for the @count parameter.
In this example, my input data is stored in a CSV file - but it could be any other type of data source. The CSV file is also added as a data source to Data Discovery:
Data Discovery - cube
These two data sources, the Stored Procedure data source and the CSV data source, are now combined into a simple Data Discovery cube:
Stored Procedure data in the TARGIT client
In the TARGIT client, we can now build something like this, with no input value selected:
With one input value selected:
With two input values selected: