Centralized business rules are custom data validation business attached directly to a table rather than being defined in a particular pipeline or data entry form.
When business rules are attached to a table rather than a pipeline or a form, they are always applied when the table is being updated regardless of how data enters the table.
In this way it is not necessary to repeat the same validation rules for the same fields in a pipeline or in a form.
At the moment, the supported rules are the following:
- TestValueInRange
- TestNotEmpty
- TestColumnsExist
- TestPattern
Here is a quick description of the rules as they appear in the script guide
TestValueInRange: Checks that a value is above, below or within a certain range of limits.
TestNotEmpty: Tests for the existence of required values. It is possible to configure whether whitespace is considered empty or not.
TestColumnsExist: Verifies that a list of columns exists in the data being loaded.
TestPattern: Verifies that a value matches a regular expression pattern
Starting point
In order to use the Centralized Business Rules feature, you need to simply:
- Navigate to your mart
- Open the model page
- Select a table
- Click on the validation rules option
- Choose the validation rule you wish to use
Setting the Rules
All validation commands share the following properties Column, Tag, Impact, Message if invalid and Context Columns.
| Property | Description |
|---|---|
| Column | It’s a required field, where you need to specify, using the drop down list, the column for which the rule will be implemented |
| Tag | Appears as a menu item in the issues summary to the data uploader, to group issues in a meaningful way. |
| Impact | The impact of failed validation. Possible values: |
| - Warning, raises a warning but no change made to data | |
| - Error_RemoveValue, value replaced by NULL. | |
| - Error_RemoveRow, record not uploaded. | |
| - Error_RemoveColumn, column removed. | |
| - Error_RemoveTable, entire table not uploaded | |
| Message if invalid | You may specify an informative message to give feedback to the users. Can contain {0} to be replaced by the invalid value. |
| Context Columns | If an error occurs, controls which columns are displayed to the user. |
Let’s have an overview on how the rules are set. For each of the rules there are some rule type specific properties.
TestValueInRange
Low/High Limit: First you need to specify if either of the limits of the range will be a literal value or Compared to Column and afterwards you may specify the values for both low and high limits. You may also use ${Now.DateUtc} to set the literal value to today’s date.
Inclusive: Set the value to true or false
TestNotEmpty
Mode: There is a drop down list from where you may choose between Nulls, Null or Empty and Null or Whitespace which is the default option.
TestColumnsExist
TestPattern
Pattern: Regular expression pattern that is matched against each value in the column
Setting a rule example
Here is an example of setting a Test Value in Range rule on a table column.
Once you have completed building the rules according to your needs, you may click on the “Show XML” button, to preview the code that is created.
Running/testing the rules
Use the Run button to run the rules against a portion or the entirety of a table, without having to create any batches. Next to the RUN button you may specify the number of rows for which the rule will be applied in order to check how it works.
Below is the output of the above example. By clicking the green button on the right you have the option to download the results to excel.
Batch preview
Let’s see also how the batch preview looks like from a data uploader’s perspective, after implementing a centralized rule. For this example we performed a standard upload, meaning, we uploaded a file directly to the table using the upload function on the data page. So there are no custom pipelines involved or any other custom validations.
Here is how the issues tab looks:
How it works - view pipeline xml
If you are curious, it works by embedding the centralized validation rules into whatever pipeline is running (there is always a pipeline involved, even for UI edits and data entry forms). You can see the final generated pipeline for any batch by clicking the Pipeline XML tab on the batch preview page.
Here is a small section of generated pipeline xml from the batch preview page, where our rule above is highlighted.
