Skip to Content

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 – 

/wp-content/uploads/2013/10/5_1_294864.png

     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

/wp-content/uploads/2013/10/5_2_294865.png

     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

/wp-content/uploads/2013/10/5_3_294893.png

     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      

/wp-content/uploads/2013/10/5_4_294894.png

     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/wp-content/uploads/2013/10/5_5_294895.png

     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’,?)

/wp-content/uploads/2013/10/5_6_294896.png

     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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Bharath Kumar Bachu

    Hi Archana,

    Thank you very much for providing such a useful information. I am using Table type to create a Decision table and have the following questions.

    1. Is there a way to use the decision table created using Table type in a Calculation view?

    2. Like in your example, I need to calculate agent points and department points and merge them into one table. so I am trying to join/union the 2 procedure calls in a calculation view but getting the below error.

    “Only table variable is allowed in input parameter in a nested call.”

    The following is the calc view code. Any help would be highly appreciated. Thanks in advance.

    /********* Begin Procedure Script ************/
    BEGIN
     
      t_agent_points = CE_CALC_VIEW (“_SYS_BIC”.”AgentScoreCard.MonthlyMeasure/CV_AGENT_MEASURES”,[EFFECTIVE_DATE,AGENT_REGION,AGENT_DEPARTMENT,SUBJECT,MEASURE,PERFORMANCE]);
      t_dept_points = CE_CALC_VIEW(“_SYS_BIC”.”AgentScoreCard.MonthlyMeasure/CV_DEPARTMENT_MEASURES”,[EFFECTIVE_DATE,AGENT_REGION,AGENT_DEPARTMENT,SUBJECT,MEASURE,PERFORMANCE]);
     
      CALL   “_SYS_BIC”.”AgentScoreCard.MonthlyMeasure/DT_SCALE_DATA” (:t_agent_points,it_agent_points);
     
      CALL   “_SYS_BIC”.”AgentScoreCard.MonthlyMeasure/DT_SCALE_DATA” (t_dept_points,it_dept_points);

    var_out = CE_JOIN (:it_agent_points,:it_dept_points,
         [EFFECTIVE_DATE,AGENT_REGION,AGENT_DEPARTMENT,MEASURE,PERFORMANCE,POINTS],
         [EFFECTIVE_DATE,AGENT_REGION,AGENT_DEPARTMENT,SUBJECT,MEASURE,PERFORMANCE,POINTS]
        
         );
     

    END /********* End Procedure Script ************/

    (0) 
    1. Archana Shukla Post author

      Hello Bharath,

      I am glad that my blog helped you in some manner. Here are the answers to your queries –

           1. Yes, decision table can be used in Calculation View. You can refer another blog of mine of how to use decision table in Calculation view – http://scn.sap.com/community/developer-center/hana/blog/2013/10/10/scripted-calculation-view-based-on-decision-table

          2. I need more information on your decision table to be able to help you. Send me more details on your usecase and I would be happy to help you !

      Cheers,

      Archana

      (0) 

Leave a Reply