FNO data transformation to flexnetreporting fails with Error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_USR"

[#1]

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 FNO 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 FNO 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"

      a) from FNO DB i.e from PLT_USER table and from the flexnetreporting DB i.e from USER_DIMENSION   table.  (Or)

      b) Use soap webservice call deleteUser.

2. Fix the user, by linking them to an account by using soap call updateUser.