Breadcrumbs

Advanced Exclusion Rules

This page describes the new exclusion rule capability introduced in version 9.21. For the original field exclusion rule feature, see Field Exclusion Rules. The new rule works independently of the existing feature.

In most cases you will want to disable the existing feature when using the new rules as all the existing capability can now be applied using the new feature.

You can use the exclusion rules feature to limit the combinations of items from particular groups that can be added to the table at any one time. The new exclusion rule capability allows you to create more advanced and flexible rules, as it allows you to define groups and then choose how you want those groups to be combined. For example, you can create groups that are only applied when some other group’s limit is reached or exceeded.

In addition, the new feature allows you to specify rules that apply at the value set level.

Step 1 - Confirm that the new Exclusion Rule is Active

  1. Open <tomcat_home>\webapps\webapi\WEB-INF\data\.repository\RulesEngine.xml in a text editor.

  2. Locate the CDataOnlineEditRules section:

    XML
    	<rules:RulesPipe name="CDataOnlineEditRules">
        <!-- <rules:rule-name name="OverrideDefaultSummationRule"/> -->
        <!-- <rules:rule-name name="MandatoryFieldsRule"/> -->
        <!-- <rules:rule-name name="MandatoryValuesRule"/> -->
        <!-- <rules:rule-name name="DBSummationOptionsRule"/> -->
        <!-- <rules:rule-name name="GuestUserCellLimitRule"/> -->
        <!-- <rules:rule-name name="noConcatenationRule"/> -->
         <rules:rule-name name="GroupQueryLimitRule"/>
        <!-- <rules:rule-name name="HierarchyLevelRule_All_Old"/> -->
        <!-- <rules:rule-name name="HierarchyLevelRule_All"/> -->
        <rules:rule-name name="DemographicVariablesRule"/>
        <rules:rule-name name="TableCellsNumberRule"/>
        <!-- <rules:rule-name name="checkAddToDimensionRule"/> -->
        <!-- <rules:rule-name name="checkDoubleCountingRule"/> -->
        <rules:rule-name name="SummationOptionsRule"/>
        <!-- <rules:rule-name name="DutyOfCareRule"/> -->
        <rules:rule-name name="FieldExclusionRule"/>
        <!-- <rules:rule-name name="ExclusionRule"/> -->
    </rules:RulesPipe>

    Remove the comments around the ExclusionRule (you may also want to comment out the existing FieldExclusionRule at this point, as shown below):

    XML
            <!-- <rules:rule-name name="FieldExclusionRule"/> -->
        <rules:rule-name name="ExclusionRule"/>
    </rules:RulesPipe>

  3. In addition, locate the QuantileFilterRules and NewTableRules sections:

    XML
        <rules:RulesPipe name="QuantileFilterRules">
        <rules:rule-name name="FieldExclusionRule"/>
        <!-- <rules:rule-name name="ExclusionRule"/> -->
    </rules:RulesPipe>

    <rules:RulesPipe name="NewTableRules">
        <rules:rule-name name="FieldExclusionRule"/>
        <!-- <rules:rule-name name="ExclusionRule"/> -->
        <!-- <rules:rule-name name="MandatoryFieldsRule"/> -->
        <!-- <rules:rule-name name="MandatoryValuesRule"/> -->
    </rules:RulesPipe>

    Remove the comments around the ExclusionRule in both cases:

    XML
        <rules:RulesPipe name="QuantileFilterRules">
        <!-- <rules:rule-name name="FieldExclusionRule"/> -->
        <rules:rule-name name="ExclusionRule"/>
    </rules:RulesPipe>

    <rules:RulesPipe name="NewTableRules">
        <!-- <rules:rule-name name="FieldExclusionRule"/> -->
        <rules:rule-name name="ExclusionRule"/>
        <!-- <rules:rule-name name="MandatoryFieldsRule"/> -->
        <!-- <rules:rule-name name="MandatoryValuesRule"/> -->
    </rules:RulesPipe>

  4. Save your changes to the file.

  5. Restart SuperWEB2 or Tomcat to apply the changes.

Step 2 - Check for existing Exclusion Rules in SuperADMIN

Before starting to create or update rules, you should check whether there are any existing exclusion rules defined. Login to SuperADMIN and use the following command (replace <dataset_id> with the ID of the dataset):

cfg db <dataset_id> superweb2.rules.exclusion

SuperWEB2 will either display the list of existing rules (if some rules are already defined), or display "not found".

The easiest way to define or update the rules is to use a text editor and then import the complete configuration. If there are existing rules defined then you should export those first and use that file; otherwise you can start from an empty text file. To export the existing configuration, use the following command (replacing <dataset_id> with the dataset ID, and <filename> with the full path and filename of the location to save the file on disk; do not use quotes even if the path contains spaces):

cfg db <dataset_id> superweb2.rules.exclusion save <filename>

Step 3 - Define the Groups and Rules

The advanced exclusion rules feature is very flexible, and allows you to construct complex rules from simple building blocks. The basic structure of the overall configuration is as follows:

JSON
{
    "groups": {
        ...
    }
    "rules": {
        "root": { ... }
    }
}

Groups:

  • Within the “groups” section, you can define as many groups as you need, each with their own unique name of your choice.

  • Groups can include fields and value sets (value sets allow you to refer only to specific levels in a hierarchical field, such as a particular level of geography).

  • All elements need to be referenced using their IDs. Display names are not supported:

    • For fields you specify them using the fact table ID and field ID.

    • For value sets you specify them using the fact table ID, field ID, and value set ID.

  • Value sets are applied explicitly, so if you want to block everything in a hierarchy from a certain level down, you need to specify each individual value set in the group.

  • Groups can optionally include a “limit” setting, which acts as a default limit for that group (the limit can also be defined/overridden when the group is used in the “rules” section).

The following shows an example of some group configuration (items in < and > would be replaced with the relevant values):

JSON
"groups": {
    "<group_name_1>": {
        "fields": [
            { "fact": "<fact_table_id>", "field": "<field_id>" },
            { "fact": "<fact_table_id>", "field": "<field_id>" },
            { "fact": "<fact_table_id>", "field": "<field_id>" }        
        ],
        "limit": 2
    },
    "<group_name_2>": {
        "fields": [
            { "fact": "<fact_table_id>", "field": "<field_id>", "valueSets": [ "<valueset_id>", "<valueset_id>" ... ] }
        ]
    },
    "<group_name_3>": {
        "fields": [
            { "fact": "<fact_table_id>", "field": "<field_id>" },
            { "fact": "<fact_table_id>", "field": "<field_id>" }        
        ]
    }
    ...
}

All defined groups must be used in at least one rule. If there are groups defined that are not used, SuperWEB2 will reject the configuration, block access to the dataset, and generate an error in its log file.

Fact table IDs, field IDs and value set IDs are validated at run time in SuperWEB2 (they are not validated when the configuration is loaded in SuperADMIN). If SuperWEB2 detects that an ID is invalid then it will reject the configuration, block access to the dataset, and generate an error in its log file.

Obtaining Fact Table, Field and Value Set IDs

You can find the IDs you need for the configuration in SuperADMIN by typing cat <dataset_id> <field_name>. For example:

> cat bank Age
[ XTAB Field : 'Age' ]
    [ ID : 'SXV4__Retail_Banking__F_Customer__Age_FLD' ]
    [ Value Set : 'SXV4__Retail_Banking__C_Age' ]

The reported ID value is in the following format; you will need the individual components, which are separated by double underscores, in your configuration):

SXV4__<dataset>__<fact_table_id>__<field_id>_FLD

For value sets, typing cat <dataset_id> <field_name> for the parent field will return all the value sets. For example:

> cat bank Area
[ XTAB Field : 'Area' ]
    [ ID : 'SXV4__Retail_Banking__F_Customer__Area_FLD' ]
    [ Value Set : 'SXV4__Retail_Banking__C_State' ]
        [ Value Set : 'SXV4__Retail_Banking__C_Geography_2' ]
            [ Value Set : 'SXV4__Retail_Banking__C_Geography_1' ]
                [ Value Set : 'SXV4__Retail_Banking__C_Geography_0' ]

Each value set ID is in the following format; you will need the final part of this ID for your configuration, in addition to the fact table and field IDs:

SXV4__<dataset>__<fact_table_id>__<valueset_id>

Rules Overview:

The “rules” section is where you define your rules themselves:

  • It must include exactly one instance of “root”, which is the starting point for your chain of rules.

  • You can define as many other rules as you need to within the “rules” section, and combine them together in a chain.

  • “root” must contain one of the following:

    • “any”, set to a list of other rules enclosed in square brackets. This means that if any one of the listed rules is triggered, the table will be blocked;

    • “all”, set to a list of other rules enclosed in square brackets. This means that the table will only be blocked if all the listed rules are triggered (in the case where if/then rules are included in the list, this means that both the if and the then conditions need to be satisfied); or

    • An individual rule. In this case this will be the only rule applied.

All defined rules must be used in the chain. If there are rules defined that are not used, SuperWEB2 will reject the configuration, block access to the dataset, and generate an error in its log file.

Types of Rules:

The following rule types are supported:

  • “activate”: this rule triggers when the specified limit is reached for the specified group. When defining the rule you must specify the group it applies to and the limit (this can be either in the group definition or in the rule; if both are set then the limit at rule level takes precedence).

  • “exclude”: this rule type is the same as “activate” except that it triggers when the limit is exceeded.

  • “if” … “then”: this rule type combines two other rules: the “then” rule will only be applied when the “if” rule is triggered.

The following shortcut rule types are also supported:

  • “mutuallyLimiting”, which must be set to a list of two groups enclosed in square brackets. The referenced groups must have a “limit” set at group level. This rule means that the table will be blocked only when the limits from both groups are exceeded. Each group can individually exceed its configured limit when the other group does not exceed its limit.

  • “mutuallyExclusive”, which must be set to a list of two groups, enclosed in square brackets. The referenced groups do not need to have their own limits defined at group level. This rule means that if any item from one of the groups is in the table, all of the items in the other group will automatically be blocked from being added to the table.

  • “ifAct” … “thenEx”, which is a shortcut for defining an if/then rule with an activation and exclusion group. It requires limits to be set at group level. See the example below for more details.

Example Rule Definitions

This example defines three sets of if/then rules and will block the table if any one of the rules is triggered:

JSON
"rules": {
    "<activate_rule_1>": { "activate": "<group_name_1>", "limit": <value> },
    "<exclude_rule_1>": { "exclude": "<group_name_2>", "limit": <value> }
    "<if_rule_1>": { "if": "<activate_rule_1>", "then": "<exclude_rule_1>" },

    "<activate_rule_2>": { "activate": "<group_name_3>", "limit": <value> },
    "<exclude_rule_2>": { "exclude": "<group_name_4>", "limit": <value> }
    "<if_rule_2>": { "if": "<activate_rule_2>", "then": "<exclude_rule_2>" },

    "<activate_rule_3>": { "activate": "<group_name_5>", "limit": <value> },
    "<exclude_rule_3>": { "exclude": "<group_name_6>", "limit": <value> }
    "<if_rule_3>": { "if": "<activate_rule_3>", "then": "<exclude_rule_3>" },
    "root": { "any": [ "<if_rule_1>", "<if_rule_2>", "<if_rule_3>" ] }
}

This example just has a single rule, that triggers when this group exceeds the specified limit:

JSON
"rules": {
    "root": { "exclude": "<group_name>", "limit": <value> }
}

This example shows how the above rules could be combined in the “any”/”all” lists under “root”:

JSON
"rules": {
    "<activate_rule_1>": { "activate": "<group_name_1>", "limit": <value> },
    "<exclude_rule_1>": { "exclude": "<group_name_2>", "limit": <value> }
    "<if_rule_1>": { "if": "<activate_rule_1>", "then": "<exclude_rule_1>" },

    "<activate_rule_2>": { "activate": "<group_name_3>", "limit": <value> },
    "<exclude_rule_2>": { "exclude": "<group_name_4>", "limit": <value> }
    "<if_rule_2>": { "if": "<activate_rule_2>", "then": "<exclude_rule_2>" },

    "<activate_rule_3>": { "activate": "<group_name_5>", "limit": <value> },
    "<exclude_rule_3>": { "exclude": "<group_name_6>", "limit": <value> }
    "<if_rule_3>": { "if": "<activate_rule_3>", "then": "<exclude_rule_3>" },
    "root": { "any": [ { "exclude": "<group_name>", "limit": <value> }, "<if_rule_1>", "<if_rule_2>", "<if_rule_3>" ] }
}

Following are some examples of complete configuration that can be tested against the sample Retail Banking dataset:

Original Field Exclusion-style Rules

This rule replicates the original field exclusion rule feature, allowing a maximum of any two fields from the group of Gender, Occupation or Marital Status:

JSON
{
    "groups": {
         "field_exclusion_group": {
            "fields": [
                { "fact": "F_Customer", "field": "Gender" },
                { "fact": "F_Customer", "field": "Occupation" },
                { "fact": "F_Customer", "field": "Marital_Status" }
            ]
        }
    },
    "rules": {
        "root": { "exclude": "field_exclusion_group", "limit": 2 }
    }
}

This example shows how multiple groups can be combined, allowing a maximum of two out of the first group and a maximum of one out of the second group:

JSON
{
    "groups": {
         "field_exclusion_group_1": {
            "fields": [
                { "fact": "F_Customer", "field": "Gender" },
                { "fact": "F_Customer", "field": "Occupation" },
                { "fact": "F_Customer", "field": "Marital_Status" }
            ]
        },
		"field_exclusion_group_2": {
            "fields": [
                { "fact": "F_Customer", "field": "Age" },
                { "fact": "F_Account", "field": "Product_Type" }
            ]
        }
    },
    "rules": {
		"rule_1": { "exclude": "field_exclusion_group_1", "limit": 2 },
		"rule_2": { "exclude": "field_exclusion_group_2", "limit": 1 },
        "root": { "any": [ "rule_1", "rule_2" ] }
    }
}
If/Then Rule Examples

This rule shows an example of an if/then rule. In this case:

  • If there is at least one of Gender, Marital Status or Occupation in the table, then Postcodes (the lowest level of Area) cannot be in the table.

  • Other levels of geography can be tabulated against one or more of those three fields.

  • There is no restriction on tabulating Gender, Occupation and Marital Status against each other when postcodes is not in the table.

  • Postcodes can be added to the table whenever those three fields are not in the table, and can be tabulated against any other field.

JSON
{
    "groups": {
         "field_exclusion_group": {
            "fields": [
                { "fact": "F_Customer", "field": "Gender" },
                { "fact": "F_Customer", "field": "Occupation" },
                { "fact": "F_Customer", "field": "Marital_Status" }
            ]
        },
		"lower_level_area" : {
			"fields": [
			    { "fact": "F_Customer", "field": "Area", "valueSets": [ "C_Geography_0" ] }
			]
		}
    },
    "rules": {
		"area_activate": { "activate": "field_exclusion_group", "limit": 1 },
        "area_exclude": { "exclude": "lower_level_area", "limit": 0 },
		
        "root": { "if": "area_activate", "then": "area_exclude" }
    }
}

The following is another example with multiple groups and multiple if/then rules: the two lower-level Area fields will be blocked if there are any two fields from the first group or any field from the second group in the table:

JSON
{
    "groups": {
         "field_exclusion_group_1": {
            "fields": [
                { "fact": "F_Customer", "field": "Gender" },
                { "fact": "F_Customer", "field": "Occupation" },
                { "fact": "F_Customer", "field": "Marital_Status" }
            ]
        },
		"field_exclusion_group_2": {
            "fields": [
                { "fact": "F_Account", "field": "Product_Type" },
                { "fact": "F_Customer", "field": "Age" }
            ]
        },
		"lower_level_area" : {
			"fields": [
			    { "fact": "F_Customer", "field": "Area", "valueSets": [ "C_Geography_1", "C_Geography_0" ] }
			]
		}
    },
    "rules": {
		"area_activate_1": { "activate": "field_exclusion_group_1", "limit": 2 },
        "area_exclude_1": { "exclude": "lower_level_area", "limit": 0 },
        "area_v_rule_1": { "if": "area_activate_1", "then": "area_exclude_1" },
		"area_activate_2": { "activate": "field_exclusion_group_2", "limit": 0 },
        "area_exclude_2": { "exclude": "lower_level_area", "limit": 0 },
        "area_v_rule_2": { "if": "area_activate_2", "then": "area_exclude_2" },
        "root": { "any": [ "area_v_rule_1", "area_v_rule_2" ] }
    }
}

The following example is functionally the same as the previous one, but using the “ifAct” and “thenEx” shortcuts instead (as shown in this example, when using the shortcuts, the limits must be defined at group level):

JSON
{
    "groups": {
         "field_exclusion_group_1": {
            "fields": [
                { "fact": "F_Customer", "field": "Gender" },
                { "fact": "F_Customer", "field": "Occupation" },
                { "fact": "F_Customer", "field": "Marital_Status" }
            ],
            "limit": 2
        },
		"field_exclusion_group_2": {
            "fields": [
                { "fact": "F_Account", "field": "Product_Type" },
                { "fact": "F_Customer", "field": "Age" }
            ],
            "limit": 0
        },
		"lower_level_area" : {
			"fields": [
			    { "fact": "F_Customer", "field": "Area", "valueSets": [ "C_Geography_1", "C_Geography_0" ] }
			],
            "limit": 0
		}
    },
    "rules": {
        "area_v_rule_1": { "ifAct": "field_exclusion_group_1", "thenEx": "lower_level_area" },
        "area_v_rule_2": { "ifAct": "field_exclusion_group_2", "thenEx": "lower_level_area" },

        "root": { "any": [ "area_v_rule_1", "area_v_rule_2" ] }
    }
}

The rules section for the previous example could also be further simplified as follows using the “ifAct” and “thenEx” shortcuts:

JSON
    "rules": {
        "root": { "any": [ { "ifAct": "field_exclusion_group_1", "thenEx": "lower_level_area" }, { "ifAct": "field_exclusion_group_2", "thenEx": "lower_level_area" } ] }
    }
Mutually Limiting Examples

This example shows how the “mutuallyLimiting” shortcut can be used. In this example users will be blocked from adding more than two items from both groups in the same table. So, for example: Age by Gender by Marital Status would be permitted on its own, but would be blocked if both Company and Product Type are also in the table.

JSON
{
    "groups": {
        "group1": {
            "limit": 2,
            "fields": [
                { "fact": "F_Customer", "field": "Age" },
                { "fact": "F_Customer", "field": "Gender" },
                { "fact": "F_Customer", "field": "Marital_Status" },
                { "fact": "F_Customer", "field": "Occupation" }
            ]
        },
        "group2": {
            "limit": 2,
            "fields": [
                { "fact": "F_Customer", "field": "Cust_Mail_Ind" },
                { "fact": "F_Customer", "field": "Company" },
                { "fact": "F_Account", "field": "Product_Type" }
            ]
        }
    },
    "rules": { "root": { "mutuallyLimiting": [ "group1", "group2" ] } }
}
Mutually Exclusive Examples

This example shows how the “mutuallyExclusive” shortcut can be used. In this example, any one field from “group1” blocks all fields from “group2”, and vice versa. There is no restriction on adding multiple fields from one group when there is nothing from the other group in the table.

Although there are limits set at the group level in the example configuration, these limits are not used as they are overridden by the “mutuallyExclusive” rule.

JSON
{
    "groups": {
        "group1": {
            "limit": 3,
            "fields": [
                { "fact": "F_Customer", "field": "Age" },
                { "fact": "F_Customer", "field": "Gender" },
                { "fact": "F_Customer", "field": "Marital_Status" },
                { "fact": "F_Customer", "field": "Occupation" }
            ]
        },
        "group2": {
            "limit": 2,
            "fields": [
                { "fact": "F_Customer", "field": "Cust_Mail_Ind" },
                { "fact": "F_Customer", "field": "Company" },
                { "fact": "F_Account", "field": "Product_Type" }
            ]
        }
    },
    "rules": { "root": { "mutuallyExclusive": [ "group1", "group2" ] } }
}

Step 4 - Upload Your Rule Configuration

Once you have finished configuring your rules, upload them to the SuperADMIN configuration server using the following command:

cfg db <dataset_id> superweb2.rules.exclusion load <filename>

Optional Configuration

Define a Custom Error Message for Rule Breaches

When a user’s selection breaches one of your configured rules, SuperWEB2 displays a standard error message. The text of this message is defined by the fieldExclusion.failure property defined in CDataOnlineEditRules.properties (and the equivalent versions for other UI languages), which is located in <tomcat_home>\webapps\webapi\WEB-INF\data\.repository\.

You may wish to edit this message to reflect your preferred message to users when they exceed one of the configured limits.

fieldExclusion.failure=Error: Table contains an excluded set of items.