Click here to Skip to main content
16,015,991 members
Articles / Database Development / SQL Server
Tip/Trick

Leveraging SQL Server - Custom Rule Engine

Rate me:
Please Sign up or sign in to vote.
2.33/5 (5 votes)
6 Mar 2015CPOL4 min read 34.9K   119   4   12
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionshare code samples Pin
abhi_sne24-Aug-18 1:08
abhi_sne24-Aug-18 1:08 
QuestionCan you please share any example code you have ? Pin
Aarkay Reddy Tekula16-Sep-16 9:00
Aarkay Reddy Tekula16-Sep-16 9:00 
GeneralRe: Can you please share any example code you have ? Pin
karan vidyut23-Nov-16 22:57
karan vidyut23-Nov-16 22:57 
QuestionIS there code to download? I don't see it. Pin
Member 1152290613-Mar-15 5:44
Member 1152290613-Mar-15 5:44 
AnswerRe: IS there code to download? I don't see it. Pin
Pareek Ankit13-Mar-15 6:23
Pareek Ankit13-Mar-15 6:23 
GeneralRe: IS there code to download? I don't see it. Pin
Member 1152290613-Mar-15 6:33
Member 1152290613-Mar-15 6:33 
GeneralRe: IS there code to download? I don't see it. Pin
Pareek Ankit13-Mar-15 6:40
Pareek Ankit13-Mar-15 6:40 
GeneralRe: IS there code to download? I don't see it. Pin
Member 1152290613-Mar-15 6:48
Member 1152290613-Mar-15 6:48 
GeneralRe: IS there code to download? I don't see it. Pin
Pareek Ankit16-Mar-15 5:24
Pareek Ankit16-Mar-15 5:24 
GeneralRe: IS there code to download? I don't see it. Pin
Member 1151391523-Jul-15 22:53
Member 1151391523-Jul-15 22:53 
QuestionDo you have the UI for this?, if yes please do share Pin
SILU SAMAL11-Mar-15 10:24
SILU SAMAL11-Mar-15 10:24 
AnswerRe: Do you have the UI for this?, if yes please do share Pin
Pareek Ankit13-Mar-15 6:26
Pareek Ankit13-Mar-15 6:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.