The child delimiter property drop-down list contains with the following common delimiters:
{CR}{LF}
{CR}
{LF}
{TAB}
{SPACE}
{0x1A}
,
;
Ref: http://msdn.microsoft.com/en-us/library/aa577936(v=BTS.70).aspx
Sunday, November 28, 2010
Simple example of using BizTalk Business Rules Engine (BRE) with Database
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()) -- PlatinumInsert into CreditLimit VALUES ('G', 100000, 'RAHUL', GetDate()) -- GoldInsert into CreditLimit VALUES ('S', 50000, 'RAHUL', GetDate()) -- SilverInsert 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.
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()) -- PlatinumInsert into CreditLimit VALUES ('G', 100000, 'RAHUL', GetDate()) -- GoldInsert into CreditLimit VALUES ('S', 50000, 'RAHUL', GetDate()) -- SilverInsert 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.
Subscribe to:
Posts (Atom)