BPC Script logic for Dummies? (Part 1)

BPC Script logic for Dummies? (Part 1) James Lim   Business Card Company: SAP Posted on May. 24, 2011 10:45 AM in Business Intelligence (BusinessObjects), Enterprise Performance Management

Even though I have a long experience with BPC, if someone asks me to a write a script logic, I might not write that code within 10 minutes.

It is not a problem of my knowledge but writing a script logic needs to understand your financial requirements and knowledge of your application like account member id and name of properties.

There are some documents and help files about the script logic and HTG but end users may feel that is not easy. I agree with that but if you understand its structure and concept, I can guarantee you can read and understand what it means and what the purpose of that script logic is. On top of that, you might be enable to modify or create it. It is the same thing that is not easy to write a book but reading a book is a different story.

 

Let’s learn it step by step.

Understanding 3 logic parts.

   We can say that BPC logic consiss of 3 parts.  Scoping, calculation (create/Record) and Writing.

 

   A. Scoping

       BPC is based on NW BI or MSAS which has a lot of data. Therefore, if you don't specify scope,
     it will take a lot of time. Let's say you need to calculate 2011.January, actual data and only one
     account like 'Discounted External sales' based on the External Sales.

     How can we scope it from a big database?

 

    The answer is....  *XDIM_MEMBERSET

    (Of course there is more advanced scoping command but we will discuss it in next post.)

    *XDIM_MEMBERSET is using for scoping data by each dimension.

 

   Here is the grammar of XDIM_MEMBERSET. 

    *XDIM_MEMBERSET <DIMENSIONNAME> = <MEMBERNAME 1>,<MEMBERNAME2>

     ...<MEMBERNAME n>

 

   Let's scope above example.

 

   For scoping 2011.January, *XDIM_MEMBERSET TIMEDIM=2011.JAN

   For scoping actual,       *XDIM_MEMBERSET CATEGORYDIM=ACTUAL

   For scoping external sales, *XDIM_MEMBERSET ACCOUNTDIM=EXTSALES

   (Note: we need to scope External sales because discounted External sales will be calculated based on the External Sales.) 

 

   B. calculation

       We just finished scoping so it is time to calculate (create) data.

       BASIC statement for calculate (create/Record) is *REC.  (It means Record)

 

       Here is the grammar of *REC statement

  *REC [([FACTOR|EXPRESSION = {Expression}[,{dim1}={member},{dim2}=…])] 

 

     Using that grammar, we can make our script as below.

     *REC (FACTOR = 0.9, ACCOUNT="DISCOUNTED_EXTSALES")

     It means multiply by 0.9 to current scoped a record and create a record

     after replace account member with DISCOUNTED_EXTSALES

 

     Here is an example what will happen with above statement.   

 

    <Scoped record>

         EXTSALES, 2011.JAN, ACTUAL, 10000

    <Generated record>

         DISCOUNTED_EXTSALES, 2011.JAN, ACTUAL, 9000

   

   What if you want to put a generated record into BUDGET?

    Then statement should be

 

          *REC (FACTOR = 0.9, ACCOUNT="DISCOUNTED_EXTSALES", CATEGORY="BUDGET")

 

   and records will be

    <Scoped record>

         EXTSALES, 2011.JAN, ACTUAL, 10000

    <Generated record>

         DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, 9000

 

 

    OK, Now you want to put 80% value into FORECAST at the same time. What should we do?

    We can use another *REC statement at the same time.

 

         *REC (FACTOR = 0.9, ACCOUNT="DISCOUNTED_EXTSALES", CATEGORY="ACTUAL")

         *REC (FACTOR = 0.8, ACCOUNT="DISCOUNTED_EXTSALES", CATEGORY="FORECAST")

 

    <Scoped record>

         EXTSALES, 2011.JAN, ACTUAL, 10000

    <Generated record>

         DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, 9000

         DISCOUNTED_EXTSALES, 2011.JAN, FORECAST, 8000

 

    Getting easier? I hope so :)

 

   Please keep in mind below rule.

        a. Each REC instruction generates ONE new record.

        b. Each source record can generate as many records as desired.

            It means you scoped 1 record but you can create multiple records using this.

           Currency translation is the best example because you need multiple

           converted currencies using a local currency record.

           Therefore, you can imagine there will be multiple *REC statement

           in your currency conversion script logic.

        c. Destination cell can be same. All values will be accumulated.

           *REC statement will generate a new record so it doesn't matter even though destination is same.

 

    C. Writing

        As a final step, we need to write data into database.

        Script command is really simple one.

 

        *COMMIT

 

        Fortunately, it doesn't have any parameter. Just use *COMMIT. 

        When BPC script engine execute *COMMIT, generated records will be posted

        to the table using BPC sending engine which is same engine that you submit

        data from the Excel workbook.

 

 We reviewed three main parts of BPC Logic script as a first step.

 I will explain advanced scoping, recording and commit command in the next post.

James Lim   SAP RIG America - BPC MS platform architect & Customer Care

---

Please leave any comment if you had any experience about logic script.
Comment on this weblog

Showing messages 1 through 6 of 6.
Titles Only	Main Topics	Oldest First

• BPC Script logic
  2011-06-27 09:01:26 Dorothy Wilson Business Card [Reply]
  
  Helpful post!

• Thanks
  2011-06-01 05:58:14 v sapfico Business Card [Reply]
  
  Thanks for the information James.

  
  Can you also please show me an example of how to 'get' the value of a member from a dimension, and use that value in the *REC statement? Basically, I want the system to 'read' values from the master data and use those values in my calculations.

  
  Thanks,
  V.

• Very useful
  2011-05-25 05:30:54 Raja Thangamani Business Card [Reply]
  
  Its very useful. Looking forward to see more blogs on the same :)

  
  Raja

• Very helpful
  2011-05-25 05:18:39 James Link Business Card [Reply]
  
  James,

  
  Thank you very much for this blog series. While I'm not a script logic writer myself, I find it very valuable to understand at least the basics. This helps me to learn more of the IT speak so I can help translate business requirements into language that IT folks can better understand. I look forward to your future posts on this subject.

  
  ~Jim

• Perfect way to get started with script logic.
  2011-05-24 13:29:12 Scott Bean  Business Card [Reply]
  
  This is great for someone who is just gettting started with script logic. Showing not just statement examples, but also the results of those statements is really helpful. Thanks James.
  • Perfect way to get started with script logic.
    2011-06-01 05:56:54 v sapfico Business Card [Reply]
    
    Thanks for the information James.

    
    Can you also please show me an example of how to 'get' the value of a member from a dimension, and use that value in the *REC statement? Basically, I want the system to 'read' values from the master data and use those values in my calculations.

    
    Thanks,
    V.

---

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/24735