Clariba website

View Original

Decision tables - Bringing SAP HANA power to the business

Since release of SAP HANA more and more calculations and rules are pushed into database layer to fully gain all performance benefits that SAP HANA is providing. As every other database, implementation of these rules is highly dependent on database developers.

What SAP HANA from SP05 is providing is rule engine that is easily understandable by business users and gives flexibility of implementing rules in business layer.

Implementation of rule engine is done through decision tables which don’t require from business users to have advanced knowledge of SQL or any other database procedural languages.

Application of these rules is truly large. Once that rules are accessible they can be used for multiple purposes from operational business rules like pricing rules, claims, data cleaning to different types of what-if analysis etc..

Example of decision table with rules defined in Excel 2010:

excell rules

On example that we will go through we want to make analysis of what is the impact on transactional fees if we change model on how are the fees applied.

Database model

data model

For showcase purposes database model has been really simplified and is containing following tables:

  • D_ACCOUNT – table with all the accounts including OWNER_ACCOUNT_NUMBER that is ordinal number of every account that customer has opened

  • D_CUSTOMER – table with all the customers including internal CUSTOMER_CLASS segmentation

  • D_FEE_RATE – table with all the rates and fee amounts that are applied on financial transactions

  • F_FINANCIAL_TRANSACTIONS – fact table with all the financial transactions

Goal of analysis

The goal of our analysis is to get info of what is going to be financial impact if we try to apply new package rates of fees on existing portfolio.

On new rates packages only 2 parameters will have impact:

  • OWNER_ACCOUNT_NUMBER – what is the ordinal number of particular account that is belonging to specific customer

  • CUSTOMER_CLASS – internal segmentation of customers

Implementation

          1.       Decision Table

For this purpose a new decision table will be created in our HANA environment that is going to hold rules of fee amounts imported from excel:

decision table

After the decision table is activated (and decision table does not update a data set) - internally SAP HANA creates a procedure and column view that can be used further inside other SAP HANA objects.

Decision table can be used inside other procedure or can be used inside a calculation view.

For this purpose we are going to show usage in calculation view.

2.       Calculation View

Inside calculation view we are going to join already existing analytical view that is containing fee amounts that have been already paid and we are going to compare them with new fees from decision tables.

calculation view join

Because of multiple parameters that are impacting the fee amount, we need to calculate a new fee amount inside a calculation view as a calculated column and apply parameters provided inside a decision table:

new fee calculation

After new calculation in applied we can instantly see what is the impact from change of fee amounts:

final result

Conclusion:

Decision tables are really useful way of implementing business rules without too much of effort from IT side. Once that decision tables are implemented all the power of SAP HANA can be used which is enabling users to run rules on massive amount of transactions without a need for reloading of ETL batches as in conventional RDBMS.