Understanding how-to use Table & its Functions in Business Rules
Business rules capability in SAP Workflow Management offers 3 kinds of data object (a) Element, (b) Structure and (c) Table. While element and structure are simpler to use and consume, I have often seen developers struggle to understand the Table data object and how it can be used while modelling decision table, and how are execution results evaluated.
Using Business rules you can model and manage decisions for cloud, S/4HANA and HANA runtimes from same interface. While S/4HANA and HANA based rules are modelled using Tables & HANA/CDS views imported from respective systems, there might be requirement to also build rules (based on table data object) natively in cloud as well.
In this blog, I will uncover these mysteries of Table data object for cloud runtime. You will learn when you should use table data object, how are table data object used while modelling decision table and text rule, what are some unique benefits and functions supported by Table data object, and finally how are the rules (based on tables) executed.
Before we start, first understand the three data object representation as shown below. Notice the difference between them. If you want to build rules based on table, then your data object must be transformed into the given array-like JSON.
Now let’s assume you have decided to use rules based-on tables for your use case.
1. Model Data Objects
First, start with creating the data object. Whenever you want to create a data object for table, you have to first create a structure which will act as a Table-Type (definition: A line type which is used to define structure attributes of a table)
For example, a line-item in the example table above will evaluate to a structure data object like shown below. In simple terms, each Employee in the table of Employees
Next, create a table data object based on the table-type structure. Notice the Type and Reference Data Object properties in the screenshot below. The structure Employee is now bounded with this Table Employees.
Note: It is highly recommended that you do not use the structure data object used for Table for any individual modelling purpose. Use it only within the scope of table. Reason is that during rule execution, this table-type structure will be used to store line-item value of each row of the table and if you have used it for independent evaluation then it might hinder with correct evaluation result.
Bringing it all together.
- You have created a table (with name: Employees) based on a table-type structure (with name: Employee). Now if you pass the table as the input data object to rule service, each row of the decision table will be evaluated based on the structure. That is how Table > Structure are related and it is important to understand this relationship which will ease your understanding of modelling and evaluation of rules based on tables.
2. Model Rules based on Table data object
You can use both decision table and text rule models to design rules based on tables, and leverage some really interesting functions and operators available only for Tables.
For more details on the functions and operators follow the link.
Thumb-rule : Use only table functions and operators while modelling rules based on the table data object.
CAUTION: Operators & Functions that work with structure will not work with tables. This is where most of the developers make mistake. For example, following is the wrong format.
- You cannot model rule directly on a Table data object with operators based on structure.
- You have to use table functions like Aggregate or Select to model the rules.
For example, the below rule will evaluate as :
If MAX(Employees) = 1000 then Approval is needed
note: Few aggregate functions only works on Number fields. Ensure that your table structure has some Number attribute against which you can run functions like MAX, SUM etc.
… another way is If COUNT(Employees) where JobTitle = ‘VP’ and Employee Is Fulltime then > 0 then approval is needed.
If you now think how my business users will understand and update these technical statements with Aggregate and Select functions?
- There is a solution for such scenarios as well – where you want your business users to edit the rules which are based on tables functions.
How it works: Create a vocabulary rule based on table-type. This will be rule on one single line-item. Iterate over all the line-items of the Table to run this vocabulary rule on each entry of the table, collect the result of the vocabulary rule in a structure/element/another table
- Model the decision table based on table-type, and use this rule as Vocabulary Rule (read my blog to know more about vocabulary rule)
- Create another text rule, and use combination of FOREACH, APPEND and UPDATE functions based on your requirements.
These functions are hidden gems of business rules and can be used as advanced expressions while writing complex rules.
- FOREACH will iterate over each row of the table and execute the statements.
- APPEND will add a new entry in the table where the new entry is the table-type structure.
- UPDATE will modify the element or structure value
In the below screenshot, see how the you can model rule in different ways. In route 2 below, each Employee of the Employees table will be evaluated for GetApproval vocabulary rule and the output from the rule will be stored in EquipmentApproval data object.
- Note: If you use element or structure as result data object, then only the result from the last row of the Employees table will be returned because EquipmentApproval is a structure/element and it will overwrite the result with each row execution.
In route 1 below, the filter is applied. The FOREACH function with “where” statement acts like a filter on the table. First the filter is applied on the table which means only those Employee of the Employees table will be selected whose country is “IND” or “GER” and then the UPDATE statement is executed for each of the selected Employee
Now, let us say you want to collect all the results for every row execution and then want to operate on them.
Thumb-rule 2 : Whenever you need to collect multiple rule execution results for same rule, you have to use Table data object as result.
In the example below, I have created a Table data object named EquipmentApproval based on EquipmentApproval structure data object.
notice that I first collect the response of GetApproval rule in the structure (which acts as a table-type for the response table) and then use APPEND function to add the EquipmentApproval response to the EquipmentApproval table.
So in this case execution result for each line-item of the input table is stored in the output table.
Isn’t that amazing and powerful as how you can write expressions on these advanced functions to achieve complex operations on the table.
- Now with the output result table (like EquipmentApprovals), you can further apply COUNT, MAX or any such aggregate functions to further analyse the result.
You can give the Vocabulary rule to business users to modify, and the text rule binds the item-level execution to the table so that the business users are not exposed to the technical expressions.
Note: Table data object can be used as Input and Output of the rule. Above explanation is for Input evaluation which means IF-expressions. Table is used as Output when you have Hit Policy as All-Match where you want to have multiple row evaluation as Output.
In this example, rule engine will scan all the rows of the decision table, and will return a List of Equipment for all those rows which get satisfied with the input payload.
3. Create Ruleset
As a final assembly, create a ruleset with Orchestration policy and add the text rule to it.
4. Create RuleService
The last step is to create the rule service and deploy it.
In the below example, I have used EquipmentApprovals (which is a table) as the result data object which means I want to iterate through all the line-items of Employees table and collect the response of each line item execution in the table.
You can also use EquipmentApproval structure as result data object and then you have to model your rules accordingly as explained earlier.
5. Run the Business Rules
Let us run these different rule combinations with different input payload and study the result
Result 1: Employees from input payload are filtered for the ones where the country is IND or GER (explanation: two rows will be available).
Then for each line-item the vocabulary rule is run and the result is collected in EquipmentApprovals table (explanation: two rows are returned as output with No approval as country does not match with the first 2 rows decision table and the last default row is returned)
Result 2: If I change the company in input-payload to SAP, then the result with two output is returned – but now one result entry is with Approval Needed and another with No Approval.
The first No result-entry is for a successful match with the second row in the decision table (with GER as country) and the Yes result-entry is for the successful match with first row in the decision table (with IND as country)
Result 3: If the decision table is directly modelled on the table functions and you want to collect only a single response in a structure/element. In such cases, either your business users are OK to edit the technical expressions like Select, Count etc. or these rules are never not meant to be changed by business users and are only modified by the IT team of your company.
The rules are executed as :
- First, filter rows from the Employees table where country of the employee is either ‘IND’ or ‘GER’ and company is ‘SAP’
- If the count of the rows returned is > 0 then Approval is needed (which means there are entries in the table which satisfied the filter condition)
These are the scenarios where you are not interested in the item-level execution result but want a collective output after execution of all the rows of the decision table.
With this you are now fully trained on Table data object, and can try using them in your projects. It is one of the well-built feature of the Business Rules which is not consumed to its full power.
Hope this blog gives you enough information and ideas on how to use Table data object and its unique functions to fulfil your complex requirements with business rules.