KB36694: How to restore missing fact data in MicroStrategy Enterprise Manager 9.x

In rare cases, migration time windows may be skipped over due to a failure during the data load process. If the statistics tables still have data, the following steps can be followed to move the data to the fact tables for the missing time window.
 

• Open up Strategy Enterprise Manager Console and navigate to tools>options and check the 'Log migration SQL' checkbox as shown below:
  

• Go to tools>data load options and run a custom time window for the period where data is missing as shown in the image below:
  

• The dataload will likely fail with a 'PRIMARY_KEY' violation as noted below; this is to be expected depending on where the dataload failed.
  

• Navigate to C:\Program Files\MicroStrategy\Enterprise Manager and open up the MSTRMigration.log file and look for the section titled 'Remove data from custom time window'. It should look similar to the following:     
  
  Sample Code/Error
  
  5/31/2011 6:49:53 PM   Server: Strategy ANALYTICS MODULES Project name: Strategy TUTORIAL
  5/31/2011 6:49:53 PM   Begining of Window : 5/1/2011 12:00:01 AM
  5/31/2011 6:49:53 PM   End of Window : 5/31/2011 6:49:53 PM
  5/31/2011 6:49:53 PM   Removing data from table 'IS_REP_FACT'.
  5/31/2011 6:49:53 PM   DELETE FROM IS_REP_FACT WHERE IS_PROJ_ID IN (5) AND EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND IS_SERVER_ID = 1
  5/31/2011 6:49:53 PM   Removing data from table 'IS_DOC_FACT'.
  5/31/2011 6:49:53 PM   DELETE FROM IS_DOC_FACT WHERE IS_PROJ_ID IN (5) AND EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND IS_SERVER_ID = 1
  5/31/2011 6:49:53 PM   Removing data from table 'IS_SESSION'.
  5/31/2011 6:49:53 PM   DELETE FROM IS_SESSION WHERE EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (5)) AND IS_SERVER_ID = 1
  5/31/2011 6:49:53 PM   Removing data from table 'IS_SESSION_TMP'.
  5/31/2011 6:49:53 PM   DELETE FROM IS_SESSION_TMP WHERE RECORDTIME BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND SESSIONID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (5)) AND SERVERID = '1'
  5/31/2011 6:49:54 PM   Removing data from table 'IS_SESSION_TMP1'.
  5/31/2011 6:49:54 PM   DELETE FROM IS_SESSION_TMP1 WHERE EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (5)) AND IS_SERVER_ID = 1
  5/31/2011 6:49:54 PM   Removing data from table 'IS_SESSION_TMP2'.
  5/31/2011 6:49:54 PM   DELETE FROM IS_SESSION_TMP2 WHERE EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (5)) AND IS_SERVER_ID = 1
  5/31/2011 6:49:54 PM   Removing data from table 'IS_PROJECT_FACT_1'.
  5/31/2011 6:49:54 PM   DELETE FROM IS_PROJECT_FACT_1 WHERE EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND IS_PROJ_ID IN (5) AND IS_SERVER_ID = 1
  
  
   
  
• Copy and paste the SQL into a text editor, and remove any references to IS_SERVER_ID (in table IS_SESSION_TMP, the column is SERVERID instead of IS_SERVER_ID) as shown below:
  
  Sample Code/Error
  
  DELETE FROM IS_REP_FACT WHERE IS_PROJ_ID IN (5) AND EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53'
  DELETE FROM IS_DOC_FACT WHERE IS_PROJ_ID IN (5) AND EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53'
  DELETE FROM IS_SESSION WHERE EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (5))
  DELETE FROM IS_SESSION_TMP WHERE RECORDTIME BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND SESSIONID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (5))
  DELETE FROM IS_SESSION_TMP1 WHERE EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (5))
  DELETE FROM IS_SESSION_TMP2 WHERE EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (5))
  DELETE FROM IS_PROJECT_FACT_1 WHERE EM_RECORD_TS BETWEEN '05/01/2011 00:00:01' AND '05/31/2011 18:49:53' AND IS_PROJ_ID IN (5)
  
  
   
  
• Connect to the Enterprise Manager Warehouse using DBQuery Tool, and run the SQL from step 5. It is recommended to make a copy of the database before doing this should anything go wrong. This will delete any partial data that may have been moved into the fact during a previous migration.
  
• Run the custom time window again for the specified time window as done in step 2. This may return an error as shown below, if so refer to the following Strategy Knowledge Base technical note:
   
  
• KB8192(KB6400-721-0093 ): 'More than one failed migration windows were found. This process cannot continue' error message appears when performing a dataload in Strategy Administrator - Enterprise Manager.

• Once resolved, the custom time window will run successfully and migrate any data over from the statistics tables to the fact tables for the missing time window so that it can be reported on. 
  

KB36694