Compare queries have 3 types of columns, Keys, Values and Informational. When two data sources are compared, all value columns use the value comparison to determine if an error should be raised.
Value comparison consists of error and threshold options, each having 3 modes.
The error options decide in which cases comparison of two values results in the compare query raising an error.
The error options are:
Option 1 is the default option, which simply compares the two values and raises an error if they are not the exact same value. Option 2 and 3 only raises errors if the values are not equal and the larger value is in the correct data source.
Imagine a scenario were we are comparing planned costs versus actual costs. It would be an error if actual costs are higher than planned costs, however, it would be fine if the actual costs are lower than the planned costs.
Threshold is the allowed difference between two values, that will not result in an error.
To set the threshold, user has 3 options:
The first two options are calculated the same, it’s a simple value, where comparer raises an error if the difference is more than the threshold value.
Percentage threshold is calculated as the difference between the first and second data source divided by the second data source, or: ((Datasource2 value – Datasource1 Value) / Datasource2 Value)
To visualize these differences lets compare two data sources, Planning and Actual against each other with different options. Both will contain the column Cost, with Planning’s column having the value 1000 and Actual having 900.
|
No threshold |
Threshold: 99 |
Threshold: 150 |
Threshold: 5% |
Threshold: 15% |
|
|
Not Equal |
 |
 |
 |
 |
 |
|
Planning more than Actual |
 |
 |
 |
 |
 |
|
Planning less than Actual |
 |
 |
 |
 |
 |
The results can be summarized as such:
