We developed a software product to replace the use of spreadsheets as a data processing solution within a specific business area. This paper explains the characteristics of the tool and the findings, both resulting from a process of 3 years real life refinement inside the ICM domain, and that we postulate can be valid in other business domains.
The last four years our company has been working in the field of Incentive Compensation Management –ICM-. Current solutions, based on calculating performance-based payment for employees, are complex and highly dynamic.
Worldwide, “only a 10% of sales organizations with more than
100 payees deploy prepackaged sales ICM applications” [
In our market, the most important features of ICM software are flexibility, security, auditing capabilities, and allowing the end users to update the product themselves by including changes in business rules.
Some or the issues of pre-existing ICM solutions are: World Class ICM software solutions are costly and demand long implementation processes In-house developments are slow1 and rigid2 1 in the range of 2 hrs per 2.000 transactions
Excel-based solutions are fragile, difficult to audit and error prone3 Currently available solutions don’t attempt to improve problem representation4 beyond conventional system documentation Excel formulas are one-line expressions and are thus difficult to read (e.g. nested if statements)
There are two elements we consider important. First, we are putting in practice some ideas (see Section 8) that may be useful in other areas of enterprise software development. Second, we want to determine how well our selection of functionalities succeeds in creating a tool that best takes advantage of a mental model of spreadsheets.
Customized ICM applications developed with SBBRENG have been in use for more than a year in several companies from different areas: car dealerships, banks, retail, etc. This happens in the Chilean market where we use the product name IM4
Business rules engines aren’t a new product category, they started
around the 80s [
Drools is a low level programming environment oriented to efficiently manage a large quantity of conditions of any type. It provides APIs for integration with other languages, tools and processing environments. On the other hand, Operational Decision Management –ODM- is a more business oriented solution that conceptually splits systems into two different components, talking to each other under a data contract. One is a traditional Data Processing System for storing, updating and reporting information related to some business domain, and the other is a specialized system for managing and executing the business rules of the same business domain. 2 no provisions for isolation or special management of business logic 3 http://eusprig.org/horror-stories.htm 4 problem representation has impact on the maintenance agility same as on the ability to preserve application coherence 5 http://drools.jboss.org/drools-guvnor.html SBBRENG is closer to ODM with some big differences: domain model is not Object Oriented and input/output documents are simple shared folders for storing interchanging files
External Systems
Input Document
Document Rules Engine Business User
A WorkSheet -WS- is a set of files and columns such as each column has a unique name and each cell stores an immutable value (current implementation does not yet force this immutability). A SBBRENG Process is a specific sequence of steps that modifies a WorkSheet. There is an operation ApplyRules (•) for implementing SBBRENG Processes, following statements of Business Rules. A Business Rule is -in the context of SBBRENG- a directive detailing how to calculate the numeric values used to run the business.
We represent a SBBRENG Process using the formula
BRk • WSp => (WSkp, OFp)
• is the ApplyRules operation BRk is a subset of the Business Rules comprising the
Application WSp is a current WorkSheet that is part of the
Application WSkp is a new WorkSheet that will be part of the
Application OFp is an Output File that consists of a subset of
WSkp Operation • adds new columns at the right of WSp. Business Rules define how to calculate the immutable values of the new cells. New columns can reference any column located at its left (Figure 2). .
processing secuence AA BB CC DD EE FF GG HH II JJ a1 b1 c1 d1 e1 f1 a2 b2 c2 d2 e2 f2 a3 b3 c3 d3 e3 f3 g1 g2 g3 can use any value from
columns AA to GG
An Input File becomes a WS when it contains all the information referenced by one or more Business Rules. When Business Rule references are contained in several Input Files, it is necessary to build a WorkSheet by assembling several Input Files. We use the Assemble operation (+) for this purpose, as shown in the following formula.
IFi (p) + IFk (q) => WSa Where p is a column of IFi and q is a column of IFk, and they provide a mechanism for matching rows of the Input Files. Operation + produces a WorkSheet out of all the columns of both Input Files. The WorkSheet contains all the IFi (p) rows and for each of them, only one matching IFk (q) row. The matching logic is the same of an Excel Table Lookup operation, in which p is a column in the data and q represents the first column of the table. The + operation is associative but not commutative.
The + operation can also be applied to a WorkSheet. In such cases we have a precedence of Processes. Figure 3 shows an example in which BR1• WSa precedes BR2 • WSb. There are situations where it is necessary to assemble the same file more than one time, using different column keys. In such a case SBBRENG adds a prefix to column names to avoid collisions. In the following example, IFk is applied twice:
( IFi (p) + IFk (q)) (r) +IFk (s) => WSb
The most important difference with Spreadsheets is spreadsheet interactivity, because SBBRENG follows a batch processing model. Other examples of incompatible features are Table Lookup, Dynamic Tables, external links, totals and other columns or rows of the Matrix. Each box has a label that makes apparent its associated condition.
New Products
Old Products Spot Recurrent Premiun Recurrent Premiun
Clients Clients Clients Clients Clients
Salesmen type A Salesmen North Region type B
South Region Salesmen
Type C
North Region Central Region South Region 5% 4% 3% 5% 4% 3% 6% 5% 4% 6% 5% 4% 7% 0% 0% 7% 6% 0% 8% 0% 0% 8% 6% 0% aggregated values in the same column as the original data, macros, different formulas in the same column, and the programming language.
A SBBRENG application offers “referential transparency”, which is the base for providing reproducible results. In order to achieve that goal, it is necessary to replace links to external sources (other spreadsheets, Databases, etc.) by static Input Files containing the external linked information.
In the context of SBBRENG, Parameters are a special type of data: input files are produced by other systems, but parameters are maintained by users. Parameters represent a high level system abstraction, which is required to adapt the system behavior. Data is stored in Files and WorkSheets, and Parameters are stored in a special repository. SBBRENG’s IDE provides the means for Parameter editing.
The calculus performed on each column follows a cycle. Rows are filtered by conditions and grouped by some column values. The logic applied to the cells belonging to a group, is repeated for each group until reaching the last. Some SBBRENG core functions offer aggregated operations over groups, e.g. count, sum, average, max.
Five objects support Domain modeling: Matrix, Classifier, List, Rules and Files. Files are input/output files. Rules are pieces of code that have some specific properties (i.e. name, filter, sequence and granularity). The three remaining objects are the most important, because they store in their structure the values of the Parameters of the application. This allows a direct user interaction with the Domain Model representation, when adjusting Parameters values.
Parameters directly represent elements of the ubiquitous language
[
As needed, some Parameters may have embedded logic that is executed every time they are used in a Rule.
Matrixes are bi-dimensional arrays of values and conditions that return a value (or several values) based on the evaluation of its embedded logic.
Matrixes have two headings, X and Y. Each heading represents a tree of conditions: sibling nodes make an OR and parent-child nodes make an AND. It is very easy to see the tree as a set of adjacent boxes with the outermost boxes of the headings matching B c 7% 6% 5% 7% 5% 5%
Classifiers are Boolean expressions whose value is automatically set based exclusively on the input data and remain immutable until the input data change. They represent business concepts, mostly corresponding to nouns in the ubiquitous language. Regardless of how many relationships input fields have in the system they comes from, Classifiers implements only those conditions required by our application. Classifiers are used by Matrixes to build its embedded logic.
Classifiers create a conceptual layer for mapping a SBBRENG application Domain with the Domain of systems where the input data were generated. Classifiers are used by Matrixes to build its embedded logic. Classifiers increase program readability and improve our ability to adapt to changes in the Input Files.
A List is a Dictionary where a value associated to an entry can be simple or complex. Constants are Lists that use a special syntax.
We use JavaScript to replace spreadsheets’ functions. To improve productivity, we developed a library of "core functions" frequently used in our Domain of applications. It is easy to add new core functions.
We also provide a graphic block language, similar to MIT's
Scratch [
A Run is a complete execution of a SBBRENG Application. Each Run is stored as a backup document containing all inputs, outputs, parameters and logic utilized. SBBRENG automatically assigns a unique ID to each Run. Later, a Run can be opened as read-only for revision, but it cannot be modified. It is possible to reprocess a backup document, generating a new backup document with a different ID.
Additionally, there is a log of the changes made to the parameters,
the input files and the logic, indicating old and new values, the
user involved and date/time of all changes.
5.7 IDE
There is a special IDE -Integrated Development Environment- to
support all tasks: application development, documentation,
design, testing, etc. It also has functions for running applications,
for reviewing previous Runs and for downloading results.
The IDE offers two views: a conventional nested folders type and
an advanced mental map type [
Documentation is a part of a broader content we call problem representation. It includes parameters, code, blocks, ad-hoc descriptions, etc. Additional to the content, there are tools for filtering information, displaying information, and displaying information relationships. Some of this functionality is currently in use; some is pending development. Because documentation is supported by the IDE, it is always available on line when working with the application.
On the Server side, there is a Web application than runs on IIS using .NET and SQL Server.
On the Client side, there is the IDE running in any modern browser.
Security and auditability of the applications were improved in relationship to spreadsheets, as a result of some new specific functionality (see Subsections 5.1, 5.2 and 5.6).
Documentation was improved when compared to conventional solutions, because of the integration of different types of information into one common repository (see Subsections 5.7, 5.8) and the availability of new capabilities based on the use of a Mental Map.
The use of the Domain Model (see Subsection 5.4) enhanced productivity of development and maintenance, because less code is required to implement the same business logic compared to solutions using spreadsheet (See Sub Subsection 5.4.2) Performance is good. We were expecting 10 min per 2.000 transactions and 4 hrs per 3.000.000 transactions, but real numbers were 4 min and 1 hr 45min, respectively. We were using a conventional entry level server.
Looking at one of the components of the productivity equation, we think we successfully tried some new ideas, like a new approach for representing the Business Rules Domain, a method for avoiding complex nested conditions, an IDE based in a Mental Map, a graphic replacement for the programming language of spreadsheets, some mechanisms to improve security and auditability, etc.
But looking at the other component -the process of getting and
agreeing to specifications for building the application- we think it
is necessary to achieve important improvements. The ubiquitous
language requires more elaboration6. The cognitive process that
ends with a working application can probably take advantage of
the impressive new findings in neuroscience. Focus, resources,
new instruments and new methodologies are moving the limits.
“Constant development of more sensitive and accurate
neuroimaging and data analysis methods creates new research
possibilities” [
We are interested in two areas for future development. The first is improving automatic analysis capabilities used during the testing phase, and the other is improving visualization capabilities for mental maps in the IDE. 6 it has been apparent that some additional concepts are necessary