Welcome!

Industrial IoT Authors: Pat Romanski, William Schmarzo, Elizabeth White, Stackify Blog, Yeshim Deniz

Related Topics: PowerBuilder, Industrial IoT, Microservices Expo

PowerBuilder: Article

A Rules Engine Built in PowerBuilder

A neat way to use computed fields

PBDJ Feature Story

BPEL is a mystery to me. EbXML? Sorry, not a clue. These things, of course, are about defining and executing business rules and processes in enterprise application servers. On my travels through the world of PowerBuilder applications I've frequently come across the need for business rules to be implemented in the applications I've been working on. They often end up hard-coded in non-visual objects where they deny perhaps their most vital attribute: they should be dynamic and preferably user-editable.

Although many projects to create applications have to implement business rules, they often don't or can't stretch to the industrial-strength servers supporting "languages" like BPEL and EbXML. As a result the business rules for the application end up written into the code, threatening the application's adaptability and so its future, and heralding a maintenance headache.

What's needed is a low-cost and relatively simple adaptable rules engine. With some thought and careful design we can use a combination of our database and PowerBuilder to build just the thing. The PowerBuilder family already offers us a great starting point, the DataWindow and the technology around it. The DataWindow's power in working with datasets is well known but the techniques discussed here use another part of its amazing technology.

One of the neat things you can do with PowerBuilder is have columns change their content as user inputs are made into other columns. This is often done with totals fields on reports, but for the detail band developers often write code into the DataWindow's ItemChanged to do the same thing. A better way is to use the computed field, and apart from being cool in demos it works well in application interfaces where recalculations are made as the user enters data.

To understand what follows it is important to remember that although a computed field has an expression it's the value resulting from that expression that the datawindow engine makes accessible. For example, if the field contains ‘2 x 2 x 3,' using the GetItemNumber function will return 12. This is key to how computed fields and the DataWindow engine will be used to build this small but effective rules engine.

An example application called RulesEngine has been created (in PowerBuilder 10.5.2) to support this article and I recommend you download it and use it in conjunction with this text. You can download it here.

The PowerBuilder demonstration database can be used to invent a couple of business rules that must be implemented in an imaginary application. The design and content of that database is a little constraining and the rules may not be particularly realistic but they'll serve to illustrate what we're trying to achieve here.

First, being based in Boston (naturally!) the rising cost of fuel is increasing our transport costs so a transport levy will have to be imposed on customers based in other regions (see Table 1). The starting point for this calculation is the discounted price that customers are charged. This discounted price is worked out using the second rule: customers who do business with us are offered a discount based on the volume of their business (see Table 2). Customers doing $8,000 or more will get a 5% discount, between $4,000 and $4,999 will get 2%, and below $3,000 no discount at all.

These numbers have to be dynamic and available to management users to change. If the numbers can be kept in a database then it becomes a simple matter to write a user interface to edit them. A table is needed to hold the content of the rules:

CREATE TABLE      Rules(
rule_subject     varchar(16) NOT NULL,
rule_identifier varchar(16) NOT NULL,
rule_type         char(1) NOT NULL CHECK(rule_type
IN (‘A','N','C')),
rule_content    varchar(255) NOT NULL,
rule_qualifier   varchar(255) NOT NULL,
identifier_numeric       decimal(12,4) NULL ,
PRIMARY KEY(rule_subject,
rule_identifier));

In Table 3 are a couple of the rows that will be stored in this table to illustrate the usage of the columns.

‘Subject' is plainly the name for a set of rules, and ‘identifier' a qualification in that set of rules. The key to how this is going to work is in the content and qualifier columns; as can be seen in these hold string expressions that are going to make their way through code into DataWindow computed columns and these expressions can reference other columns in the DataWindow.

The first rule has 1.04 in the ‘rule_qualifier' column representing a region loading and this will be loaded into a computed column on the DataWindow called ‘region_loading.' A reference to this is found in the table in the neighboring column, ‘rule_content.' Another column ‘discounted_price' appears here.

The "secret" of the rules engine is to put the expression "discounted_price * region_loading" (i.e., the contents of the Rules table's ‘rule_content' column) in a computed column of its own called, say, ‘fuel_loading.' Whenever the value of the ‘discounted price' or the ‘region_loading' columns change, the DataWindow engine will automatically recalculate the value in ‘fuel_loading' giving the loading to be added.

Similarly, the ‘sales_volume' rule that has an identifier of 8000, 0.95 will be read from ‘rule_qualifier' and loaded into a column called ‘volume_discount.' When the ‘selling_price' column is populated, the engine will calculate a new value in the column where ‘rule_content' has been loaded.

As an aside, it might be argued that hard-coding column names in these rules is "not generic enough" or difficult for the user to change. Remember that this is just a plain example of a simple engine. There's plenty of scope for programming to use the techniques illustrated here in more sophisticated ways.

This is about a "rules engine" so the functionality will be built into a custom class user object (NVO). In the design used here, two datastores are used, one of which retrieves the rules from the database with a simple DataWindow object. The other datastore is where the action is and it has its dataobject specified by the caller at runtime using a method called, say, ‘of_SetEvalutationDataObject.'

In this method the caller will supply a dataobject name, a string array of rule subjects for retrieval, and the transaction object to use with the database. The rules engine also provides methods to retrieve a point rule (the fuel loading for a region) or a range rule (the discount for a customer's level of business). There's a method to return a reference to the evaluation datastore and one to modify a computed field in it. We'll see this method work later.

To work with the rules engine and the business object that we'll look at later, a subclassed datastore and DataWindow class are used. In the base class there's a set of methods to gather internal information when the user sets that dataobject. This will provide good validation when we allow the caller to pass column and compute names into the methods. This class is again subclassed to give u_dw_entity and n_ds_entity. These are specifically to interact with our business NVOs and will redirect important events to a registered entity class.

For the first illustration of our rules engine we'll produce a report that shows the sales for a selected customer together with the fictional discount it received and the fuel loading for the delivery. This gives a simple insight into the workings of the rules engine and emulates the functionality that we'll build into our NVOs later.

Looking at the source of the DataWindow object in Figure 1, these fields are created with an expression but this is worthless because we are going to overwrite it. The expressions look meaningful because when developing the example program I found that "real" expressions worked consistently rather than just putting ‘1' or ‘0.' Doing the latter often produced unexpected results. I surmise that the DataWindow engine sometimes thinks the computed column with ‘0' or ‘1 is a numeric type and won't allow a string expression to be loaded in code later on. It could be just me, but I've certainly had most success using expressions, however meaningless, at design time.

In the DataWindow object there's a column called selling_price that's computed in the SQL query (where it has an alias of ‘total_sales'). When the query is executed it calculates the sales on an order for a given item. At the same horizontal position in the detail band are also discount_offered, fuel_load, and final_price. These are all computed fields based on the four seen below them. In Figure 1 these four intermediate fields are visible but the user doesn't need to see them. Before saving the DataWindow the detail band will be dragged up to hide them.

As will be found in the sample program the user will select a customer from a treeview DataWindow that shows the customers in their states. A window function is called that first retrieves the customer using the customer identifier as the key. The SQL for the treeview includes the customers' region.

After it's fetched, the region is used for a call to the rules engine to get the fuel loading, which is a simple point rule (region X = loading X). The of_GetPointRule method is used and this returns the rule content and qualifier (as seen in Table 1). The rule returned is ‘discounted_price * region_loading' and a numeric value for the loading. Using dot notation or the Modify function the rule is put into ‘fuel_loading.' The qualifier for this rule is the value of the loading and this is inserted into the computed field called ‘region_loading.' Now whenever the value of the region_loading or discounted_price columns changes, the DataWindow engine will calculate a new value in fuel_loading.

The fuel_loading rule relies on another computed field ‘discounted_price', so that now has to have its value loaded.

From the DataWindow we use GetItemNumber to extract the value in the computed field that has summed the customer's sales (seen in the footer of the report - "Customer Sales"). This is a range rule since the sales value will lie between two limits and that determines the rule selected. The sales value is sent as an argument to the of_GetRangeRule method on the engine with ‘sales_volume' as the requested rule. This method uses the supplied value to search the list of values for the type of rule (held in the ‘identifier_numeric' column) and then returns the specific rule and a numeric value, as illustrated in Table 2. The number goes into the ‘volume_discount' field and the rule into ‘discounted_price.'

Now that all of the computed fields are in place the discounted price, fuel load, and final price fields all automagically recalculate and the effect of the business rules can be seen.

It was mentioned that these rules should be dynamic and that this was achieved by holding them in the database. To illustrate this, a simple response window lets the user change the value of the fuel loading in the database. The next time the rules for the updated region(s) are retrieved the effect can be seen. In the example program a refresh of the report is triggered when closing the response window so that the effect is seen immediately.

Changing a number from the database that is then used in a calculation might not seem to prove much, but the important thing is that the content of the rule is shown to be dynamic. It's perfectly possible to reference other columns that are present in the DataWindow - you might like to use a database tool to change the "rule content" definitions to do this. This might make the report a little strange, but the point would be made.

Global functions can of course be referenced in computed fields, and if you use them, you can also add them into the computed fields' definitions. Be careful though of having to redeploy libraries if the functions change. That's exactly what we're trying to avoid with this engine.

Having illustrated the technique let's now consider where it's most likely to be used, with a non-visual object. Again this will use the PowerBuilder demonstration database to illustrate the rules engine rather than build a realistic business application. In the example application there's an employee details form where the employee details could be updated (if we'd coded it) but the basic employee data will be embellished with imaginary salary packaging that takes into account the three benefit columns on the employee table and a number of vacation days based on the years of service the employee has accrued.

For the benefits that an employee receives, a value will be added to the employer's cost-of-salary package. For life and health insurance this is a simple dollar value. For day care the cost will be $600 a year unless the employee lives in Texas then it will be 48 times a given value, presently $20. There's also a rule for vacation days that will be discussed a little later. Under a key ‘salary_packaging' we can add the following values into the Rules table (see Table 4).

What is it with Texas? Well, the author is Australian and wouldn't have a clue about daycare in Texas or anywhere else in the U.S. Texas was chosen simply because it is known to be in the dataset of employees. If it troubles you, replace Texas with another state.

What is important about using ‘TX' here is that this rule highlights a likely problem in this particular design. As shown in Table 4, if we're going to be processing values based on State or any other criterion, then plainly the expressions to be loaded into the DataWindow will get large and difficult to understand very quickly. The solution is simple of course: we just need to add another column to the Rules table to allow selection by state or any other criterion that may be needed at rule retrieval time and code the methods accordingly.

An "entity" object is created for the employee. This has a Set method to retrieve the keyed row from the Employee table. The RetrieveEnd event in the datastore object of the framework is coded to trigger a corresponding event on the registered entity object. The entity's ue_RetrieveEnd event now executes a function to calculate the salary package values that don't come from the database.

The of_SetPackageDetails method will first instantiate a rules engine object, if not done already, loading it with a named evaluation dataobject. This DataWindow object might be used in the GUI but is more likely to be created especially for use in the rules engine; in the example the DataWindow object was copied (using Save As) from the employee form and had columns added to handle the rules defined for salary packaging.

The appropriate rules key is sent to the engine with the SetEvaluationDataObject method. If the employee has changed since the last time the rules engine was asked to populate the evaluation dataobject, the of_Evaluator method gives a reference to the engine's internal datastore and Retrieve() called with the employee key, populating the datastore holding the evaluation dataobject.

The health insurance, life insurance, and daycare values are simple point rules but this time, instead of retrieving the rule itself, the rule engine's method of_EvaluatePointRuleNumber is called.

The arguments to this method include the rule subject and identifier and the column names for the rule content and qualifier. These last two refer to fields inside the evaluation datawindow object (see Figure 2). The method gets the rule values from the database and loads them into the evaluator and since this causes the datawindow engine to calculate the new values, it's a simple matter to get the resulting value from the computed field (in the engine) and return it.

Here's the code to have the rules engine calculate some of these fields.

ldb_result = inv_rules.of_EvaluatePointRuleNumber( "salary_packaging", &
"bene_health_ins", "health_ins_rule", "health_ins_value" )
This.il_health_bene_value = ldb_result

li_service = This.of_GetService()
inv_rules.of_SetExpression( "years_service", String( li_service ) )
ldb_result = inv_rules.of_EvaluatePointRuleNumber( "salary_packaging", &
"ls_leave_days", "long_service_leave", "leave_days"
This.il_long_service_days = ldb_result

The last four lines deal with leave or vacation time. In Australia an employee can accrue extra vacation (known as long service leave) over the course of their employment. Frequently this is based on a formula something like "one week per 5 years of service". Looking at the rule put into the database for ls_leave_days in Table 4 - Salary Packaging Rules - this has been somewhat simplified so that the employee has 20 days of leave plus 5 days for every year of service.

For the calculation we need to know how long the employee has served with the company so a method on the Employee NVO, of_GetService, does this. It uses the employee's start date and today's date to calculate the number of full years since the employee started. This value is to be used in the evaluation DataWindow; it can be seen in the rule_content for ls_leave_days in Table 4. The of_SetExpression method is provided to allow it to be entered. The values for expressions are usually retrieved from the Rules table but this method allows "manual" setting, as is done here.

The of_EvaluationPointRuleNumber method is called again to read the rules from the database, trigger the re-calculation and return the result. The return values of all the methods that return rule results are stored in instance variables. These have ‘get' methods so the user of the entity NVO can now retrieve the values produced from the rules calculations at will. In the GUI of the example program, the window has methods used to get the values from the employee NVO and set them into the form displayed to the user.

It might be useful to have a look at the logic used to display the employee form to the user and clarify the part the rules engine plays (see Figure 3).

  • When the user selects an employee from the treeview its clicked event calls the wf_SetEmployee method, passing the employee's ID number.
  • This method saves the ID number in an instance variable and calls of_SetEmployee on the n_employee NVO.
  • In the of_SetEmployee method the instance datastore is told to retrieve the employee using the passed ID number. The datastore (from the framework) has the retrieveend event coded to pass processing to the u_nv_retrieveend event on the registered entity NVO, which for the employee NVO is itself.
  • The u_nv_retrieveend event calls the employee NVO's own of_SetPackageDetails method. As explained, this is where the rules engine is called and when this is finished the ‘extra' value columns are available to the caller, in this case the window.
  • Returning control to the window's wf_Set Employee method the form DataWindow is retrieved with the employee ID. This DataWindow also uses the retrieveend event to call the window's own methods, which get the values just calculated in the employee NVO, the costs, and vacation days. These are set into the ‘extra' columns on the form.

There are of course many ways to code this sequence but using the retrieveend event when the employee NVO is populated means that the package details are always calculated and available whenever the employee is retrieved. There's no chance of an employee being fetched and gaining a leftover package. The other thing to note is that although the rules engine has been used several times in this process, all the interaction has been generic. The engine has no notion of working on employee data; it's simply told which rules to apply to which columns in a named DataWindow object.

What happens if the package is changed? The user might select or deselect an insurance option or change the start date. This isn't difficult to deal with. The itemchanged event can be used to call of_SetPackageDetails on the NVO and then it's a simple matter to refresh the GUI. What if we want to change the number of days of long service leave that is given for each year of service? After all, five days per annum does seem a little excessive. Maybe it should have been 0.5. Now the rules engine comes into its own. Using ISQL we can log into the database and apply a change to the rules:

UPDATE Rules SET rule_qualifier = ‘0.5' WHERE rule_subject =
‘salary_packaging' AND rule_identifier = ‘ls_leave_days';

Now, wherever the application is in use, the number of days given to employees for each year of service as calculated by the rules engine will be a more reasonable (if you're the employer) 10% of what it was in the past. Luckily in this scenario the database doesn't record the long service leave value and the example application has no coding to store it so there aren't any data cleanup issues because of this little mistake.

This is all very well if you calculate vacation the Australian way. Maybe we want to use this application elsewhere in the world. Consider the method used to calculate vacation in one U.S. company. For the first year an employee gets 15 days of leave, for years 2 to 8 they get 20 days and for over 8 years of service the employee receives 25 days of leave, oops, I mean vacation! Again the rules engine proves its worth. No modifying functions, no remodelling NVOs, and no redistributing software. For a fresh implementation we'd install the database with the rule that follows, but if the database is already in place, we simply log in to the database with ISQL and execute some SQL:

UPDATE Rules SET rule_content = ‘15+ ( case( years_service when is < 2 then 0 when is > 8 then 2 else 1) * leave_days )', rule_qualifier = ‘5' WHERE rule_subject = ‘salary_packaging' AND rule_identifier = ‘ls_leave_days';

Now the application calculates an employee's vacation time in a rather different way (U.S.) than the original (Australian). Sadly, the data in the PowerBuilder demonstration database as supplied doesn't have much variation in the employee's start dates. A quick session with ISQL updating a few of those will let you see the rule working more clearly.

It is important to remember that the rule content above is a DataWindow expression, that is the CASE is a DataWindow CASE, not an SQL CASE. Also, as already discussed, using the CASE statement might be getting a little too complex; it does work and for this illustration is nicely effective. For a production system some more up-front work on the design and content of the database's Rules table will pay dividends in legibility and extensibility.

This then is a rules engine executed in PowerBuilder, leaning on the technology of the DataWindow. It isn't going to change the world and certainly won't supplant the heavyweights executing the likes of BPEL and ebXML but does offer one flexible and straightforward solution to the often vexing need for business rules to be implemented in PowerBuilder applications yet remain flexible and dynamic at a reasonable cost. The only constraint on it is the combination of skill and imagination that frames and shapes the content of the computed fields. The example shown here uses only two columns but that isn't mandatory, it's just how the author designed it. In fact we saw a flaw in the design of the example - the omission of States in defining the rules for the cost of childcare.

If we were a credit agency (and still in business - Doh!) we might assess an applicant's suitability based on a table of rules. If we based it on state we can put some rules together (see Table 5).

To be assessed a good risk the customer must achieve a certain value in points. For this we can code a rules engine to retrieve the value for a client's home state and pass in the customer's details, returning a number of points that the client has accumulated.

The (in production) non-visual object that inspired this article has no methods that return values; each rule is assessed inside a generic method and a Boolean returned to indicate pass or fail. For this credit assessor, given a Rule and a ValueCol, a Boolean might be returned. After checking the rules, if everything is positive we can continue talking to the customer.

The deliberate design choice to utilize a user-supplied evaluation DataWindow object and allow the caller access to the rules engine object maintains a nicely generic approach without imposing naming constraints at any level. If rigid encapsulation is the goal, then this could easily be achieved by applying naming standards and using specific DataWindow objects. This article is simply demonstrating a rather neat way to use computed fields; it's not attempting to lay down a fixed methodology. If you haven't already done so, please do download the sample program and steal the ideas to solve that sometimes nasty problem of getting generic rule processing into your PowerBuilder applications.

More Stories By Paul Slater

Paul Slater joined Powersoft Australia as a PowerBuilder instructor in 1995 and has enjoyed mixing periods of freelance work with time as a senior consultant for Sybase Australia. He is presently working for the Australian Red Cross Blood Service as a transplant systems analyst on the National Organ Matching System. As well as being a devotee of all things PowerBuilder he’s fanatical about PowerDesigner and doesn’t understand people who aren’t.

Comments (1)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


IoT & Smart Cities Stories
The platform combines the strengths of Singtel's extensive, intelligent network capabilities with Microsoft's cloud expertise to create a unique solution that sets new standards for IoT applications," said Mr Diomedes Kastanis, Head of IoT at Singtel. "Our solution provides speed, transparency and flexibility, paving the way for a more pervasive use of IoT to accelerate enterprises' digitalisation efforts. AI-powered intelligent connectivity over Microsoft Azure will be the fastest connected pat...
There are many examples of disruption in consumer space – Uber disrupting the cab industry, Airbnb disrupting the hospitality industry and so on; but have you wondered who is disrupting support and operations? AISERA helps make businesses and customers successful by offering consumer-like user experience for support and operations. We have built the world’s first AI-driven IT / HR / Cloud / Customer Support and Operations solution.
Codete accelerates their clients growth through technological expertise and experience. Codite team works with organizations to meet the challenges that digitalization presents. Their clients include digital start-ups as well as established enterprises in the IT industry. To stay competitive in a highly innovative IT industry, strong R&D departments and bold spin-off initiatives is a must. Codete Data Science and Software Architects teams help corporate clients to stay up to date with the mod...
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
Druva is the global leader in Cloud Data Protection and Management, delivering the industry's first data management-as-a-service solution that aggregates data from endpoints, servers and cloud applications and leverages the public cloud to offer a single pane of glass to enable data protection, governance and intelligence-dramatically increasing the availability and visibility of business critical information, while reducing the risk, cost and complexity of managing and protecting it. Druva's...
BMC has unmatched experience in IT management, supporting 92 of the Forbes Global 100, and earning recognition as an ITSM Gartner Magic Quadrant Leader for five years running. Our solutions offer speed, agility, and efficiency to tackle business challenges in the areas of service management, automation, operations, and the mainframe.
The Jevons Paradox suggests that when technological advances increase efficiency of a resource, it results in an overall increase in consumption. Writing on the increased use of coal as a result of technological improvements, 19th-century economist William Stanley Jevons found that these improvements led to the development of new ways to utilize coal. In his session at 19th Cloud Expo, Mark Thiele, Chief Strategy Officer for Apcera, compared the Jevons Paradox to modern-day enterprise IT, examin...
With 10 simultaneous tracks, keynotes, general sessions and targeted breakout classes, @CloudEXPO and DXWorldEXPO are two of the most important technology events of the year. Since its launch over eight years ago, @CloudEXPO and DXWorldEXPO have presented a rock star faculty as well as showcased hundreds of sponsors and exhibitors! In this blog post, we provide 7 tips on how, as part of our world-class faculty, you can deliver one of the most popular sessions at our events. But before reading...
DSR is a supplier of project management, consultancy services and IT solutions that increase effectiveness of a company's operations in the production sector. The company combines in-depth knowledge of international companies with expert knowledge utilising IT tools that support manufacturing and distribution processes. DSR ensures optimization and integration of internal processes which is necessary for companies to grow rapidly. The rapid growth is possible thanks, to specialized services an...
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...