There are two distinct hqRule use cases.
The first is the creation and management of Rulesets. This can be accomplished using the hqRule user interface, or via direct interaction with hqRule tables. See the example code below as well as the hq Schema Reference for more info on how to do that efficiently.
The second is the exploitation of configured Rulesets to evaluate data. This activity is performed by passing data to hqRule function hq.tvf_Rule_Evaluate.
Creating & Managing Rulesets
hqRule features a simple data model that is used to define and configure Rulesets and associated objects.
There is a powerful user interface that facilitates this process (click here to test drive it). Rulesets can also be managed programmatically via direct interaction with hqRule tables. Data evaluation is just as effective either way.
Whichever option the development team chooses, under the hood Ruleset creation & configuration will generally follow these steps:
- Create a Ruleset by inserting a new row to table hq.t_Rule_Ruleset. See stored procedure hq.usp_Rule_Ruleset_Insert.
- Create Terms associated with the new Ruleset by writing new rows to table hq.t_Rule_Term. See stored procedure hq.usp_Rule_Term_Insert.
- The Primary Key (TermId) is an incrementing identity column.
- There is a Unique Index on the Token and RulesetId columns, so no two Terms can have the same Token within a given Ruleset.
- The deletion of a Ruleset will cascade to all related Terms. The deletion of a Term will throw an exception if there exist any related RuleTerms. See stored procedure hq.usp_Rule_Term_Delete.
- Create a hierarchy of Results associated with the new Ruleset by inserting new rows to table hq.t_Rule_Result. See stored procedure hq.usp_Rule_Result_Insert.
- The Primary Key (ResultId) is an incrementing identity column.
- There is a Unique Index on columns Value, ParentResultId, and RulesetId, so no two Results can have the same Value at the same hierarchy level within a given Ruleset.
- The deletion of a Ruleset will cascade to all related Results, and the deletion of a Result will cascade to all Results below it in the hierarchy, as well as to all related Rules, RuleTerms, and RuleTermValues. See stored procedure hq.usp_Rule_Result_Delete.
- A list is just a flat hierarchy: ParentResultId is NULL for all Results.
- Create Rules associated with each Result by inserting new rows to hq.t_Rule_Rule. See stored procedure hq.usp_Rule_Rule_Insert.
- The Primary Key (RuleId) is an incrementing identity column.
- There is a Unique Index on columns Title and ResultId, so no two Rules associated with a given Result can have the same Title.
- The deletion of a Result will cascade to all related Rules, and the deletion of a Rule will cascade to all related RuleTerms and RuleTermValues. See stored procedure hq.usp_Rule_Rule_Delete.
- If Priority is populated, then Rules will be evaluated against an Input in Priority order.
- There is an implicit OR relationship between Rules associated with the same Result. Only one Rule must evaluate TRUE for the associated Result to evaluate TRUE.
- Create RuleTerms associated with each Rule by inserting new rows to hq.t_Rule_RuleTerm. See stored procedure hq.usp_Rule_RuleTerm_Insert.
- The Primary Key (RuleTermId) is an incrementing identity column.
- The deletion of a Rule will cascade to all related RuleTerms, and the deletion of a RuleTerm will cascade to all related RuleTermValues. See stored procedure hq.usp_Rule_RuleTerm_Delete.
- There is an implicit AND relationship between RuleTerms associated with the same Rule. All RuleTerms must evaluate TRUE for the associated Rule to evaluate TRUE.
- Create RuleTermValues associated with each RuleTerm by inserting new rows to hq.t_Rule_RuleTermValue. See stored procedure hq.usp_Rule_RuleTermValue_Insert.
- The Primary Key (RuleTermValueId) is an incrementing identity column.
- There is a Unique Index on the Value and RuleTermId columns, so no two RuleTermValues associated with a given RuleTerm can have the same Value.
- The deletion of a RuleTerm will cascade to all related RuleTermValues. See stored procedure hq.usp_Rule_RuleTermValue_Delete.
- There is an implicit OR relationship between RuleTermValues associated with the same RuleTerm. Only RuleTermValue must evaluate TRUE for the associated RuleTerm to evaluate TRUE.
- The RuleTermValue Value is stored as text. When evaluating a RuleTerm, hqRule will detect this value’s datatype based on its format, and will use the assigned Operator appropriately. hqRule currently supports string and integer data types. More are in the Road Map.
Server_DB features a stored procedure (hq.usp_Rule_Demo_Restore) that creates the Ruleset used in the examples above. This code uses a number of helper functions built into hqRule, and serves as a great example for teams who wish to create Rulesets programmatically instead of using the Ruleset mangement UI. See the hq Schema Object Reference for more info.
The same Ruleset will also appear in the UI demo, refreshed every hour on the hour.
Evaluating data against a Ruleset requires the following steps:
- Shape the Input data to conform to Rule Engine formatting requirements.
- Invoke the Rule Engine to evaluate the shaped Input data against a Ruleset.
Two formats is currently supported for Inputs: Denormalized Query and Normalized XML. Other Input formats are on the Road Map, but for now Input data must take one of these two forms, described in function hq.tvf_Rule_Evaluate documentation.
The following SQL code demonstrates how to shape tabular data as Normalized XML, and then evaluate it against the Ruleset created in the above example:
-- Clean up from last time.
IF OBJECT_ID('tempdb..#Source', 'U') IS NOT NULL DROP TABLE #Source;
-- Create a table to simulate a data source.
CREATE TABLE #Source (
-- Populate the table with a record containing the data from our example.
INSERT #Source VALUES (1, 'Development', 1, 'Asset', 'Structure', 1, 'Startup', 'Core', 'Medium', -2, 'Non-essential', 1);
-- Find our example Ruleset.
DECLARE @RulesetId bigint = hq.svf_Rule_Ruleset_GetId('Private Capital Asset Classification');
-- Format source data & transform into normalized XML.
DECLARE @Inputs nvarchar(max) = (
CAST(ControlChange AS nvarchar(128)) AS ControlChange,
CAST(isDistressed AS nvarchar(128)) AS isDistressed,
CAST(ProfitMargin AS nvarchar(128)) AS ProfitMargin,
CAST(Tangible AS nvarchar(128)) AS Tangible
UNPIVOT (TermValue FOR TermToken IN (
FOR XML RAW('Input'), ROOT('Inputs')
-- Compose configuration XML.
DECLARE @Config xml = '
-- Evaluate input data against example Ruleset.
FROM hq.tvf_Rule_Evaluate(@RuleSetId, @Inputs, @Config)
ORDER BY SourceUniqueId, ResultValue
The result looks like this: