SOA/BPM Suite 12c: Oracle Business Rules – MS Excel Integration

In this blog post I will give a step-by-step example of the Excel integration for Business Rules. As a starting point I created a composite with a BPM, a BPEL and a Rules component. Both BPM and BPEL components use the same Rules component. See the following three screenshots. You can download the sample application with this starting point from here.

Composite View

BPM Process

BPEL Service

Open the sample application in JDeveloper 12c to follow the step-by-step example for a hands-on experience.

This blog is divided into the following four part:

• Explore the sample Business Rule
• Export the Business Rule to MS Excel
• Edit the Business Rule in MS Excel
• Import the Business Rule from MS Excel

Explore the sample Business Rule

Open the Business Rules file. You have access to it from two different places. From the BPM branch and the SOA branch.

The excel integration is implemented for Decision Tables. As you can see in the image above there is a decision table ‘PensionDT’ available.  Open this table by double clicking on it’s name. The table has the following content:

The input payload for this rule is information about a pension participant. The following information is supplied:

• dateOfBirth
• dateOfDeath (filled if participant past away)
• dateOfJobEnd (when participant is not being productive anymore)
• applicable Pension Products (OP: Old-age pension, WP: Orphan pension, PP: Widow’s pension)
• Partner information
  • dateOfBirth
  • DateOfDeath (filled if partner past away)
• Child(ren) information (for simplicity of the example, the rule currently supports only one child)
  • dateOfBirth
  • DateOfDeath (filled if child past away)

The output payload of this rule is a statement about the current pension status. Possible outcomes are:

• Contributor (participant is productive and is paying contribution).
• Sleeper (participant is not productive in this trust and is not paying contribution).
• Notified contributor  (participant is productive, but get’s retired within a year).
• Retired (participant has ended his working career and receives an old-age pension).
• Widow and / or Orphan (participant has died. The widow and / or orphan’s receive a pension).
• Out Of Scope (e.g. participant and relatives all died).

Export the Business Rule to MS Excel

In the top menubar of the Decision Table a new <strong>Excel menu is available. Select ‘Export To Excel..’ from this drop-down menu.

The ‘Export To Excel’ dialog appears. Specify the filename (and location) for the Excel file.

After that, click the green plus sign to select the Decision Table that’s being exported.

Then click the Export button to actually create the file.

Because JDeveloper is running (in my case) on a Linux machine without MS Excel, I first have to move the created Excel file to a machine with Excel. After that we can start to use it.

Edit the Business Rule in MS Excel

Open the created ‘OBR_Excel.xslm’ file. It is an Excel file with macro’s. So macro’s must be enabled, as shown on the ReadMe sheet. The ReadMe sheet also describes the structure of the Excel file and the supported operations. It’s best to read and understand these before starting to edit the sheet.

Besides the ReadMe sheet, there’s a sheet with the used ValueSets and a sheet with de PensionDT Decision Table. The ValueSets sheet contains a bucket set for the supported products and a bucket set for the different age ranges.


The Oracle Business Rules menu provides convenient options to perform the following operations.

• Add/Delete Rule
• Modify Rules
  • Edit a Decision Table cell
  • Merge/Split Cells
• Add description to a Rule/Condition/Action using their Labels

Change Bucket Value

Currently the ValueSet has an age range whereas the ‘Old-age pension’ starts at an age of 65. Lets assume the law is changing this to 66. To implement this change, update all the bucket values as shown below.

Add Bucket

Next assume that participants younger than 25 have to pay a lower contribution. To implement this, we need an additional bucket and a new rule in the decision table. First add the bucket. For this, select one of the buckets (e.g. <65). Then click the ‘Add Value’ menu button.

A popup appears where you can enter the new range splitter. Enter 25.

As you can see the ranges are automatically updated.

As you like, you can also update the bucket names. These will be shown in the decision table.

Now we are going to modify the decision table. Select the PensionDT sheet. Then first update the used age labels via the drop-down lists on the C4 row. Implicitly this also means that we have update the used age range from 65 tot 66.

Add Rule

Next we also need to add a rule for the youngster. For this click the ‘add rule’button in the ‘Oracle Business Rule’ menu. As a result of this the Decision Table is extended with an additional column.

Now it is probably the best time to play arround a little bit with the table. Before continuing, modify the table until you have the following result:

Make sure you have saved the modified Excel file before quitting MS Excel and continuing with the next step. Also make sure the file is at a location where JDeveloper can reach it.

Import the Business Rule from MS Excel

Go back to JDeveloper to import the modified excel file into the Decision Table. In the top menubar of the Decision Table select ‘Import From Excel..’.

The ‘Import From Excel’ dialog appears. Select the modified Excel file to import.
With ‘Perform Diff-Merge On Import’ checked off you have the possibility to create a new Decision Table or to overwrite an existing one.

We will used the ‘Perform Diff-Merge On Import’ checked on to demonstrate the Diff-Merge functionality. In our case it is not needed to specify a Base Dictionary.

After the import a ‘Delta’ sign appears on several places to notify that there are differences between the original Decision table and the imported table. In the top left corner there are two buttons you can use to ‘approve’ or ‘reject’ all changes at once. Don’t do this yet !!!.

Open the Decision Table. There you see the same thing on a lower level. You see that the new added column is there and that the modified Age labels are shown next to their old value.

Right click one one of these changes. You will see a menu that gives the possibility to approve or reject an individual change.

Select the option and a new popup will appear. Select Approve for this change. Repeat this for all changes one-by-one or approve them all at once if you like.

Now something strange happened. All Age label changes result in an error message.

This is caused by the fact that labels are not updated in the translation file.

You have to do these translations by hand.

After saving and reopening the rules file, the problems are solves. The Decision table reflects all the modification made from within MS Excel.

This brings me to the end of this step-by-step overview of the MS Excel integration of Oracle Business Rules. If you like to practice a little more you can try to update the PP-rule so that it is only used for the widow and add an additional rule for the orphan(s).

To my opinion this integration really can help to bring understanding and control over the implemented Business Rules to ‘THE BUSINESS’, where it belongs.