Go to jail! Go directly to jail! Do not pass 'Go'! Do not collect $200!
The role of JAIL-it is to check the integrity of data being loaded into the Academy Warehouse, both from a Technical Structural and also a Business Rules point of view.
By checking the “Jailer” check-box on the Data Loader Details screen, you indicate to the data loader builder that you wish to implement Data Jailer integrity checking.
Automatically, the structural integrity checks will be applied to the data loader. No more action need be taken. This means that the Data Loader will validate each individual data row, as it is imported into the "staging" area, to ensure that the data is valid from a technical perspective. (Examples of this would be dates outside the permitted range, or text data which is longer expected.) Note that selecting Fast Load will by-pass the structural integrity checks in the Jailer.
If you wish to apply some business rules checks to the jailer process, these must be defined.
Data for each individual table is received from the data source, via the data loader, into the staging area. The jailer initially applies Technical Structural integrity checking. Any rows not confirming to these tests are immediately placed in the data jail tables (tables are suffixed with the name “_Errors”). Subsequently, the jailer applies the business rules defined in the Jailer profile for the incoming table. Any rows failing these checks are also jailed in the same table and marked with the name of the first failing rule.
The Data Jailer has three modes of operation, definable from the user interface for each data loader, since the jailer process is associated with each data loader.
Jail Invalid Data: Data that passes validation is released through to the Data Warehouse automatically, invalid data is placed in the data jail.
Jail and Quarantine: Invalid data is placed in the data jail. In the event that there are records in any of the tables in the jail, the loader will not complete, and hence the warehouse is not updated. Any future attempts to run the data loader will fail until the jail tables are cleared or appropriately dealt with.
Caution: All data passes through to the warehouse regardless of whether or not the data fails checks. A copy of the data that has failed the checks will be placed in the data jail.
A user interface into the data jail tables is provided to allow the user to interactively reject or correct the failing rows. Any corrected rows can then be passed back through the jailer and released into the live Warehouse environment.
Jailer Rule Building
Jailer business rules are SQL Server “User Defined Functions” (UDF) which are associated via Jailer profiles, to the table for which the rule applies. Jailer rules can be formulated in one of two ways.
- Use the jailer rule builder to interactively define your business rules.
- Build your own rules by writing a UDF and adding it to the jailer profile, for the table that the rule relates to.
There are a number of rule types that can be built using the “Rule Builder”.
Value: Compares a field in the target table with an absolute value. E.g. Order Quantity > 0.
Simple Lookup: Validates the contents of a field in the target table exists in another table. E.g. Gender field is validated against a list of valid gender codes.
Complex Lookup: Initially Validates the contents of a field in the target table exists in another table. If found, the contents of another field can be compared against an absolute value. E.g. Lookup Gender, if found check the title of the person is relevant to their gender.
Foreign Key: Checks that a field containing a foreign key is contained in the parent table. E.g. An Order has a valid Customer ID.
Compare: Compares the contents of a field with the contents of another field in the same table. E.g. Date Of Birth must be before Date Of Death.
From the LOAD-it home screen click on the "JAIL-it" icon associated with your Data Loader. Our first task is to ensure that the Jailer check-box is enabled.
We can now drill into individual tables within this Data Loader in order to create some business rules validation.
Click on the "Rule Builder" button to create a new rule. The example below shows a "Value" rule which checks that the customer has a name entered - i.e. that the value of the Customer Name field has a text length of greater than zero.
From a technical perspective what the Rule Builder is doing is to create SQL User Defined Function (UDF) which contains the logic required to apply the rule to your data. The “Rule Name” field is what will be reported in the “Data Jail” as the description of the reason for failure; and is also the name of the UDF. (If a UDF of that name already exists, you will be prompted to check the "Overwrite Existing" box.)
Editing JAIL-it Rules
The JAIL-it Rule Builder is a unidirectional mechanism, so you can't directly "edit" rules in the traditional sense. If you need to change the properties of a rule, you have two options:
- Create a new rule, with the same name, and "overwrite" the previous one.
- Edit the resultant UDF in Management Studio.
(The ability to edit rules is a feature request for a future version of Data Academy.)
Running The Jailer
Having now checked the jailer is enabled and defined a rule, all that remains is to test whether the rule is working. Remember, the jailer is part of the data loader process, therefore to execute the jailer all we need to do is run a data load, by selecting "Run" from Data Loader Central.
Now read about Handling Jailed Data!