I see this advanced menu option in all the tables but I don’t know how can it be used.
Solved
Checkpoints in Prepare instances
+4Best answer by Christian Hauggaard
So the first step is to define the field validation rule that will result in an error.
In my example I want rows to result in an error (i.e. not reach the valid table), if my JSONField is empty.
Once I click add, then I can see it has been added.
Then I deploy and execute my table. If I preview my table, I can see I only have 2 rows in the valid table
If I go to reports and then errors
I can see that I have several records (104) that resulted in an error (i.e. did not pass the validation rule) and consequently were not put in the valid table because the JSONField was null.
I now set up a checkpoint for maximum 100 errors before the execution fails
And I deploy and execute, as expected we encounter an error in the execution, because only 100 errors are allowed and there are 104 errors in total.
If I now update my checkpoint to be maximum 105 errors allowed before the execution fails
The execution is successful because the maximum limit of errors (105) is not exceeded, since only 104 errors occurred.
+4Perhaps it’s related to the previous versions:
https://legacysupport.timextender.com/hc/en-us/articles/360051848311-Deploying-and-Executing
And it do nothing in the last version isn’t it?
The checkpoints are related to the execution. In the checkpoints you can specify the number of rows and the table. The checkpoint stops the execution of the table if it doesn't meet the row rules set.
+4I setup a table like this:
Executing the table (it has 32 records) nothing happens, except that it’s indicate in the log. I’ve tried the same with 30 ant the result it’s the same.
Perhaphs this feature it’s for the versions previous to the 6XXX.
Have you set any field validation rules ?
https://legacysupport.timextender.com/hc/en-us/articles/360051848311-Deploying-and-Executing
Verifying data against checkpoints: The process of checking the data that is being processed against the checkpoints you have specified. You can specify rules that will end the execution process if not met. This way, you avoid overwriting the data in your data warehouse with non-valuable data.
+5So the first step is to define the field validation rule that will result in an error.
In my example I want rows to result in an error (i.e. not reach the valid table), if my JSONField is empty.
Once I click add, then I can see it has been added.
Then I deploy and execute my table. If I preview my table, I can see I only have 2 rows in the valid table
If I go to reports and then errors
I can see that I have several records (104) that resulted in an error (i.e. did not pass the validation rule) and consequently were not put in the valid table because the JSONField was null.
I now set up a checkpoint for maximum 100 errors before the execution fails
And I deploy and execute, as expected we encounter an error in the execution, because only 100 errors are allowed and there are 104 errors in total.
If I now update my checkpoint to be maximum 105 errors allowed before the execution fails
The execution is successful because the maximum limit of errors (105) is not exceeded, since only 104 errors occurred.
+7The other way of using this is when you need to check if an external SSIS package ran successfully, see: https://legacysupport.timextender.com/hc/en-us/articles/210439263-How-to-make-a-scheduled-execution-fail-if-an-external-SSIS-package-fails-
Also, why are the value types “Percentage of value” and “Value” greyed out? What do they do?
Thanks,
Pontus
+5Hi
The percent of valid value and value can be applied for tables where there is at least one numeric field. By selecting a numeric field, these options will be available (no longer greyed out).
Percent of valid table can be set in order to stop the execution if the percentage of the field value of error records compared to the total records (i.e. valid and error records) goes above a certain level.
In the example below I have set up a validation rule on my table. Any records with a WaitTime greater than 999 will be marked as errors.
If deploy and execute the table and go to Errors in the Reports menu, and select the relevant database, I can see 7 error rows where the sum of the WaitTime is 12078
I then check my valid table and see that the sum of WaitTime for valid records is 12024.
So the total value for WaitTime, including both valid records and errors, is 12078+12024=24102
If I now divide the value of the field for the error records by this total value, I get:
12078/24102=0.5011=50.11% (TimeXtender rounds this to the nearest integer so 50%)
So if I set the following checkpoint of maximum 49%
Then I get the following error
However if I set the maximum value for the checkpoint at 50% or 51%, it executes successfully.
Regarding the value checkpoint option, this is the total value for the error records that is acceptable. So if I set the following checkpoint
I get an error because the total value for the error records is 12078, which is higher than the set maximum. If I set the checkpoint at 12078 or 12079 then the execution completes successfully.
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
