Symptoms

FlexNet Operations On-premises reporter transformation jobs fail with the error "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_USR". The conflict occurred in database "flexnetreporting", table "dbo.USER_DIMENSION", column 'ID'."

Diagnosis

This error occurs when a user in FlexNet Operations (FNO) is created without an account. This creates a record in PLT_USER table for the user but with no relation to account (ORG_ID) in PLT_ORGUNIT table of FlexNet Operations database. So when the transformation is run, it fails with the given error trying to insert transformed data into reporter DB and the table "USER_ORG_RELATION" as this table has "not a null" column structure for ORG_ID.

Or in simpler words, the table USER_ORG_RELATION doesn't accept the empty or null values for ORG_ID column and hence the error.  Below is the screenshot showing sample logs for the issue:

The above highlighted error means that the transformation failed for the record number 49 from the table USER_DIMENSION which got failed to Insert In to USER_ORG_RELATION table of flexnetreporting database.

Solution

Always make sure that all the users in FlexNet Operations is created with an account linked to it. Now to fix the issue, take the problematic record/user and do one of the following :

1. Delete the problematic record/user by following either "a " or "b"
   1. From FlexNet Operations DB i.e from PLT_USER table and from the flexnetreporting DB i.e from USER_DIMENSION table. (Or)
   2. Use soap webservice call deleteUser.
2. Fix the user, by linking them to an account by using soap call updateUser.