Checkpoints in MDW instances

  • 16 February 2023
  • 10 replies

Userlevel 4
Badge +1

I see this advanced menu option in all the tables but I don’t know how can it be used.



Best answer by Christian Hauggaard 17 February 2023, 15:17

View original

10 replies

Userlevel 4
Badge +1

Perhaps it’s related to the previous versions:

And it do nothing in the last version isn’t it?

Userlevel 1

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.

Userlevel 4
Badge +1

@ShivamKapoor I don’t undestand how it works.

I 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.


Userlevel 3
Badge +3

Have you set any field validation rules ?
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.

Userlevel 4
Badge +1

@Syed Yousuf I think that this option do nothing in the current version

Userlevel 6
Badge +5

@rvgfox The purpose of this feature is to halt the execution if a certain number of error rows appear in the table. So you need to define a data validation rule that results in errors, in order to see the checkpoints have an effect and for the execution to stop.

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. 


Userlevel 6
Badge +7

The other way of using this is when you need to check if an external SSIS package ran successfully, see:

Userlevel 4
Badge +1

@rory.smith In the last version of TX, SSIS it’s deprecated

Userlevel 3
Badge +1

@Christian Hauggaard, Can Checkpoints be used to stop an execution based on validation rules that use Warnings instead of Errors?

Also, why are the value types “Percentage of value” and “Value” greyed out? What do they do?



Userlevel 6
Badge +5

Hi @pontus.berglund it can only be setup for errors, not warnings.

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.