BPC Script logic for Dummies? (Part 2)

I explained basic 3 parts of script logic in the last post. It was Scoping, Calculation and writing.

We will find out more advanced features for scoping In this post.

1. Scope using member property

      We found how to use *XDIM_MEMBERSET last time.

      *XDIM_MEMBERSET is for scoping based on the member ID.
      What if user wants to scope members based on a specific property value?
      For example, a user wants to filter Account dimension members those are Asset.
      To achieve this, we need to use ACCTYPE property which has the type value of account.
      AST is the value for ASSET account. (Note: Value is based on the APSHELL of BPC.)
      The command is *XDIM_FILTER.
      The usage is

*XDIM_FILTER <DIMENSIONNAME> = [DIMENSIONName].Properties("Property name") = "Property value"

      So above example can be written as below.
      *XDIM_FILTER ACCOUNT = [account].properties(“ACCTYPE”)=”AST”

      Let's say Account dimension has 3 members as below.

         ID           ACCTYPE
      Extsales          INC
      CASH              AST
      TAXES             EXP
      NETINCOME    INC

      Then about *XDIM_FILTER_ACCOUNT will select CASH member only.

      Let's assume If you already used multiple *XDIM_MEMBERSET command

      and below are selected data from the fact tables.

      *XDIM_MEMBERSET TIME = 2011.JAN
      *XDIM_MEMBERSET CATEGORY = BUDGET

     <Result>
     EXTSALES , 2011.JAN, BUDGET, 9000
     CASH     , 2011.JAN, BUDGET, 3000
     TAXES    , 2011.JAN, BUDGET,  800
     NETINCOME, 2011.JAN, BUDGET, 1500

     Now if you add *XDIM_FILTER against ACCOUNT dimension.

     *XDIM_MEMBERSET TIME = 2011.JAN
     *XDIM_MEMBERSET CATEGORY = BUDGET
     *XDIM_FILTER ACCOUNT = [account].properties(“ACCTYPE”)=”AST”

     Only one record will be selected from above result because CASH is

     the only account member that has 'AST' value of ACCTYPE property.

     <Result>
     CASH     , 2011.JAN, BUDGET, 3000

2. Scope using member value
      We just figured out how to scope the source data based on the property.
      Then someone might ask this question.
      "Can we scope based on the value?

      For example, can we select all data that an account value greater than 100?

      Of course, we can do it.
      The command is *XDIM_GETMEMBERSET. Unlike other command,

      it needs  *ENDXDIM command to specify data.

      Here is the grammar of XDIM_GETMEMBERSET and an example.

      *XDIM_GETMEMBERSET {dimension} [={member set}]
           [*APP={application}] //optional
           [*XDIM_MEMBERSET {dimension} [={member set}] //as many of these as needed
           [*QUERY_TYPE= 0 | 1 | 2] //optional
           *CRITERIA {expression} //required
      *ENDXDIM

      *XDIM_GETMEMBERSET P_CC=[P_CC].[H1].[AAPJ].CHILDREN
           *APP=PLANNING
           *XDIM_MEMBERSET P_DataSrc=INPUT
           *CRITERIA [P_ACCT].[H1].[CE0001000]>1000 
      *ENDXDIM  

      It will get the data and those are..

           a. Children member of AAPJ in the P_CC dimension.  AND
           b. from the PLANNING application  AND
           c. INPUT member of P_Datasrc dimension AND
           d. CE0001000 member's value of the P_ACCT dimension should be greater than 100000

      Let's Assume Fact table has below records.

      CE0001000, 2011.JAN, ACTUAL, INPUT, KOREA , 2500
      CE0002000, 2011.JAN, ACTUAL, INPUT, CHINA , 5000
      CE0001000, 2011.JAN, ACTUAL, ADJ  , CHINA , 3000
      CE0002000, 2011.JAN, ACTUAL, INPUT, JAPAN , 1999
      CE0003000, 2011.JAN, ACTUAL, INPUT, JAPAN , 2222
      CE0001000, 2011.FEB, BUDGET, ADJ  , KOREA ,  345
      CE0001000, 2011.FEB, BUDGET, INPUT, TURKEY, 1999
      CE0003000, 2011.JAN, ACTUAL, INPUT, TURKEY, 1100
      CE0001000, 2011.FEB, BUDGET, INPUT, CHINA , 1050
      CE0001000, 2011.FEB, BUDGET, INPUT, JAPAN ,  450

      Which records will be selected?

      The answer is         
      CE0001000, 2011.JAN, ACTUAL, INPUT, KOREA, 2500
      CE0001000, 2011.FEB, BUDGET, INPUT, CHINA, 1050

      Below records will not be selected even though P_ACCT is CE0001000
      because its value is less than 1000
      or Datasrc is not INPUT
      or it is not the child member of AAPJ (Asia Pacific)
      CE0001000, 2011.JAN, ACTUAL, ADJ  , CHINA , 3000  (datasrc is not input)
      CE0001000, 2011.FEB, BUDGET, ADJ  , KOREA ,  345  (datasrc is not input)
      CE0001000, 2011.FEB, BUDGET, INPUT, TURKEY, 1999  (Turkey is not child member of AAPJ)
      CE0001000, 2011.FEB, BUDGET, INPUT, JAPAN ,  450  (Value is less than 1000)

      Here are some important Notes for using this command.

      Note 1: This command works only for BPC MS.
      Note 2: if you don't specify each dimension's scope, it will be performed in the

           corresponding members of the pre-selected region which is defined with

           DIMMEMBERSET of previous line or Passed by Data Manager.

      Note 3: This command will generate MDX statement so it takes more time to execute.
                 if your dataset has only base members, you can use

                      *XDIM_GETINPUTSET. (please refer help file)

3. When user wants to add more members on top of current scoped data.

      Let's say a user wants to add USASales entity on top of predefined memberset.
      In that case user defines as below.

      *XDIM_ADDMEMBERSET Entity = USASales

      The main reason why we need this is
          a. Sometimes XDIMMEMBERSET doesn't work with some specific functions like BAS(parent).
             For example, IN BPC NW,  *XDIM_MEMBERSET = BAS(US),CANADA will not work.
             Therefore, we should use *XDIM_MEMBERSET and *XDIM_ADDMEMBERSET.

             *Note: In BPC MS, BAS() will not work with XDIM_MEMBERSET.

          b. if user always wants to run a specific memberset whenever logic runs,

              should use *XDIM_ADDMEMBERSET

4. Dynamic Scope and saving it to a variable.

      Sometimes we need to save our scoped data into a script logic variable.
      But... what if your dimension members are updated frequently?
      As I know, almost every customer updates their dimension at least once a month.

      If customer changes their dimension members, what will happen in your script logic?
      You can use *Filter but sometimes it may not work all the time.

      Then we can use *SELECT and *MEMBERSET command as a dynamic scope tool. 

      Like other script engine, Logic script also supports Variable to save some data.
      The Variable is defined using % symbol.Here are some examples, %MYTIME% , %CUR% etc.

      So how can we save some data into the variable and when it can be used?

      Usually, the variable can be filled using *SELECT command and *MEMBERSET command.
      Both of them is scope command but *SELECT will be faster because it will create SQL statement.
      Here is the grammar of both commands.
          *SELECT ({variable}, {What}, {From}, {Where})
          *MEMBERSET({variable}, {member set in MDX format})

      Let's see how to use *SELECT command.

           *SELECT(%REPORTING_CURRENCIES%, “ID”, “CURRENCY”, “[GROUP] = 'REP'”)

      This command will get the 'member ID(what)' from the 'currency dimension(From)' that the

      GROUP property has the value 'REP' (where).

      Actually, it will create a SQL statement as below

SELECT ID from mbrCurrency where [GROUP] = 'REP'

      After it executes above SQL command, all result will be saved into

      %REPORTING_CURRENCIES% variable.

      Here is an example of *MEMBERSET which will make same result but execute MDX statement

      instead of SQL.
           *MEMBERSET(%REPORTING_CURRENCIES%, “filter{[CURRENCY].members,

           [currency].properties(“GROUP”)=”REP””)   
      The variable can be used anywhere in the logic, like in this example:
                        *XDIM_MEMBER_SET CURRENCY=%REPORTING_CURRENCIES%

      Let's assume Currency dimension has below members.
          ID           GROUP

         USD            REP
         EUR            REP
         KRW           
         JPY

      Then above statement will be converted as
                        *XDIM_MEMBER_SET CURRENCY = USD,EUR   

      When you define and fill in data using *SELECT and *MEMBERSET,

      please remember this as 'MEMBERSET Variable'

      Note: MEMBERSET command is only supported by MS version.

We reviewed key command of scoping today.

We will review advanced calculation command and control command like *IF or *FOR - *NEXT in the next post.

If you have questions or want to know something about it, please leave a comment.

Thank you.

When to use the Write-back badi in BPC7.x NW

SAP Business Objects Planning and Consolidation version for Netweaver effectively leverages the Netweaver infrastructure and we can use the Business Add-In functionality (http://help.sap.com/saphelp_nw04/helpdata/en/e6/d54d3c596f0b26e10000000a11402f/content.htm). In this blog we will try to explore the features of write-back badi and discuss when we can use it to our advantage in the implementation projects.

You are in the best position to use the write back badi if you are on SAP Business Objects Planning and Consolidation 7.5 version for Netweaver. At present, the latest support pack for 7.5NW is SP4 but even if you are on an earlier SP level, you can still use the write-back badi. If you are on BPC7.0NW though, you should be at least on SP4 or higher to take advantage of the write-back badi.

One may ask, is it mandatory for us to use a write-back badi to write any records using BPC? The answer is ‘no'. It is not mandatory for us to use write-back badi if we don't want to.  Even without a write back badi, several BPC customers have been entering data through input schedules, data manager packages, comments etc. Write-back badi is generally of use when we want to write-back records in a fashion not supported by the regular write back operations.

For example, let us consider a simple case of budgeting by cost centers. Let us consider that our Entity type dimension is Cost_Center and it has a hierarchical structure with Worldwide as the top node, followed by Europe, Asia and North America as lower level nodes. Under each of these nodes, let us assume that we have individual countries. So North America may have USA, Canada and Mexico; whereas Europe may have UK, Germany, France and Asia may have Singapore, India, China etc. Hence, in this dimension, the individual countries are the lowest level nodes and typically we can do planning ONLY at the lowest level nodes. So in a normal situation, we can plan at the level of India, USA, Canada, Germany etc but we may not be able to plan at the level of Europe, North America, and Asia. This is good if we want to do bottom-up planning. If we have to do top-down planning where it may become important to enter the data at the higher level nodes, then the normal write back operation may not allow us to do so. In such cases, write-back badi can be effectively used. With the write-back badi we can enter the plan data at a higher level node and the data will get stored at the base level dimension members by means of the disaggregation mechanism defined in the write-back badi. The disaggregation mechanism can be defined in the badi implementation. This may include different types of distributions, including even distribution, based on a percentage value which is maintained in a property, or distribution based on previous year's data, etc.  Some type of ABAP program could also be developed to allow maintenance of such distribution rules for parent level entries per Appset/Application/Parent Member.

Another example where write back badi may become necessary is where we have matrix security and the data entry has to be secured based on the matrix security. Let us take an example to clarify this scenario.

Let us consider that a member access profile (say MAP1) is set up as follows:

Category =       Plan -               Read/Write

Time =             2009.OCT -     Read/write

Time =             [ALL] -            Read only

The idea here is that the user should be able to read the plan values for all time periods but write to only 2009. OCT month plan values.

Now consider that there is another member access profile (say MAP2) that is set up as follows:

Category =       Forecast -                    Read/Write

Time =             [ALL] -                        Read/Write

The idea here is that the user should be able to write forecast values to all periods.

Now, if these profiles are assigned to two different users there is no issue. However if they are assigned to the same user, then there is a conflict. Clearly, the administrator of the security intends to RESTRICT writing of values to Plan category for time other than 2009.OCT. However what happens is that if the user who has both these member access profiles assigned, enters a Plan value for say 2009.SEP, it gets saved according to the rule of least restrictions by dimension - in this case the time dimension. This is not what the administrator might have intended.

In this case also the write-back badi can come to our rescue. With the write-back badi we can very effectively handle the situations like this.

The write back badi is a ‘pre-process' badi. This means that it is called before any other checks are processed. Since the write-back badi is called before the standard security checks, validation checks, work status checks etc, it can accomplish the tasks that are generally not possible with the standard write-back operations. The Enhancement Spot for the write-back badi is  ‘UJR_WRITE_BACK' and it includes filters for AppSet ID, Application ID, and Module ID.

Appset and Application IDs are self explanatory. The module can be either Manual planning, journals, data manager, comments or document modifications. This enables us to use a PRE-PROCESS method where the logic for pre-processing for the write back can be written. BAdI Implementation UJR_BADI_SAMPLE_DISAGGREGATE is shipped as an sample implementation for disaggregation.

There is also a how-to guide available for the step-by-step instruction for the write-back badi implementation for disaggregation. (http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f0309226-814f-2d10-61a6-ef8da29e3727?QuickLink=index&overridelayout=true)

Thus write-back badi can b

Technorati Tags: SAP BPC Best Practices sap bpc benchmarking sap bpc tutorial sap bpc examples outlooksoft best practices sap bps best practices sap bpc tips sap bpc standards sap bpc guidelines

e a very useful utility for us to leverage on out BPC NW implementation projects

SAP BPC Tutorials | SAP BPC Training | SAP BPC Interview Questions |SAP BPC Books