The example “Unpaid Orders” deals with the classic MySQL Sample Database that has the following organization:
We could use the following SQL query
to find customers with unpaid orders. However, this SQL query provides only a selection criteria for choosing such orders from the above database. Our objective is to build a complete decision model capable to produce produce certain alerts for the “essential” unpaid order based on the Total Ordered Amount and Unpaid Amount. We would like to generate different alerts under the conditions specified the following business rules:
This decision table should be applied to every unpaid order received from a database using a selection criterion similar to the above SQL. Such selection criterion can be moved from SQL to the following OpenRules table “UnpaidOrders” of the type “DataSQL“ :
To incorporate this table in our decision model, we need to define the corresponding business concepts in the Business Glossary:
Note that here the business concept “UnpaidOrders” has the same name as defined above in the DataSQL table and its attributes use exactly the same names as defined in the query’s SELECT statement (with aliases ‘c’, ‘o’, and ‘p’).
We also added another business concepts “Stats” with decision variables Unpaid Amount, Unpaid Ratio, and the array of Alerts, which should be created by our decision model. It can be done using the following decision table ”DefineAlerts”:
This table iterates over UnpaidOrders using the SQL query defined in the DataSQL table “UnpaidOrders”. For each selected “UnpaidOrder” this table calculates decision variables “Unpaid Amount” and “Unpaid Ration”, and then executes the already defined table decision table “AddAlert”.
This way our decision model will produce alerts for all unpaid orders that satisfy the above conditions the table “AddAlert”.
To print the Alerts we may use the following table with a snippet of Java:
To finalize our decision model, we may need the main table that executes two already defined tables: “DefineAlerts” and “ShowResults”:
It is also important to define the Environment for this decision model:
The first 4 properties are standard for the majority of OpenRules decision models. The last 3 properties specify the access attributes to our database.
Before executing our decision model, we need to make sure that the database is open by doing the following: 1) in the Windows Start menu type ‘services’ in the search bar; 2) Scroll down to find ‘MariaDB’, right-click on it, and choose ‘Start the service’.
Now we are ready to execute this decision model using the standard file “test.bat“. It will read directly from the database and will produce the following results:
If your run “explore.bat” you will see the decision diagram and would be able to execute the decision model rule by rules, record by record:
Thus, we clearly separated the selection logic that is DB-specific and business logic that can be easily adjusted by business analysts and will stay the same when a database is changed. This approach allows subject matter experts to add more complex business logic by simply changing the decision tables without any changes in DataSQL.
If you want to add more business decision variables, you can make the proper changes in the business glossary and rules, and without any additional coding your decision model will be ready to execute advanced business logic.
If you need to use additional variables from the database, you may adjust your selection criteria only in the tables DataSQL and the Glossary, and your decision model will work with an updated database. In all cases, no additional programming is required.
This example shows that without sacrificing power of SQL OpenRules allows you to naturally integrate business rules with a relational database.
Decision Intelligence Platform 