Wednesday, 19 December 2018

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.

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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”

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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.

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

This will create a new “Action” for the parameter

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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.

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

After selecting the value from the list, select OK again

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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.

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

After, it will look like this:

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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.

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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.

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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

SAP HANA Study Materials, SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

No comments:

Post a Comment