SYMPTOM:
When running a custom time window dataload in Enterprise Manager, the process fails with a violation of duplicate values being inserted in Strategy 9.x. The MSTRMigration.log shows the following error when the database is Oracle (this error may vary depending on the database):
Oracle Error seen in the DSSErrors.log:
ORA-00001: unique constraint ... violated
The user may notice that before the custom time window dataload was performed, there was a previous instance of a failed dataload in the MSTRMigration.log. This results in a recovering window that is run during the custom time window dataload. The user sees that the recovering window time range is within the custom window time range as shown below:
MSTRMigration.log output:
---------------------------------------------------------
Migrate fact data from custom time window
---------------------------------------------------------
8/19/2013 8:46:23 PM Begining of Window : 8/14/2013 12:00:01 AM
8/19/2013 8:46:23 PM End of Window : 8/19/2013 8:46:19 PM
8/19/2013 8:46:23 PM 1 Intelligence Servers listed in Database.
8/19/2013 8:46:24 PM Previous incomplete migration found.
8/19/2013 8:46:24 PM Retrieving previous timestamp.
---------------------------------------------------------
Recovering: Window Begin = 8/15/2013 9:00:28 PM; Window End = 8/19/2013 2:35:15 PM.
---------------------------------------------------------
CAUSE:During a custom time window dataload, the process starts out by removing data from the warehouse within the time window as shown below:
MSTRMigration.log output:
---------------------------------------------------------
Remove data from custom time window
---------------------------------------------------------
...
8/19/2013 8:46:20 PM Begining of Window : 8/14/2013 12:00:01 AM
8/19/2013 8:46:20 PM End of Window : 8/19/2013 8:46:19 PM
...
Then, the process starts to move data to the warehouse tables for the custom time window. However, if it finds a previously failed dataload, it will first go into the recovering window:
MSTRMigration.log output:
---------------------------------------------------------
Migrate fact data from custom time window
---------------------------------------------------------
8/19/2013 8:46:23 PM Begining of Window : 8/14/2013 12:00:01 AM
8/19/2013 8:46:23 PM End of Window : 8/19/2013 8:46:19 PM
8/19/2013 8:46:23 PM 1 Intelligence Servers listed in Database.
8/19/2013 8:46:24 PM Previous incomplete migration found.
8/19/2013 8:46:24 PM Retrieving previous timestamp.
---------------------------------------------------------
Recovering: Window Begin = 8/15/2013 9:00:28 PM; Window End = 8/19/2013 2:35:15 PM.
---------------------------------------------------------
However, after successfully migrating data for that recovering window, the process then starts the actual custom time window and attemps to migrate some of the same data that was already inserted through the recovering window:
MSTRMigration.log output:
---------------------------------------------------------
Custom Window: Window Begin = 8/14/2013 12:00:01 AM; Window End = 8/19/2013 8:46:19 PM.
---------------------------------------------------------
It is during this step that the process errors out as duplicate entries are being inserted into the database.
ACTION:Update the EM_IS_LAST_UPDATE table as per the steps mentioned in the following Strategy KnowledgeBase technical note so only the default row has "IS_STATUS=0" and all other rows have "IS_STATUS=1".
KB8192 - 'More than one failed migration windows were found. This process cannot continue' error message appears when performing a dataload in Strategy Administrator - Enterprise Manager.
After doing so, the dataload process will no longer find a previously failed incomplete dataload. Then, re-run the custom time window and it will reload the data again without going into the recovering window mode and will not run into the issue of duplicate entries being inserted into the database.
Enterprise Manager warehouse modification:
WARNING:
Manually editing values in the Strategy Enterprise Manager warehouse incorrectly may cause serious, project-wide problems that may make your project unusable and/or the data load to fail. Since these are user-initiated changes, they are not covered by any Strategy warranty. Users are strongly encouraged to back up the Strategy Enterprise Manager warehouse prior to any alteration.
