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
No comments:
Post a Comment