Like other program language or script, the logic script also supports Loop statement.

Let's see how it works.

Here is the syntax of *FOR - *NEXT statement.
 
     *FOR  {variable1} = {set1} [ AND {variable2={set2}]

           {other statement...}

     *NEXT 

And here is an example.

     *FOR %CURR%=USD,EURO

           *REC(FACTOR=1.1,TIME=2011.OCT,CURRENCY=%CURR%)

     *NEXT  

So what is the meaning of the example above?

1. We set a variable as %CURR%
2. %CURR% variable will be replaced with two values USD and EURO.
3. *REC statement includes %CURR% variable.
4. Therefore, it will be translated two statement as below
   because *REC statement exists inside of *FOR - *NEXT statement.

   *REC(FACTOR=1.1,TIME=2011.OCT,CURRENCY=USD)     
   *REC(FACTOR=1.1,TIME=2011.OCT,CURRENCY=EURO)    

Let's assume %CURR% varible has USD,EURO,CHF,KRW then the it will be translated as below.

   *REC(FACTOR=1.1,TIME=2011.OCT,CURRENCY=USD)     
   *REC(FACTOR=1.1,TIME=2011.OCT,CURRENCY=EURO)

    *REC(FACTOR=1.1,TIME=2011.OCT,CURRENCY=CHF)     
   *REC(FACTOR=1.1,TIME=2011.OCT,CURRENCY=KRW)    

Someone may say "we can use multiple line of *REC statement".

Of course, it is correct if it is simple but we need *FOR - *NEXT statement because it can be used as a nested form.

For example,

        *FOR %YEAR%=2003,2004,2005 
                *FOR %MONTH%=JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
                    *REC(FACTOR=GET(ACCOUNT="TOT.OVRHEAD",TIME="TOT.INP")/100,TIME="%YEAR%.%MONTH%") 
                *NEXT 
        *NEXT

If the user is using *REC statement,user should write 36 statements instead of above simple a *FOR - *NEXT statement.

(NOTE: BPC NW supports Nested FOR - NEXT in the version 7.5 )

In addtion, User may use two variable sets like the exmaple below.

        *FOR %X%=1,2,3 AND %Y%=A,B,C

             *REC(FACTOR=1.5,TIME=%X%,CURRENCY=%Y%)

        *NEXT     

So the first variable set and the second variable set will be matched 1 to 1; then will be replaced as the example below.

             *REC(FACTOR=1.5,TIME=1 ,CURRENCY=A) 
             *REC(FACTOR=1.5,TIME=2 ,CURRENCY=B) 
             *REC(FACTOR=1.5,TIME=3 ,CURRENCY=C)

What if the number of values is not matched between first and second variable?

If the first variable has less values than  the  second  variable,
the  extra  values  of  the  second  variable  will be  ignored. 

If  the  first  variable  has more values than the second variable,
the missing values of the second variable will be assumed null so please be careful to match the number of varible.

The last thing about *FOR - *NEXT is using data set variable as values.
Users can use data_set like %TIME_SET% instead of specifying all time members.
This is very useful when we use script logic with dynamic dataset.

For example, We can use 
 
        *FOR %MYTIME%=%TIME_SET%
 
instead of             

        *FOR %MYTIME%=2003.JAN,2004.JAN,2005.JAN               

Therefore, users can execute script logic dynamically based on the passed data sets.

We will see how to use GET and FLD function and memory variable in the next post as the last topic.

OK, let’s start to find out the answer about one of the questions that we had in the last post.

"How can I get the value from the other application?"
The simple answer is... USE *LOOKUP/*ENDLOOKUP!

The simplest example is the currency conversion because you need to read rate value from the rate application to convert

currency values of the finance application.

 (NOTE:*LOOKUP/*ENDLOOKUP also can be used for reading value of the current application.)

Here is the syntax of *LOOKUP/*ENDLOOKUP
    
The syntax is: 
 
*LOOKUP {Application}
*DIM [{LookupID}:] {DimName}="Value" | {CallingDimensionName}[.{Property}]
[*DIM …] 
*ENDLOOKUP
 
 
   {Application} is the name of the application which you will retrieve value.
 
   {DimensionName} is a dimension in the lookup application.
 
   {CallingDimensionName} is a dimension in the current application.
 
   {LookupID} is an optional variable that will hold the value so that you can use it in the script.
              This is only required when multiple values must be retrieved.      

Now, let's do it step by step.

Here are our requirements for the currency conversion.
1. You need to get the rate values from rate application for currency conversion (LC to USD and EUR). 
2. The member id of RATE dimension in the rate application should be the same as RATETYPE property of the account dimension in the finance application.
3. The member id of RATEENTITY dimension in the rate application should be "DEFAULT"
4. The rule of currency conversion is 'DESTINATION CURRENCY/CURRENT CURRENCY'
  
    
First, you need to define *LOOKUP with application name.
   *LOOKUP RATE
    
   *ENDLOOKUP

Second, specify dimensions of RATE application with *DIM statement.
   (Let's assume the rate application has RATEENTITY, INPUTCURRENCY, RATE, CATEGORY and TIME dimension.)  

   *LOOKUP RATE
     *DIM RATEENTITY
     *DIM INPUTCURRENCY
     *DIM RATE
     *DIM CATEGORY
     *DIM TIME
   *ENDLOOKUP
 
Third, assign the member id value of each dimension from the current application (Finance) or use fixed value.
       If you need to retrieve multiple value according to different member id values of specific dimensions,
       Make copies of that dimension and assign different values.
      
   *LOOKUP RATE
     *DIM RATEENTITY="DEFAULT"      // Fixed value
     *DIM INPUTCURRENCY="USD"       // Fixed value
     *DIM INPUTCURRENCY="EUR"       // Fixed value, Copy same dimension for another value
     *DIM INPUTCURRENCY=ENTITY.CURR // added one more for the currency conversion as variable value
     *DIM RATE=ACCOUNT.RATETYPE     // Variable value based on the current application
     *DIM CATEGORY
     *DIM TIME
   *ENDLOOKUP

Fourth, Put variables for multiple retrieving values in front of each duplicate dimension name. 

   *LOOKUP RATE
     *DIM RATEENTITY="DEFAULT"              
     *DIM DESTCURR1:INPUTCURRENCY="USD"     
     *DIM DESTCURR2:INPUTCURRENCY="EUR"     
     *DIM SOURCECUR:INPUTCURRENCY=ENTITY.CURR
     *DIM RATE=ACCOUNT.RATETYPE               
     *DIM CATEGORY
     *DIM TIME
   *ENDLOOKUP

   -------------------------------------------------------------------------
   Note: If you want to get some value based on two or more dimensions,
         You should use the same variable name when you map dimensions.
         Here is an example.

         *LOOKUP OWNERSHIP
             *DIM INTCO="IC_NONE"
             *DIM PARENT="MYPARENT"

             *DIM PCON:ACCOUNTOWN="PCON"      // PCON is used for ACCOUNTOWN
             *DIM PCON:ENTITY=ENTITY          // PCON is used for ENTITY

             *DIM IC_PCON:ACCOUNTOWN="PCON"    // IC_PCON is used even though it searches same "PCON"
             *DIM IC_PCON:ENTITY=INTCO.ENTITY  // IC_PCON is used for INTCO.ENTITY
         *ENDLOOKUP

         Even though the member id of ACCOUNTOWN dimension is same, the variable should be defined as a different variable because the member id of ENTITY dimension is different in the combination.
 
         If the 'ENTITY' property of INTCO dimension has I_FRANCE value, above *LOOKUP will select below two records and each variable will have different value.

             IC_NONE,MYPARENT,PCON,FRANCE,100    => PCON
             IC_NONE,MYPARENT,PCON,I_FRANCE,80   => IC_PCON
   ---------------------------------------------------------------------------
 

Last, Remove dimension names (TIME and CATEGORY> that don’t have any fixed value or variable value because it will be passed as current value

automatically. 
 
   *LOOKUP RATE
     *DIM RATEENTITY="DEFAULT"   
     *DIM SOURCECUR:INPUTCURRENCY=ENTITY.CURR
     *DIM DESTCURR1:INPUTCURRENCY="USD"
     *DIM DESTCURR2:INPUTCURRENCY="EUR"
     *DIM RATE=ACCOUNT.RATETYPE 
   *ENDLOOKUP
  
Now we get the values so how can we use these values?
You can use it using LOOKUP(Variable) in your *REC statement as below

   *WHEN ACCOUNT.RATETYPE
      *IS "AVG","END"
         *REC(FACTOR=LOOKUP(DESTCURR1)/LOOKUP(SOURCECURR),CURRENCY=”USD”)
         *REC(FACTOR=LOOKUP(DESTCURR2)/LOOKUP(SOURCECURR),CURRENCY=”EUR”)
   *ENDWHEN

   NOTE: You can use LOOKUP(variable) with *WHEN and *IS statement.

       Ex) *WHEN LOOKUP(PCON)          //as a condition value of when
              *IS <= LOOKUP(IC_PCON)   //as a value of IS
                  *REC(FACTOR=-1, PARENT ="MYPARENT",DATASRC="ELIM")
           *ENDWHEN 

We reviewed how to define *LOOKUP/*ENDLOOKUP statement and how to use it.

Now it is time to find out how it works in the script engine.

Let's assume below records are in the rate application and see what will happen during execute of the script logic.

RATEENTITY, INPUTCURRENCY, RATE, CATEGORY, TIME, SIGNEDDATA

DEFAULT, USD, AVG, ACTUAL, 2011.JAN, 1
DEFAULT, EUR, AVG, ACTUAL, 2011.JAN, 1.22
DEFAULT, CHF, AVG, ACTUAL, 2011.JAN, 0.91
DEFAULT, USD, END, ACTUAL, 2011.JAN, 1
DEFAULT, EUR, END, ACTUAL, 2011.JAN, 1.24
DEFAULT, CHF, END, ACTUAL, 2011.JAN, 0.93
RATCALC, USD, AVG, ACTUAL, 2011.JAN, 1
RATCALC, USD, AVG, ACTUAL, 2011.JAN, 1

Here are your current finance application records that need to be processed.

ACCOUNT, ENTITY, CATEGORY,  TIME, CURRENCY, SIGNEDDATA
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, LC, 5000
REVENUE,   SWITZERLAND, ACTUAL, 2011.JAN, LC, 1000

As you can see, there is no relationship between finance application and rate application.
We know Switzerland currency is CHF but there is no information in each fact table record.
It only has LC (local currency) value.

Then, how can script logic find the value and calculate it?

The key point is 'ENTITY.CURR' which we used it for mapping dimension as below.

    *DIM SOURCECUR:INPUTCURRENCY=ENTITY.CURR

ENTITY.CURR means 'CURR' property value of ENTITY dimension.
Therefore, Switzerland which is one of the Entity dimension member should have 'CHF' value in its 'CURR' property.

Same thing is for mapping RATE dimension of rate application as below.
    *DIM RATE=ACCOUNT.RATETYPE

So the 'RATETYPE' property value of INVENTORY and REVENUE account should have 'AVG' or 'END' value.

Therefore, the Script engine will do the following steps to process the first record of the fact table.

1. Select RATEENTITY = "DEFAULT" 

2. Select INPUTCURRENCY = "CHF” (because current Entity member's 'CURR' property value is 'CHF')
   OR INPUTCURRENCY = "USD"     
   OR INPUTCURRENCY = "EUR"      

3. Select RATE = "END” (because current account member's 'RATETYPE' property value is 'END')

4. Select CATEGORY = "ACTUAL” (There is no statement so it is same as current application CATEGORY value.)

5. Select TIME = "2011.JAN” (There is no statement so it is same as current application TIME value.)

All above selection will be combined with 'AND' condition.

So the 3 records below will be selected and its signeddata value will be assigned to each variable.

DEFAULT, USD, END, ACTUAL, 2011.JAN, 1    => DESTCURR1 will be 1
DEFAULT, EUR, END, ACTUAL, 2011.JAN, 1.24 => DESTCURR2 will be 1.24
DEFAULT, CHF, END, ACTUAL, 2011.JAN, 0.93 => SOURCECUR will be 0.93

After the script logic engine executes below statements, it will generate 2 records.

   *WHEN ACCOUNT.RATETYPE
      *IS "AVG","END"
         *REC(FACTOR=LOOKUP(DESTCURR1)/LOOKUP(SOURCECURR),CURRENCY=”USD”)
         *REC(FACTOR=LOOKUP(DESTCURR2)/LOOKUP(SOURCECURR),CURRENCY=”EUR”)
   *ENDWHEN

ACCOUNT, ENTITY, CATEGORY,  TIME, CURRENCY, SIGNEDDATA
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, LC,  5000
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, USD, 5376.34     // 5000 * (1/0.93)
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, EUR, 6666.67     // 5000 * (1.24/0.93)

For the 2nd record in the fact table, the 3 records below will be selected from the rate application fact table because

Revenue account has 'AVG' RATETYPE.

DEFAULT, USD, AVG, ACTUAL, 2011.JAN, 1
DEFAULT, EUR, AVG, ACTUAL, 2011.JAN, 1.22
DEFAULT, CHF, AVG, ACTUAL, 2011.JAN, 0.91

After it processes 'REVENUE' records, there will be 6 records in the fact table as below.
(4 records will be generated in total.)

ACCOUNT, ENTITY, CATEGORY,  TIME, CURRENCY, SIGNEDDATA
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, LC,  5000
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, USD, 5376.34    
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, EUR, 6666.67    
REVENUE,   SWITZERLAND, ACTUAL, 2011.JAN, LC,  1000
REVENUE,   SWITZERLAND, ACTUAL, 2011.JAN, USD, 1098.90     // 1000 * (1/0.91)
REVENUE,   SWITZERLAND, ACTUAL, 2011.JAN, EUR, 1340.66     // 1000 * (1.22/0.91)

We finished learning how to use *LOOKUP/*ENDLOOKUP statement.

Here are some questions and answers that I got frequently.

Question 1: What if rate application doesn't have the value?
            Then currency conversion will not happen.

Question 2: I don't have any records in the fact table of current application. What will happen?
            The script logic always reads records from the current application.
            Therefore, if there are no records in the fact table of the current application,
            Nothing will happen.

Question 3: Can we lookup parent member value instead of base member (leaf member)?
            MS version can do it with *OLAPLOOKUP statement instead of *LOOKUP but NW version doesn't have it yet.

  
I will explain about *FOR/*NEXT in the next post.

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, Calculation and 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 the Account value, the Category value and its signeddata vale (or measure value) using the *REC statement. 

The other dimension that is not specified in the *REC statement will be same as scoped data so 2011.JAN and each entity value will not be changed.

2. Grammar of *REC statement.

Here is the grammar of *REC statement. You can use FACTOR or EXPRESSION for various calculations for the 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 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  // 6/2 = 3 so 3 times.    

What if you want to add or divide? then you should use EXPRESSION.
The EXPRESSION can be 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 //  10000 + 5000 = 15000

4. The real example of the *REC statement  

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.

I explained basic 3 parts of script logic in the last post. It was Scoping, Calculation and writing.

We will find out more advanced features for scoping In this post.

1. Scope using member property

      We found how to use *XDIM_MEMBERSET last time.

      *XDIM_MEMBERSET is for scoping based on the member ID.
      What if user wants to scope members based on a specific property value?
      For example, a user wants to filter Account dimension members those are Asset.
      To achieve this, we need to use ACCTYPE property which has the type value of account.
      AST is the value for ASSET account. (Note: Value is based on the APSHELL of BPC.)
 
      The command is *XDIM_FILTER.
      The usage is

*XDIM_FILTER <DIMENSIONNAME> = [DIMENSIONName].Properties("Property name") = "Property value"

      So above example can be written as below.
      *XDIM_FILTER ACCOUNT = [account].properties(“ACCTYPE”)=”AST”

      Let's say Account dimension has 3 members as below.

         ID           ACCTYPE
      Extsales          INC
      CASH              AST
      TAXES             EXP
      NETINCOME    INC

      Then about *XDIM_FILTER_ACCOUNT will select CASH member only.

      Let's assume If you already used multiple *XDIM_MEMBERSET command

      and below are selected data from the fact tables.

      *XDIM_MEMBERSET TIME = 2011.JAN
      *XDIM_MEMBERSET CATEGORY = BUDGET

     <Result>
     EXTSALES , 2011.JAN, BUDGET, 9000
     CASH     , 2011.JAN, BUDGET, 3000
     TAXES    , 2011.JAN, BUDGET,  800
     NETINCOME, 2011.JAN, BUDGET, 1500

     Now if you add *XDIM_FILTER against ACCOUNT dimension.

     *XDIM_MEMBERSET TIME = 2011.JAN
     *XDIM_MEMBERSET CATEGORY = BUDGET
     *XDIM_FILTER ACCOUNT = [account].properties(“ACCTYPE”)=”AST”

     Only one record will be selected from above result because CASH is

     the only account member that has 'AST' value of ACCTYPE property.

     <Result>
     CASH     , 2011.JAN, BUDGET, 3000

2. Scope using member value
      We just figured out how to scope the source data based on the property.
      Then someone might ask this question.
      "Can we scope based on the value?

      For example, can we select all data that an account value greater than 100?
     

      Of course, we can do it.
      The command is *XDIM_GETMEMBERSET. Unlike other command,

      it needs  *ENDXDIM command to specify data.

      Here is the grammar of XDIM_GETMEMBERSET and an example.

      *XDIM_GETMEMBERSET {dimension} [={member set}]
           [*APP={application}] //optional
           [*XDIM_MEMBERSET {dimension} [={member set}] //as many of these as needed
           [*QUERY_TYPE= 0 | 1 | 2] //optional
           *CRITERIA {expression} //required
      *ENDXDIM

      *XDIM_GETMEMBERSET P_CC=[P_CC].[H1].[AAPJ].CHILDREN
           *APP=PLANNING
           *XDIM_MEMBERSET P_DataSrc=INPUT
           *CRITERIA [P_ACCT].[H1].[CE0001000]>1000 
      *ENDXDIM  

      It will get the data and those are..

           a. Children member of AAPJ in the P_CC dimension.  AND
           b. from the PLANNING application  AND
           c. INPUT member of P_Datasrc dimension AND
           d. CE0001000 member's value of the P_ACCT dimension should be greater than 100000

      Let's Assume Fact table has below records.

     
      CE0001000, 2011.JAN, ACTUAL, INPUT, KOREA , 2500
      CE0002000, 2011.JAN, ACTUAL, INPUT, CHINA , 5000
      CE0001000, 2011.JAN, ACTUAL, ADJ  , CHINA , 3000
      CE0002000, 2011.JAN, ACTUAL, INPUT, JAPAN , 1999
      CE0003000, 2011.JAN, ACTUAL, INPUT, JAPAN , 2222
      CE0001000, 2011.FEB, BUDGET, ADJ  , KOREA ,  345
      CE0001000, 2011.FEB, BUDGET, INPUT, TURKEY, 1999
      CE0003000, 2011.JAN, ACTUAL, INPUT, TURKEY, 1100
      CE0001000, 2011.FEB, BUDGET, INPUT, CHINA , 1050
      CE0001000, 2011.FEB, BUDGET, INPUT, JAPAN ,  450

      Which records will be selected?

      The answer is         
      CE0001000, 2011.JAN, ACTUAL, INPUT, KOREA, 2500
      CE0001000, 2011.FEB, BUDGET, INPUT, CHINA, 1050

      Below records will not be selected even though P_ACCT is CE0001000
      because its value is less than 1000
      or Datasrc is not INPUT
      or it is not the child member of AAPJ (Asia Pacific)
 
      CE0001000, 2011.JAN, ACTUAL, ADJ  , CHINA , 3000  (datasrc is not input)
      CE0001000, 2011.FEB, BUDGET, ADJ  , KOREA ,  345  (datasrc is not input)
      CE0001000, 2011.FEB, BUDGET, INPUT, TURKEY, 1999  (Turkey is not child member of AAPJ)
      CE0001000, 2011.FEB, BUDGET, INPUT, JAPAN ,  450  (Value is less than 1000)

      Here are some important Notes for using this command.

        
      Note 1: This command works only for BPC MS.
      Note 2: if you don't specify each dimension's scope, it will be performed in the

           corresponding members of the pre-selected region which is defined with 

           DIMMEMBERSET of previous line or Passed by Data Manager.

      Note 3: This command will generate MDX statement so it takes more time to execute.
                 if your dataset has only base members, you can use  

                      *XDIM_GETINPUTSET. (please refer help file)

3. When user wants to add more members on top of current scoped data.

 
      Let's say a user wants to add USASales entity on top of predefined memberset.
      In that case user defines as below.

      *XDIM_ADDMEMBERSET Entity = USASales

      The main reason why we need this is
          a. Sometimes XDIMMEMBERSET doesn't work with some specific functions like BAS(parent).
             For example, IN BPC NW,  *XDIM_MEMBERSET = BAS(US),CANADA will not work.
             Therefore, we should use *XDIM_MEMBERSET and *XDIM_ADDMEMBERSET.

             *Note: In BPC MS, BAS() will not work with XDIM_MEMBERSET.

          b. if user always wants to run a specific memberset whenever logic runs,

              should use *XDIM_ADDMEMBERSET
 
  

4. Dynamic Scope and saving it to a variable.

      Sometimes we need to save our scoped data into a script logic variable.
      But... what if your dimension members are updated frequently?
      As I know, almost every customer updates their dimension at least once a month.

      If customer changes their dimension members, what will happen in your script logic?
      You can use *Filter but sometimes it may not work all the time.

      Then we can use *SELECT and *MEMBERSET command as a dynamic scope tool. 

      Like other script engine, Logic script also supports Variable to save some data.
      The Variable is defined using % symbol.Here are some examples, %MYTIME% , %CUR% etc.

      So how can we save some data into the variable and when it can be used?

      Usually, the variable can be filled using *SELECT command and *MEMBERSET command.
      Both of them is scope command but *SELECT will be faster because it will create SQL statement.
     
      Here is the grammar of both commands.
          *SELECT ({variable}, {What}, {From}, {Where})
          *MEMBERSET({variable}, {member set in MDX format})

      Let's see how to use *SELECT command.

           *SELECT(%REPORTING_CURRENCIES%, “ID”, “CURRENCY”, “[GROUP] = 'REP'”)

      This command will get the 'member ID(what)' from the 'currency dimension(From)' that the

      GROUP property has the value 'REP' (where).

      Actually, it will create a SQL statement as below

           SELECT ID from mbrCurrency where [GROUP] = 'REP'

      After it executes above SQL command, all result will be saved into

      %REPORTING_CURRENCIES% variable.

      Here is an example of *MEMBERSET which will make same result but execute MDX statement

      instead of SQL.
         
           *MEMBERSET(%REPORTING_CURRENCIES%, “filter{[CURRENCY].members,

           [currency].properties(“GROUP”)=”REP””)   
 
      
      The variable can be used anywhere in the logic, like in this example:
 
                        *XDIM_MEMBER_SET CURRENCY=%REPORTING_CURRENCIES%

      Let's assume Currency dimension has below members.
 
          ID           GROUP

         USD            REP
         EUR            REP
         KRW           
         JPY
       

      Then above statement will be converted as
                      
                        *XDIM_MEMBER_SET CURRENCY = USD,EUR   

      When you define and fill in data using *SELECT and *MEMBERSET,

      please remember this as 'MEMBERSET Variable'

     
      Note: MEMBERSET command is only supported by MS version.

We reviewed key command of scoping today.

We will review advanced calculation command and control command like *IF or *FOR - *NEXT in the next post.

If you have questions or want to know something about it, please leave a comment.

Thank you.

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.

Understanding 3 logic parts.

   We can say that BPC logic consiss of 3 parts.  Scoping, calculation (create/Record) and Writing.

   A. 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. 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 it from a big database?

    The answer is....  *XDIM_MEMBERSET

    (Of course there is more advanced scoping command but we will discuss it in next post.)

    *XDIM_MEMBERSET is using for scoping data by each dimension.

   Here is the grammar of XDIM_MEMBERSET. 

    *XDIM_MEMBERSET <DIMENSIONNAME> = <MEMBERNAME 1>,<MEMBERNAME2>

     ...<MEMBERNAME n>

   Let's scope above example.

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

   B. calculation

       We just finished scoping so it is time to calculate (create) data.

       BASIC statement for calculate (create/Record) is *REC.  (It 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="DISCOUNTED_EXTSALES")

     It means multiply by 0.9 to current scoped a record and create a record

     after replace account member with DISCOUNTED_EXTSALES

     Here is an example what will happen with above statement.   

    <Scoped record>

         EXTSALES, 2011.JAN, ACTUAL, 10000

    <Generated record>

         DISCOUNTED_EXTSALES, 2011.JAN, ACTUAL, 9000

   What if you want to put a generated record into BUDGET?

    Then statement should be

          *REC (FACTOR = 0.9, ACCOUNT="DISCOUNTED_EXTSALES", CATEGORY="BUDGET")

   and records will be

    <Scoped record>

         EXTSALES, 2011.JAN, ACTUAL, 10000

    <Generated record>

         DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, 9000

    OK, 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="DISCOUNTED_EXTSALES", CATEGORY="ACTUAL")

         *REC (FACTOR = 0.8, ACCOUNT="DISCOUNTED_EXTSALES", CATEGORY="FORECAST")

    <Scoped record>

         EXTSALES, 2011.JAN, ACTUAL, 10000

    <Generated record>

         DISCOUNTED_EXTSALES, 2011.JAN, BUDGET, 9000

         DISCOUNTED_EXTSALES, 2011.JAN, FORECAST, 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.

    C. Writing

        As a final step, we need to write data into database.

        Script command 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.

Best laid plans can come apart at the seams, and video shoots are no exception. I had lined up a "HANA at TechEd" preview video taping for JD-OD.com with two HANA rock stars (a technical term for guys I trust for their expertise and fairness, far better than "gurus"), but the Skype video session borked (borked being another highly technical term for miscellaneous Skype failures). Fortunately I had a decent backup plan with an audio dial in. It may be that it worked out for the best: what we got instead was not only good banter about HANA TechEd themes but an in-depth HANA skills discussion, something I for one have been looking for.

So what we have here is a 50 minute podcast to get you geared up for HANA at TechEd and beyond, with fellow SAP Mentors Vijay Vijayasankar and Harald Reiter. Make sure to stay tuned to the end for some joking around about "gurus" and why none of us wants to be one. This is Harald's first time on a JonERP podcast (I'm sure there will be more), and the skills discussion is inspired in part by Harald's excellent SCN blog post, "The SAP HANA skills trap."

A couple thoughts on the skills discussion: of particular interest were Harald and Vijay's comments about future roles for ABAP developers on HANA projects. There's no perfect answer to this question yet, but it's an important topic. I also really enjoyed honing in on the data modeling and SQL scripting skill set that is vital to these first wave HANA Proof of Concept (POC) projects these guys have been involved with.

Note that you can look up Vijay and Harald's SAP TechEd Vegas sessions in a couple of ways: 1. check out their speaker biographies, and also go through all the HANA sessions (keyword search: HANA)  to find the expert lounge and hands-on sessions they are involved with.

(If for any reason the player doesn't work, you can download the podcast using the "download media" link on the right hand side).



(Trouble downloading? if for some reason it's not playing in its entirety for you, check out the version on JonERP.com in the meantime. This podcast is also available on the JonERP iTunes feed.)

Ordinarily in include in-depth text highlights from the podcasts, but in the interests of time I am skipping those to get this out to you prior to the show. But I will include a few basic timestamps and topics below to give you a flavor in case you want to skim.

I. TechEd Preview, HANA News Stories Rehash, TechEd Burning HANA Questions (00:00 - 20:30)

II. HANA Skills discussion and breakdown (20:30 -  46:40)

(with some bonus discussion of BI 4.0 and HANA)

III. parting shots and jokin' around about gurus and "thought leaders" or lack thereof (46:40 -50:00)

Plenty of interesting questions came up during the podcast, including but not limited to: 

- Whether the HANA cloud is relevant to customers now

- The impact of BW running on HANA in future releases

- The relative importance of HANA apps and what we're expecting there

Skills: 

- Why data modeling skills are so important

- Whether outside HANA experts are needed on HANA projects

- Will ABAP developers have a role in the HANA world? 

Final note: usually I add music and some finishing touches to my podcasts. I'm skipping those this time in the interests of timely content. I think you'll find the discussion worthwhile.

Funny how things work. Technical frustration led to one of my favorite podcasts of the year. Thanks to Harald and Vijay for such a great discussion.

Useful podcast links: You can look up Vijay and Harald's SAP TechEd Vegas sessions in a couple of ways: 1. check out their speaker biographies, and also go through all the HANA sessions (keyword search: HANA)  to find the expert lounge and hands-on sessions the guys are involved with. Other links: SAP Mentor Initiative. You can also check out SAP's In-Memory Home Page on SCN. Vijay Vijayasankar has been blogging in detail on HANA. Also recommended: the HANA posts of fellow SAP Mentors Vitaily Rudnytskiy and John Appleby, who has blogged in detail on HANA on his Bluefin Solutions blog and also on his blog on SCN. Fellow SAP Mentor Dick Hirsch's forward-thinking SCN post on data markets in the HANA App Cloud also comes up in the podcast discussion.

Look forward to seeing many of you in Vegas! If you're looking for me there and can't find me, pinging me on Twitter might be best.