Skip to Content

Filters are often applied by the business users or rule designers to control the output based on the multiple parameters specific to any industry like filter customers that have a specific plan in telecom industry or filter deals based on autmobile manufacturer, model etc. or filtering customers that have specific policy in Life Insurance sector or filtering messages in your inbox. This blog will walk you through the process of defining rules and using them to filter the content based on specified action and/or input.

Facts

  • Decision Table directly does not support filtering of rules. It will be used in consonant with Calculation View to achieve filtering.
  • There could be several approach based on the requirements like performance, filtering to be done first and then the rules are to be executed or vice versa etc. In all the approach Calculation View has to be used to filter no-matter at which stage you choose to filter.
  • This solution could be applied since HANA release SP06

Usecase
There is an online company that offers discount coupons on car deals from various dealers based on the car model and manufacturer. A set of rules are run to decide the discount given by various dealers from different regions. Customers can use these discount coupons with the dealers when purchasing the car.

Solution
Here is a step-to-step guide that could be used to filter rules using decision table based on the usecase described above.  Explore this solution that has been divided into 3 sections (a) Data model (b) Decision Table model (c) Consumption model

Note: All the images are based on modeling done on HANA Studio SP07, but the same usecase could be designed
in same way in SP06 as
well.

(a) Data Model

I have created three database tables named CAR, DEALER and DISCOUNT_COUPON. CAR table contains all the metadata about the car, DEALER contains all the metadata about the dealers and their location and DISCOUNT_COUPON contains discount information that would later be suggested to the customers who are looking for best buy before purchasing the car.

Image1.jpg

(b) Decision Table Modeling

Data Foundation

  • Use the tables to create the data foundation of the decision table

Image2.jpg

  • Add the Attributes from the data foundation to create Condition and Actions of decision table

Image3.jpg

     Note:  Action is a Parameter – DISCOUNT, which is set to after the rules are executed.

Decision Table

  • Fill the decision table with Condition and Action values

        Image4.jpg

  • Finally, Save, Validate and Generate Decision Table

Note: This would generate the Result View that would be used in Calculation View.

For more details on modeling decision table refer my blog series

Calculation View

  • Use the result view of the decision table in Projection shown as Projection_1

        Note: You can find result view in “_SYS_BIC/<your-package>/<your-decision-table-name>_RV

  • In this Projection_1,  create Input Parameters and Filter.
    • Input parameter are the one the you want user to input like Model and Manufacturer – based on which the deals from various dealers would be suggested. 

          Image5.jpg

    • Filter is based on the Model and Manufacturer entered by the user as INPUT PARAMETERS

     

               Image6.jpg

  • Finally, Save , Validate and Generate Calculation View
  • Test
      • To test the outcome of the Calculation View, use Data Preview of Calculation View

        Image7.jpg

       Image9.jpg

(c)  Consumption Model

Calculation view can further consumed using OData service.

You can thus use decision table to control the items that are consumed in your application, and can bring in ability of controlled consumption to the database. Follow this blog to successfully create custom application in HANA especially where filtering-rules are needed. Do write in your suggestions and feedback. If you have any queries on filtering rules then drop me comments, I would be happy to help you !

To report this post you need to login first.

16 Comments

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

  1. Rohit Gupta

    Hi Archana,

    Thanks for the informative post.

    I have similar requirement, in which i have created one attribute view, i will use its result to populate BW info-object master data.

    Attribute view working is fine and i am able to get the result at BW master data.

    Issue is — I want to apply a filter over the result set, the filter value restriction will come from a customizing table. PLease help me here to achieve this.

    Example- I want to update the activity type master data to one my info-object say ‘ZRESOURCE’. So i have created one attribute view over table ‘CSLA’ . But i want to restrict the activity type based on the controlling area maintained in a customizing table.

    As this controlling area is not fixed, i want it to be filled at run time, and restrict the result set.

    Please suggest. Let me know if anything is not clear.

    Regards,

    Rohit

    (0) 
    1. Archana Shukla Post author

      Hello Rohit,

      I am assuming that you are talking of Attribute View of HANA Studio.

      Filters cannot be applied to Attribute View. To use filters you have to switch to Calculation View instead of Attribute view Or you can create a new Calculation View with Projection – use Attribute View result view in Projection and apply filter to it.

      Hope that helps !

      Cheers,

      Archana

      (0) 
      1. Rohit Gupta

        Hi Archana,

        Yes, i know that i can not apply filters on attribute view. As i explained my above scenario, I have one info-Object ‘ZRESOURCE’ whose master data will get populated using calculation view.

        I will use projections from my similar attribute views to above calculation view. So At this calculation view i want to apply this filter, the restriction that we are using for filter , it will come through one customizing table. So how to pass multiple values maintained in customizing to this calculation view.

        For Example-  I am fetching Activity Types maintained in CSLA table in an attribute view. I projected this into a calculation view, At this place i want two filters to be applied

        1. Controlling Area — 1000 and 2000

        2. Activity Unit — HR ,  DAY

        So please suggest how to pass these multiple values of filter restriction to restrict the final result set.

        I hope it is more clear now. Let me know if you still need any clarification.

        Regards,

        Rohit

        (0) 
        1. Archana Shukla Post author

          Hello Rohit,

          If I understand your problem correctly – You can define expressions in the Filter where you can use Controlling Area and Activity Unit attributes from the calculation view something like “(Controlling_Area = 1000 AND Controlling_Area = 2000) AND (ACTIVITY_UNIT = HR OR ACTIVITY_UNIT = DAY)”

          Regards,

          Archana

          (0) 
  2. Jon-Paul Boyd

    Hi Archana, many thanks for the blog.  I have a calculation view with input parameters, when executed “stand-alone” in data preview the user is prompted for input, fine.  A decision table consumes this same calculation view in the foundation and the user IS NOT prompted for input values.

    This leads me to your blog which proposes a workaround to provide filter constraint after decision table is applied to the complete foundation without restraint.   I am correct in recognizing the inability of decision tables to accept input parameters as placeholders which can be used to constrain the foundation?

    Such a shame, it could be that you only want decision based rules applied to 1 customer out of 1 million, a waste.

    (0) 
    1. Archana Shukla Post author

      Hello Jon,
      There are two points here. (1) Currently there is a restriction in decision table that you cannot use calculation view that has input parameters. Validation would fail here. This limitation has been considered and you’ll see it gone in future release. (2) This blog on filter rules in not a workaround for input parameters. As filters are common requirement while running rules, with current abilities of the decision table, this blog proposes a way in which filters can be applied. Yes, this might not be the ideal way as decision table rules are run before filters are applied.

      (0) 
      1. Jon-Paul Boyd

        Hi Archana, thanks for coming back to me. Regarding your points.

        1. Clarifying your “cannot use calculation views that has input parameters.  Validation would fail here” – you CAN consume calculation views having input parameters in decision tables, you set defaults against each parameter in the CV and the DT uses the defaults (does not propose selection in data preview).  Depending upon the requirement this can produce expected or unexpected results (you may want CV result set “fixed” with defaults).

        2. There would be less information model maintenance overhead if modellers had ability to define input parameters in the DT’s themselves and DT’s consumed data foundation model input parameters properly (map and data preview propose, not just fall back to parameter value defaults if defined). 

        As you say, “filters are a common requirement while running rules” and I look forward to proper integration of IPs in DTs.

        (0) 
        1. Archana Shukla Post author

          Hello Jon,

          This is not possible. Even if you assign default value to the parameters in Calculation View, when this calculation view is used in decision table then validation would fail stating “Modeler View used has Input Parameters. View with Input Parameters is not supported”

          We do not support Calculation View with Input Parameters. If you are bypassing the validation and activating the decision table – in that case decision table will always pick default value of the parameters. Any reasons to bypass validation ?

          Please completely share your scenario so that I can clearly understand what you are trying to do here and also which revision of HANA you are using.

          Regards,

          Archana

          (0) 
  3. Jon-Paul Boyd

    Hi Archana,

    AWS Hana Developer SPS07 70.

    1. Create simple table

    DROP TABLE CARS;

    CREATE COLUMN TABLE CARS

    (

           DEALER CHAR(10),

           MANUFACTURER char(10),

           MODEL        NVARCHAR(15),

           PRICE        DECIMAL(7,2),

           PRIMARY KEY (DEALER, MANUFACTURER, MODEL)

    );

    INSERT INTO CARS VALUES (‘PRESTIGE’,’AUDI’,’A3′,30000.00);

    INSERT INTO CARS VALUES (‘PRESTIGE’,’AUDI’,’A4′,40000.00);

    INSERT INTO CARS VALUES (‘PRESTIGE’,’AUDI’,’A5′,50000.00);

    INSERT INTO CARS VALUES (‘KINGS’,’AUDI’,’A3′,32000.00);

    INSERT INTO CARS VALUES (‘KINGS’,’AUDI’,’A4′,42000.00);

    INSERT INTO CARS VALUES (‘KINGS’,’VW’,’GOLF’,28000.00);

    INSERT INTO CARS VALUES (‘KINGS’,’VW’,’PASSAT’,30000.00);

    2. Create calculation view CV_CARS with CARS table as fact foundation

    Note input parameters, and default for IPS (DEALER=PRESTIGE, MANUFACTURER=AUDI, MODEL=A3).

    DT1.JPG

    3. Data preview with KINGS selected as dealer

    DT2.JPG

    4. Decision table DT_CARS consumes CV_CARS in foundation

    DT4.JPG

    DT5.JPG


    5. Validation of model in Studio successful. Activation is successful. 


    DT7.JPG



    6. No validation rules bypassed in preferences to force successful activation/activation despite input parameter existence.


    DT8.JPG



    7. Data Preview of of DT_CARS – defaults taken from input parameters of information model

    DT6.JPG

    My DT model successfully activates with a foundation containing input parameters but reverts to default on execution.  If modellers like myself do not get a validation error regarding input parameters from foundation they are likely going to be very surprised/confused when viewing the outcome of their decision table.

    That was my context in saying what you blogged here acted as a workaround for me (removed input parameters from CV, meaning the full CV result pushed through decision table, then created another CV with DT as foundation and created constraint filters on the projection).

    Thank-you again for your blog which provided a solution for me.

    (0) 
  4. Anil Kumar Yaralagadda

    Hi Shukla,

    Everytime we query the result veiw of decsion table its going to run the rules against all the records of the data foundation, Filter the result on our WHERE condition and give us the output.

    Is this the correct way ? Dont you think there might be performance implications when data is large in the foundation tables/views.

    What if we use Table type as data foundation to the Decision table and use SQL Calc view and use the DT procedure call inside it…

    (0) 
  5. Anindita Bhowmik

    Hi Shukla,

    Would it be possible to use the SAP Decision Table for the use case where we need to apply many ( active ) business rules one by one to a particular transactional  record and the decision table is  a plain lookup table and there is exists no join condition between the transaction table and the decision table?

    Regards…

    (0) 
    1. Archana Shukla Post author

      Hello Anindita,

      Decision Table described above are build on HANA Database and have Procedures as Runtime artifact. Applying rules One-by-One is not available out-of-box but you can surely write your own script to call the decision table (-runtime procedures-) as required.

      Regards,

      Archana

      (0) 

Leave a Reply