How to write dynamic BPC Script Logic – part 1
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.
It's better to clearly define from the very beginning - you are talking about DM package scripts, not about default.lgf!
B.R. Vadim
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
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.
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
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
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