SSISsy Spacek and FICO Blaze Advisor Business Rules Engine vs Extract Transform and Load
What is a business rules engine?
A business rules engine allows an ETL team to design their data flow processes in such a way that the business rules are segregated from the ETL process.
In an ideal world, nothing would ever be hard-coded in your packages. Imagine a solution where EVERYTHING is configurable. From an ETL framework perspective we should be familiar with this concept.
What does this mean from a business rules perspective? In my opinion it means that anything in your SQL query where clause filters, case statements, and join conditions (other than surrogate keys) are candidates for business rules segregation. Many will initially cry overkill; but, having worked in large corporate environments allow me to put forth the proposition that it is far easier to submit a single SQL script to a DBA that will update a business rules configuration table than it is to make a change to an ETL package. The latter typically requires: code checkout, unit testing, data validation, peer review, code check-in, versioning, documentation, request to the DBA to deploy to QA, QA Review, QA approval, request to DBA to deploy to production and perform production pre-install analysis, and finally production post install analysis. Programming your SSIS packages in such a way that your business rules are removed from the package itself will provide a huge benefit to the ETL developer in that he or she will also be removed from the process as business rules change throughout the course of time.
When I was first introduced to the concept of a business rules engine I was asked to evaluate the potential of the FICO Blaze Advisor software to integrate with the Microsoft SQL Server Integration Services 2008 ETL processes. My initial trepidations centered around how it would be able to handle large volumes of data. Would it be able to perform set based operations or require row by rows calls out to some engine sitting on some other server? The concept from the BRE champions is that all of your organizations business rules are centrally located in a single instance for all applications.
Googling provided very few leads.
and Sandeep never shared his results… (see comments.)
Posting on the Blaze Forums bore no fruit…
To make a long story longer, this is what I think from an ETL development perspective…
Using Blaze in conjunction with your ETL process is like trying to fit a square peg in a star schema (LOL, couldn’t resist.) Couple that analogy with hitting a nail with a sledgehammer and I think I made my point.
What it gives you is a web based interface for your end users to make changes to business rules which can be used to create dynamic SQL, populate lookup tables, and even perform as a sort of lookup component in themselves. Complex business rules can be defined by the ETL developer in a variety of fashions including decision trees, matrices, case type logic, and complex “if then else” procedural logic. Designed properly, there should be no need for any future involvement from the ETL team. Pipe dream? Perhaps… but that is the concept.
I would like to see a BRE designed specifically for MS SSIS. I don’t think it would be hard to develop and it would serve a great purpose. Recently, I’ve been looking at some of the excellent SSIS components available through Pragmatic Works in their revolutionary Task Factory product. Perhaps they will be the company that brings such a product to light.
In the meantime, If you must have BRE solution that integrates with SSIS, I would suggest that you either roll your own, or download the evaluation version of Blaze Advisor. Go through the tutorials. They are a little buggy; but, you can get through them.
Finally, download the tutorial I created that goes hand in hand with the tutorials they created. I even use the same examples.
Once you get through that, explore passing an array to the Blaze engine, retrieving the results, and matching them up (via checksum I guess) w/ rows in the SSIS buffer to get better performance out of it (if you NEED it for a lookup.) Or just use it for dynamic SQL and populating small lookup tables like I did (however, I still used arrays.)
Hit me up if you get stuck. I’ll update this post as I get questions and have more time.