Decision Table in HANA! Why, When we need and How to Create.

[narasingha]

Decision tables are very important concepts in SAP HANA native modelling. Ideally when we are working with IBP and APO even ECC or S/4 HANA source systems in SAP HANA Native modelling then definitely one might had created and used decision tables.

Decision tables are kind of ETL rules applied on a Column. So it a table with ETL rules wrapped in ti with Conditions and Actions.

There are few information about Decision Table but we must know when we need it and if at all we need How to do it. Below blog post Cleary illustrates the creation and usage of DT in SAP HANA 2.0 SPS 04

Suppose Business want to plan discounts based on some products.

Before giving or declaring the discount the business want to analyze if we give the discounts will it give us profit or loss etc. Decision table helps in directly updating the value or it returns certain action on which business can decide on further decisions.

in such cases it ideal to create a decision table.

 

Decision tables are of 2 types

1) Decision Table Update Value.( When the user want to change and update the values in DB itself)

2) Decision table Return Value (If user want to add new field and change the values).

I had explained them in a Pictorial representations. Consider we are giving 10 % discounts to all of our customer then what exactly both the decision tables do to the data in a table in Database.

 

Now let us create a decision table. For Every decision table we need to have a condition and a action to the measure or the Key Figure KF.

I created two tables out of Sales Oder header and Item table as Below. HANA provides excellent feature like Teradata to create a table on the fly same as a base table. see Below : -

 

 

Now I will create a decision table as Below : -

 

 

 

The first time when you create a decision table it looks like as below : -

 

I am going to now add the ZVBAP_TEST table and I created that with the intention not to affect the original VBAP table. 🙂

I added the MATNR which is the material as the condition and NETWR to the action as in above snapshot.

 

 

 

 

Now I am applying the logic of the discounts as in below snapshot. We can also import the calculations from an excel file as well. Import from excel file I Will cover letter.

 

 

Save Validate and Activate the data. By Default Decisions tables are created under "_SYS_BIC" schema but we can create a decision table under any schema if we have right previlage enabled for the Decision Table Object or artifact.

 

 

we cannot see the data in a decision table directly in Data Preview just as we do with a table or a CV. Whenever we create a decision table a Stored procedure is also getting created under the "_SYS_BIC_" SCHEMA.

let us find the stored procedure and to the data in the DT we need to call the procedure. A stored procedure is just like a function module or a executable statement which can be used n number of times. we can call the procedure in scripted CVS. Point to note here is that we cannot call the SP inside a TF as Table functions are not allowed to modify database state, and stored procedures are allowed to modify database state. Therefore, it is not allowed to execute a stored procedure from within a function.

Now let us call the SP created for the Decision Table.

 

 

 

Once you call the SP you will see that data is getting updated to the base NETWR value is DB.

Here as I had created the decision table on ZVBAP_TEST the NETWR value is being updated to 10% or 15% or 20% for the material I choose for the table ZVBAP_TEST.

You can choose a base table too when we are creating a decision table. But ideally better not to choose the actual base table as the base table may be used for other modelling purposes. Create a copy of the base table and create decision table on top that. Depends on your business scenario.

Now data in S/4 HANA Source OLTP system.

 

Data in ZVBAP_TEST(10%DiscountAppliedfortheMaterial)

ZDEC_TEST decision table is created under the "_SYS_BIC" as a Column View which eventually is updating the ZVBAP_TEST via a stored procedure call.

We need to schedule the stored procedure based on the business requirements so that data is getting updated for the selected materials. Say every 1 hour or 2 hour etc.

We can also feed the decision table to a calculation view and also we can use inside a table function Node (HANA 2.0 SPS 04)

see below diagram

 

 

also we can return calculated attributes as below

 

 

These calculated attributes can only be added to a condition to a measure. Suppose we want to again apply some kind of filters say Material Like "M-100%" etc then we derive calculated attribute and apply the action on this condition on a different Measure.

We can also parameterize the decision table with an IP parameters which can further be passed to a CV or a TF.

 

 

This blog post will help you to create robust decision tables on Native HANA environments.

Thank You.

Narasingha Patro.