Implementation of Decision Tables with Return Values in SAP HANA

Introduction to Decision Table

Decision table creates the business rules in a tabular format for automating the business decisions. In this blog, I would like to show a step by step process of creating a simple decision table on HANA and how to execute the same.

Prerequisite for creating a Decision Table

We can create a decision table in HANA based on different sources:

• Multiple tables or Table type
• Information view (Attribute or Analytic or Calculation view)

Let’s take a scenario where business wants to see how the revenue will be affected if they give discounts on specific product groups depending on the quantity bought by customers. We will be implementing this scenario in the below steps.

In this blog, we will be creating a decision table in HANA based on the table.The process of creation of Decision tables with “update values” and Decision tables with “return values” are almost same except that we use columns from the physical tables as actions in first case while parameters will be used as actions in the latter one.

Now, there is a transaction table called SALES1. It is storing the data for sales for different products IDs and their quantity and amount along with the sale date.

Steps for creating a Decision Table in HANA

1. Let us start by creating a “Decision Table” in HANA Modeler

Right click on the specific package under which you want to create the “Decision Table” and select  “New” -> “Decision Table”

Provide the initial details (Name and Description) and click on finish. Let call it “DT_SALES_AMOUNT”

2. Click on the + icon on the data foundation and add the “SALES1” table

3. Select PRODUCT_ ID and AMMOUNT columns from the table.

4. Select PRODUCT_ ID under the “Attributes” and Right click and select “Add as Conditions”. By this we are this we are marking the product IDs from the SALES1 table.

5. Now, create a new parameter by right-clicking on the Parameters and click “New…”

Provide the following details like “Name”, “Description” and “Data Type” and click on OK

6. Right click on the newly created parameter “NEW_AMMOUNT” and select “Add as Actions”

This will create a new “Action” for the parameter

7. Now, click on “Decision Table” node and the following scenario appears in the “Details” section

8. Right click on PRODUCT_ ID and click on “Add Condition Values”. By doing this we are adding the specific product ID in which business wants to the new amounts.

Click on the icon to open the list of values window as shown below

Select a specific PRODUCT_ID (e.g. 1) from the list of values and click on OK

After selecting the value from the list, select OK again

9. Now, right click on “NEW_AMMOUNT” in the Details panel and click “Set Dynamic Value”. This is the part where we set the variance (1.5 times of the original value) which is desired by the business.

10. Enter “AMMOUNT” * 1.5 in the text box and then press ‘alt + enter’ to set. This is the step where we are actually setting the

After, it will look like this:

11. Now, again right click on PRODUCT_ ID and add another condition value by selecting another product ID (2) from the list of values and repeat the process again. The “Dynamic Value” (variance) will get repeated automatically by default for the other “PRODUCT_ID” as well. We can change as per business requirement.

12. Save and validate and save and activate the decision table.

13. Once the decision table is activated successfully, then go under “Catalog” and expand the “SYS_BIC” schema. In the “SYS_BIC” schema, go under “Procedures” and look for the newly created decision table.

Double click on the “DT_SALES_AMMOUNT” and we can check the underneath stored procedure codes

Decision Table Execution Process

• Open the SQL console in HANA studio by right-clicking over the system.
• The type call “_SYS_BIC”.”<package_name>/DT_SALES_AMMOUNT” (?)
• Click on Execute

Once the SQL code is successfully executed, we can see that the values in “AMMOUNT” column have increased by 1.5 times the original amount  for the product ID 1 and 2

Conclusion

By this exercise, we have implemented the business logic where business is seeing the AMOUNT is getting changed by 1.5 times for the specific product ID groups (1 and 2) which was bought by the customers. We can add more conditions as well (e.g. QUANTITY) based on the business requirement.