SBBRENG: Spreadsheet Based Business Rule Engine Pablo D. Palma Incentings Latadia 4623, Santiago, Chile +562 2207 7158 pablo.palma@incentings.com  Excel-based solutions are fragile, difficult to audit and error ABSTRACT prone3 We developed a software product to replace the use of  Currently available solutions don’t attempt to improve spreadsheets as a data processing solution within a specific problem representation4 beyond conventional system business area. This paper explains the characteristics of the tool documentation and the findings, both resulting from a process of 3 years real life  Excel formulas are one-line expressions and are thus difficult refinement inside the ICM domain, and that we postulate can be to read (e.g. nested if statements) valid in other business domains. 1.3 Importance and state of our work General Terms There are two elements we consider important. First, we are Documentation, Design, Security, Human Factors, Languages. putting in practice some ideas (see Section 8) that may be useful Verification in other areas of enterprise software development. Second, we want to determine how well our selection of functionalities Keywords succeeds in creating a tool that best takes advantage of a mental Spreadsheet, Business Rules Engine, DDD, SEmS'14 model of spreadsheets. Customized ICM applications developed with SBBRENG have 1. INTRODUCTION been in use for more than a year in several companies from different areas: car dealerships, banks, retail, etc. This happens in 1.1 Use of spreadsheets for ICM solutions the Chilean market where we use the product name IM4 The last four years our company has been working in the field of Incentive Compensation Management –ICM-. Current solutions, 2. BUSINESS RULES ENGINE based on calculating performance-based payment for employees, Business rules engines aren’t a new product category, they started are complex and highly dynamic. around the 80s [2]. Since then, many products and companies have undergone a cycle of creation, development, merging and Worldwide, “only a 10% of sales organizations with more than death. We will use two currently successful solutions as 100 payees deploy prepackaged sales ICM applications” [1]. comparison standards: Drools (see Drools Guvnor Knowledge Almost all the remainder uses Excel. This is a reaction to the Base)5 [3] -a component of the open source platform JBoss combination of factors: high rate of change, short time available BRMS- and ODM [3, 4] by IBM. Both are much larger systems for implementation, and typically long cycles in IT development. than SBBRENG, sharing the same global objective: make the However, Excel introduces its own limitations. It requires a lot of application more business agile. human intervention that results in overpayment, and user- generated errors that could be reduced “by more than 90%” [1] Drools is a low level programming environment oriented to (see subsection 5.2). In addition, there is “dissatisfaction with the efficiently manage a large quantity of conditions of any type. It reliability of spreadsheets in adequately supporting compensation provides APIs for integration with other languages, tools and processes” [1]. Excel also does not accomplish auditing, processing environments. On the other hand, Operational accounting and regulation requirements. Decision Management –ODM- is a more business oriented solution that conceptually splits systems into two different In our market, the most important features of ICM software are components, talking to each other under a data contract. One is a flexibility, security, auditing capabilities, and allowing the end traditional Data Processing System for storing, updating and users to update the product themselves by including changes in reporting information related to some business domain, and the business rules. other is a specialized system for managing and executing the business rules of the same business domain. 1.2 Goals for a new ICM software Some or the issues of pre-existing ICM solutions are:  World Class ICM software solutions are costly and demand 2 long implementation processes no provisions for isolation or special management of business  In-house developments are slow1 and rigid2 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 1 http://drools.jboss.org/drools-guvnor.html in the range of 2 hrs per 2.000 transactions SBBRENG is closer to ODM with some big differences: domain A SBBRENG Application is a sequence of Processes as seen model is not Object Oriented and input/output documents are below: simple shared folders for storing interchanging files Process 1: BR1 • WSa Process 2: BR2 • WSb Input ……………………… . External Document Rules Process k: BRk • WSm Systems Engine Output Document Business User 3.2 The Assemble operation An Input File becomes a WS when it contains all the information Figure 1: ODM high level view 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 3. THE SPREADSHEET-LIKE SIDE OF Assemble operation (+) for this purpose, as shown in the SBBRENG following formula. 3.1 The ApplyRules operation A WorkSheet -WS- is a set of files and columns such as each IFi (p) + IFk (q) => WSa column has a unique name and each cell stores an immutable Where p is a column of IFi and q is a column of IFk, and they value (current implementation does not yet force this provide a mechanism for matching rows of the Input Files. immutability). A SBBRENG Process is a specific sequence of steps that modifies a WorkSheet. There is an operation Operation + produces a WorkSheet out of all the columns of both ApplyRules (•) for implementing SBBRENG Processes, Input Files. The WorkSheet contains all the IFi (p) rows and for following statements of Business Rules. A Business Rule is -in each of them, only one matching IFk (q) row. The matching logic the context of SBBRENG- a directive detailing how to calculate is the same of an Excel Table Lookup operation, in which p is a the numeric values used to run the business. column in the data and q represents the first column of the table. We represent a SBBRENG Process using the formula The + operation is associative but not commutative. BRk • WSp => (WSkp, OFp) The + operation can also be applied to a WorkSheet. In such Where: • is the ApplyRules operation BRk is a subset of the Business Rules comprising the cases we have a precedence of Processes. Figure 3 shows an Application example in which BR1• WSa precedes BR2 • WSb. 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). can use any value from processing columns AA to GG . secuence Figure 3: Process precedence columns AA BB CC DD EE FF GG HH II JJ names There are situations where it is necessary to assemble the same file a1 b1 c1 d1 e1 f1 g1 more than one time, using different column keys. In such a case SBBRENG adds a prefix to column names to avoid collisions. In a2 b2 c2 d2 e2 f2 g2 the following example, IFk is applied twice: a3 b3 c3 d3 e3 f3 g3 ( IFi (p) + IFk (q)) (r) +IFk (s) => WSb WSp columns calculated by 4. NON COMPATIBLE SPREADSHEET Business Rules FUNTIONALITY The most important difference with Spreadsheets is spreadsheet interactivity, because SBBRENG follows a batch processing WSkp model. Other examples of incompatible features are Table Lookup, Dynamic Tables, external links, totals and other Figure 2: A SBBRENG Process aggregated values in the same column as the original data, columns or rows of the Matrix. Each box has a label that makes macros, different formulas in the same column, and the apparent its associated condition. programming language. New Products Old Products 5. SOME ADITIONAL FEATURES OF Spot Recurrent Premiun Recurrent Premiun Clients Clients Clients Clients Clients SBBRENG Salesmen type A 5% 6% 7% 7% 8% 5.1 Referential Transparency A SBBRENG application offers “referential transparency”, which Salesmen North Region 4% 5% 6% 0% 0% is the base for providing reproducible results. In order to achieve type B South Region 3% 4% 5% 0% 0% that goal, it is necessary to replace links to external sources (other North Region 5% 6% 7% 7% 8% spreadsheets, Databases, etc.) by static Input Files containing the Salesmen Central Region 4% 5% 5% 6% 6% external linked information. Type C South Region 3% 4% 5% 0% 0% 5.2 Separation of Data and Parameters Figure 4: A Matrix In the context of SBBRENG, Parameters are a special type of data: input files are produced by other systems, but parameters are Matrixes change the way in which complex nested conditions are maintained by users. Parameters represent a high level system visualized (see figure 5) abstraction, which is required to adapt the system behavior. Data is stored in Files and WorkSheets, and Parameters are stored in a A B special repository. SBBRENG’s IDE provides the means for 5 6 C Parameter editing. 1 2 3 4 a b c d e 5.3 Iteration over Data The calculus performed on each column follows a cycle. Rows are (A && (1 || 2 || 3)) filtered by conditions and grouped by some column values. The || (B && (5 && (a || b || c)) || (6 && (d || e)) logic applied to the cells belonging to a group, is repeated for || C each group until reaching the last. Some SBBRENG core functions offer aggregated operations over groups, e.g. count, Figure 5: Equivalence of nested conditions sum, average, max. Matrixes are self-explanatory for anyone familiar with the Business Domain of the application. Their behaviour doesn’t 5.4 Domain Model depend on the context in which they are used; it only depends on 5.4.1 Introduction the values of some of the input data in a clear and explicit manner. Five objects support Domain modeling: Matrix, Classifier, List, Matrixes provide a powerful mechanism of Domain Rules and Files. Files are input/output files. Rules are pieces of representation, because of its expressivity and because of the way code that have some specific properties (i.e. name, filter, sequence they isolate behavior. 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 5.4.3 Classifiers Classifiers are Boolean expressions whose value is automatically with the Domain Model representation, when adjusting set based exclusively on the input data and remain immutable Parameters values. until the input data change. They represent business concepts, Parameters directly represent elements of the ubiquitous language mostly corresponding to nouns in the ubiquitous language. [5] Those elements appear in several real life working documents: Regardless of how many relationships input fields have in the memos, agreements, contracts, regulations, etc. The shape of the system they comes from, Classifiers implements only those Parameters as used in SBBRENG mimics its representation in conditions required by our application. Classifiers are used by documents. Therefore, business users understand them without Matrixes to build its embedded logic. requiring further explanations. Classifiers create a conceptual layer for mapping a SBBRENG As needed, some Parameters may have embedded logic that is application Domain with the Domain of systems where the input executed every time they are used in a Rule. data were generated. Classifiers are used by Matrixes to build its embedded logic. Classifiers increase program readability and 5.4.2 Matrixes improve our ability to adapt to changes in the Input Files. Matrixes are bi-dimensional arrays of values and conditions that return a value (or several values) based on the evaluation of its embedded logic. 5.4.4 List and Constants A List is a Dictionary where a value associated to an entry can be Matrixes have two headings, X and Y. Each heading represents a simple or complex. Constants are Lists that use a special syntax. 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 5.5 Programming Language adjacent boxes with the outermost boxes of the headings matching 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 The use of the Domain Model (see Subsection 5.4) enhanced new core functions. productivity of development and maintenance, because less code is required to implement the same business logic compared to We also provide a graphic block language, similar to MIT's solutions using spreadsheet (See Sub Subsection 5.4.2) Scratch [6] and others [7]. Blocks automatically generate the equivalent JavaScript instructions. Blocks are very well suited to Performance is good. We were expecting 10 min per 2.000 SBBRENG because each Rule is made of a few instructions. transactions and 4 hrs per 3.000.000 transactions, but real Blocks were initially implemented for the Assemble operation, numbers were 4 min and 1 hr 45min, respectively. We were using and we have plans to extend it to the Rules. a conventional entry level server. 5.6 Auditing 8. KEY LESSONS LEARNED FROM A Run is a complete execution of a SBBRENG Application. Each WORKING WITH SBBRENG Run is stored as a backup document containing all inputs, outputs, Looking at one of the components of the productivity equation, parameters and logic utilized. SBBRENG automatically assigns a we think we successfully tried some new ideas, like a new unique ID to each Run. Later, a Run can be opened as read-only approach for representing the Business Rules Domain, a method for revision, but it cannot be modified. It is possible to reprocess for avoiding complex nested conditions, an IDE based in a Mental a backup document, generating a new backup document with a Map, a graphic replacement for the programming language of different ID. spreadsheets, some mechanisms to improve security and Additionally, there is a log of the changes made to the parameters, auditability, etc. the input files and the logic, indicating old and new values, the But looking at the other component -the process of getting and user involved and date/time of all changes. agreeing to specifications for building the application- we think it is necessary to achieve important improvements. The ubiquitous 5.7 IDE language requires more elaboration6. The cognitive process that There is a special IDE -Integrated Development Environment- to ends with a working application can probably take advantage of support all tasks: application development, documentation, the impressive new findings in neuroscience. Focus, resources, design, testing, etc. It also has functions for running applications, new instruments and new methodologies are moving the limits. for reviewing previous Runs and for downloading results. “Constant development of more sensitive and accurate neuroimaging and data analysis methods creates new research The IDE offers two views: a conventional nested folders type and possibilities” [10]. an advanced mental map type [8, 9]. The latter is the base for some advanced visualization options that ease the understanding of an Application (pending development). 9. FUTURE DEVELOPMENTS We are interested in two areas for future development. The first is improving automatic analysis capabilities used during the testing 5.8 Documentation phase, and the other is improving visualization capabilities for Documentation is a part of a broader content we call problem mental maps in the IDE. representation. It includes parameters, code, blocks, ad-hoc descriptions, etc. Additional to the content, there are tools for filtering information, displaying information, and displaying 10. REFERENCES information relationships. Some of this functionality is currently [1] Dunne, M. 2010. MarketScope for Sales Incentive in use; some is pending development. Because documentation is Compensation Management Software. Gartner MarketScope supported by the IDE, it is always available on line when working Series (March 2010) with the application. [2] Bosh 2010. The Past, Present, and Future of Business Rules. Bosch Software Innovations GmbH. (March 2010) 6. SOFTWARE STRUCTURE [3] Craggs, S. 2012. Competitive Review of Operational On the Server side, there is a Web application than runs on IIS Decision Management, Lustratus Research (October 2012) using .NET and SQL Server. [4] IBM 2012. Why IBM Operational Decision Management? On the Client side, there is the IDE running in any modern Software. Thought Leadership White Paper (June 2012) browser. [5] Evans, E. 2003. Domain-Driven Design. Addison Wesley; E (August 2003) 7. RESULTS Security and auditability of the applications were improved in [6] Resnick, M., Maloney, J., Monroy-Hernández, A., Rusk, N., relationship to spreadsheets, as a result of some new specific Eastmond, E., Brennan, K., Millner, A., Rosenbaum, E., functionality (see Subsections 5.1, 5.2 and 5.6). Silver, J., Silverman, B. and Kafai Y. Scratch: Programming for all. Communications of the ACM (November 2009) 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. 6 it has been apparent that some additional concepts are necessary [7] Hosick, E. 2014. Visual Programming Languages - (USI), Lugano, Switzerland Snapshots. (February 2014) [9] Novak, J., and Cañas, A. 2008. The Theory Underlying http://blog.interfacevision.com/design/design-visual- Concept Maps and How to Construct and Use Them. Florida progarmming-languages-snapshots/ Institute for Human and Machine Cognition (IHMC) [8] Eppler, M. 2006. A comparison between concept maps, [10] Jääskeläinen, L. 20012. Cognitive Neuroscience: mindmaps, conceptual diagrams, and visual metaphors as Understanding the neural basis of the human mind . complementary tools for knowledge construction and Jääskeläinen & Ventus Publishing ApS (November 2012)| sharing. Faculty of Communication Sciences, University of http://bookboon.com/ Lugano .