Decision Table on HANA Database Tables
Just simulation to observe the trend does not suffice all the productive use cases. You may still want to manage the tremendous amount of data contained in tuples in SAP HANA – in real time, with significant turnaound time and basic knowledge on SQL Language. This blog introduces you to Decision Table modeling on single tuple or multiple tuples with joins.
In real world database, you would find data spanning across multiple tuples – more often we find the case that certain columns are predominantly used and remaining have low usage. In such cases, you use Data Foundation of Decision Table to model business rules on selected columns of database tables. Data Foundation handles the tuples efficiently by providing the options of joins and selection of a fixed set of prime columns on which the rules could be written and executed. Decision Table modeled on database tables have capabilities to update the content of the database.
Let us model business rules to calculate discount on an electronics gadget. To start with, first you have to create the decision table and construct Data Foundation. What you see here is snapshot of Data Foundation. Few things to mark –
- The tuples in HANA Studio could be seen in HANA Studio at: HANA System/Catalog/<your-schema>/Tables
- Two tuples SALES.ORDER and SALES.PRODUCT are joined together where SALES as schema and ORDER and PRODUCT are tuples in that schema
- Only selective columns from two tuples have been considered as Condition and Actions
Figure 3.1 – Data Foundation view where in left pane ORDER and PRODUCT tuples JOINed together and
right pane has the vocabulary for the decision table, together with columns selected as Conditions and Actions
Next you fill the Condition and Action Values in the Decision table
Figure 3.2 – Decision table with values
Finally – Save, Validate and Activate the decision table. Your decision table is now ready to be consumed ! Let us do a quick sneak-peek into the data content, of PRODUCT and ORDER tuples respectively, before the decision table is executed so that you are able to trace the difference. Note the DISCOUNT column – all the values are 0:
Figure 3.3 – Content of PRODUCT (top) and ORDER (bottom) tuples
Now, execute the decision table by making a call to the generated procedure and note the DISCOUNT column again. You see that the DISCOUNT column values in the database has changed as per the decision table evaluation.
Figure 3.4 – SQL Editor showing the call statement (top) executed to comsume decision table and the content of ORDER tuple (below) after consumption
You may be wondering – is it that simple ? And answer is Yes. The additional advantage lies in simplicity to design, no technical details and easy adaptation.
Let me summarize the steps again to model a decision table using database columns:
- Create Decision table in HANA Studio
- Drag and Drop database tables onto Data Foundation section
- Join the tables (if there are more than 1 table) using database join
- Add selective columns as Conditions and Actions of the decision table
- Set the values of Condition and Action
- Save, Validate and Activate
For more refer http://help.sap.com/hana_platform > HANA Developers Guide > Setting up the Analytical Model > Creating Decision Tables
Compared to use of Parameters, business rules modeled on decision tables – based on tuples – can update millions of records efficiently in quick real time and the impact can be directly accessed by the applications where decision have to made quick decisions – based on changing market.
In conclusion, it is important that you know your purpose for modeling business rules and consequently model the decision table based on Parameters and/or Tuples. The two approaches enables use of Decision Tables for analysis and/or actual data revisions and offers opportunities for completely new applications. Get started with decision table modeling and comsumption – and share with us your feedback and let us know if you need support.
Other Related Blogs
- Introduction – Big Data Decision Making using Decision Tables
- Decision Table in SAP HANA – Concept
- Getting Started – Model simple decision table,
Upcoming Blogs in Store