Currently Being Moderated
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

 

   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.

        *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.

Comments

Actions

Filter Blog

By author:
By date:
By tag: