Introduction
This document covers the approach of passing scope/prompt from SAP BPC data manager package to SQL Server stored procedure. This applies to all versions of SAP BPC Version for Microsoft. This approach can be used as an alternate to the approach specified in my earlier document on How to Pass Parameters to SSIS package.
Business Case
The business case is to pass the parameters from SAP BPC data manager package to a stored procedure. The BPC users will pass the scope (selections) via the data manager prompts. This selection will be passed to a stored procedure via the data manager package. The stored procedure will receive the scope(selections) from BPC data manager package and store it to a table.
BPC Setup
BPC model with the following structure
Environment Name: BADSHELL
Model Name: Planning
Development Details
Step 1: Create a scope table to receive the scope (selections) passed from SQL Server. This table will be used to receive the parameters passed from the BPC data manager package. Execute the following script in the SQL Server side.
USE [BADSHELL]
GO
CREATE TABLE [DBO].[BPC_SCOPE](
[SCOPETBL] [NVARCHAR](100) NULL,
[DIMENSION] [NVARCHAR](32) NULL,
[MEMBER] [NVARCHAR](32) NULL
) ON [PRIMARY]
GO
Step 2: Create stored procedure. This stored procedure will receive the selection from BPC and store it in a table (BPC_SCOPE). This table can be used in the further operations like a filter table. To facilitate parallel call of the data manager package this table (BPC_SCOPE) should always be used with the filter SCOPETBL = @SCOPETABLE.
Execute the following script in the SQL Server side. Please read the comments and insert your code...
USE [BADSHELL]
GO
CREATE PROCEDURE [DBO].[USP_CALC_BLOG]
(
@SCOPETABLE VARCHAR(50)
)
AS
BEGIN
EXEC('INSERT INTO [BPC_SCOPE] SELECT ''' + @SCOPETABLE + ''', DIMENSION, MEMBER FROM ' + @SCOPETABLE)
/*
INSERT YOUR CODE HERE, YOU CAN USE THE BPC_SCOPE TABLE TO FILTER RECORDS
TO MAKE SURE WE ISOLATE CONCURRENT EXECUTION USE THE VARIABLE @SCOPETABLE
TO FILTER BPC_SCOPE TABLE.
*/
/*
FINALLY YOU CAN DELETE ALL THE RECORDS IN THE BPC_SCOPE TABLE
DELETE FROM BPC_SCOPE WHERE SCOPETBL = @SCOPETABLE
*/
END
Step 3: Create BPC script logic to call the stored procedure. %SCOPETABLE% variable passes the parameter table to the stored procedure.
Step 4: Create data manager package to invoke the script logic
On the web server side, copy the standard “Default Formulas” data manager package (BLOG_10)
Configure the package in the data manager interface in EPM add-in for excel. Go to Data Manager >> Organize package list >> Add package and select the copied package
Modify the package and add the following by specifying following script
Step 5: Execute data manager package by selecting prompts
Step 6: Check the results
Important Notes
- Add necessary dimensions to the data manager prompt
- Clear the scope table after the calculation (note SQL section commented in the document)
- Set up appropriate database security and connection credentials as suited to your need
- For further reference refer to SAP’s help documentation
Reference Links
How to pass a custom parameter to Data Manager Package
SAP BPC MS: Passing Data Manager Selection (Scope) to SSIS Package
3 Comments
