Menu
hqRulehqRule Reference

hqRule Documentation

hqRule is the business rules engine for Data Management teams who would rather focus on the features that make their business unique.

Here’s how to use it.

hqRule Reference

Yell for help!

hq Schema

Functions

svf_Rule_Operator_GetId

Scalar function that returns the OperatorId of an Operator on the basis of its unique Token.

Parameter Data Type Direction Description
@Token nvarchar(16) In Unique operator token (e.g. ‘=’).
@RETURN_VALUE bigint Return Value OperatorId.

svf_Rule_Result_GetId

Scalar function that returns the ResultId of a Result on the basis of its parent Ruleset, its position within the Result hierarchy, and its Value.

Parameter Data Type Direction Description
@RulesetId bigint In Ruleset unique identifier.
@ParentResultId bigint In Identifies position of Result in hierarchy.
@Value nvarchar(128) In Result value.
@RETURN_VALUE bigint Return Value ResultId.

svf_Rule_Rule_GetId

Scalar function that returns the RuleId of a Rule on the basis of its associated Result and its Title.

Parameter Data Type Direction Description
@ResultId bigint In Result unique identifier.
@Title nvarchar(128) In Rule Title.
@RETURN_VALUE bigint Return Value RuleId.

svf_Rule_Ruleset_GetId

Scalar function that returns the RulesetId of a Ruleset on the basis of its unique Title.

Parameter Data Type Direction Description
@Title nvarchar(128) In Ruleset Title.
@RETURN_VALUE bigint Return Value RulesetId.

svf_Rule_RuleTermValue_GetId

Scalar function that returns the RuleTermValueId of a RuleTermValue on the basis of its associated RuleTerm and its Value.

Parameter Data Type Direction Description
@RuleTermId bigint In RuleTerm unique identifier.
@Value nvarchar(128) In RuleTermValue Value.
@RETURN_VALUE bigint Return Value RuleTermValueId.

svf_Rule_Term_GetId

Scalar function that returns the TermId of a Term on the basis of its parent Ruleset and its Token.

Parameter Data Type Direction Description
@RulesetId bigint In Ruleset unique identifier.
@Token nvarchar(128) In Term Token.
@RETURN_VALUE bigint Return Value TermId.

tvf_Rule_Evaluate

Table-valued function that evaluates Input data against a Ruleset.

Parameter Data Type Direction Description
@RulesetId bigint In Ruleset unique identifier.
@Inputs nvarchar(max) In String expressing input data in a valid format.
@Config xml In XML configuration snippet.
@RETURN_VALUE bigint Return Value TermId.

Two formats are currently supported for @Inputs:

  • Denormalized Query. This is the most efficient method: pass the text of a query into the @Inputs parameter. By default the query will execute against the local database, but another connection string can be designated in the @Config parameter. Each column name in the source query will become a Term in the input.
  • Normalized XML. Data is passed as XML, normalized by Id, which is any string that uniquely identifies a row of data. Multi-column keys may be converted to text and concatenated, ideally with delimiters.
<Inputs>
    <Input Id="1" Term="Activity">Development</Input>
    <Input Id="1" Term="ControlChange">1</Input>
    <Input Id="1" Term="Focus">Company</Input>
    <Input Id="2" Term="Activity">Renovation</Input>
    <Input Id="2" Term="ControlChange">0</Input>
    <Input Id="2" Term="Focus">Asset</Input>
</Inputs>

Sample Input Normalized XML

Other input formats are on the Road Map.

Note that there is no penalty for adding more Terms to the Input than are strictly required by the Ruleset. This is a great way to future-proof an implementation, since the unused Terms can have new Rules built around them simply by configuring data on the hqRule tables, without changing or deploying any code!

The @Config parameter is a snippet of XML that delivers configuration options to the Rule Engine. It takes the following form:

<Config>
	<InputOptions>
		<ConnectionString>Data Source=INPUT_SERVER;Initial Catalog=INPUT_DB;Integrated Security=True;</ConnectionString>
		<InputForm>Denormalized</InputForm>
		<InputType>Query</InputType>
	</InputOptions>
	<EngineOptions>
		<ConnectionString>Data Source=RULESET_SERVER;Initial Catalog=RULESET_DB;Integrated Security=True;</ConnectionString>
		<KeyTerms>
			<Term>TermA</Term>
			<Term>TermB</Term>
		</KeyTerms>
		<EvaluateAllRules>true</EvaluateAllRules>
		<ProgressiveEvaluation>true</ProgressiveEvaluation>
		<ReturnAllResults>true</ReturnAllResults>
	</EngineOptions>
</Config>

Stored Procedures

usp_Rule_Operator_Delete

Deletes an Operator from table hq.t_Rule_Operator. Throws an exception if the Operator is referenced by any RuleTerm.

Parameter Data Type Direction Description
@OperatorId bigint In Operator unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_Operator_Insert

Inserts an Operator to table hq.t_Rule_Operator. Throws an exception if uniqueness constraint is violated.

Parameter Data Type Direction Description
@Token nvarchar(16) In Operator token.
@OperatorId bigint Out Operator unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_Result_Delete

Deletes a Result from table hq.t_Rule_Result. Deletion cascades to all descendant Results and related Rules, RuleTerms, and RuleTermValues.

Parameter Data Type Direction Description
@RuleId bigint In Rule unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_Result_Insert

Inserts a Result to table hq.t_Rule_Result. Throws an exception if uniqueness constraint is violated.

Parameter Data Type Direction Description
@RulesetId bigint In Ruleset unique identifier.
@ParentResultId bigint In Parent Result unique identifier.
@Value nvarchar(128) In Result value.
@ResultId bigint Out Result unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_Rule_Delete

Deletes a Rule from table hq.t_Rule_Rule. Deletion cascades to all related RuleTerms and RuleTermValues.

Parameter Data Type Direction Description
@RuleId bigint In Rule unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_Rule_Insert

Inserts a Rule to table hq.t_Rule_Rule. Throws an exception if uniqueness constraint is violated.

Parameter Data Type Direction Description
@ResultId bigint In Result unique identifier.
@Title nvarchar(128) In Rule title.
@Priority bigint In Rule processing priority.
@RuleId bigint Out Rule unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_Ruleset_Delete

Deletes a Ruleset from table hq.t_Rule_Ruleset. Deletion cascades to all related Results, Rules, RuleTerms and RuleTermValues, and Terms.

Parameter Data Type Direction Description
@RulesetId bigint In Ruleset unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_Ruleset_Insert

Inserts a Ruleset to table hq.t_Rule_Ruleset. Throws an exception if uniqueness constraint is violated.

Parameter Data Type Direction Description
@Title nvarchar(128) In Ruleset title.
@isDynamic bit In Indicates whether Ruleset is dynamic.
@RulesetId bigint Out Ruleset unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_RuleTerm_Delete

Deletes a RuleTerm from hq.t_Rule_RuleTerm. Deletion cascades to all related RuleTermValues.

Parameter Data Type Direction Description
@RuleTermId bigint In RuleTerm unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_RuleTerm_Insert

Inserts a RuleTerm to table hq.t_Rule_RuleTerm.

Parameter Data Type Direction Description
@RuleId bigint In Rule unique identifier.
@TermId bigint In Term unique identifier.
@OperatorId bigint In Operator unique identifier.
@RuleTermId bigint Out RuleTerm unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_RuleTermValue_Delete

Deletes a RuleTermValue from table hq.t_Rule_RuleTermValue.

Parameter Data Type Direction Description
@RuleTermValueId bigint In RuleTermValue unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_RuleTermValue_Insert

Inserts a RuleTermValue to table hq.t_Rule_RuleTermValue. Throws an exception if uniqueness constraint is violated.

Parameter Data Type Direction Description
@RuleTermId bigint In RuleTerm unique identifier.
@Value nvarchar(128) In Input data will be evaluated against this value.
@RuleTermValueId bigint Out RuleTermValue unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_Term_Delete

Deletes a Term from table hq.t_Rule_Term. Throws an exception if the Term is referenced by any RuleTerm.

Parameter Data Type Direction Description
@TermId bigint In Term unique identifier.
@RETURN_VALUE int Return Value Error code.

usp_Rule_Term_Insert

Inserts a Term to table hq.t_Rule_Term. Throws an exception if uniqueness constraint is violated.

Parameter Data Type Direction Description
@RulesetId bigint In Ruleset unique identifier.
@Token nvarchar(128) In Term token.
@TermId bigint Out Term unique identifier.
@RETURN_VALUE int Return Value Error code.

Glossary

Config

Config is an optional parameter to hq.tvf_Rule_Evaluate that takes the form of an XML string. If Config elements (or the whole parameter) are omitted, default values are as indicated below.

  • InputOptions
    • ConnectionString indicates the data source for Query-type inputs. By default this is the same database from which the evaluation function is executed.
    • InputForm indicates the normal form of the input, and can take one of two values: Denormalized or Normalized.
    • InputType indicates the input encoding method, and can take one of two values: Query or Xml.
  • EngineOptions
    • ConnectionString indicates the data source for the Ruleset against which the Input will be evaluated. By default this is the same database from which the evaluation function is executed.
    • KeyTerms is set of XML tags in the form <Term>Token</Term>. These indicate how rows will be identified on the output. See this section for an example.
    • EvaluateAllRules (EAR) indicates whether Rule evaluation for a given Input should stop after the first Rule in priority order evaluates TRUE. This (EvaluateAllRules = FALSE) is the highest-performance mode of operation, and is appropriate for flat Result sets like country or currency codes. In a hierarchical result set, a given Input may resolve to multiple levels of the hierarchy. In this case, EvaluateAllRules should normally be set to TRUE. Note that a given Result in a hierarchy can only evaluate TRUE if its parent Results have also evaluated TRUE. Defaults to FALSE.
    • ProgressiveEvaluation (PE) indicates whether Results should be evaluated progressively. Progressive evaluation means that no Result can evaluate TRUE unless its parent Result evaluates TRUE. This means that a Result inherits its ancestors’ rule sets (if any) in an AND-wise fashion. In a non-progressive evaluation, a Result is evaluated only according to its own Rules.
    • ReturnAllResults (RAR) indicates whether a single, lowest-level Result should be returned from a hierarchy, or whether all of its parent Results should be returned as well.

Environment

An Environment is the set of all databases served by a single instance of hqRule. By default, this is a single database. With appropriate permissioning and cross-database queries, one instance of hqRule can serve every database in a given SQL Server instance. With appropriate server links, a single instance of hqRule could serve every database, in every SQL Server instance, across the entire enterprise.

Input

Inputs to hqRule are expressed as XML and normalized, meaning that a single row of input data may be represented by several Inputs, one for each Term-value pair. A single rule engine call can process input XML representing millions of Inputs. An Input to the rule engine has three elements:

  • SourceUniqueId is a string that uniquely identifies the source row, and serves to group related Terms.
  • TermToken is the string representing a given Term in the rule engine, whose value is expressed in the input data.
  • TermValue is a string representing the value of the associated Term delivered by the data source. Non-character data types should be converted to text in the same manner as their values are represented in the matching RuleTerm.

See function hq.tvf_Rule_Evaluate for more info. Other Input formats are on the Road Map.

Operator

Operators are defined on database table hq.t_Rule_Operator. This table is populated on installation, and its contents should not be changed. An Operator indicates how a Term relates to a RuleTermValue within a given RuleTerm. Operators work against various data types in a manner consistent with T-SQL norms. The following is a list of currently defined operators, along with their tokens:

  • Equality (=)
  • Inequality (!=)
  • Greater Than (>)
  • Less Than (<)
  • Greater Than or Equals (>=)
  • Less Than or Equals (<=)
  • Regex (regex)

This is a real-world example of how Operators participate in the definition of a Ruleset.

Result

Results are defined on database table hq.t_Rule_Result. A Result is a defined outcome of a mapping operation within a Ruleset. Results may be expressed as a simple list (e.g. a list of country codes), or as a hierarchy with parent-child relationships between Results (e.g. a complex asset classification scheme). If Results are hierarchical, Rules may be evaluated progressively down the hierarchy, which can significantly simplify Rule logic.

This is a real-world example of how Operators participate in the definition of a Ruleset.

Rule

Rules are defined on database table hq.t_Rule_Rule. A Rule is a set of conditions that are evaluated against an Input. Rules share an implicit OR relationship: if several Rules are defined against a given Result, only one needs to evaluate TRUE in order for the Result to be returned. Rules are evaluated in priority order. Depending on configuration, a given Input may produce more than one Result (e.g. a Result at the bottom of a hierarchy, plus all of its parent Results).

This is a real-world example of how Rules participate in the definition of a Ruleset.

Ruleset

Rulesets are defined on database table hq.t_Rule_Ruleset. A Ruleset is set of related Results and associated Rules. Rulesets are independent from one another, so a single instance of hqRule can handle every mapping and translation use case in a given Environment.

This is a real-world example of the definition of a Ruleset.

RuleTerm

RuleTerms are defined on database table hq.t_Rule_RuleTerm. A RuleTerm links a Term, an Operator, and a set of RuleTermValues within a given Rule. RuleTerms share an implicit AND relationship: all RuleTerms associated with a Rule must evaluate TRUE in order for the Rule to evaluate TRUE.

This is a real-world example of how RuleTerms participate in the definition of a Ruleset.

RuleTermValue

RuleTermValues are defined on database table hq.t_Rule_RuleTermValue. A RuleTermValue is one of potentially several values that could cause a RuleTerm to evaluate TRUE.

This is a real-world example of how RuleTermValues participate in the definition of a Ruleset.

Term

Terms are defined on database table hq.t_Rule_Term. A Term is parameter of a Rule, and is represented by a Token (e.g. ‘CountryOfDomicile’). Individual Input data elements are identified by Term.

More info:

This is a real-world example of how Terms participate in the definition of a Ruleset.

Release Notes

v1.2.0: SourceQuery

Added Denormalized Query to the set of available Input types.

Added strong typing for Terms, eliminating any ambiguity as to how Operators should process values.

Added a Regex operator, which offers incredible text pattern-matching ability.

Upgraded the Ruleset definition user interface with a major cosmetic facelift.

v1.1.0: RazorView

Added a Ruleset management user interface (test drive it here).

v1.0.0: HelloWorld

Hello, World!

Road Map

Currently, function hq.tvf_Rule_Evaluate only accepts Inputs as Term-normalized XML. Future Input options will include (in order of priority):

  • Denormalized XML
  • Term-normalized source query
  • Term-normalized JSON
  • Denormalized JSON

Other road map items:

  • Utility stored procedures.