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-
- Select the decision table node in Output view
- In the Properties view, change the value of Mutually Exclusive
- 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 1st 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 1st 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 * 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,
- Open the decision table editor
- Go to decision table tab
- Select the cell, and right click
- 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
- Select Set Dynamic Value option in the context menu
- 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 - Enter ALT+ENTER to set the expression of ESC to cancel
- 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
Very Informative !!
Nice blog
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
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
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
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.
Hello Jon,
You can find the syntax and expressions for dynamic values in SAP HANA Developer’s guide.
Regards,
Archana
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.”
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
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.
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
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
Hello Naresh,
You mean Calculated Attribute and not Calculated Column ? What is the return type of the Calculated Attribute that you created ?
Yes, I mean calculated attribute and return type is Integer
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?
Great blog! Thanks!
Can we have IF/Case Conditions in the Expression of Calculated Column or in the Actions Parameters ?
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
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.
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
Hi,
For a simple decision table I am receiving the following error:
Internal deployment of column view failed;Repository: Encountered an error in repository runtime extension;Internal Error:Central table does not exist in column store: $varSchema$:$varTableName$.
Here’s my design: