SAP BPC MS: Passing Data Manager Selection (Scope) to SSIS Package

Introduction

The document below covers an approach of passing data manager selections (scope/prompt) to SQL Server integration services (SSIS) package. This applies to all versions of SAP BPC Version for Microsoft. This approach will also facilitate using the prompts it in the data flow task.

Business Case

The business case is to export BPC data (pertaining to a user selection) to a custom data warehouse (DWH) table that is not part of BPC structure.  The users will be prompted to select an intersection of dimension members that they would like to export. This selection will be passed to a data flow task via the SSIS package. The data flow task will move the data from BPC tables to the custom DWH table.

Technical Setup

• BPC model with the following structure

        Environment Name: BADSHELL

        Model Name: Planning

• DWH Table

USE [BADDWH]

GO

CREATE TABLE [dbo].[BPC_INBOUND](

       [ACCOUNT] [nvarchar](20) NOT NULL,

       [AUDITTRAIL] [nvarchar](20) NOT NULL,

       [CATEGORY] [nvarchar](20) NOT NULL,

       [ENTITY] [nvarchar](20) NOT NULL,

       [FLOW] [nvarchar](20) NOT NULL,

       [INTERCO] [nvarchar](20) NOT NULL,

       [PRODUCT] [nvarchar](20) NOT NULL,

       [RPTCURRENCY] [nvarchar](20) NOT NULL,

       [TIMEID] [nvarchar](20) NOT NULL,

       [SIGNEDDATA] [decimal](38, 10) NULL

) ON [PRIMARY]

GO

SSIS Package Creation

Step 1: Create a SSIS package and use a data flow task

Step 2: Create a connection to BPC

Step 3: Create a connection to DWH

Step 4: Create a variable named “FACT_SELECT”  of type string

Step 5: Use a dummy select statement to facilitate SSIS mapping. The dummy select statement used in this example is

SELECT ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,PRODUCT,RPTCURRENCY,TIMEID,SIGNEDDATA  FROM TBLFACTPLANNING WHERE 1=2

(modify the select statement as per your BPC model)

Step 6: Create an OLEDB source

Step 7: Associate the source to BPC connection

Step 8: Select data access mode as “SQL Command from Variable”

Step 9: Select the variable (FACT_SELECT) create in step 4.

Step 10: Create an OLEDB Destination.

Step 11: Associate the destination to data warehouse connection

Step 12: Set data access to table or view

Step 13: Select DWH table (BPC_INBOUND) created as part of the technical set up

Step 14: Go to mappings section and map the source column

Step 15:  Move the package to the data manager folder of the model. In the sample example \\webserver\Webfolders\BADSHELL\Planning\DataManager\PackageFiles

Step 16:  Go to BPC for excel >> Data Manger >> Organise Package List >> Add the package

Step 17: After adding package, go to Organise package list >> Modify Package >> Modify Script >> Advanced and add the following script

PROMPT(SELECTINPUT, %SELECTION%, , “Select Dimension”,”CATEGORY,TIME,ACCOUNT”)

BEGININFO(%FACTSQL%)

SELECT ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,PRODUCT,RPTCURRENCY,TIMEID,SUM(SIGNEDDATA) AS SIGNEDDATA FROM

(

SELECT ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,PRODUCT,RPTCURRENCY,TIMEID,SIGNEDDATA FROM TBLFACT%Model% WHERE %SELECTION%

UNION ALL

SELECT ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,PRODUCT,RPTCURRENCY,TIMEID,SIGNEDDATA FROM TBLFACTWB%Model% WHERE %SELECTION%

UNION ALL

SELECT ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,PRODUCT,RPTCURRENCY,TIMEID,SIGNEDDATA FROM TBLFAC2%Model% WHERE %SELECTION%

)

AS FACTTBL

GROUP BY ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,PRODUCT,RPTCURRENCY,TIMEID

ENDINFO

GLOBAL(FACT_SELECT,%FACTSQL%)

Note: This script does the following

1. PROMPT: Prompts the user to select members of category, time and account.
2. BEGININFO: Creates a statement by applying a where clause assigning it to variable %FACTSQL%. Select clause should be modified based on your model.
3. GLOBAL: Passes select statement (%FACTSQL%) to package variable “FACT_SELECT”.

Step 18: Save the package (you have to choose save in three different screens)

Step 19: Execute the package by selecting dimension members

Step 20: Check the results in the DWH table

Important Notes

• Add necessary dimensions to the data manager prompt
• Clear the DWH table before loading it (for brevity this was not handled in this document)
• Set up appropriate data base security and connection credentials as suited to your need
• Create all variables (both BPC and SSIS) in upper case
• If new dimension(s) is added to BPC all the steps should be revisited
• For further reference refer SAP’s help documentation

Reference Links

How to pass a custom parameter to Data Manager Package