Skip to Content

This is the first part of a series that shows how you can write more powerful Script Logic for the BPC Netweaver version. We are about to embark on a quest through the thicket of BPC Script Logic so lean back and enjoy the ride. If you are completely uninitiated with Script Logic, I recommend reading BPC Script Logic for Dummies by James Lim and the help documentation first.

Background

It is well documented that the BPC Netweaver version features a limited set of Script Logic commands. Some keywords available in the Microsoft version such as FLD are not supported. As a result it has become common to use ABAP not only because of performance bottlenecks but also to work around these limitations. Sometimes ABAP has been applied even when Script Logic might have been a better choice for the task.

When Script Logic is used it is often written in a static fashion including a lot of hard-coded values. This kind of code is simple to create and easy to read. However, it also tends to be high-maintenance when things change (as they always do).

Incomplete and sometimes contradicting documentation seems to be a contributing factor to these trends. This series of articles is my attempt to help developers and business users to see the many possibilities Script Logic offers. There is much more than meets the eye at first blush. Before going to technical details let’s lay out the business case.

Business example

In our business example we want to read salary account values, calculate associated payroll charges and save the calculated values on separate accounts. In our example, the applicable percentage depends on the source AUDITID. Target ACCOUNT varies depending on the source ACCOUNT.

Source values to be read are stored with AUDITID dimension members PAYR01/PAYR02 and ACCOUNT dimension members PL5110,PL5111 / PL6110,PL6111. Calculation results should be saved using AUDITID member CALCRES and ACCOUNT members PL5119/PL6119. The table below summarizes our posting logic.

Source AUDITID

Source ACCOUNT

Percentage

Target AUDITID

Target ACCOUNT

PAYR01

PL5110

0.25

CALCRES

PL5119

PAYR01

PL5111

0.25

CALCRES

PL5119

PAYR01

PL6110

0.25

CALCRES

PL6119

PAYR01

PL6111

0.25

CALCRES

PL6119

PAYR02

PL5110

0.31

CALCRES

PL5119

PAYR02

PL5111

0.31

CALCRES

PL5119

PAYR02

PL6110

0.31

CALCRES

PL6119

PAYR02

PL6111

0.31

CALCRES

PL6119

Let’s have a look at a simple script suitable for our business case. Later on, we will learn how it can be improved with minimum effort.

Simple Script

Script Logic is still often used in projects because it is easy to learn and it can be maintained by business users instead of IT specialists. Below you can find a simple Script Logic that meets our business case requirements. The keyword FACTOR is used in this example but you can easily enhance the script by using EXPRESSION instead.

*XDIM_MEMBERSET AUDITID = PAYR01, PAYR02
*XDIM_MEMBERSET ACCOUNT = PL5110, PL5111, PL6110, PL6111
*WHEN AUDITID
    *IS PAYR01
      *WHEN ACCOUNT
        *IS PL5110, PL5111
          *REC(FACTOR = 0.25, ACCOUNT = PL5119, AUDITID = CALCRES)
        *IS PL6110, PL6111
          *REC(FACTOR = 0.25, ACCOUNT = PL6119, AUDITID = CALCRES)
      *ENDWHEN
    *IS PAYR02
      *WHEN ACCOUNT
       *IS PL5110, PL5111
          *REC(FACTOR = 0.31, ACCOUNT = PL5119, AUDITID = CALCRES)
       *IS PL6110, PL6111
          *REC(FACTOR = 0.31, ACCOUNT = PL6119, AUDITID = CALCRES)
      *ENDWHEN
*ENDWHEN

Some remarks

There is nothing inherently wrong writing script this way especially when only a few static operations are needed. However, adding new members and more complicated conditions might require numerous nested WHEN statements. As time passes and the system evolves, a statically written script tends to become longer and longer. Lack of forethought during the design phase can make the system difficult to maintain and even prone to errors.

In the second part of this series, I will show step by step how easy it is to improve this sample script.

To report this post you need to login first.

6 Comments

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

    1. Sami Järvinen Post author

      I have a confession to make. I am a habitual user of *XDIM_MEMBERSET within Default Logic. Phew, feels so much better now that I got that off my chest. The reason for my misbehavior is it being too useful to give up. If I find other ways to achieve the results I want I will amend my ways. Using a For Next loop as in your How-To: Write default.lgf example might do the trick but some serious testing is required. Until then all I can say is: ‘Lord make me chaste but not yet’.

      Sami

      (0) 
    2. Sami Järvinen Post author

      I thought about Vadim’s post and the advice not to change scope with XDIM_MEMBERSET in default logic. It is clear that XDIM_MEMBERSET should be used carefully as it can cause incorrect results and it might have an impact on performance. Let’s compare the two methods. I don’t have system access right now to check the syntax but I’ll give it a try. Normally, I wouldn’t hard-code values like this but here’s a simplified logic with XDIM_MEMBERSET.


      *XDIM_MEMBERSET ACCOUNT = QUANTITY
      *WHEN PRODUCT
      *IS WIDGET
      *REC(FACTOR = ([P_ACCOUNT].[PRICE]), ACCOUNT = WIDGETSALES)
      *IS GADGET
      *REC(FACTOR = ([P_ACCOUNT].[PRICE]), ACCOUNT = GADGETSALES)
      *ENDWHEN


      The second method works with the original scope and XDIM_MEMBERSET is not used. I omitted the FOR/NEXT loop for clarity and wrote the two resulting statements instead. I added extra parentheses but without syntax check and testing I’m not sure if they are necessary.


      *WHEN ACCOUNT
      *IS QUANTITY
      *WHEN PRODUCT
      *IS WIDGET
      *REC(FACTOR = ([ACCOUNT].[PRICE]), ACCOUNT = WIDGETSALES)
      *IS GADGET
      *REC(FACTOR = ([ACCOUNT].[PRICE]), ACCOUNT = GADGETSALES)
      *ENDWHEN
      *ENDWHEN


      *WHEN ACCOUNT
      *IS PRICE
      *WHEN PRODUCT
      *IS WIDGET
      *REC(FACTOR = ([ACCOUNT].[QUANTITY]), ACCOUNT = WIDGETSALES)
      *IS GADGET
      *REC(FACTOR = ([ACCOUNT].[QUANTITY]), ACCOUNT = GADGETSALES)
      *ENDWHEN
      *ENDWHEN


      I don’t expect to experience a discernible difference in performance while unit testing either script. If only a single QUANTITY or PRICE figure is thrown at the script, they should behave pretty much the same way. The first version could have a significant performance impact if used incorrectly because it can update data records even when both QUANTITY and PRICE are unchanged. This is unlikely to happen if your model also has PRODUCT dimension as in the above example.


      As a general rule, multiple nested WHEN/ENDWHEN statements and FOR/NEXT loops make the script harder to read and to maintain. In my opinion, the first method is easier to read and write in most cases, even if in these simplified examples the difference isn’t pronounced.


      A high number of data records and overall complexity of the default script might render the default script useless if the first style is used incorrectly. The second style with a loop could also be problematic if used inappropriately but probably it is less vulnerable to misuse.


      So it really depends on your specific use case. I would also advice to be careful with the first style because of potential performance issues. Either way, always test and remember also to stress-test with real-life data if possible.

      (0) 
      1. Vadim Kalinin

        Hi Sami, just assume that you have multiple accounts in the model. Sending data to any account will trigger recalculation because of XDIM_MEMBERSET ACCOUNT=…

        Vadim

        (0) 
        1. Sami Järvinen Post author

          Hi Vadim,

          Yes it will if all dimension members except for the account are equal. This can be avoided by proper design. In the above example, PRODUCT dimension would prevent recalculation from happening in most cases. So fixed cost planning would not be affected. But special care should be taken if the same model handles product cost planning. Style 2 is more forgiving in this respect.

          Sami

          (0) 
          1. Vadim Kalinin

            PRODUCT dimension is useful when you have significant number of products. But in case of PRODUCT dimension you will not need to have different accounts for calculation result 🙂 Then even style 2 will be very simple (2 WHEN/ENDWHEN loops with single REC each).

            For a simple model with 5 products like in my project I use individual accounts for price, q-ty and amount without PRODUCT dimension. And I also use style 2.

            Vadim

            (0) 

Leave a Reply