Summary

Question

What causes the Data Transformation failing with error "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_LI_EXP". The conflict occurred in database table dbo.TIME_DIMENSION", column 'ID' in FlexNet Operation On-Premises..

Sample from flexnet log.

2016-04-11 07:01:01,193 ERROR [reporting.services] [Sched1_Worker-13] Reporting Data Transformation Failed:
[Incident# 8611-07118] com.flexnet.operations.exceptions.reporting.DataAccessException: Data transformation failed for entity LineItem. [Incident# 8611-07118]
                at com.flexnet.operations.reporting.transformer.BaseDataTransformer.execute(BaseDataTransformer.java:208)
                at com.flexnet.operations.reporting.transformer.DimensionsDataTransformer.execute(DimensionsDataTransformer.java:29)
                at com.flexnet.operations.services.reporting.DataTransformationService.executeDataTransformation(DataTransformationService.java:169)
                at com.flexnet.operations.services.reporting.DataTransformationService.performTransformation(DataTransformationService.java:95)
                at com.flexnet.operations.services.reporting.ReportingTransformJob.executeJob(ReportingTransformJob.java:38)
                at com.flexnet.platform.services.scheduler.AbstractStatefulJob.execute(AbstractStatefulJob.java:62)
                at org.quartz.core.JobRunShell.run(JobRunShell.java:191)
                at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:516)
Caused by: java.sql.BatchUpdateException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_LI_EXP". The conflict occurred in database "fnpops2016reporting", table "dbo.TIME_DIMENSION", column 'ID'.
                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1160)
                at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:519)
                at com.flexnet.operations.reporting.transformer.BaseDataTransformer.executeBatch(BaseDataTransformer.java:246)
                at com.flexnet.operations.reporting.transformer.LineItemTransformer.executeEntityBatch(LineItemTransformer.java:177)
                at com.flexnet.operations.reporting.transformer.BaseDataTransformer.updateBatch(BaseDataTransformer.java:232)
                at com.flexnet.operations.reporting.transformer.BaseDataTransformer.execute(BaseDataTransformer.java:191)
                ... 7 more

Answer

Please check for line items that have abnormal expiration dates in OPS_ENTITLEMENT_PRODUCT table, VALID_UNTIL column.

Look for dates that are either before or after entries in the TIME_DIMENSION table.

Make sure that all entitlements are with an expiry dates that are are set within time dimension range.