KB198868: MicroStrategy Enterprise Manager data load fails with the error 'unique constraint violated.INSERT INTO IS_SESSION ....'

SYMPTOM
Strategy Enterprise Manager 9.x data load fails with an error similar to the following:

Error executing SQL :Execute Query failed. 
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [23000:1: on SQLHANDLE] [Strategy][ODBC Oracle Wire Protocol driver][Oracle]ORA-00001: unique constraint (xxx.IS_SESSION_PK) violated. INSERT INTO IS_SESSION (EM_RECORD_TS, EM_LOAD_TS, ........MINUTE_ID, IS_REPOSITORY_ID FROM IS_SESSION_TMP1

 
Note: The above error message is from an Oracle database. The exact error can be different based on the database type being used for the Strategy Enterprise Manager warehouse.
 
STEPS TO REPRODUCE

1. Consider that a data load is initiated on Strategy Enterprise Manager 9.4.x.
2. For some reason, this data load fails when inserting data into the table IS_SESSION_TMP1 or is cancelled right during this transaction.
3. A data load is again kicked off from Strategy Enterprise Manager console 9.4.x and the primary key violation error is received.

 
CAUSE
This issue can be seen in the case where a data load failure is first encountered or is cancelled when inserting data into the IS_SESSION_TMP1 table. 
As part of the data load process, session data from IS_SESSION_STATS is inserted into the IS_SESSION_TMP1 table. When the data load fails when inserting data into this table, the next time a data load kicks off, the data load recovery process kicks in. With the recovery process, data load picks off from the point of failure again and Statistics data from IS_SESSION_STATS is again inserted into this table. As there are no primary key constraints on this table, this transaction finishes successfully. However, when these duplicate entries are attempted to be inserted into the IS_SESSION table, a primary key violation error is received.
 
ACTION
The following steps can be implemented to fix this issue:

1. Run the following query against the Enterprise Manager Warehouse
   

   
   SELECT * FROM EM_IS_LAST_UPDATE WHERE IS_STATUS=0

   
   
2. Update the EM_IS_LAST_UPDATE table so that there is only one row with IS_STATUS=0 and that should be the default row as explained in the following Strategy Knowledge Base article: KB16148: How to identify unused facts and tables without checking each fact individually in MicroStrategy Developer 9.4.x-10.x
3. Run a custom window data load for the data load window which was initially unsuccessful i.e., the 1st data load which failed when inserting data into IS_SESSION_TMP1 (A full data load will first truncate the data in IS_SESSION_TMP1 before inserting new data. With a recovery data load, this truncation does not happen and duplicate rows end up in the table)
4. Run a manual data load so that the Enterprise Manager Warehouse data is in sync with the Statistics tables and metadata.