Leveraging SQL Server - Custom Rule Engine

Summary

This paper defines a customized rule engine that is completely scalable, manageable and provides high end performance. It supports both static and dynamic rules that are stored in tables that are normalized for better manageability as well as few de-normalized tables for better operation. The engine is fully based on T-SQL objects for the backend and can be called through an EXE, SSIS.

Introduction

This document defines the component architecture and methodology for the custom rule engine or rule handler. The result of this approach is the efficiently managing and implementing simple/complex or static/dynamic rules that are there in every organization during various phases or development cycle. There is a minimal Microsoft SQL Server database system architecture, including software and hardware, required to achieve and maintain a baseline level of out-of-box performance for many data warehousing workloads.

Audience

The target audience for this document consists of IT planners, architects, DBAs, and SQL Server users with an experience in hardships faced while managing multiple rules to stream-line data according to various rules in their organization.

Need of the Hour

In every organization, we come across various set of conditions that need to be implemented during various stages of the ETL (Extract- transform – load) process which involves cleansing data, mapping data, filtering or deleting data and finally transforming the data according to rules determined by the business or end user.

It becomes very hard for the IT team to work with rules that are dynamic in nature and hard to manage with plug and play requirement and shorter development cycles.

The need of having a proper rule management is beneficial for both the business user and developer. Business user can plug and play with rules and analyze the impact or each rule while for IT team, it become easier to manage the ETL process with better performance, manageability, scalability and error handling of their system.

Rule Category

Static Rules

Rules that do not change over time and have static condition.

Example: All customers for a product to be above 18 years of age.

Dynamic Rules

Rules that have dynamic condition or action and take dynamic values that are user defined and passed in parameter form.

Example: If customer belongs to “NY”, then Product available {}, if “CA” then {}, etc.

Complex Rules

Rules that are aggregate in nature or can be applied after analyzing set of data based on their rank, etc.

Example: If customer has bought the same product over time, consider the latest order date only.

Pointers

1. Identify the order in which rule is to be applied.
2. Identify the stage in which rule is to be applied.
3. Identify the best suitable way to store rule category within the table. (Normalize or de-normalize)

Data Model

Resolve the many-many relationship between the Rule Master and the action master table.

Resolve Phase and source relationship as well. You can implement SCD (slowly changing dimension) for rule management.

Rule Action could be an Update/Delete for set of data. Action could be the action on certain transformation on the column of the data.

You can keep Dynamic parameter for staging table for each phase and apply action. This ensures Code reusability.

Handling Complex Rules

Not every rule can be managed through Rule tables. But, if you wish to have a dynamic framework around such rules, you can achieve them by wrapping such rules within a stored procedure and passing parameters as Phase to them and applying the logic.

Such rule based procedures/functions can be managed within a Master table.

Rule Application

So far, we have seen how to store rules within the database for various stages, data sources, conditions, actions, etc. on different tables in various databases.

But, the beauty of this architecture not only lies in storing and managing the rules but also its application. Here are the steps you need to consider for Rule application.

1. Create a user-defined stored procedure with PhaseId as one of the parameters.
2. Within the procedure, identify the sorted rules, tables, database on which the rules are to be applied.
3. Create a dynamic query using the set based loop to identify the set of data on which rules are to be applied using the Rule Master table.
4. Once the Rule, Set on which the rule is to be applied is identified, apply the action.
5. The target staging table in this phase would serve as source table for the next phase.

We have achieved the overall rule application using single procedure which can be reused for the next phase.

Managing Rules

Once we have a working model in place, we can have a C#/.NET based CRUD UI on the top of our tables to enable business user manage rules.

Impact Analysis

While applying rules on different set of data, we can maintain Audit log for the data which can serve to analyze impact of each rule and help business user to take decision before the next release.

Conclusion

Though it’s true that not all the rules can be automated but the idea behind designing this solution is to enable business user more flexibility to modify rules and analyze their impact at the same time it provides IT to cut their release cycle and provide more time for development.

Reference

• Joshua Greenberg, Ph.D. Building a Rule Engine with SQL Server
  https://technet.microsoft.com/en-us/library/aa964135%28v=sql.90%29.aspx