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

        /wp-content/uploads/2013/12/image001_347832.png       

  • 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)

/wp-content/uploads/2013/12/image002_347833.png

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.

/wp-content/uploads/2013/12/image003_347843.png

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

/wp-content/uploads/2013/12/image004_347844.png

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

/wp-content/uploads/2013/12/image005_347845.png

Step 20: Check the results in the DWH table

/wp-content/uploads/2013/12/image006_347846.png

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

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Roberto De la Torre

    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!

    (0) 
    1. Badrish Shriniwas Post author

      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

      (0) 
      1. Roberto De la Torre

        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

        (0) 
        1. Badrish Shriniwas Post author

          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

          (0) 
          1. Roberto De la Torre

            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 😆

            (0) 
  2. john joseph

    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.

    (0) 

Leave a Reply