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.
1. Understand 3 logic parts.
Logic is a special script engine and it consists of 3 parts. Scoping, calculation(create/Record) and Writing.
2. 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 to read data.
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 this from a big database?
The answer is.... *XDIM_MEMBERSET
*XDIM_MEMBERSET is using for scope data by each dimension.
Here is the grammar of XDIM_MEMBERSET.
*XDIM_MEMBERSET <DIMENSIONNAME> = <MEMBERNAME 1>,<MEMBERNAME 2>...<MEMBERNAME n>
Now, let't scope above exampe.
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.)
3. Now, we just finished scoping so it is time to calculate(create) data.
Unlike other script engine, there is no temporary variable in the logic script engine so it will create a record that has same as fact table structure.
and it will replace or change its value uaing '*REC' command. (Note: *REC 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="DISCOUNT_EXTSALES")
Which means multiply by 0.9 to current scoped record and replace account member with DiSCOUNT_EXTSALES
Here is an example what happens with above statement.
<Scoped record>
EXTSALES,2011.JAN,ACTUAL,10000
<Generated record>
DISCOUNT_EXTSALES,2011.JAN,ACTUAL,9000
What if you want to put generated record into BUDGET category?
Then statement should be
*REC (FACTOR = 0.9,ACCOUNT="DISCOUNT_EXTSALES",CATEGORY="BUDGET")
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="DISCOUNT_EXTSALES",CATEGORY="BUDGET")
*REC (FACTOR = 0.8,ACCOUNT="DISCOUNT_EXTSALES",CATEGORY="FORECAST")
<Scoped record>
EXTSALES,2011.JAN,ACTUAL,10000
<Generated record>
DISCOUNT_EXTSALES,2011.JAN,BUDGET,9000
DISCOUNT_EXTSALES,2011.JAN,FORECAS,8000
Getting easier? I hope so :smile:
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.
4. As a final step, we need to write data into database.
script statement is really simple one.
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.
