The core of the exception manager is that each exception has a unique primary key. This unique key can be a single column or a composite key made out of multiple columns, but it can only occur once in the results of a test.
The reason primary keys need to be unique lies in the way exception manager works. If an exception occurs, a user, normally the exception owner will have to change value columns in the underlying data source, to fix the exception.
The next time the test will be executed one of these will happen:
- Test’s primary key did not contain changed columns
- Exception closes or stays open, depending on if the user fixed the underlying data source.
- Test’s primary key contained changed columns
- Exception closes. A new exception is created if the user did not fix the underlying data source.
By default the primary key of a test with exception manager enabled is a combination of all of the test’s columns except for the amount column.
This works as a good starting point, but it has two issues:
- Danger of creating new exceptions as explained above
- When column is added to test
- All open exceptions are closed and recreated
- Exceptions marked as “Not an exception” will be closed and reopened
The primary key tab in exception manager properties helps user set a unique primary key. For the custom section to work accurately make sure you have ran your test. The resulting data will be used to determine if combination of columns is unique.
Note: Even though a combination of columns is unique for current dataset it is not certain it will be unique for future data.
Lets look at a test and see how it’s columns can be combined into a primary key, which columns work together, which cause primary key error and how those errors effect the test.
Here we see a query that returns 5 columns, ProductID, StartDate, EndDate, StandardCost and ModifiedDate.
Looking at the columns, it is certain we’d want StandardCost to be our AmountColumn. That means the rest of the columns would be our primary keys. While this causes a unique primary key, it will recreate exceptions if we need to update EndDate or ModifiedDate. This behavior also makes it harder to later add columns to the query.
Primary key correctly selected
When selecting the ProductID column along with either StartDate or EndDate columns, each exception becomes unique. This is likely the unique key we’re looking for which allows us to focus on the value of the other columns.
Primary key column selected with one or more value columns
When we have a primary key column and a value column selected it results in odd behavior. If we look at our query, if we were to select ProductID and StandardCost as our primary keys, the test would only work as long as StandardCost was unique for each ProductID. This test might run for months or even years without a failure, but eventually it’s likely it’d fail by returning duplications.
Partial primary key selected
This primary key failure is a probably the easiest to distinguish as the test will likely never successfully execute until other columns are added to the primary key. An example of this is if we’d select only ProductID as a primary key. In our case we’d have multiple duplications of each ProductID which would result in the test failing to execute.
One or more value columns selected
When only value columns are selected exceptions are chaotically linked together.