6.5. Using Decision Tables in Spreadsheets

Decision tables are a way of representing conditional logic in a precise manner, and they are well suited to business level rules.

6.5.1. Decision Tables in Spreadsheets

JBoss BRMS supports managing rules in a spreadsheet format. Supported formats are Excel (XLS) and CSV. This means that a variety of spreadsheet programs (such as Microsoft Excel, OpenOffice.org Calc, and others) can be utilized.

Note

Use XLS format for decision tables if you are building and uploading them using Business Central. Business Central does not support decision tables in CSV format.

6.5.2. OpenOffice Example

OpenOffice Screenshot

Figure 6.3. OpenOffice Screenshot

In the above examples, the technical aspects of the decision table have been collapsed away (using a standard spreadsheet feature).
The rules start from row 17, with each row resulting in a rule. The conditions are in columns C, D, E, etc., and the actions are off-screen. The values' meanings are indicated by the headers in Row 16. (Column B is just a description.)

Note

Although the decision tables look like they process top down, this is not necessarily the case. Ideally, rules are authored without regard for the order of rows. This makes maintenance easier, as rows will not need to be shifted around all the time.

6.5.3. Rules and Spreadsheets

Rules inserted into rows
As each row is a rule, the same principles apply as with written code. As the rule engine processes the facts, any rules that match may fire.
Agendas
It is possible to clear the agenda when a rule fires and simulate a very simple decision table where only the first match effects an action.
Multiple tables
You can have multiple tables on one spreadsheet. This way, rules can be grouped where they share common templates, but are still all combined into one rule package.

6.5.4. The RuleTable Keyword

When using decision tables, the spreadsheet searches for the RuleTable keyword to indicate the start of a rule table (both the starting row and column).

Important

Keywords should all be in the same column.

6.5.5. The RuleSet Keyword

The RuleSet keyword indicates the name to be used in the rule package that will encompass all the rules. This name is optional, using a default, but it must have the RuleSet keyword in the cell immediately to the right.

6.5.6. Rule Template Example

Rule Templates use tabular data source as a source of rule data and populate a template to generate many rules. With Rule Templates, the data is separated from the rule and there are no restrictions on which part of the rule is data-driven. So it allows you to do everything that is possible in decision tables, and in addition to that, you can also:
  • Store your data in a database (or any other format)
  • Conditionally generate rules based on the values in the data
  • Use data for any part of your rules (such as condition operator, class name, and property name)
  • Run different templates over the same data
Consider the sample template data below. In case of a regular decision table, there would be hidden rows before row 1 and between rows 1 and 2 containing rule metadata. With rule templates, the data is completely separate from the rules.
This is a picture that illustrates a template data example

Figure 6.4. Template Data

So you can apply multiple rule templates to the same data and your data is not tied to your rules at all. Here is a sample rule template:
This is a picture that illustrates a rule template example

Figure 6.5. Rule Template

  • Line 1: All rule templates start with template header.
  • Lines 2-4: Following the header is the list of columns in the order they appear in the data. In this case we are calling the first column age, the second type and the third log.
  • Line 5: An empty line signifies the end of the column definitions.
  • Lines 6-9: Standard rule header text. This is standard rule DRL and will appear at the top of the generated DRL. Put the package statement and any imports and global and function definitions into this section.
  • Line 10: The keyword template signals the start of a rule template. There can be more than one template in a template file, but each template must have a unique name.
  • Lines 11-18: The rule template.
  • Line 20: The keywords end template signify the end of the template.
The rule templates rely on MVEL to do substitution using the syntax @{token_name}. The built-in expression @{row.rowNumber} gives a unique number for each row of data and enables you to generate unique rule names. For each row of data, a rule is generated with the values in the data substituted for the tokens in the template. With the example data above, the following rule file is generated: 
package org.drools.examples.templates;

global java.util.List list;

rule "Cheese fans_1"
when
  Person(age == 42)
  Cheese(type == "stilton")
then
  list.add("Old man stilton");
end

rule "Cheese fans_2"
when
  Person(age == 21)
  Cheese(type == "cheddar")
then
  list.add("Young man cheddar");
end
Use the following code to run the rule: 
DecisionTableConfiguration dtableconfiguration =
    KnowledgeBuilderFactory.newDecisionTableConfiguration();
dtableconfiguration.setInputType( DecisionTableInputType.XLS );

KnowledgeBuilder kbuilder = KnowledgeBuilderFactory.newKnowledgeBuilder();

kbuilder.add( ResourceFactory.newClassPathResource( getSpreadsheetName(),
                                                    getClass() ),
              ResourceType.DTABLE,
              dtableconfiguration );

6.5.7. Data-Defining Cells

There are two types of rectangular areas defining data that is used for generating a DRL file. One, marked by a cell labelled RuleSet, defines all DRL items except rules. The other one may occur repeatedly and is to the right and below a cell whose contents begin with RuleTable. These areas represent the actual decision tables, each area resulting in a set of rules of similar structure.
A Rule Set area may contain cell pairs, one below the RuleSet cell and containing a keyword designating the kind of value contained in the other one that follows in the same row.

6.5.8. Rule Table Columns

The columns of a Rule Table area define patterns and constraints for the left hand sides of the rules derived from it, actions for the consequences of the rules, and the values of individual rule attributes. A Rule Table area should contain one or more columns, both for conditions and actions, and an arbitrary selection of columns for rule attributes, at most one column for each of these. The first four rows following the row with the cell marked with RuleTable are earmarked as header area, mostly used for the definition of code to construct the rules. It is any additional row below these four header rows that spawns another rule, with its data providing for variations in the code defined in the Rule Table header.

Note

All keywords are case insensitive.
Only the first worksheet is examined for decision tables.

6.5.9. Rule Set Entries

Entries in a Rule Set area may define DRL constructs (except rules), and specify rule attributes. While entries for constructs may be used repeatedly, each rule attribute may be given at most once, and it applies to all rules unless it is overruled by the same attribute being defined within the Rule Table area.
Entries must be given in a vertically stacked sequence of cell pairs. The first one contains a keyword and the one to its right the value. This sequence of cell pairs may be interrupted by blank rows or even a Rule Table, as long as the column marked by RuleSet is upheld as the one containing the keyword.

6.5.10. Entries in the Rule Set Area

Table 6.1. Entries in the Rule Set area

Keyword Value Usage
RuleSet The package name for the generated DRL file. Optional, the default is rule_table. Must be First entry.
Sequential "true" or "false". If "true", then salience is used to ensure that rules fire from the top down. Optional, at most once. If omitted, no firing order is imposed.
EscapeQuotes "true" or "false". If "true", then quotation marks are escaped so that they appear literally in the DRL. Optional, at most once. If omitted, quotation marks are escaped.
Import A comma-separated list of Java classes to import. Optional, may be used repeatedly.
Variables Declarations of DRL globals, i.e., a type followed by a variable name. Multiple global definitions must be separated with a comma. Optional, may be used repeatedly.
Functions One or more function definitions, according to DRL syntax. Optional, may be used repeatedly.
Queries One or more query definitions, according to DRL syntax. Optional, may be used repeatedly.
Declare One or more declarative types, according to DRL syntax. Optional, may be used repeatedly.

6.5.11. Rule Attribute Entries in the Rule Set Area

Important

Rule attributes specified in a Rule Set area will affect all rule assets in the same package (not only in the spreadsheet). Unless you are sure that the spreadsheet is the only one rule asset in the package, the recommendation is to specify rule attributes not in a Rule Set area but in a Rule Table columns for each rule instead.

Table 6.2. Rule Attribute Entries in the Rule Set Area

Keyword Initial Value
PRIORITY P An integer defining the "salience" value for the rule. Overridden by the "Sequential" flag.
DURATION D A long integer value defining the "duration" value for the rule.
TIMER T A timer definition. See "Timers" section.
CALENDARS E A calendars definition. See "Calendars" section.
NO-LOOP U A Boolean value. "true" inhibits looping of rules due to changes made by its consequence.
LOCK-ON-ACTIVE L A Boolean value. "true" inhibits additional activations of all rules with this flag set within the same ruleflow or agenda group.
AUTO-FOCUS F A Boolean value. "true" for a rule within an agenda group causes activations of the rule to automatically give the focus to the group.
ACTIVATION-GROUP X A string identifying an activation (or XOR) group. Only one rule within an activation group will fire, i.e., the first one to fire cancels any existing activations of other rules within the same group.
AGENDA-GROUP G A string identifying an agenda group, which has to be activated by giving it the "focus", which is one way of controlling the flow between groups of rules.
RULEFLOW-GROUP R A string identifying a rule-flow group.
DATE-EFFECTIVE V A string containing a date and time definition. A rule can only activate if the current date and time is after DATE-EFFECTIVE attribute.
DATE-EXPIRES Z A string containing a date and time definition. A rule cannot activate if the current date and time is after the DATE-EXPIRES attribute.

6.5.12. The RuleTable Cell

All Rule Tables begin with a cell containing "RuleTable", optionally followed by a string within the same cell. The string is used as the initial part of the name for all rules derived from this Rule Table, with the row number appended for distinction. (This automatic naming can be overridden by using a NAME column.) All other cells defining rules of this Rule Table are below and to the right of this cell.

6.5.13. Column Types

The next row after the RuleTable cell defines the column type. Each column results in a part of the condition or the consequence, or provides some rule attribute, the rule name or a comment. Each attribute column may be used at most once.

6.5.14. Column Headers in the Rule Table

Table 6.3. Column Headers in the Rule Table

Keyword Initial Value Usage
NAME N Provides the name for the rule generated from that row. The default is constructed from the text following the RuleTable tag and the row number. At most one column
DESCRIPTION I A text, resulting in a comment within the generated rule. At most one column
CONDITION C Code snippet and interpolated values for constructing a constraint within a pattern in a condition. At least one per rule table
ACTION A Code snippet and interpolated values for constructing an action for the consequence of the rule. At least one per rule table
METADATA @ Code snippet and interpolated values for constructing a metadata entry for the rule. Optional, any number of columns

6.5.15. Conditional Elements

Given a column headed CONDITION, the cells in successive lines result in a conditional element.
  • Text in the first cell below CONDITION develops into a pattern for the rule condition, with the snippet in the next line becoming a constraint. If the cell is merged with one or more neighbours, a single pattern with multiple constraints is formed: all constraints are combined into a parenthesized list and appended to the text in this cell. The cell may be left blank, which means that the code snippet in the next row must result in a valid conditional element on its own.
    To include a pattern without constraints, you can write the pattern in front of the text for another pattern.
    The pattern may be written with or without an empty pair of parentheses. A "from" clause may be appended to the pattern.
    If the pattern ends with "eval", code snippets are supposed to produce boolean expressions for inclusion into a pair of parentheses after "eval".
  • Text in the second cell below CONDITION is processed in two steps.
    1. The code snippet in this cell is modified by interpolating values from cells farther down in the column. If you want to create a constraint consisting of a comparison using "==" with the value from the cells below, the field selector alone is sufficient. Any other comparison operator must be specified as the last item within the snippet, and the value from the cells below is appended. For all other constraint forms, you must mark the position for including the contents of a cell with the symbol $param. Multiple insertions are possible by using the symbols $1, $2, etc., and a comma-separated list of values in the cells below.
      A text according to the pattern forall(delimiter){snippet} is expanded by repeating the snippet once for each of the values of the comma-separated list of values in each of the cells below, inserting the value in place of the symbol $ and by joining these expansions by the given delimiter. Note that the forall construct may be surrounded by other text.
    2. If the cell in the preceding row is not empty, the completed code snippet is added to the conditional element from that cell. A pair of parentheses is provided automatically, as well as a separating comma if multiple constraints are added to a pattern in a merged cell.
      If the cell above is empty, the interpolated result is used as is.
  • Text in the third cell below CONDITION is for documentation only. It should be used to indicate the column's purpose to a human reader.
  • From the fourth row on, non-blank entries provide data for interpolation as described above. A blank cell results in the omission of the conditional element or constraint for this rule.

6.5.16. Action Statements

Given a column headed ACTION, the cells in successive lines result in an action statement:
  • Text in the first cell below ACTION is optional. If present, it is interpreted as an object reference.
  • Text in the second cell below ACTION is processed in two steps.
    1. The code snippet in this cell is modified by interpolating values from cells farther down in the column. For a singular insertion, mark the position for including the contents of a cell with the symbol $param. Multiple insertions are possible by using the symbols $1, $2, etc., and a comma-separated list of values in the cells below.
      A method call without interpolation can be achieved by a text without any marker symbols. In this case, use any non-blank entry in a row below to include the statement.
      The forall construct is available here, too.
    2. If the first cell is not empty, its text, followed by a period, the text in the second cell and a terminating semicolon are stringed together, resulting in a method call which is added as an action statement for the consequence.
      If the cell above is empty, the interpolated result is used as is.
  • Text in the third cell below ACTION is for documentation only. It should be used to indicate the column's purpose to a human reader.
  • From the fourth row on, non-blank entries provide data for interpolation as described above. A blank cell results in the omission of the action statement for this rule.

Note

Using $1 instead of $param will fail if the replacement text contains a comma.

6.5.17. Metadata Statements

Given a column headed METADATA, the cells in successive lines result in a metadata annotation for the generated rules:
  • Text in the first cell below METADATA is ignored.
  • Text in the second cell below METADATA is subject to interpolation, as described above, using values from the cells in the rule rows. The metadata marker character @ is prefixed automatically, and should not be included in the text for this cell.
  • Text in the third cell below METADATA is for documentation only. It should be used to indicate the column's purpose to a human reader.
  • From the fourth row on, non-blank entries provide data for interpolation as described above. A blank cell results in the omission of the metadata annotation for this rule.

6.5.18. Interpolating Cell Data Example

  • If the template is Foo(bar == $param) and the cell is 42, then the result is Foo(bar == 42).
  • If the template is Foo(bar < $1, baz == $2) and the cell contains 42,43, the result will be Foo(bar < 42, baz ==43).
  • The template forall(&&){bar != $} with a cell containing 42,43 results in bar != 42 && bar != 43.

6.5.19. Tips for Working Within Cells

  • Multiple package names within the same cell must be comma-separated.
  • Pairs of type and variable names must be comma-separated.
  • Functions must be written as they appear in a DRL file. This should appear in the same column as the "RuleSet" keyword. It can be above, between or below all the rule rows.
  • You can use Import, Variables, Functions and Queries repeatedly instead of packing several definitions into a single cell.
  • Trailing insertion markers can be omitted.
  • You can provide the definition of a binding variable.
  • Anything can be placed in the object type row. Apart from the definition of a binding variable, it could also be an additional pattern that is to be inserted literally.
  • The cell below the ACTION header can be left blank. Using this style, anything can be placed in the consequence, not just a single method call. (The same technique is applicable within a CONDITION column.)

6.5.20. The SpreadsheetCompiler Class

The SpreadsheetCompiler class is the main class used with API spreadsheet-based decision tables in the drools-decisiontables module. This class takes spreadsheets in various formats and generates rules in DRL.
The SpreadsheetCompiler can be used to generate partial rule files and assemble them into a complete rule package after the fact. This allows the separation of technical and non-technical aspects of the rules if needed.

6.5.21. Using Spreadsheet-Based Decision Tables

Procedure 6.5. Task

  1. Generate a sample spreadsheet that you can use as the base.
  2. If the JBoss BRMS plug-in is being used, use the wizard to generate a spreadsheet from a template.
  3. Use an XSL-compatible spreadsheet editor to modify the XSL.

6.5.22. Lists

In Excel, you can create lists of values. These can be stored in other worksheets to provide valid lists of values for cells.

6.5.23. Revision Control

When changes are being made to rules over time, older versions are archived. Some applications in JBoss BRMS provide a limited ability to keep a history of changes, but it is recommended to use an alternative means of revision control.

6.5.24. Tabular Data Sources

A tabular data source can be used as a source of rule data. It can populate a template to generate many rules. This can allow both for more flexible spreadsheets, but also rules in existing databases for instance (at the cost of developing the template up front to generate the rules).