Business Rules and ETL
As has been mentioned by others, a growing architecture is to put more business rules in a Business Layer, rather than simply relying on the database alone. A number of system requirements are driving this technique:
- The growth of NoSQL
- The growth of distributed SQL
- The growth of not always connected applications performing data synchronization
- Support of multiple data engines in a third party product
- The growth of Stateless applications and services (ie. Web)
Each of these system requirements do not apply to every application purchased or developed in house. As a result, I find myself once again saying, “The correct implementation is not fixed.” The answer depends on a number of factors, and the requirements of the system as a whole.
ETL (Enterprise Transform and Load) is an area that is often overlooked with implementing business rules.
If all your business rules are in stored procedures, then bringing data into your application must go through your stored procedures in order to be validated…or all the business rules in your stored procedures must be duplicated in your ETL process.
I worked on an auction system that had 100% of the business rules written in stored procedures. It was blindingly quick. However, when we started to convert data from other auction systems into our new database, we ended up importing data through a robot executing the web interface, because it was too complicated to write the import using the stored procedures directly, or duplicate all the business logic they contained.
Conversely, having business rules encapsulated in a middle tier does not directly lend itself to working with current ETL tools. ETL tools tend to like working with data stores…not data objects. That’s not to say that they can’t work with a Business Object layer…they just tend to be designed to working with data sources more readily.
One advantage of having your business rules in a central repository, such as a business logic tier, is that those rules may be applied to any data store. For applications that import and/or export data to other data stores, often outside your control, having centralized business logic allows it to be applied to all data, even if it is never physically persisted in a local relational database. However, this methodology requires a great amount of care to be successful, and is not warranted in systems that only interact with a local relational data store using only Oracle or SQL Server, etc.
Do you have other factors you’d like to share that should impact the architectural decision for Business Rules? Send your comments, questions, or expierince to me at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
The Benefits of Using Performance Dashboard
SQL Server Performance Dashboard is a set of RDLs deployed using the “custom reports” feature in Management Studio, which helps resolve common performance issues without having to execute T-SQL queries.
Featured White Paper(s)
Enterprise Content Management
The goal of an enterprise content management (ECM) system is to seamlessly connect a company’s business processes, knowledge … (read more)
Featured Script
Trigger: checkForJobFail
We do not use SQL mail on our SQL servers. So, we developed this trigger on sysjobhistory to notify us of job failures. We… (read more)