Using Oracle Business Rules as an ETL Front-End
I went to see a new customer last week who was looking to put together a company MIS system. The system needed to load and transform some data prior to analyzing it using OracleBI Discoverer, and to do the transformation they were looking at using OracleBI Warehouse Builder, and a new Oracle product called Oracle Business Rules. Oracle Business Rules was announced last year at OpenWorld and is a component of the recently released Oracle Application Server 10.1.3, and it provides a means for business users to define rulesets for use with applications running on (for example) Oracle Application Server. What the customer was thinking of doing was using Oracle Business Rules to provide a user-friendly environment for defining ETL rules, such as "if this sale was to this organisation, then multiply the figure by 120%, but if is was to these organisations, then we can't count it". Ideally, Oracle Business Rules would provide an alternative to using OracleBI Warehouse Builder to define data mappings and transformations, and would allow the IT Department to hand over this task to the business units. According to the product documentation:
"Business rules are statements that describe business policies. For example, a car rental company might use the following business rule:
If a driver's age is younger than 21, then decline to rent.
An airline might use a business rule such as the following:
If a frequent flyer's total miles for the year are greater than 100,000, then status is Gold.
A financial institution could use a business rule such as:
If annual income is less than $10,000, then deny loan.
These examples represent individual business rules. In practice, using Oracle Business Rules you can combine many business rules.
For the car rental example, you can name the driver age rule the Under Age rule. Traditionally, business rules such as the Under Age rule are buried in application code, and might appear in a Java application as follows:
public boolean checkUnderAgeRule (Driver driver) { boolean declineRent = false; int age = driver.getAge(); if( age < 21 ) { declineRent = true; } return declineRent; }This code is not easy for non-technical users to read and can be difficult to understand and modify. For example, suppose that the rental company changes its policy to "Under 18", so that all drivers under 18 match for the Under Age rule. In many production environments, the developer would need to modify the application, recompile, and then redeploy the application. Using Oracle Business Rules, this process can be simplified.
Oracle Business Rules allows a business analyst to change business policies that are expressed as rules, with little or no assistance from a programmer. Applications using Oracle Business Rules, called rule enabled applications, can quickly adapt to new government regulations, improvements in internal company processes, or changes in relationships between customers and suppliers."
On the face of it then, this sounds like a good way to get business users to define data transformation rules. So what's the product really like, and is it something that can be used as part of an ETL process? I had a day free on Friday and so I downloaded Application Server 10.1.3 and took a look.
Application Server 10.1.3 is only available at the time of writing for Linux x86, and so I set up a VMWare virtual machine running Centos 4.1 to use as a testbed. I installed Oracle Database 10g 10.2 to use as the database, and installed 10.1.3 (using Tim Hall's instructions) in another Oracle Home. There's a couple more configuration steps you need to run; the "Rule Author" application that you use to define and manipulate rule sets is a separate install (an .ear file), you need to define at least one user using the OC4J Admin website to work with the application, and you have to copy across a file that becomes your rule repository. Once you've got it up and running though, the web interface looks like this:
What you're connecting to here is the "Rule Author" part of Oracle Business Rules, a web application that lets you define rules, the objects that the rules will operate on and so on. The first thing I noticed here was that rules seem to be designed to work on Java classes and XML documents, not database tables, so I made a note of this and moved on.
What you do next is connect to a repository, which is a file that's either accessed via the filesystem or WebDAV. Once you've connected to a repository and defined a "dictionary" to hold your definitions, you get a web page like this:
Next up, I thought I'd start working through the User Guide and try and define some business rules. The first step then is to click on the "Definitions" tab and define the data model.
Looking through the user guide, the way the system works is that you define "Facts" which are then used as part of the rules that you define. A fact could be a customer, or a product, or a transaction, and in this version at least a fact has got to be either a java class or an XML document. Because a java class has methods and properties, you can query the property of the class to determine what customer it is, and then use the methods - such as decline order, give discount and so on - to perform the action that your rule mandates. It became pretty clear then that Oracle Business Rules isn't going to be a simple way of creating database transaction rules - certainly it's not something that it does out of the box - and so if we wanted to make this application part of our ETL suite, the way we'd accomplish this is to do something like creating Java Beans for each of our tables, expose the columns as properties, have insert, update and delete as methods, and publish these to the application to provide a means of transforming the data. Alternatively, you could publish some OWB mappings and give them a java wrapper, then have Oracle Business Rules call them - all speculation on my part but these two ways are the ways I'd approach it if the customer wanted to go further.
Because I hadn't got time to put something like this in place, I took a look through the rest of the manual to see how rules and facts get put together.
In the above "Car Rental" example, the "Driver" applying to rent a car is a fact on which a rule is to be based. The details of the driver come from a "Car Rental" java class.
Once the fact has been defined, you can then build up a rule based on it.
This is the bit that the business user is supposed to use. The idea with this is that you can build an application - a loan processing application, or a car rental application - and expose the underlying business rules via an application like this, with the idea being that the business can change the way the application works without having to delve into the java code.
Once you've defined the rule, you can then specify the conditions.
Then, when you've specified the condition, you can view the rule that you've created.
So, not bad, but it's not something you can just slot in to an ETL process and hand over to business users. For a start, it's clearly aimed at the Java / BPEL / SOA-type market, where you're building an application and you want to expose the underlying business rules for users to work with, just like BPEL exposes the underlying business process. With a bit of work you could get it to work with tables of data, as I said through building some Java classes to provide access to the data in the database, but your users are still going to have to be fairly computer literate at least in this early release, as it's a fairly "techy" interface and you quickly have to start working with something called Oracle Rule Language, a Java-style language for defining how your rules work. I think for the time being it's more of an "interesting" technology than something I'll be looking to add to my ETL toolbox, although like BPEL it's probably something we'll see surface as part of OWB in a few years time.
As an aside, this issue - giving users a friendly front-end tool for the ETL process - is also something that's being addressed in OWB "Paris". The new "Experts" feature allows you to put together an application, using Tcl and OWB mappings, for users to use for a specific ETL process.
Using an OWB "Paris" Expert, you could publish an ETL process that asks the user for some values, passes those values to a mapping, and (I think) allows you to execute conditional logic. You could see an Expert being used in this situation as well.
Just to wrap up, here's some Oracle Business Rules resources to take a look at: