Partager via

Simple example of using BizTalk Business Rules Engine (BRE) with Database

  • Article

Folks, I was recently building a proof of a rules based BizTalk Application for a customer where the specific requirement was to be able to change the rules in a simplistic way so that Business Users could update the rules without having to go through the gory details of undeploy, version, publish & redeployment process that comes with the Business Rules Composer. When I enquired into the details of what they exactly meant by Business Users changing the rules as:

a) Organizations often land in trouble when they empower employees to do that as there can be no accountability of who changes what.

b) BRE is not simple enough for the non-IT folks to directly change the rules and test & deploy the new set of rules.

Upon deeper discussions it was revealed that they would like the Business Users to change the data of the rule set and not so much the rules themselves (e.g. the BAs of the company should be able to change the CreditLimit of an Order which determines if it needs approval from a Senior Manager or not). This is something that can be easily done with BizTalk BRE as you can get the Rules Engine to lookup the data from the underlying Database and have a simpler role-based web GUI (.NET) for them to change the data if required. Actually, one should almost always store the data of the BRE in the Database (there has to be a very good reason to justify why not) as it separates the Data from the Rules in just about the same way as BRE separates the Rules from the Orchestration. So here is a simple Tutorial style walkthrough (a BizTalk BRE 101) of the steps that I'm reconstructing for the benefit of a new starter in the BizTalk land..

  • Create a new Biztalk project and call it 'CreditCheck' and setup the strong name in the assembly and call the Application name 'CreditCheck' as well (in the Deployment Configuration).
  • Create a new schema (right click the project Add -> New Item -> Schema Files) and call it CustomerCheck . xsd
  • Rename the Root Node name to Customer.
  • Right click the Customer node -> Insert Schema Node -> Child Field Element and call it CustomerID with the type (xs:int). Repeat this to create CustomerStatus with the default type (xs:string), Amount with the type (xs:unsignedInt) and isApproved with type as (xs:boolean). Click on the Save All.
  • Create a DB on the SQL Server and lets call it 'Customer' and lets create Create a simple SQL table, CreditLimit, which checks the CreditLimit that each type of existing customer has based on their status (these are typically stored in your Siebel or any other CRM tables) e.g. for a Platinum customer (who's doing an almighty lot of existing business with your company), you'd automatically like approve a fairly high amount (lets say $500K). For a Gold customer (where you do a lot of business but not that much), you'd keep that to $100K and for Silver $50K. Lets also say for new customers, who are not classified as one of the 'P','G' or 'S' in our systems yet, we classify them as 'N' for New in our CrediCheckLimit table and keep set their limit to $5K: 

CREATE TABLE [dbo].[CreditLimit](
[CustomerStatus] [varchar](1) NOT NULL,
[CreditLimit] [numeric](18, 0) NOT NULL,
[LastUpdatedBy] [varchar](10) NOT NULL,
[LastUpdated] [datetime] NOT NULL
) ON [PRIMARY]

Insert into CreditLimit VALUES ('P', 500000, 'RAHUL', GetDate()) -- Platinum
Insert into CreditLimit VALUES ('G', 100000, 'RAHUL', GetDate()) -- Gold
Insert into CreditLimit VALUES ('S', 50000, 'RAHUL', GetDate()) -- Silver
Insert into CreditLimit VALUES ('N', 5000, 'RAHUL', GetDate()) -- New

  • Fire up the Business Rules Composer, In the Facts Explorer window, right click on Vocabulary -> Add New Vocabulary and call it CreditCheck. Right click on (Version 1.0 not saved) and 'Add new Definition' click on the last radio button 'Database Table or Column' put 'CustomerType' in the Definition name, click on the Browse button ( select Windows authentication for convenience ) select the CreditLimit table and CustomerStatus column . Select the 'Perform Get operation' radio button and click Finish. Repeat the steps for CreditLimit (Right click on (version 1.0 not saved) and 'Add New Definition' click on the last radio button 'Database Table or Column' put 'CreditLimit' in the Definition name, click on the Browse button (select Windows authentication for convenience) select the CreditLimit table and CreditLimit column. Select the 'Perform Get operation' radio button and click Finish). Repeat the steps for Amount (Right click on (version 1.0 not saved) and 'Add New Definition' click on the last radio button 'XML Document Element or Attribute' put 'Amount' in the Definition name, click on the Browse button (select your CustomerCrediCheck  schema) and Select the Amount field in the schema  Select the 'Perform Get operation' radio button  and click Finish). Repeat the steps for CustomerStatus(Right click on (version 1.0 not saved) and 'Add New Definition' click on the last radio button 'XML Document Element or Attribute' put CustomerStatus in the Definition name, click on the Browse button (select your CustomerCrediCheck schema) and Select the CustomerStatus field in the schema  Select the 'Perform Get operation' radio button  and click Finish). Repeat the steps for Approved (Right click on (version 1.0 not saved) and 'Add New Definition' click on the last radio button 'XML Document Element or Attribute' put Approved in the Definition name, click on the Browse button (select your CustomerCrediCheck schema) and Select the isApproved field in the schema  Select the 'Perform Set operation' radio button  and go Next and Finish. 
  • Now you've the Vocabulary in place, right click on the 'version 1.0 not saved' and publish.
  • Go to the Policies Explorer right click on 'Policies' and 'Add New Policy' call it CreditPolicy. Right click on Version 1.0 Not Saved -> Add New Rule and call it 'ApproveCredit'. Right Click on the ConditiConditions text and ons text on the IF pane and select the Predicates 'Equal'. Add the 2 fields from the Vocabulary so it reads: IF CustomerStatus Equals CustomerType. Go up again on the Conditions text and 'Add Logical AND' . Then Add another argument (Right Click on the AND text and select the Predicates 'Less than Equal to' and add the fields Amount & CreditLimit). Next in the Actions pane drag & drop the Approved to True so the whole rule reads like):

IF

Conditions

AND

CutomerStatus is equal to CustomerType

Amount is less than or equal to CreditLimit

Actions

Approved = True

  • You should repeat this to create another Rule in the same Policy called 'DenyCredit' which reads like this:

IF

Conditions

AND

CutomerStatus is equal to CustomerType

Amount is greater than CreditLimit

Actions

Approved = False

  • You can now Test this policy br creating instances of the input Messgae & DB (right click on version 1.0 and select Test Policy). Once you're happy with your results just right click on the Version 1.0 - Publish & Deploy
  • Go back to your Visual Studio and add right click the project Add -> New Item -> Orcehstration and call it CC.odx
  • On the project reference the following 3 DLLs: System.Transactions, System.Data & Microsoft.RuleEngine.dll (right click the Reference -> add Reference and click on the System.Transactions & System.Data on the .NET pane and do the same for RuleEngine.dll (you may have to go to the Browse pane and select -> C:\Program Files\Common Files\Microsoft BizTalk Server\ OR C:\Program Files\Microsoft BizTalk Server\). You'll need these to get the Data Connection for the Rules Engine.
  • Drop a Receive Port RP_CreditCheck of type RPT_CreditCheck and Always Receive message on this port
  • Drop a Receive shape. set the activate to true.
  • On the orchestration pane on the studio, right click on the Msg -> New Message -> MsgCreditCheck of type CustomerCreditCheck.xsd
  • Assign the message to the receive shape and connect it with the port. Click on the empty space in the orchestration and make the transaction type as long running.
  • Now drag a scope shape from the toolbox and set this scope shape transaction type as Atomic. Call the name of the shape as 'CallRulesEngine'
  • We now need to set up a connection string to pass to the Rules Engine. In the CallRulesEngine scope, create 2 new variables:

SQLConn of type System.Data.SqlClient.SqlConnection and RulesDataConn of type Microsoft.RuleEngine.DataConnection (as you added these in the references earlier)

  • In the scope shape, drag and drop an Expression shape and chuck the following code in:

SQLConn = new System.Data.SqlClient.SqlConnection("Initial Catalog=Customer;Data Source=(local);Integrated Security=SSPI;");
RulesDataConn = new Microsoft.RuleEngine.DataConnection ("Customer", "CreditLimit", SQLConn);

  • Just below the Expression shape, drag the CallRules shape and configure it to use CreditPolicy. You will get the 2 parameters automatically - the MsgCreditCheck & RulesDataConn so configure them and click OK.
  • Drop a Send shape from the tool box below the Scope and configure it to send the MsgCreditCheck
  • Drop a send port SP_CreditCheck of type SPT_CreditCheck and select Always Send messages on this port
  • Right Click the project and click Deploy and then configure the Send & Receive ports from the BizTalk Admin Console and test your messages & the Rules Engine!

The good thing about separating the Rules Data from the Rules Policy is that now, if you really wanted, you could expose the data through another GUI (e.g. a role based application that allows you to modify the credit limit for a particular type of customer - so for instance you are an Enterprise Sales Manager responsible for all the Sales Management for large accounts - you could modify credit limits for all kinds of accounts as opposed to a Small Business Sales Manager who could modify credit limit for only Silver customers and you can set the limits to which they can change - all independently of the deployed Rules). This strategy allows flexibility of changing the rules data with a better user experience for Business Users without necessarily changing the rules themselves. Let me know what you think about this.

Comments

  • Anonymous
    May 05, 2009
    Hello, this is a great example.  I am trying something similar.  Here is my scenario.  I am using biztalk 2009 with sql 2005.  I want to read table A and based on values in certain columns, run the business rule and insert a new record in table B.  Can I use the above method you have described to insert a new record?  I will be working with atleast 100K records.  Is the route above, if it can actually insert a record, best in the scenario where there are so many records?  Thank you very much for your help.

  • Anonymous
    May 05, 2009
    Yes, what I would do is use the DataConnection vocabularies (as described above to read table A columns) and create the rules  that will update the relevant fields in the XML schema and then have the SQL adapter to insert the rows in table B from the Updated Schema.

  • Anonymous
    June 30, 2009
    Thanks for the article. VERY helpful overview/example of what BRE can do. My biggest concern about this thing is the way we specify the data source in the orchestration. There has to be a better way of doing this. Why wouldn't there be the ability to link the Call Rules to a send/receive port? We're pretty much hard-coding the data source into the orchestration, instead of keeping the physical abstraction that the orchestration strives to provide. Any suggestions?

  • Anonymous
    June 30, 2009
    Interesting point.. if you are actually not interested in calling Rules from Orchestration, you can still invoke the BRE from the code itself using the APIs..

  • Anonymous
    December 13, 2009
    Wonderfull and very usefull article. I followed all instructions but in VS 2008, BTS2009, when I try to configure  CallRules shape , after the expression shape, I don,t get 2 parameteres as indicated  "the MsgCreditCheck & RulesDataConn". I have only RulesDataConn. Apart form that, the Salconn variable, declaqred but not used which I think this will not allow BRE to function. Can you please let me know what I am missing. I would like ald so to indicate that, 1st testing the policy from within BR composer works fine. 2nd, In spite of that I compiled the project, deployed it, droped an instance, just went to output difrectoy without of course policy applied and no error was generated. Thanks in advance for your help

  • Anonymous
    March 22, 2010
    Hi Is this Table create in Rules Engine Database or ... Can you let me know how to create lookuptable in RulesEngine

  • Anonymous
    June 02, 2010
    Excelente article!! Congratulations!!! @BizAus - The table is created in your DataBase server!!

  • Anonymous
    June 02, 2010
    Excelente article!! Congratulations!!! @BizAus - The table is created in your DataBase server!!

  • Anonymous
    June 07, 2010
    Thanks for the article. I do have a question: how would this adapt to a scenario in which the input value just has to be in a range of values?  My situation is that I'm dealing with data that just has to be IN(x, y, z, ...).  So, for instance, in SQL I would do "If @variable IN(SELECT Column FROM Table WHERE Condtions) Do X"  How would I do the same thing in the BRE?

  • Anonymous
    June 08, 2010
    Very interesting question Allen - let me think about this and get back to you (it looks do-able with the OR clause and the 'Range' or 'Between' predicates but I'll have to test that out)

  • Anonymous
    September 23, 2010
    I have tried this application, but whatever the incoming message you placing in receive location the same message is coming as outgoing message. but it suppose to update IsApproved field in the out going message. please check the following incoming messages. <ns0:Customer xmlns:ns0="http://CreditCheck.CustomerCheck">  <CustomerID>1658</CustomerID>  <CustomerStatus>P</CustomerStatus>  <Amount>35800</Amount>  <IsApproved>false</IsApproved> </ns0:Customer> in the outgoing message, IsApproved field must be updated. but it is not happening. please help me Thanks, Shekhar

  • Anonymous
    September 23, 2010
    Check if you have deployed the latest rules. If you forget to deploy them, it'll take the previous version of deployed rules. You should also test the rules independently and make sure they are firing as they should.

  • Anonymous
    October 26, 2010
    I have the same problem that Salam had. So, I am posting the same issue: "I followed all instructions but in VS 2008, BTS2009, when I try to configure  CallRules shape , after the expression shape, I don't get 2 parameteres as indicated  "the MsgCreditCheck & RulesDataConn". I have only RulesDataConn. Apart form that, the Sqlconn variable, declared but not used. Thanks in advance for your help"

  • Anonymous
    October 26, 2010
    If you have not deployed the rules, it would not come up whilst configuring

  • Anonymous
    October 26, 2010
    Thanks for the prompt response. I have deployed both the rules. Both CreditCheck vocabulary and CreditPolicy policy show "Version 1.0 - Published". Did you test it with VS 2008/BizTalk 2009?

  • Anonymous
    October 26, 2010
    Hey Prasad, Also deploy them (not just publish).

  • Anonymous
    November 09, 2010
    The comment has been removed

  • Anonymous
    December 02, 2010
    Hi Salam, Prasad Here we are creating Vocabularies. INSTEAD what we can do is... In Facts Explorer.. Browse for XML Schemas( Schema that we have created i.e. CustomerCheck.xsd) and Databases (Browse to Customer-> Credit Limit). Create Rules as created before by using Schema and DB. Test and Deploy it. Now try to call this Policy from CallRules. It will show both the Parameters. @rgarg Very informative Article.. I also have a doubt ...Why to create Vocabularies if we can do it without creating Vocabularies.(like I explained above)? Thanks, Kunal (6thsense.cse@gmail.com )

  • Anonymous
    December 03, 2010
    By giving the example is very good.while deploying the applications showing the error. but doing everything but i am getting that a non-serializable object type 'System.Data.SqlClient.SqlConnection sqlcon' can only be declared within an atomic scope or service a  non-serializable object type 'Microsoft.RuleEngine.DataConnection rulecon' can only be declared within an atomic scope or service.

  • Anonymous
    May 15, 2011
    Hi Rahulgarg, Its really a wonderfull articall and usefull to so meny people. Thanks for the post. All The Best

  • Anonymous
    March 06, 2012
    For the two errors, "Error 3 a non-serializable object type 'System.Data.SqlClient.SqlConnection SQLConn' can only be declared within an atomic scope or service Error 4 a non-serializable object type 'Microsoft.RuleEngine.DataConnection  RulesDataConn'can only be declared within an atomic scope or service ", drag your 2 variables RulesDataConn and SQLConn into CallRulesEngine->Variables in the Orchestration View.  

  • Anonymous
    May 15, 2012
    The comment has been removed

  • Anonymous
    October 03, 2013
    Thank you for filling the void of the sparse and incomprehensible documentation on this aspect of BRE.

  • Anonymous
    May 20, 2014
    THis is a superb walkthrough. But, I am facing one issue. while testing the policy in BRE by adding an instance for the xml schema. It is not working as expected. It is not checking the conditions provided in ApprovalRule or DenyRule. Hope to get some help. Regards

  • Anonymous
    August 11, 2014
    good one thanks, its possible to inserting data in to the table using BRE?

  • Anonymous
    November 29, 2016
    Awesome...I had forgotten all about using the BRE as a database lookup provider in such a manner.

  • Anonymous
    February 27, 2018
    Hi Rahul,Thank you for nice example , i have problem with "CallRules shape", when i configuring rule set i am able to see only one Parameter i.e MsgCreditCheck, i am unable to find "RulesDataConn" parameter in configuration. Need suggestion how to reslove this issue.

    • Anonymous
      April 01, 2018
      Must be a cache issue, delete it and try again