Decision Table on Table Type

By now you must have known three flavors to model decision table (a) Parameters (b) Database tables and (c) Modeler views. In this blog, I will explain you yet another flavor – Modeling decision tables using Table Types. The main driver for this flavor is to encourage the reuse of decision table where the content is fetched from different tables in different sources, bearing the same table structure. Like in different offline or online forms that are used for validation where vendor wants to capture the business rules as reusable structure that can be applied across industries without changing the rules. While traditional approaches deal with actual database content or logic in some way, the advent of using table type in decision table has opened gates for reusable table structure talking to the database content. This abstraction provides more flexibility to manage the data without contaminating the actual content.

Let us start modeling the decision table using table type and understand the relevance in bit more detail. For this I have considered the same scenario, of calculating or setting DISCOUNT to a particular ORDER of the electronic gadgets, based on the QUANITY, MANUFACTURER and MODEL of the PRODUCT. However, there is change – instead of ORDER and PRODUCT as two database tables, I would use single table type with all the required fields  from  the two database tables.

First we, create a Table Type as follows – 

     Figure 5.1 –  SQl Editor showing the successful creation of Table Type

Next, create a decision table and use Table Type columns to model the decision table as shown

     Figure 5.2 – Output view showing the columns of Table Type used as Conditions and Actions

Later we fill the decision table condition and action columns with values

     Figure 5.3 – Decision table with values

Finally, Save Validate and Activate the decision table. Your decision table is now ready to be consumed; but before we see the consumption and analyze the result, there are few most important things to notice –

• As the modeling flavor is Table Type, you cannot directly access their fields in procedure as explained in another blogs
• Columns of table type can be used only as conditions
• You need to have an actual database table that have the same set of columns of the table type.
• At time of execution of decision table, you have to pass the actual table as input parameter. This input parameter decides on which data the decision table would be evaluated.

Let us execute the decision table procedure, and learn more about it. As I explained before we are taking fields from two different tables to construct the table type, we now have to have one or more tables that has all the columns of the table type. Assume you have created different tables based on REGIONs and there may/maynot have additional fields specific to a region.Like you might have tables like ORDER_INDIA for Orders pertaining to India, ORDER_GERMANY for for Orders pertaining to Germany, ORDER_US for Orders pertaining to US locations etc. If not already available, you can also create the tables as

CREATE TABLE “SALES”.“ORDER_INDIA” AS (

SELECT “SALES”.“ORDER”.“ID”, “QUANTITY”, “DISCOUNT”, “PRICE”,“MANUFACTURER”, “MODEL”, “REGION” 

FROM “SALES”.“ORDER”, “SALES”.“PRODUCT”

where “SALES”.“ORDER”.“PRODUCT_ID” = “SALES”.“PRODUCT”.“ID”

AND “SALES”.“ORDER”.“REGION” = ‘India’)

This table also has data from the two ORDER and PRODUCT table filtered as per the constraint      

     Figure 5.4 – Content of ORDER_INDIA table created on top of ORDER and PRODUCT table for India regions

Now, we execute the decision table as follows –

CALL “_SYS_BIC“.”<package-name>/<decision-table-name>”(‘SALES.ORDER_INDIA’,?)

You will see that the GET_DISCOUNT column is updated with the discount after decision table evaluation

     Figure 5.5 – ORDER_INDIA table content showing DISCOUNT parameter updated after decision table execution

Now, I will show you the advantage of decision table on Table Types

You can again call the same decision table on a different table let say SALES.ORDER_GERMANY and you see different set –

CALL “_SYS_BIC“.”<package-name>/<decision-table-name>”(‘SALES.ORDER_GERMANY’,?)

     Figure 5.5 – ORDER_GERMANY table content showing DISCOUNT parameter updated after decision table execution

So you saw that you can execute the same decision table for different set of database tables without any affect to actual database tables. This strikes-off the need to create several decision tables for each of the database tables. However, the only operation you can perform is with such decision table is – selection of the data – that you can use for simulation or to insert into another database tables etc.

For more refer http://help.sap.com/hana_platform > HANA Developers Guide > Setting up the Analytical Model > Creating Decision Tables

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 HANA Modeler Views