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

2
Share this on .. Tweet about this on TwitterShare on LinkedIn4Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

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.

image1
Composite View

image2
BPM Process

image3
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.
image4
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:
image5
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.
image6
The ‘Export To Excel’ dialog appears. Specify the filename (and location) for the Excel file.
image7
After that, click the green plus sign to select the Decision Table that’s being exported.
image8
Then click the Export button to actually create the file.
image9
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.
image11
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.
image12
image13
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

image14

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.
image14

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.
image15
A popup appears where you can enter the new range splitter. Enter 25.
image16
As you can see the ranges are automatically updated.
image17
As you like, you can also update the bucket names. These will be shown in the decision table.
image18
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.
image19

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.
image20
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:
image21
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..’.
image22
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.
image23
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.
image24
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 !!!.
image25
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.
image26
Right click one one of these changes. You will see a menu that gives the possibility to approve or reject an individual change.
image27
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.
image28
Now something strange happened. All Age label changes result in an error message.
image29
This is caused by the fact that labels are not updated in the translation file.
image30
You have to do these translations by hand.
image31
After saving and reopening the rules file, the problems are solves. The Decision table reflects all the modification made from within MS Excel.
image32
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.

Share this on .. Tweet about this on TwitterShare on LinkedIn4Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

About Author

As a SOA and BPM professional, Marcel is an experienced consultant in the field of process design and process modeling. Within this field system integration and IT-transcending business processes have Marcel's special interest.

2 Comments

  1. Hello,

    Can we export the conflict resolutions? I tried in 11g and 12c JDeveloper and it doesn’t work. How can we do this?

  2. Hey, Marcel. Thanks for the post, it was really helpful. Just have one question. I see that on image29, the value “true” that you set earlier on Excel is not there anymore. Later on, it re-appears on image32. Did you add it manually? It happened to me too, but I don’t know why it keeps doing that. Could that be a bug?

Leave a Reply