Skip to Content
I am sorry for the late posting of this series but I had to take my vacation and needed to get some training about HANA 🙂
Let’s start to learn how to caluate and write some data using the script logic.
Again, the script logic consists of 3 parts; Scoping, Calculationand Writing.

1. Basic concept of Writing and *REC statement

  As we saw in my first posting of this series, *REC statement is used for writing data.
  You need to keep in mind that *REC will create records based on the scoped records.

  For example, if your scoped record is same as below. 

 

    <Scoped record>
         EXTSALES, 2011.JAN, ACTUAL, USA, 10000

         and your *REC statement is below.
        *REC (FACTOR = 0.9, ACCOUNT=”DISCOUNTED_EXTSALES”, CATEGORY=”BUDGET”)

    Then your generated record will be 

    <Generated record>
        DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, USA, 9000

  What if your scoped record is not a single one but multiple record?

    <Scoped record>
         EXTSALES, 2011.JAN, ACTUAL, USA,   10000
         EXTSALES, 2011.JAN, ACTUAL, KOREA, 3000
         EXTSALES, 2011.JAN, ACTUAL, CANADA, 5000

  Then your generated records will be 

    <Generated record>
         DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, USA,    9000
         DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, KOREA, 2700
         DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, CANADA, 4500

  As you can see, we changed Account value, Category value and its signeddata vale (or measure value) using *REC statement.
  The other dimension that is not specified in the *REC statement will be same as scoped data so 2011.JAN and each country (entity) doesn’t 
be changed.

2. Grammar of *REC statement.

   Here is the grammar of *REC statement. You can use FACTOR or EXPRESSION for various calculations for signeddata vale (or measure value).
   And specify dimension name and member to change its value.
 
   *REC[([FACTOR|EXPRESSION={Expression}[,{dim1}={member},{dim2}=?)] 

 
3. What is the difference between FACTOR and EXPRESSION?

   The FACTOR is a factor(multiply) by which the retrieved amount is to be multiplied.

   Here is an example.

    <Scoped record>
         EXTSALES, 2011.JAN, ACTUAL, 10000

         *REC(FACTOR=6/2)

    <Generated record>
         EXTSALES, 2011.JAN, ACTUAL, 30000

   What if you want to add or divide? then you should use EXPRESSION.
   The EXPRESSION is any formula that will result in the new value to post.
   The formula can include regular arithmetic operators, fixed values and the Script logic keyword %VALUE%
   this is representing the original retrieved value of the scoped record.

   Here is an example. 

    <Scoped record>
         EXTSALES, 2011.JAN, ACTUAL, 10000

         *REC(EXPRESSION=%VALUE% + 5000)

    <Generated record>
         EXTSALES, 2011.JAN, ACTUAL, 15000

Now we got the basic things of *REC statement but you may ask below questions.
  
   “There are some scoped data and I need to do different calculations based on each specific dimension member.”
   “I need to copy a value to multiple destinations!” 
   “How can I get the value from the other application?”
   “I want to use some value from other records to calculate the result.”
   “Can I use a property value to calculate the result?”

The script logic can handle above requirements.
I will explain first question in this post and will do others in the next post.
“There are some scoped data and I need to do some calculations based on each specific dimension member.”
  
Yes. That’s why *you MUST use *REC statement with *WHEN ~ *IS ~ *ELSE ~ *ENDWHEN statement.
Let’s assume you want to create forecast values of salary and put it into the forecast category based on the country’s actual salary values of January, 2011.
We need to increase 10% for US, 5% for Canada and 3% for other countries.
Let’s assume ENTITY dimension has country information.
To do this, you need to scope first.
  
  *XDIM_MEMBERSET ACCT = SALARY
  *XDIM_MEMBERSET TIME = 2011.JAN
  *XDIM_MEMBERSET CATEGORY = ACTUAL
        
Now you need to write the *REC statements
   *REC(FACTOR = 1.1, CATEGORY=“FORECAST“)  // 10%
   *REC(FACTOR = 1.05, CATEGORY=“FORECAST“) // 5%
   *REC(FACTOR = 1.03, CATEGORY=“FORECAST“)  // 3%
Finally, you should specify a condition of each *REC statement.
For doing this, you MUST use *WHEN ~ *IS ~ *ELSE ~ ENDWHEN statement.

First, Write down *WHEN and *ENDWHEN outside of the *REC statement
     *WHEN 
           *REC(FACTOR = 1.1, CATEGORY=“FORECAST“)  // 10%
           *REC(FACTOR = 1.05, CATEGORY=“FORECAST“)  // 5%
           *REC(FACTOR = 1.03, CATEGORY=“FORECAST“)  // 3%
     *ENDWHEN

       NOTE : You don’t need to use the indentation of code in the script logic
                 but I would like to recommend using it for better readability.

Second, write a dimension name that you want to compare next to *WHEN.
            In this example, it will be ENTITY dimension.

      *WHEN ENTITY
           *REC(FACTOR = 1.1, CATEGORY=“FORECAST“)  // 10%
           *REC(FACTOR = 1.05, CATEGORY=“FORECAST“)  // 5%
           *REC(FACTOR = 1.03, CATEGORY=“FORECAST“)  // 3%
     *ENDWHEN

Third, put *IS statement on top of each *REC statement and *ELSE statement on top of the last *REC statement.
           We need two *IS statements and   *ELSE statement because there are two conditions and others will be calculated as one condition.
      *WHEN ENTITY
            *IS
                  *REC(FACTOR = 1.1, CATEGORY=“FORECAST“)  // 10%
            *IS
                  *REC(FACTOR = 1.05, CATEGORY=“FORECAST“)  // 5%
            *ELSE
                  *REC(FACTOR = 1.03, CATEGORY=“FORECAST“)  // 3%
      ENDWHEN

Fourth, put each condition value next to *IS
 
      *WHEN ENTITY
            ***IS USA 
                  *REC(FACTOR = 1.1, CATEGORY=“FORECAST“)  // 10%
            ***IS CANADA
                  *REC(FACTOR = 1.05, CATEGORY=“FORECAST“)  // 5%
            ***ELSE
                  *REC(FACTOR = 1.03, CATEGORY=“FORECAST“)  // 3%
      *ENDWHEN

As a last step, put *COMMIT at end of the script so that logic engine can post data to Database.
so final version should be same as below code.
      *XDIM_MEMBERSET ACCT = SALARY
      *XDIM_MEMBERSET TIME = 2011.JAN
      *XDIM_MEMBERSET CATEGORY = ACTUAL
      *WHEN ENTITY
            ***IS USA 
                  *REC(FACTOR = 1.1, CATEGORY=“FORECAST“)  // 10%
            ***IS CANADA
                  *REC(FACTOR = 1.05, CATEGORY=“FORECAST“)  // 5%
            ***ELSE
                  *REC(FACTOR = 1.03, CATEGORY=“FORECAST“)  // 3%
      *ENDWHEN
      *COMMIT

       Note 1 : You can use multiple condition value like *IS VALUE_A, VALUE_B
       Note 2 : You can use >, <= with numeric value with *IS statement.  ex) *IS > 4
                   By default, it is equal (=) so it will be ok even though you don’t specify it.
       Note 3 : can’t use AND, OR and NOT with *IS
       Note 4 : ” (double quotation) is not mandatory for comparing string value with *IS statement.
       Note 5 : *WHEN statement can be nested. For example,
               *WHEN xxx
                          *IS “A”
                                 *REC(…)
                                 *REC(…)
                          *IS “B”
                                 *REC(…)
                                 *WHEN yyy
                                         *IS “C”,”D”,”E”
                                                   *REC(…)
                                         *ELSE
                                                   *REC(…)
                                 *ENDWHEN
               *ENDWHEN
       Note 6 : You can use property value with *IS statement.  ex) *IS Intco.Entity
 
Now we finished learning 3 basic parts of the script logic.
As I explained you in the first post of this series, I hope you feel script logic is not too complex. 🙂
I will post a couple of advanced features like LOOKUP in the next post for answering other questions.
To report this post you need to login first.

4 Comments

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

  1. Mansi Dandavate
    Hi James,

    Can you tell me what will happen if I have a statement like below

    REC(Expression = 0)

    Will it create a new record with all the dimensions same as existing record with signdata = 0
    or
    will it modify the record in scope with signdata = 0.

    (0) 
    1. James Lim Post author
      Mansi,

      If you have some data in the selected region, it will post apposite value to make them zero.

      If you don’t have it, Logic engine will not post anything because there is no data to process.

      I guess you may need to create zero value data but logic can’t do it.

      Even though you can load zero value data using Data Manager, those records will be removed after you run full optimization with compression mode.

      In addition, loading zero value into the cube is not a good idea because the number of data cell will be exploded!

      Regards,
      James Lim.

      (0) 
  2. Michael Tibbit

    Hi James,

    Thank you for writing this blog, it has really helped me get my head around writing some script logic. In this blog you mention the question  “I need to copy a value to multiple destinations!” and say you will answer it in a future blog, I have looked through your other blogs and cant find the answer. I have managed to get this to work using multiple *REC statements specifying the individual accounts to copy the data to but I have hundreds of different combinations that I need to copy data to.

    Is it possible to copy data to multiple accounts based on a property, something like

    *REC(EXPRESSION=LOOKUP(….),[ACCOUNT].PROPERTIES(PROPERTY1)=”X”)

    Any help you can offer would be much appreciated.

    Thanks

    Mike

    (0) 
    1. James Lim Post author

      Hi Michael,

      I am sorry for late reply because I was travelling and training for  BI.

      I think you can find a way using *FOR-*NEXT with *XDIM_FILTER. (I explain it in the part5.) Then you don’t need to specify all REC satements.

      Thank you.

      James Lim

      (0) 

Leave a Reply