FlexNet Operations On-Premises error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AO_ORG"

Yes

Symptoms:

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

Diagnosis:

This error occurs when a Device in FNO is created without an account or sold to value. This creates a record in LFS_HOST_HISTORY table with enterprise ID as "null". Since the table "ORG_UNIT_DIMENSION" has not a null column structure for ID. It doesn't accept the null values and gives an error. Below is the sample logs for the issue:

020-11-04 12:02:02,934 [Thread-506] ERROR (services) [${ctx:TENANTID}] Reporting Data Transformation Failed:
com.flexnet.operations.exceptions.reporting.DataAccessException: Data transformation failed for entity DeviceAddonFact at row 352.568. [Incident# 7229-292491]
    at com.flexnet.operations.reporting.transformer.BaseDataTransformer.execute(BaseDataTransformer.java:254) ~[classes:?]
    at com.flexnet.operations.services.reporting.DataTransformationService.executeFactTransformer(DataTransformationService.java:219) ~[classes:?]
    at com.flexnet.operations.services.reporting.DataTransformationService.executeDataTransformation(DataTransformationService.java:177) ~[classes:?]
    at com.flexnet.operations.services.reporting.DataTransformationService.performTransformation(DataTransformationService.java:100) [classes:?]
    at com.flexnet.operations.services.reporting.StartNowReportingTransformJob.executeJob(StartNowReportingTransformJob.java:39) [classes:?]
    at com.flexnet.platform.services.scheduler.Scheduler$SchedulerThread.run(Scheduler.java:146) [classes:?]
Caused by: java.sql.BatchUpdateException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AO_ORG". The conflict occurred in database "flexnetreporting", table "dbo.ORG_UNIT_DIMENSION", column 'ID'.
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1178) ~[sqljdbc4.jar!/:?]
    at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1077) ~[?:?]
    at com.flexnet.operations.reporting.transformer.BaseDataTransformer.executeBatch(BaseDataTransformer.java:292) ~[classes:?]
    at com.flexnet.operations.reporting.transformer.BaseDataTransformer.updateBatch(BaseDataTransformer.java:277) ~[classes:?]
    at com.flexnet.operations.reporting.transformer.BaseDataTransformer.execute(BaseDataTransformer.java:237) ~[classes:?]

Solution:

To avoid this issue, please refrain from creating the Devices without account Id. Alternatively, you can check all devices created with enterprise id as "null" in tables LFS_HOST and LFS_HOST history tables. Once the problematic devices are identified, they can be assigned the account manually from the FNO producer portal to fix the reporter transformation problems.