Database table is the most common way to model decision tables. Users are accustomed to column-based business rules which are simpler to create and easy to transform for consumption. Even though the database tables can be the first step to prepare decision tables, there are also users who are already familiar with HANA modeling views like Attribute View, Analytical View, Calculation View etc. and would want to design rules based on these views. In this section we target these users and explain how to model decision table using information views and its advantage.

Each SAP HANA modeling view is designed for specific purpose and is efficient when used in that context like Analytical View is best suited for analyzing – calculation and aggregation – of the data that is arranged in star-like-schema. This is done by different set engines in HANA like JOIN engine is used for Attribute view, Calculation Engine is used for Calculation views etc. These engines are optimized to produce best results in quick time.

To leverage the high performance capabilities of these engines, users may choose to model these views first and then use them to write business rules in form of decision tables. By default the Decision Table would be executed by Join Engine. If the decision table is designed on the modeler views, then it is executed by their respective engine that are already optimized to provide improved performance.

With assumption that you already know how to work with modeling view, let us start with designing decision table using modeling views. For this example, I have picked Analytic view which has an aggregation (SUM) to calculate the sales of a particular electronic gadget. This view we use in decision table to give DISCOUNT on the products.

First model analytic view using ORDER and PRODUCT database tables. Things to mark –

  • QUANTITY is aggregated as SUM
    Note the columns selected from the two tables to model Analytic view. These are regarded as Output of the Analytic view.

/wp-content/uploads/2013/10/4_1_294857.png

     Figure 4.1 – Overview of the Analytical View modeled on ORDER and PRODUCT tupes. This Analytical view will be later used to model decision table

Next, design decision tables on this Analytic view. Things to mark –

  • The Output of the Analytic view appear as options to model the decision table

/wp-content/uploads/2013/10/4_2_294858.png

     Figure 4.2 – Data Foundation with Analytical view and the Output view showing the Condition and Actions columns

Fill the decision table with values – 

/wp-content/uploads/2013/10/4_3_294859.png

     Figure 4.3 – Decision table with values

Finally, Save Validate and Activate the decision table. Your decision table is now ready for consumption. As already explain – call the generated procedure for the respective decision table. Few things to mark here are –

  • QUANTITY is aggregated as SUM in original Analytic view
  • When QUANTITY column is used in Decision Table – it is equivalent to  SUM of QUANTITY column
  • OLAP engine – which is meant to handle efficiently aggregation and other calculation based on start schema – is used when decision table is executed. In the first screenshot below you see some contents from ORDER table marked as RED – these are those Auzuroà Handset product which is mentioned in the decision table.
  • In the second screenshot below – you would notice that QUANTITY values – for each product -are summed from the database content like Auzuro àHandset (PRODUCT ID: SAM_1000123) has 5 entries with SUM(QUANTITY) = 18. Similarly Mattle àMobile (PRODUCT ID: APP_1000124) has 3 entries with SUM(QUANTITY) = 22 etc.
  • Finally you see DISCOUNT updated for only Auzuroà Handset product as per the decision table

/wp-content/uploads/2013/10/4_4_294863.png

     Figure 4.4 – Content of ORDER tuple (top) and SQL Editor with call statement (below) showing the decision table comsumption

As Parameter is used in the Action, the result set is a simulated result – and do not modify the DISCOUNT column values of the original database table i.e. ORDER. Let me finally summarize the steps again –

  1. Create one of the modeler view – Attribute, Analytical or Calculation view
  2. Create Decision table in HANA Studio
  3. Drag and Drop these view onto Data Foundation section
  4. Select Output of the modeler view as Conditions and Actions columns of the decision table
  5. Set the values of Condition and Action
  6. Save, Validate and Activate

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

Thus, decision table can be designed on modeler view and efficiently addresses the performance bottlenecks which might occur with use of database tables. However, an initial knowledge and recommendations on working with modeling views is required to completely utilize the specialization, parallelization and performance optimizer capabilities of these engines.

Other Related Blogs

    

Upcoming Blogs in Store

To report this post you need to login first.

10 Comments

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

  1. Krishna Porandla

    Hi Archana,

    As per my knowledge, one column view table will be created in _SYS_BIC schema for the decision table defined using parameter as output.

    Thanks,

    Krishna

    (0) 
  2. Govind Parmar

    Hi Archana,

    I have question regarding Decision table, can you advise if the following is possible using decision table:

    Decision table has Conditions (based on) Column ‘A’, ‘B’, ‘C’ and ‘D’  and has two Actions ‘E’ and ‘F’.

    Can the Action ‘E’ be based on Condition ‘A’ and ‘B’ and Action ‘F’ be based on Condition ‘C’ and ‘D’?

    Thanks

    Govind

    (0) 
    1. Archana Shukla Post author

      Hello Govind,
      Yes it is possible. Use wildcard * for the conditions where you do not want to set any value and ‘Set Initial Value’ for the Actions where you do not want to set any value. So in your use case the decision table would be like

      dt.PNG

      Hope this helps.

      regards,
      Archana

      (0) 
      1. Govind Parmar

        Thanks Archana for the reply.

        I have modeled by view using the decision table as you suggested but the decision stops when the first condition is met therefore gives results only for one Action as a time. For example if L1 and L2 is met on Action E is returned and Action F is not returned even if I have L4 and L5 in my table/view.

        I was looking to update both Action F and Action E when L1, L2, L3, L4 values are met in the table.

        Thanks

        Govind

        (0) 
          1. Govind Parmar

            Archana,

            That makes sense. Is the below scenario possible using decision table:

            Table has with one record with Action E(based on A, B)  shows 3050 and F (based on C,D) shows 1300

            Untitled.jpg

            (0) 
            1. Archana Shukla Post author

              Use Dynamic Value in the expression of the Action but you have to repeat that expression for each row in case you want to achieve the same result for each row.

              (0) 
  3. Nirav Patel

    Hi Archana,

    I have a question regarding Decision table, can you advise if the following is possible :


    I want to have a drop-down on conditions and allow the user to select the attributes on which they want to perform an action.

    For example, say i have a MARA table in the data foundation, i want the output  as shown below


    Field Name


    Here user can select any attribute from MARA table

    eg: Material_type

    Operator

     

          =

    Value

    FERT

    Action

    1

         Material_type       = HAWA   0


    The field name, operator and value should all together be 1 condition and then i want one action column.

    It would be helpful if you revert back.

    Thanks.

    (0) 
    1. Archana Shukla Post author

      Hello Nirav,
      You cannot introduce custom controls inside decision table. Instead try using expressions in conditions. See ‘Advanced Decision table Features’ blog of this series.You can create your control to take input from the user and design your decision table based on those inputs.

      Hope that helps.

      regards,

      Archana

      (0) 
  4. Nirav Patel

    Hi Archana,


    I have created a decision table with the following condition and action.

    eg:

    Production Plant                Material type                Error

         1234                                   Z001                         1


    I have used this decision table view in my calculation view. Also, i have created two input parameters and my filter expression is as follows,

    (in (“Production_Plant”,$$PRODUCTION_PLANT$$)) AND (in (“Material_Type”,’$$mat_type$$’))


    My required output should be 1 if both the  criteria matches with the user input.

    But, output is blank in-spite of matching data being available.

    When i use the logical OR operator, it is giving me the output by checking just one condition.

    I want to know where am i going wrong and how can i check multiple input parameters with different data-types using the AND operator.

    Let me know if you can help.

    Thanks in advance.

    (0) 

Leave a Reply