Writers: Ankit Pareek
Published: March 2015
Applies to: SQL Server 2008 R2
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 dev –
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
Rule 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
Rule that
have dynamic condition or action and take dynamic values that are user defined
and passed in parameter form.
Example: - If
customer belong 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 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 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 parameter.
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 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 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
No comments:
Post a Comment