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
- PROMPT: Prompts the user to select members of category, time and account.
- BEGININFO: Creates a statement by applying a where clause assigning it to variable %FACTSQL%. Select clause should be modified based on your model.
- 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
Hi Badrish, great work you made here! but i’m having some issues trying to use it.
Instead of Destination Table i have a flat file.
Its working if I use the SQL query, but if I put SQL Command and use your method, the dummy query is never replaced by the package parameter in BPC 🙁
And the select statement is ok because it’s working.
Any clue of what’s happening?
Thanks!
Hi Mariano,
There are other ways to accomplish this. Please look up on *RUN_STORED_PROCEDURE on the script logic. You can execute a stored procedure using this command.
A simple implementation will look like this.
*RUN_STORED_PROCEDURE=FirstProcedure(‘%TIME_SET%’)
But you can pass the scope using scope table too (will be bit more technical).
Regards,
Badrish Shriniwas
Hi again, I need to make a custom dstx like yours with a SQL data Flow. Pass a parameter to the sql commmand nothing else.
so
In The OleDbsource sql command I’ve tried:
Dummy SQL like yours but using my select
Package:
PROMPT(SELECTINPUT, %SELECTION%, , “Please Select”,”AUDITTRAIL”)
BEGININFO(%AUDIT%)
SELECT USERID, Channel, Caa, Category, Coce, Currency, Entity, Time, MEASURES, GLAccount FROM TableX WHERE (AuditTrail = %SELECTION%)
ENDINFO
GLOBAL(AUDIT ,%AUDIT%)
But it’s not working, global variable with dummy doesn’t get updated with the package selection.
Then I’ve tried in the SQL command sentence:
SELECT USERID, Channel, Caa, Category, Coce, Currency, Entity, Time, MEASURES, GLAccount FROM TableX WHERE (AuditTrail = ?)
Where ? parameter is mapped to global variable User::AUDIT in SQL COMMAND
Package:
PROMPT(SELECTINPUT, %SELECTION%, , “Please Select”,”AUDITTRAIL”)
GLOBAL(AUDIT,%SELECTION%)
But it’s not working, variable doesn’t get updated with the value entered in the package selection anda data flow task fail.
If I write the string in the global variable in the package and debug, the query is working. So it’s something between BPC->SISS PACKAGE
Maybe Permissions? users in BPC?
What could be happening? I’m missing something…because it’s the same you’re doing 🙁 thanks
Your approach 1, has syntax issue. You can’t have “WHERE (AuditTrail = %SELECTION%)” it should be “WHERE %SELECTION%”. Please refer to the example fully.
The approach in this document only guides you on passing the select statement in a variable. This variable can then be used as source query in Data Flow Task or SQL command. You should get the help of SSIS developer too, so that they will log what is being passed from BPC etc and debug your package in detail.
I don’t think your approach 2 will work at all.
Please move this discussion to the forum so that others can chip-in and help too.
Regards,
Badrish Shriniwas
Yes Badrish, it’s working with the first one! Where clause was wrong, thanks for your approach.
I had something else wrong, Variables must be GLOBAL to the package, I had it inside Data flow task so thats why it’s not updating too.
Thanks again 😆
Hi Sir,
Thanks for this document.I have one issue when I run the data manager package.
The user has the option to select the category and time dimension.After running the data manager package the data loading in to the sql table is not loading correctly.the category dimesnion is okay.BUt the time dimension has some issues.
For example : the category 2014 Q1 time id should be 70.But the system displays many time ids for the category 2014Q1 starting from 38 to 80 instead of 77.
Looks like the parameter is not passing correctly from bpc to database.I request your help in this matter.