While working on any Data Warehouse related project one of the things it's sure you're going to bounce on are Business Rules. Data Warehouses are build for Decision Support, and those decisions are based on indicators calculated based on a multitude of Business Rules, provided, surprisingly, by the business people from within your company.
Based on this logic, Business Rules are very important, and therefore is also very important how they are actually implemented at Data Warehouse level. Here we have the following likely scenario:
1. Your company business evolves and the business people realize that they need to define a new set of business rules which will provide them a better level of granularity which will help them make better decision. Then follows a number of never-ending meetings, but, at the end, everyone agrees on them and now it's time to apply them at Data Warehouse level. And yes, they want to have these rules applied yesterday, because, you know the drill, it's business critical
2. But wait, they cannot apply them by themselves, they need the BI Department to do it, since they are the ones which have created and are maintaining the Company's Data Warehouse. So, the business people are contacting the BI Department, which will dispatch a number of BI functional consultants that will analyze their business requirements and business rules and come up with a Functional BI specification. Which does take some time.
3. Now the Functional specification reaches the BI Developers, which will analyze it and come up with a Rough Technical Analysis based on which a first estimation for the development time is made. This estimation is sent back to the BI Functional Consultants, which will evaluate the response and contact the Business Users. In an ideal world, the Business Users will agree with the estimation, and we will go ahead with the Detailed Technical Analysis and the Development.
And when the development is finally started, and the Business Users are hoping for the light at the end of the tunnel, then the problems start to arise. The BI department announces them that the business rules that they've so meticulously defined are not very compatible with the ones already implemented, and they'll need significant additional time to redesign the whole solution. Plus some of their business rules are overlapping and they must be redefined. And this is when they suspect that the light is in fact the train rushing in.
4. The Business Users will start to get upset, then very upset, and they will perceive the BI department as the bottleneck. At the end, they've done all the hard work of identifying the business rules, so why is just translating them into code so much complicated? I guess, it's all related to the fact that, when you don't know exactly what the other team is doing, you suspect that, secretly, they don't really do that much work for you, and that they spend that time working on something else. And this is when they don't play WoW or spend the whole day on Facebook, of course.
5. On the other side, the BI developers will try to adapt the existing Data Warehouse architecture in order to cope with the new Business Rules, ideally, without having the whole system crushing down. And when they've just finished the first version, and send everything for testing, the Business Users realize that their Business Rules don't work quite as expected, and start to modify them. From slightly to heavily, depends on the luck. But wait, sais the BI developer, those guys are Business People, shouldn’t they know stuff like this? How is it possible to change their mind so often?
6. And this is how you get everyone very frustrated until you reach the end result
Now, how familiar does this scenario sound? I'll venture to say, very. In fact, there are a lot of things to be learned from this scenario. The first thing is that no one is perfect. Neither you nor I are born with a list of instructions on how to do everything in life. As children’s, we learn thru trial and error. As adults, no matter how much we try to mentally simulate various scenarios, we will still be subjected to learning by trial and error. And the whole point that this is the Natural way to do things. Therefore, why should IT projects be different?
If there is something wrong with the above scenario is the fact that is Rigid. And, IT systems have this tendency to be rigid and to reluctantly accept changes. Only that, in real life, it really helps to be Flexible. Just think a bit what happens with buildings in an earthquake: the buildings which are the most rigid, which don't allow for a certain degree of flexing, are more likely to come down then the ones which are designed with a certain degree of flexibility in mind.
And this is where the concept of Business Rule Engine comes into picture. Business Rules Engines applications have been created in order to bring back control over the Business Rules to the Business people. Just because you don't know at first exactly what you want to get. Sometimes you really need to play with the data to finally understand what you really want. By using such applications, Business Users will be able to define, run the business rules and see the final result, with minimal or no assistance from the IT department.
A Business Rule Engine is a software application which includes the following main components:
1. A graphical interface where they can visualize the existing Business Rules, edit them and create new ones, using a friendly and intuitive interface
2. A Central Business Rules Repository, which will store all the Business Rules Defined
3. An Engine, which will apply a set of Business Rules on a given set of data
Business Rule Engines (BRE) can be integrated with a lot of applications, but on a Data Warehouse they will be particularly useful, since you will have a lot of very different Business Rule types, which you need to handle. Integrating a BRE into you Data Warehouse is an essential step in building a flexible system which is able to easily handle New Business Requirements with little or no assistance from BI Department / BI developer:
1. Without a BRE, implementing a Business Rule will involve at some point a BI developer, no matter the architecture implemented. Either if the Business Rules are implemented into the ETL solution or into Stored Procedures, you still need a developer to modify them. If we are talking on Rules applied on Stored Procedures, they are usually implemented as long, nested CASE structures. And this approach has ALOT of disadvantages:
a. It's very difficult to detect if there is any form of overlapping between Business Rules, when you have long CASE structures which stretch over pages of code
b. The Business Users lose visibility over the actual Business Rules applied at Data Warehouse level. Sure, there is a Functional Specification containing all the rules, ideally up to date, but you can have situations where there is a difference between the way in which the Business Rule is defined for Business point of view, and it's technical implementation. And this why the way in which the Business Rules are being applied is often perceived as a Black Box by the users
c. From developer point of view, it's really a bad practice to hard-code Business Rules into the Application Code, no matter if we are talking of Stored Procedures or ASP.net applications. Mainly because their place shouldn’t really be there. It makes the code very rigid, hard to understand, and dangerous to modify, if you really don't get the big picture. And, trust me, that am a tough challenge for anyone, when that BIG Picture involves reading a lot of code pages containing long CASE structures.
d. If you are unlucky (or lucky) enough, you'll receive as a developer, a business request which cannot be solved in the classical way. Just image implementing a single new type of rule which involves a few thousands of individual rules. I've been there, so trust me on that, this is real life scenario
2. Now, that I’ve made a case for the Business Rule Engines, let's go a step further and see which are the options available:
a. There are quite a few vendors of Business Rule Engines solutions. As a BI department, i think is necessary to present the Business Users the advantages of implementing such a solution and obtain their support, and a hefty budget for this operation. Based on this, you can go fishing for the best one for your company needs. And, hopefully, everyone lived happily ever after.
b. If you're on a tight schedule and even a tighter budget, you could venture yourself and build a customized Business Rule Engine specifically suited for your Data Warehouse needs. It might not be as generic, or as pretty, as an off the shelf application, but it will do the job. Plus it will prepare your Data Warehouse System for implementing an Off the Shelf BRE solution.
i. First things first, you should Start a Business Rules Inventory, at Data Warehouse level, of all Business Rules hard-coded on your stored procedures (or ETL). For each Data Mart for your Data Warehouse you can proceed with the creation of the Classical Excel file, where you will have:
1. One Worksheet for each Type of Business Rule
2. One row for each individual rule belonging to a certain type
3. You'll have to determine which type of rules you intend to externalize as an individual Business Rule and which one you intend to keep. Based on this logic, you might decide you want to keep in your existing code the conditions from your WHERE clause, and externalize just the long CASE structure, where most of the complexity resides. At the end, you'll end up with 3 types of business rules for UPDATE / DELETE / INSERT operations
4. As a general rule, the header of each worksheet should include the maximum number of fields that will be included in the rule definition. Typically, they will be divided between Source Fields and Destination fields.
5. The Source Fields are the ones which actually form the rule. They can be grouped into the following type of fields:
a. FIELD_NAME&VALUE - Contains the Field Name and the actual value
b. FIELD_OPERATOR - Contains all standard SQL operators: '=','<>','IN','NOT IN','LIKE','NOT LIKE' (ex. CITY = 'BUCHAREST')
c. FIELD_FORMULA (optional) - useful when you want to apply special transformations to the field (ex. DAY(ACCOUNTING_DATE)=1)
6. The Destination field is the actual value that needs to be calculated for a certain rule. Used for UPDATE and INSERT type of rules
7. If you need to have an OR between different fields, make sure you split the rule into different rules. Ideally, within a single row, all conditions on fields should combine with AND. If you need to apply that rule for a given set of values on a certain field, don't forget about the usage of the IN, NOT IN for FIELD_OPERATOR
8. For complex Business Rules, you can implement a PARENT / CHILD relationship. For example, on an UPDATE type of rule, the PARENT rules are stand alone rules (have a Destination field), while the CHILD rules don't make sense without a parent (don't have a Destination field). This type of rules are particularly useful when you have a generic rule, that you'll want to apply in all the scenarios which correspond to the FIELD_NAME&VALUE, except a few scenarios, which are listed as CHILD rules
ii. Now, that you've finished the Inventory and created the Excel files, you've ended up with a primitive user interface that the users are much acquainted with. It might not be the prettiest or the most versatile user interface, but it will do the job. Further on the road, if your company allocates the resources for it, you should build a pretty cheap ASP.net application, which will allow the users to define both new rules and new type of rules. In this way, you'll also have the big advantage of having all sorts of validations, like for overlapping rules, each time the users try to save a rule.
iii. The next step is to Design an Business Rule Import Component that is able to import data from the Excel files / Business Rule application, and transform them in a format that can be processed at Data Warehouse level
iv. Further on, the most complex step from Technical point of view is to Build the Engine part of the Business Rule Engine. In here you'll have to create a Stored Procedure or a generic ETL component which will take the imported business rules, and apply them on a designated destination table. And the key word here is Generic. It will not be easy, but the whole point is that it can be done. And it will provide a solution to all those serious problems.
The whole point of having a Business Rule engine is to have a reusable component, which would:
1. Require minimal involvement of the BI developer only when new types of rules are being created, and then only for parameterization work:
a. Mapping between Source Rule Table and Destination table
b. Mapping between Source Rule Fields and Destination table fields
2. Require no BI developer involvement when new rules are added to existing rule types
v. The very last step is to add the call of the Business Rule Engine procedure within each individual Stored Procedure where Business Rules must be applied, by passing just the @NAME_OF_RULE as a parameter.
At the end at this article I would say that implementing a Business Rule Engine within you company's Data Warehouse is nothing short of a miracle cure for a lot of cronical diseases:
- It finally brings back control of the Business Rules to the business users. The users can now happily create their own business rules with absolutely no assistance, once the setup has been performed by IT at Data Warehouse level. They will be able to learn from their mistakes very quickly, by simulating various scenarios and removing the latency induced by the involvement of the BI department
- From BI Department point of view, the implementation of a Business Rule Engine streamlines the entire Data Warehouse Architecture and frees up BI developers to be used for other developments, where their creativity is really required.
- Somehow, this change has a similar impact as the introduction, back in the 60's, of the industrial robots. Initially a lot of people were against, since they feared that they'll lose their job, but, at the end, it freed up a lot of people from doing repetitive and annoying work and really pushed them to be creative. And this is called PROGRESS.
And now, back to you: How have you handled the implementation of Business Rules within your company's Data Warehouse? Do you use a custom build Business Rule Engine or an off the Shelf Solution? Which were the main challenges associated to implementing such a tool?
Subscribe