Leveraging SQL Server – Custom Rule Engine

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 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 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              

https://technet.microsoft.com/en-us/library/aa964135%28v=sql.90%29.aspx

CodeProject

Pathway to glory!!!

Coming from a hiatus of few months, I wish to give you an insight of what went thorough for all these months. I was travelling on a slow moving bus and it seemed at a point that I was getting used to its speed. As you all do, make yourself attuned to the environment you are in and start moving with the same speed of thoughts. You might be able to recall your college days or family gathering where in the mood/thought process of the environment impacts the direction/speed call it velocity, with which you move.

I am inclined to quote a dialogue from the Oscar winning movies "The Departed" (delivered by legendary Jack Nicolson) “I don't want to be a product of my environment. I want my environment to be a product of me. Years ago we had the church. That was only a way of saying - we had each other.... no one gives it to you. You have to take it “.Not that I have watched this movie recently, I was rather moving on a low speed bus when I encountered something which is prevalent in our day to day life.

Boarding the Bus

We have been boarding a bus every now and then, to a known destination, throughout our life. Our goal is same as our fellow passengers or they escort us to the pathway. Don’t they? The bus to our destination can be a bumpy or a joy ride, can also be slow or fast depending on how we feel about the bus. Our bus travels relative to our thoughts, it depends on whether we want to reach our destination fast or want to enjoy the ride.

We also encounter passengers who disembark as they find the mode of transportation quiet slow and take a faster conveyance. We always stand a chance in making sure that we reach there on time that’s can by trying to persuade the driver for speeding the bus .That depends on our attitude.

Choosing the right one

One can’t always get the driver seat. But making sure that the driver listens to you or you at least let him know your view point is necessary to ensure your timely arrival. You can go to an extent but that depends on the driver how he wants you to take you through. He can agree or disagree with you. Your being assertive is required. You can disembark the bus like others did which might be risky but that’s the beauty of it. It might go right or wrong but sooner or later if your destination is fixed you will reach there.

Relative motion

The reason why I found it necessary to write on this topic was cause of relative motion. I was sitting on this comfortable Air conditioned bus. Being red it signified boldness and fetched my attention. I thought the bus looks in good condition and would provide me a comfortable journey to my destination, without any bumps. Though the trip fare did baffle me at first whether to take the bus or not but it was worth paying for. I sat on a comfortable push back chair stretching my legs and started enjoying the view outside. The glasses were tinted and hence the outer view became even more pleasant.  Just when I was getting to love the environment I got a reality check. A speeding black SUV (Audi Q7) moving in the same direction overtook the bus and in no time disappeared. That was the time I started getting uncomfortable inside the bus. I didn't realize how much the speed of the bus meant to my reaching the destination. I went to the driver and requested him to speed the bus but he politely declined the request stating the speed limit for Heavy Vehicles, now there was very little I could do given the next stop being quiet far. I sat back and waited for the next stop to come so as to take a faster conveyance from there.

Opportunity

Just as I had given hope to reach on time. I encountered something which I made me write this blog. I noticed that we had hit a toll block and I requested the driver to let me get down so that I can take some other conveyance. This was an opportunity which I could hardly let go and to my surprise I saw that even the Audi (the same black one) was waiting with the other vehicles in a queue, to pay the toll and speed again. I took a cab from the toll bridge and even though it meant shelling out few more bucks I made sure that I reach my destination on time.

Sooner or later we do get in hurdles or hit roadblocks. But it’s our attitude that determines how deftly we grab the opportunity provided to us and reach our destination. How and with what difficulties we completed our journey becomes history.

Amen

Desire

The last trial gone haywire
thinking about the past was this I desired
working on my future its now or never
so low is my present , I cry for heaven
thinking is a curse but were my actions that worse
trying to re-live what I had in my dreams
One chance and I would not laugh
One goal to meet the destination i aspire
Oh! what a wonderful world where do I fit in
what a life i live in
making thing complicated was not what I want
is this my fault that how complex I am
heaven came down and mountains did fall
its just the ocean that rose this time
I will rise and will not stop
will shield my mettle with qualities I found
will make my world on my own terms now
will fight back so hard and not let this past very own sight
for this is my guidance and this is my drive
and I have been travelling since victory and would never breakdown.

Amen

Tug of War

It has been long since my last writing. I wanted to write something that touched my soul and was searching for an answer how to pass a message in an encrypted form which is well received by the target audience. In case you are flabbergasted by my choice of topic then, you are thinking of dictionary meaning of the game and not the reality of the game in your day to day life. I anticipate you all have been in a situation where you had too opposing minds to please at the same time. I have defined the same situation this time.

Story of a Rope

The rope is as important in the game as the two opposing parties involved in the game. It was the coconut trees whose leaves were once collected by the workers in early morning to earn a living. I have witnessed with my own eyes how, the women wakes up early and wander on the streets of Chennai to collect what would be dried, beaten up and cut into thin pieces before entangling into one another to form a rope. The rope which was extracted from the leaves (its creator) has a completely different role to play in our lives like the role of an anchor or bondage between two objects. The leaves or the tree from which it was generated had a role to give life, shelter and food to us and but its fruit "the rope" now would provide a bondage to the objects that are to be tied to stay in accord. In other words it brings two objects together. If you look closely in real life parents provide a life and body but this is different from role of a child who bonds them together and others throughout his journey with his parents.

This rope apart from all the positives it had; caught attention of mankind in a rather contrasting way to its true nature and they started using as an object to show their dominance and their strength over others. Caught between their acts was a poor rope.

Someone is a looser but no one breaks down

Tug of war is a funny game. It involves two parties with almost equal strength trying to pull each other through a rope (the third party) to make each other cross a centre line. This game was once an inclusion in Olympics between 1900 and 1920 but soon was scrapped from it. Each of the two opposing sides is headstrong and all they care about is winning at any cost. The poor rope feels the tension from both the sides remain strong for a reasonable period of time but sometimes when it fails to take any further stress breaks down. The same happened in mid 1990's during a Tug of war competition in a small town in Germany where 650 scouts were participating and the rope broke within 30 seconds of the start leaving 2 dead and many injured. Obviously, when the anchor will break between opposing forces just imagine how far the two bodies be thrown away and the rope too being damaged beyond repair. Even a student would be able to tell you that the tension caused within the rope when pulled beyond its threshold makes it unusable for future. Then the two parties can always repent on not being on the winning side but what they fail to learn is someone was broken beyond repair during their tussle.

Conclusion

There were talks revolving on possible inclusion of the game in near future again in Olympics. But as I end my blog I wish the organising committee makes sure that the rope can withstand more tension than that applied during competition.

I would say that Tug of War is not the ultimate solution for the test of strength. It’s closer to being at war than having a constructive debate. If one needs to test their strength and importance over other there are more creative ways of doing that than simply creating tension within the rope.

A bond should always be treated with care and respect it’s not a way to find out dominance over other but a connection between them. Too much tussle has always ruptured it.

Amen

Just a thought

It all started by Watergates and soon a new way was devised to keep making money. The new way is the corporate, political clout over the denizens. These corporate or dealers strike the cord with the govt. of the countries and provide them more and more access to countries resources at higher price to their own citizen and in return share cut with those who help them “The Government” and thus making sure that their work is not hindered.

How do the government make money to deal with corporate of other countries and government?  Tax collections. But, what they missed this time is that you can fool few person most of the time but not all people all the time.

If you dig deep into the various deals between countries and countries, corporate - countries or countries-corporate-countries, you would find the common mechanism government lobbying on behalf of countries, multi million dollar deals and no effective result. The only result we find is people involved in these deals make a handsome cut for them. Since, this money is concealed routed through tax heaven to Swiss banks. The implications of these are as simple on can get.

1. Inflation shooting up.

2. Increased tax.

3. Low saving and more debt.

4. Low wages for government employees.

5. Increased corruption at lower level.

6. High level of stress and frustration.

7. Increased Unrest in the society.

Though, the British left once the World War II left their country in ruins. They had looted the countries and slowly and slowly withdrew their clout from the countries. The Independence of India not just opened a new era to us. But also to the power hungry people and that was "Divide and Rule". They gave us something that was necessary for them to survive.

 1. Divide the affluent class from ambitious class by what was called the "Licence Raj"

 2. Divide the low class into different caste so that, they remain cobwebbed in smaller issues.

 3. Divide the various clauses in constitution to accommodate the political will and use the same against those who raise their voice.

If you see in any case where a broker is involved prices are bound to go up of any deal and not only that the looser is from whom money has been extracted. The broker keeps a cut from both the parties and thus inflating the price. You will notice that at most of the places government acts a mediator between dealer and what is to be dealt with. Weather its a highway contract in which the state government is the mediator between the contractors. The bid goes to those who shower riches on the mediator. This has been a common nexus when it comes to different countries where the govt. lobbies for corporate though policy mechanism and in turn benefit from them.

The loot from people through corporate deals and money is not that discernible. The process though gradual ensures that those who rule are benefited at last.

Amen

Dirty Economics

The word dirty is commonly used for nasty and the nasty game that has been played by the Governments and the corporate houses our economic life is getting dirty day by day.

DNA of Selfishness

It has been ages since we started bartering (Refer DNA of Selfishness) but it wasn't enough for those who found a more easy way "BORROWING" and look where we have come. I am referring to the recent Domino effect that is cause by USA "The ONE" due to heavy debt that it owes to many countries and sister IMF and shamelessly raising the ceiling when it should have floored its Military spending years back. But nonetheless who has pointed finger at the GOD in public. I was reading an article on how Greece’s economy is turning into a mythology after trying the same within a year. Paul Ryan rightly says that "Borrowing and spending is not the way to prosperity". Rightly so, how can a country keep on borrowing the money and with the GDP growth that they have still finds themselves in the debt this implicates their excess spending and I am also not astonished to see IMF second their deeds now and then. There is a wise quote by George Bernard Shaw that “A government that robs Peter to pay Paul can always depend on the support of Paul”.

The story about mother visiting his son

I had a great impact on a story I came across during my schooldays in which a mother fills with pride to see her son afford a house, car, TV and maintains a lifestyle that she always dreamt of during her visit in summer. She is shocked to see in the next visit her son's condition turned upside down and finds out that it was all due to his incapability to pay back the loan he had owed to banks. This story still holds good to not only the people but also the countries. Today, even the newborn kids find themselves in debt for no good reason due to heavy borrowing by the countries to keep up with the word “development”.

If you dig deep into the market you would find that not only has the credit cards but also the banks played a role in distorting the balanced economy that it was and people unknowingly started affording the lifestyle they never had and found themselves on roads after their house of wax catches fire.

What can change and what not

With the way economy is going, I think the only way to maintain a balance is to limit our spending and clearly differentiate between our needs and desire. If we; at our level, can realize the importance of this truth our child will not face a crippled economy around the globe. If you have noticed that even after raising the interest rates so many times RBI finds it difficult to control inflation due to their inability to curb the companies from distributing Credit cards and consumers from using them. Surely, the liquidity has been arrested but not the spending for there are methods devised in the system to overcome the hurdle of money.

Nostradamus

The seer in one of his quatrain predicted that gold and silver would start rising and the scrip and the bond will be wiped out. And with the way we are moving I don’t see in this predication going wrong as Gold and Silver hold a material value and is limited in the nature unlike the paper currency or bonds that have been ever increasing every time they start losing their value. So at time when even 10p was enough 10k isn’t.

Well we all know that it has always been about knowing our limit and work accordingly. I would only like to say that knowing the need is the purest approach we can have. If we rely on borrowing it will always come back and haunt us sooner or later. So closing my blog with what Einstein once said "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe". Let’s wait and watch.

Amen

Being of the Third Kind

War and Peace

I woke up to the news of China executing the suspected people behind the recent bombing. I was left agog that without a concrete proof how can govt. order to execute someone when in other part of the world a criminal with all the reasons to be censured is still trying to get absolved. Anyway, I don’t want to go deep into what is justified. My focus would be on what I think of War and Peace (Trust me, I haven't read Tolstoy). I also don’t want you to go deep within your soul and get in war with your thought to get peace. I just thought it would be good to categorize those who are at War with themselves and don’t like to settle it down and others who make peace with them and seek company of others to share with.  

Love or Hate

I feel that “hatred is too heavy too atomic to be distributed and love is too voluminous to get exhausted while sharing”. This blog of mine is devoted to those who like being hated and those who like being loved. I am sure the latter would find out time to read all what I have written as the other kind would be busy raising a cocoon around themselves. You might have noticed how our attitude changes when we are happy or sad. We can assume here that situation plays a vital part in deriving energy, other than Food and Light. In the same way the penchant to grow is derived by some through love and others derive it through hatred. Some set benchmarks to defeat those he hate or some like being involved with loved ones and work more efficiently in their company. There might be some in here who might feel anxious to work alone and there might be some who like being hated because they fear love.

There might be some other kinds as well but why focus on them when we have the most unique two kinds:

1. Those that like being loved and share happiness with their loved ones

2. Those who like being hated and don’t bother to give a thought what others think of them. 

Why others like being hated

It is as inconceivable to me of someone you haven’t came across who likes being in cocoon as is with someone who is not jovial and easygoing. But have you ever tried to find the difference between the two. A very b'ful quote by Kurt Tucholsky a German satirist and poet describes "Those who hate most fervently must have once loved deeply; those who want to deny the world must have once embraced what they now set on fire.” and that is according to me very true. Some people in the early stage of their life where in they loved someone deeply must have lost them and started to get aloof from the social gathering and try not to get attached emotionally for the fear of losing someone again or being hurt. But eventually it’s the way you take life you would find few others who have been in the same situation but who embraced the world even more with a thought that "Life is short and happiness is true when shared with others".

Why some love and grow

For those who look at a glass half full. M K Gandhi said "Where there is love there is life." and those are the people who become alive in others company and make their surrounding alive with their love so has Henry Drummond an evangelist described that "To love abundantly is to live abundantly, and to love forever is to live forever." So wherever you see people cheerful and smiling without a doubt they would be the people who feed on the love of others and are being loved as they distribute the wisdom of love and happiness and that’s what give them the strength to grow.

Who is in emotional imbalance?

I don’t want to be judgmental about the 2nd kind as everyone has a path to follow and if someone around you is going through emotional turmoil and has reserved himself let it be for once he is out of his hibernation. He will be lot like you and even more positive in his approach as his war would end with Peace.         

To sum it up there are more b’ful quotes on Love then those for hate. In the same way there is more life around then there is misery and that’s why the world is moving and so should we.

Amen