Decision table modeling techniques discussed so far, in other blogs, was concentrated on setting up of data foundation using various available options in HANA Studio. In this blog, I will introduce you to advanced features in decision table that would help you to restructure your results in a better way.  All the features are available since  HANA SP06 release

Mutually Exclusive

This property of decision table controls the result set by stopping the execution once one or more conditions of any first logical row is satisfied. By default, this property is “true”. Users can avail the benefit in terms of performance and can control the output if there are multiple matches for a particular set of conditions. It is mostly advantageous when the wildcard character or regex expressions are used while defining the decision table.

Here are the steps to alter this property-

  1. Select the decision table node in Output view
  2. In the Properties view, change the value of Mutually Exclusive
  3. Save and Activate

/wp-content/uploads/2013/10/6_1_294897.png

          Figure 6.1 – Property view of decision table showing Mutually Exclusive property

Now, I would take you through the execution details to make you understand how this property works at runtime. For this, let us assume we already have a decision table modeled on database tables ORDER and PRODUCT. Here I will try to give different discount to the products, of a particular manufacturer, based on whether region is India or not –

/wp-content/uploads/2013/10/6_2_294898.png

     Figure 6.2 – Decision table where Handset and Note product of Auzuro manufacturer satisfies two rows

Let us first see what happens if the Mutually Exclusive property is set as TRUE. As a practice let us first see the content of the ORDER table. Mark the column DISCOUNT which has 0 values. After evaluation you see that DISCOUNT column is filled will values based on decision table evaluation. Notice that all the REGIONS with Auzuro product that are not India are set to 0

/wp-content/uploads/2013/10/6_3_294924.png

     Figure 6.3 – Content of ORDER table (top) showing discount column before and after execution when mutual exlusive property = true

 

To explain this, let us assume the input as (Auzuro, Handset, US, 2). Here is discount set is as 0. This is because the execution did not jump to another matching condition value of second column (i.e. *  ) once the conditions were not satisfied with 1st condition value of second column (i.e. Handset)

/wp-content/uploads/2013/10/6_4_294925.png

     Figure 6.4 – Decision table evaluation flow showing that the evaluation stops after Region as the condition is not matched

Now let us set Mutually Exclusive property to FALSE and see what happens.
Here you the see that the DISCOUNT column is set to as 5 for all the Auzuro product not sold in India.

/wp-content/uploads/2013/10/6_5_294926.png

     Figure 6.5 – Content of ORDER table (top) showing discount column before and after execution when mutual exlusive property = false

This is because the decision table is evaluated for all the models of Auzuro when the REGION does not match for any logical row. Let us again assume the input as (Auzuro, Handset, US, 2) and try an understand what happened.  In this case, the execution jumped to another matching condition value of second column (i.e. *  ) once the conditions were not satisfied with 1st condition value of second column (i.e. Handset) and therefore the discount is set as 5

/wp-content/uploads/2013/10/6_6_294927.png

     Figure 6.6 – Decision table evaluation flow showing that the discount is set to as 5

Let us now summarize what happened. For Mutually Exclusive = True you saw that DISCOUNT is set to 0 because decision table evaluation did not proceed as one or more condition cell matched in the same logical row like Auzuro and Handset matched, so the decision table evaluation was limited to Region and Quantity column of those logical rows. However, when Mutually Exclusive = False, the DISCOUNT is set to 5 because the decision table is evaluated for all the logical rows of Auzuro.

As the change in property causes a change in output and also affects the execution time ( if the data or decision table is large), it is recommended that you use this property only when you are very sure about the output you want and there is no other efficient way you could achieve this output.

For more information refer HANA Developer Guide:
http://help.sap.com/hana_platform > HANA Developers Guide > Setting up the Analytical Model > Creating Decision Tables

Calculated Attribute

This is another strong feature in decision table, where an attribute based on complex calculation on the columns of the data foundation can be used to describe the business rules. There would be times, where you would not want to evaluate decision table based on static values, instead would want some complex logic. This logic or calculation you want on top of columns of database table/table type/Modeler views, which are added as part of data foundation. Like for example, (a) rule that decides whether a loan has to be sanctioned to applicant based on the EMI (which is a calculation on top of current asset and loan amount),  etc.

Calculated attribute in principle have a unique Name, Data type and Expression. The data type are SQL data type and expression are combinations of operators and values. These values could be constant like 100, 200 etc or columns from the database tables, table type or modeler view added as part of data foundation. Multiple values are joined through mathematical operators. Bracket is also one of the operator to control the precedence of execution. Besides, there are also some predefined SQL functions like string, date etc. which can be used to model the expression.

Let us start with creating and using calculated attribute. To create a calculated attribute, you need create a decision table first and then go ahead to Output view to create the decision table as follows –

/wp-content/uploads/2013/10/6_7_294928.png

     Figure 6.7 – Calculated attribute being created from Output view

Next use this calculated attribute as decision table condition and set values

/wp-content/uploads/2013/10/6_8_294932.png

     Figure 6.8 – Decision Table with Calculated Attribute (TOTAL_PRICE)

Finally, Save Validate and Activate and execute them to see the results –

call “_SYS_BIC”.“<package-name>/<decision-table-name>”;

/wp-content/uploads/2013/10/6_9_294933.png

     Figure 6.9 – Content of the ORDER table showing discount column before and after decision table evaluation

    

So you see that decision table is evaluated based on Calculated Attribute. Discount is set after calculation is made for Total Price = DISCOUNT * PRICE.  Thus, you saw that you can use calculations as basis of your business rules, in easy to consume steps.

For more information refer “Using Calculated Attribute in Decision Table” in HANA Developer Guide:
http://help.sap.com/hana_platform > HANA Developers Guide > Setting up the Analytical Model > Creating Decision Tables

Expression in Condition and Action values

This feature enables you to use expressions – simple or complex as your condition and action values. Expression in principle has mathematical operators and constant value like 10, 300, JOHN, INDIA etc based on the data type of the column or dynamic value, which is another column or parameter or calculated attributes of the same type. For Example – Simple expression consist of only constant value and/or operator like 100 +4000 or 30000 etc. Dynamic expression is mixture of constant and dynamic values joined with mathematical operators like (QUANTITY * PRICE) /100 where QUANTITY and PRICE and column of table and / is symbol representing division mathematical operator and 100 is the constant.

Another important fact is that expression evaluation is based on precedence, you can control this precedence by introducing bracket like ((10 + 2) * 8). You would find different results with (10 + 2 * 8) = 26 and ((10 + 2) * 8) = 96. Brackets are important to alter the output and the control the way an expression is evaluated.

Let me guide you through the steps to set the expression of condition or action. Assuming that you already have a decision table, let me set an expression to the condition value cell. To do so,

  1. Open the decision table editor
  2. Go to decision table tab
  3. Select the cell, and right click
    1. Select Set Dynamic Value option in the context menu
      /wp-content/uploads/2013/10/6_10_294934.png
      Figure 6.10 Set Dynamic Value option on decision table cell which is used to set expression
  4. In the inplace popup, write the expression of your choice. To set the constant value or mathematical operator or bracket, you need to type the value or operator or bracket from your keyboard and to select the dynamic value you need to type and the dropdown appears with all the available options. The dropdown list is already filtered based on the data type the column.
    /wp-content/uploads/2013/10/6_11_294935.png
    Figure 6.11 – Popup showing the setting up of complex expression to condition value cell
  5. Enter ALT+ENTER to set the expression of ESC to cancel
  6. Save, Validate and Activate
    /wp-content/uploads/2013/10/6_12_294936.png

       Figure 6.12 – Decision Table condition cell shown with complex expression

For more information refer HANA Developer Guide:
http://help.sap.com/hana_platform > HANA Developers Guide > Setting up the Analytical Model > Creating Decision Tables

The expression in condition and action is different from calculated attribute in the sense that expression can be set to a cell in decision table and it has to abide with the data type of the column to which it set. This means that a column for INTEGER, you cannot use value of data type VARCHAR or DATE etc. However, in calculated attribute you have greater control over the data type and the expression can be made richer with functions like date, string etc. Also the calculated attribute expression applies to all the rows of the column and not on the selective row.

With these 3 advanced properties, decision table is characterized with increase adaptability and improved modeling capabilities; covering greater grounds. Use them to serve your purpose better. Use them in decision table of your choice and share with the feedback, if any.

Other Related Blogs

To report this post you need to login first.

20 Comments

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

  1. Yogananda Karra

    Hi Archana,

    I’m not able to use an expression for a Dynamic value for the condition Quantity. I get an error which says, “Invalid Data Expected Type: DECIMAL”. The data type for Quantity in my Data Foundation table is DECIMAL.

    Could you please explain how to set a dynamic value using expression?

    Thanks,

    Yoga

    (0) 
    1. Archana Shukla Post author

      Hello Yoga,

      Which version of HANA Studio you are using ? In SP07 Revision 70 there is an issue due to which a DECIMAL condition value has problems when setting in Dynamic Value. This issue is fixed in SP07 Revision 71. Use SP06 or SP07 Revision 71

      Regards,

      Archana

      (0) 
      1. Yogananda Karra

        Hi Archana,

        Thank you and sorry for the late response. I’m using SP07 Revision 70.

        But, it is working now.

        I just recreated the decision table but this time I made sure the data types match for the columns that are being as conditions and as dynamic values.

        Thanks,

        Yoga

        (0) 
  2. Jon-Paul Boyd

    Hi Archana,

    Another nice blog on decision tables. 

    Would you be aware of a complete list of valid syntax/expressions/predicates for dynamic values?

    Thanks.

    (0) 
      1. Jon-Paul Boyd

        The developers guide was my starting point – we get a whole 1 line, point C below, taken from page 343, hence querying if a complete list was available – are you aware of more than this?

        A little more in terms of full syntax/expressions allowed, with examples, would be welcome, as would a more sophisticated editor, along the lines of the expression editor for calculated columns.

        “Add a Complex Expression If you want to write a complex expression as an action or condition value, do the following:

        a. Right-click the action field.

        b. From the context menu, choose Set Dynamic Value.

        c. Write the expression, for example, PRICE-(PRICE*0.1).

        d. To edit a value, you need to select that value.”

        (0) 
        1. Archana Shukla Post author

          Hello Jon,

          Point taken !

          Just to brief you here on Complex Expression fragments:

          1. Brackets (Open and Close)

          2. Arithmetic Operators (+, – , *, /)

          3. Static Value (1,2,67 etc)

          4. Dynamic Value (Attributes, Parameters, Calculated Attributes of same data type)

          All the above can be used in different combinations to form the complex expression.

          For Example:  ((1 + “PRICE”) * 120)  OR  (“PRICE” + “DISCOUNT”) etc.

          Cheers,

          Archana

          (0) 
          1. Jon-Paul Boyd

            Thanks Archana.  In addition I had hoped there may have been some access to functions like abs and round within the decision table itself (thus negating an additional calculation view layer on top to do this).

            I have to assume not as I’ve not seen documentation nor coded examples.

            (0) 
            1. Archana Shukla Post author

              John, Currently there is a facility to use such Conversion Functions in Calculated Attribute and then use this calculated attribute in decision table dynamic expression. You will see abs and round under Mathematical Functions in Functions panel. 

              Cheers,

              Archana

              (0) 
  3. Naresh Setty

    Hi,

    After adding a calculated column and trying to activate, I receive the following error.

    XML Parser error: ; Decision Table XML Parser Error: attribute ‘alias:columntype’ of element ‘BaseAlias’ is missing

    Any thoughts would be appreciated. It worked fine with out calculated column, after adding it, the activation is failing.

    Thanks,

    Naresh G

    (0) 
    1. Archana Shukla Post author

      Hello Naresh,

      You mean Calculated Attribute and not Calculated Column ? What is the return type of the Calculated Attribute that you created ?

      (0) 
      1. Naresh Setty

        I think the issue is the date attribute. I have a submission_date column, the moment i add this column as Decision table attribute then I am getting the error.  If i remove this column, its working fine. I created this table using CDS and type of this date column is “Seconddate”

        Any ideas?

        (0) 
    1. Archana Shukla Post author

      No you cannot have IF-clause in the any expressions while modeling decision table.

      Decision table itself is like IF/ELSE statements. May be you would want to explain more what you want to achieve for more appropriate answer.

      Regards,

      Archana

      (0) 
  4. Nirav Patel

    Hi Archana,

    I have created a decision table with the following condition and action and also set the condition mutually exclusive = false

    Production Plant                Material type                Error

         1234                                   Z001                         1

    I have used this decision table view in my calculation view. Also, i have created two input parameters and my filter expression is as follows,

    (in (“Production_Plant”,$$PRODUCTION_PLANT$$)) AND (in (“Material_Type”,’$$mat_type$$’))

    My output is blank in-spite of matching data being available.

    When i use the logical OR operator, it is giving me the output by checking just one condition.

    I want to know where am i going wrong and how can i check multiple input parameters using the AND operator.

    Thanks in advance.

    (0) 
    1. Archana Shukla Post author

      Hello Nirav,
      Things look fine on first look, but I really need to see the project to suggest you the exact issue. You can however look for the runtime procedure of your rule and see if that helps.

      Regards,

      Archana

      (0) 

Leave a Reply