Advanced Decision Table Features in SAP HANA SP06

[Archana]

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

          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 -

     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

     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 1^st condition value of second column (i.e. Handset)

     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.

     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 1^st condition value of second column (i.e. Handset) and therefore the discount is set as 5

     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 –

     Figure 6.7 - Calculated attribute being created from Output view

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

     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>";

     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 * 😎 = 26 and ((10 + 2) * 😎 = 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
      
      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.
   
   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
   
   
      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

• Introduction - Big Data Decision Making using Decision Tables
• Decision Table in SAP HANA - Concept
• Getting Started - Model simple decision table
• Decision Table on HANA Database Tables
• Decision Table on Table Type
• What can you do with Decision Tables in SAP HANA SP05