Traditionally, Business Rule Engines do not communicate with databases directly and expect to receive input and provide output via intermediate objects defined in Java, JSON, or XML. However, our customers frequently prefer to use business-friendly rules defined in Excel instead of separately defined SQL statements. Our new product “Rule DB” does exactly this. In this post I will describe how it works using the MySQL Sample Database.

MySQL Sample Database

Let’s assume that you already installed a free MariaDB (or MySQL) and downloaded its Sample Database. We will consider simplex and more complex SQL queries applied to this database. Here is an ER-diagram of this database that will help to understand the SQL queries:

Here is an example of an SQL query that works with three tables “customers”, “orders”, and “payments”:

When we execute this SQL query, we receive the following results:

Note that the WHERE part of this query contains a special condition

If we remove it, the query will produce many records with NULL order’s status and payment’s amount. This knowledge along with two joins constitutes “technical logic” of the database organization. Alternatively, the condition

constitutes “business logic” that specifies a business criteria that, contrary to the technical part, could be frequently changed.

OpenRules Project “SampleDatabase”

Our objective is to migrate any SQL queries (much more complex than the above one) to business rules and give business people control over business logic. I will explain how to use the latest OpenRules with business rules capable to go directly to the database, select the records using a query criteria very similar to SQL, and then make certain decisions by analyzing the selected records. Let’s start with a simple example by migrating the above SQL query to OpenRules.

Our team created an OpenRules project “SampleDatabase” that is supposed to navigate through the database tables selecting certain records (similarly to the above SQL query) and then calculating the total number of the selected records and the total amount in all of them.

We created an OpenRules project “SampleDatabase” that is the standard project with one simple addition: the configuration file “pom.xml” includes an additional dependency

It will automatically add MariaDB’s JDBC driver to our project. To be able to access our database in run-time we need to specify its user and password. It can be done in the standard configuration file “project.properties”:

Migrating Simple SQL to OpenRules

First we will represent the above SQL query in OpenRules Excel tables. OpenRules 8.2.1 introduces a new table of the type “DataSQL” that allow a user to define technical parts of SQL queries independently of how complex they may be.

Here is the DataSQL table for the technical part of the above query:

We called this table “SelectedOrders”.

Now we will represent the business part of the same query in the regular decision table:

We want to execute these rules for all “SelectedRecords”, so we will call it for the table:

The column “ActionIterate” directs OpenRules engine to iterate over all records defined in the above table “SelectedRecords” and for each records execute the rules “DefineTotals”. To glue everything together, as usual we need to specify the Glossary:

This glossary for the business concept “SelectedOrders” specifies the business names of the attributes defined in our DataSQL table “SelectedOrders”. 

Now we can execute our decision model by a click on “Test.bat”. Here are the execution results:

We are ready to add many other business rules by changing only business rules.

Migrating More Complex SQL to OpenRules

Let’s consider a more complex query that is supposed to find customers with essential unpaid orders:

When we execute this query it will produce 122 records, some of which are shown below:


This query specify a selection criteria only and doesn’t define any business logic for the selection of the unpaid orders among the selected records – we want to define this logic directly in business rules. First, let’s move this query to the Excel table of the type “DataSQL” called “unpaidOrders”:

Now, we can define business rules. The decision table

will define Unpaid Amount and Unpaid Ration for each selected record. Then we can define different alerts using various (easily modified) conditions:

To run these two tables for all selected records we need to update the table that iterates over them. We simply add to the above table “DefineCustomerFinancials” one more row for UnpaidOrders:

As we want for every selected record execute two decision tables “DefineDebtRatio” and “DefineAlerts”, we added an intermediate table “DefineAlertSteps”. Note that we also made our table to be multi-hit to execute rules for both “SelectedOrders” and for “UnpaidOrders”.

The functions alert1-alert3 specify the text of the generated and can be define in the following Excel tables:

In real-world application that uses this decision model we don’t need these functions as the application that invokes our decision model will probably generate emails using the value of decision variables such as Financial Customer Number, Unpaid Amount, Sales Rep ID, etc. produced by the decision model.

To be able to test this decision model we only need to add a new Glossary:

Now we can click on the “test.bat” to execute the updated decision model. It will produce the following alerts:

Of course, now we can easily adjust the conditions in the above decision table “DefineAlerts” to generate more focused alerts.

Conclusion

OpenRules allows business analysts who used to work with SQL queries to migrate them to business rules presented in simple Excel-based decision tables. Without sacrificing the SQL power with  database selection criteria, business users can concentrate on the business logic by defining it in friendly easily modified decision tables.