Lesson number-11: Complex rule writing utilizing Rule Expression Language's advanced functionalities

[former_member84744]

HANA Rules Framework own language - Rule Expression Language (REL) , is designed to support an easy and natural speaking (currently only in English) of simple rules. Nevertheless the language has some powerful capabilities that enable to write some more complex rules.

I would like to demonstrate in this lesson some of those capabilities:

 

Aggregation functions (with 'per' and 'where'):

 

The next aggregation functions are available in REL: 'highest' (max), 'lowest' (min), 'sum of', 'average of', 'number of' and 'count distinct of'.

Here are two examples of usage of these functions:

1. sum of Amount of all Orders of a Customer is greater than average of sum of Amount of all Orders of all Customers per ID of the Customer

- in this example I check whether the total amount of current customer's orders is greater than the average of total amounts of all customers' orders

2. sum of Amount of all Orders of a Customer is greater than average of sum of Amount of all Orders of all Customers where Country of the current Customer is equal to Country of the Customer per ID of the Customer

- in this example I check whether the total amount of current customer's orders is greater than the average total amount of all orders of customers from the same country as the current customer (i.e. the one on which this query is currently asked about).

Aggregation functions with sorting:

The above aggregation functions are available in REL with the next sorting operators: 'with the highest' / 'with the lowest' (for numbers), 'sorted from a to z by' / 'sorted from z to a by' (for strings) and  'with the most recent' / 'with the earliest' (for time/date/timestamp).

Here are some examples of usage of these functions with sorting:

1. sum of 10 Amount of all Orders of a Customer with the most recent Timestamp is greater than 1000

- in this example I check whether the total sum of (current) customer's 10 most recent (latest) orders is greater than 1000

2. average of 10 Amount of all Orders of a Customer with the highest Amount is greater than 100

- in this example I check whether the average amount of (current) customer's 10 highest (maximal) orders is greater than 100

3. sum of 10 Amount of all Orders of all Customers sorted from a to z by by Name of the Customer is greater than 1000

- in this example I check whether the total sum of first 10 (sorted from a to z) customers' orders is greater than 1000

Concatenate function:

When a condition of rule is met - it is quite common that the rule's creator would like to get a result which is partially dynamic (based on rule's determination) and partially static (predefined texts).

Here is such an example: "Hello, John Smith ! Your balance is 105 USD." (the underscored parts are dynamic)

It is very easy to achieve this with REL, by utilizing the "concatenate" function:

concatenate ('Hello, ', Name of the Customer, ' ', Family of the Customer, ' ! Your balance is ', Balance of The Customer, ' ', Currency of the Customer, '.')

Usage of aliases to simplify a complex condition:

When you have to create a condition that seems to be hard to understand by other users - you can use aliases to make it more readable.

For example - the next (quite cumbersome) condition:

sum of Amount of all Orders of a Customer where Timestamp is in the last 6 months / average of sum of all Orders of all Customers where Timestamp is in the last 6 months per ID of the Customer is equal or greater than Balance of the Customer  / average of Balance of all Customers

 

So this almost unreadable condition which checks whether customer spent ratio (compared to other customers) is equal or greater than the ratio of his balance (compared to other customers) - can be turned via usage of aliases into the next much more readable condition:

Relative_Expense_Ratio is equal or greater than Relative_Balance_Ratio

 

In order to do so - I have created the next 2 aliases:

1. Relative_Expense_Ratio for: sum of Amount of all Orders of a Customer where Timestamp is in the last 6 months / average of sum of all Orders of all Customers where Timestamp is in the last 6 months per ID of the Customer

2. Relative_Balance_Ratio  for: Balance of the Customer  / average of Balance of all Customers

 

Here is another example of simplifying a condition via alias:

sum of Amount of all Orders of a Customer where Timestamp is in the last 6 months is greater than 1000 and

any of the following conditions is true:

• Country of the Customer is equal to 'US' and Age of the Customer is greater than 21


• Country of the Customer is equal to 'CA' and Age of the Customer is greater than 20


• Country of the Customer exists in ('DE','FR','UK','NL') and Age of the Customer is greater than 19


• Country of the Customer does not exists in ('DE','FR','UK','NL','CA','US') and Age of the Customer is greater than 18;

 

So the straight forward solution would be to save all the structured condition (any of the following... etc.) part as an alias (Validity_Check) - like this:

     sum of Amount of all Orders of a Customer where Timestamp is in the last 6 months is greater than 1000 and  Validity_Check

 

But a more robust solution (as it is much easier to maintain) would be to write the next decision table alias:

Usage of decision table aliases to simplify complex decision tables:

Naturally decision tables may become very complex. Imagine a decision table with dozens of columns and hundreds of lines - it is very hard to understand it and it is even harder to maintain it...

I will give you an example we had encountered in one of our projects. The customer had to re-categorize (hundreds of thousands of) master data objects. There were 5 main categories and each category had a set of 5 to 10 different condition columns and many dozens of lines with different values for the above condition columns. So instead of building one huge table with around 40 columns and hundreds of lines - we have built the next main decision table:

         

This decision table does the main categorization of the master data objects that need to be re-categorized. Each condition line refers in the output column to a decision table alias ('sub_table_one' to 'sub_table_five') that brings back the final new category. Here is an example to one of this aliases ( 'sub_table_one'):

    

So the bottom line is that there is one very clear main table that distributes the decision logic between 5 very clear sub-tables. Naturally such approach when used on the right context, can make your decision tables much simpler to be maintained and read by your end users... 

For more examples and more information -  please refer to SAP HANA Rules Framework - Rule Expression Language Guide.