KB19807: Why does custom time window delete rows from fact tables before populating them again in MicroStrategy Enterprise Manager 9.x?

• Strategy

This article explains why the custom time window delete rows from fact tables before populating them again in MicroStrategy Enterprise Manager 9.x.

In Strategy 9.x, before populating data in the fact tables, a custom time window data load first removes the already existing rows from the fact tables for the time period specified.
For example, a user performs custom time window data load for January 13 - February 12, 2013 as shown below:

As seen in the MSTRMigration log below, the rows are first removed from the fact tables listed below:

IS_REP_FACT
IS_DOC_FACT
IS_SESSION
IS_PROJECT_FACT_1

An excerpt from the MSTRMigration log (with SQL enabled) is shown below. For details on how to enable SQL to be seen in the MSTRMigration log, go to the following Strategy Knowledge Base technical note:
KB30594: How to enable the SQL executed during the Strategy Enterprise Manager 9.x data load (ETL) to be logged to the MSTRMigration.log


---------------------------------------------------------
Remove data from custom time window
---------------------------------------------------------
2/13/2013 10:46:27 AM   Server: LOCALHOST Project name: Strategy TUTORIAL
2/13/2013 10:46:27 AM   Begining of Window : 1/13/2013 12:00:01 AM
2/13/2013 10:46:27 AM   End of Window : 2/12/2013 11:59:59 PM
2/13/2013 10:46:27 AM   Removing data from table 'IS_REP_FACT'.
2/13/2013 10:46:27 AM   DELETE FROM IS_REP_FACT WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59'
2/13/2013 10:46:27 AM   Removing data from table 'IS_DOC_FACT'.
2/13/2013 10:46:27 AM   DELETE FROM IS_DOC_FACT WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59'
2/13/2013 10:46:27 AM   Removing data from table 'IS_SESSION'.
2/13/2013 10:46:27 AM   DELETE FROM IS_SESSION WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59'
2/13/2013 10:46:27 AM   Removing data from table 'IS_SESSION_TMP'.
2/13/2013 10:46:27 AM   DELETE FROM IS_SESSION_TMP WHERE RECORDTIME BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59'
2/13/2013 10:46:27 AM   Removing data from table 'IS_SESSION_TMP1'.
2/13/2013 10:46:27 AM   DELETE FROM IS_SESSION_TMP1 WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59'
2/13/2013 10:46:27 AM   Removing data from table 'IS_SESSION_TMP2'.
2/13/2013 10:46:27 AM   DELETE FROM IS_SESSION_TMP2 WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59'
2/13/2013 10:46:27 AM   Removing data from table 'IS_PROJECT_FACT_1'.
2/13/2013 10:46:27 AM   DELETE FROM IS_PROJECT_FACT_1 WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59'

During a custom time window data load, the likelihood of seeing duplicate rows is high. It is very likely that the user already had that data loaded but because they missed something the first time they try to load it again. This can lead to duplicate rows and to prevent this type of duplication, the existing rows for the custom time window timeframe are first removed and fresh data is moved from the statistics tales.
In a manual or scheduled data load, having duplicate rows is not a concern.

The beginning of the manual or scheduled data migration window is determined by the previous data load's timestamp entry in the EM_IS_LAST_UPDATE table so only the data that is not already there in the fact tables is loaded.

Comment

0 comments

Details

Knowledge Article

Published:

June 2, 2017

Last Updated:

June 2, 2017

--------------------------------------------------------- Remove data from custom time window --------------------------------------------------------- 2/13/2013 10:46:27 AM Server: LOCALHOST Project name: Strategy TUTORIAL 2/13/2013 10:46:27 AM Begining of Window : 1/13/2013 12:00:01 AM 2/13/2013 10:46:27 AM End of Window : 2/12/2013 11:59:59 PM 2/13/2013 10:46:27 AM Removing data from table 'IS_REP_FACT'. 2/13/2013 10:46:27 AM DELETE FROM IS_REP_FACT WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59' 2/13/2013 10:46:27 AM Removing data from table 'IS_DOC_FACT'. 2/13/2013 10:46:27 AM DELETE FROM IS_DOC_FACT WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59' 2/13/2013 10:46:27 AM Removing data from table 'IS_SESSION'. 2/13/2013 10:46:27 AM DELETE FROM IS_SESSION WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59' 2/13/2013 10:46:27 AM Removing data from table 'IS_SESSION_TMP'. 2/13/2013 10:46:27 AM DELETE FROM IS_SESSION_TMP WHERE RECORDTIME BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59' 2/13/2013 10:46:27 AM Removing data from table 'IS_SESSION_TMP1'. 2/13/2013 10:46:27 AM DELETE FROM IS_SESSION_TMP1 WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59' 2/13/2013 10:46:27 AM Removing data from table 'IS_SESSION_TMP2'. 2/13/2013 10:46:27 AM DELETE FROM IS_SESSION_TMP2 WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59' 2/13/2013 10:46:27 AM Removing data from table 'IS_PROJECT_FACT_1'. 2/13/2013 10:46:27 AM DELETE FROM IS_PROJECT_FACT_1 WHERE EM_RECORD_TS BETWEEN '01/13/2013 00:00:01' AND '02/12/2013 23:59:59'