KB7509: How does the recovery mechanism in MicroStrategy Enterprise Manager 9.x work?

MicroStrategy Enterprise Manager 9.x runs a data load process to update the data in the MicroStrategy Enterprise Manager warehouse. This data load process consists of two parts: the metadata data load and the statistics data (fact data) load. The metadata migration is further divided into configuration object migration and project metadata migration.

 

During the metadata data load, MicroStrategy Enterprise Manager 9.x connects to the different MicroStrategy Intelligence Servers that it tracks using the MicroStrategy Intelligence Server COM API and retrieves the information created and/or modified since the last data load.

 

 

During the statistics data load, MicroStrategy Enterprise Manager 9.x runs the Structured Query Language (SQL) scripts stored in the EM_SQL table in sequence to migrate the data from the statistics tables into its Fact tables.

 

 

The metadata data load is always performed prior to the statistics data load. If there are multiple projects and MicroStrategy Intelligence Servers that MicroStrategy Enterprise Manager 9.x tracks, the metadata migrations for all the projects under various MicroStrategy Intelligence Servers are conducted prior to the statistics migrations.

 

 

In cases where the data load fails, MicroStrategy Enterprise Manager 9.x uses a mechanism to recover from the failed load.

 

Case A: Data load failure during metadata migration

 

To illustrate how this recovery mechanism works, consider the following example: 

• MicroStrategy Enterprise Manager 9.x is configured to track a MicroStrategy Intelligence Server with project A.
• Assume that the data load fails during the metadata data load of project A:

   

  IS_PROJ_GUID	IS_ITEM_ID	IS_WIN_BEGIN	IS_WIN_END	IS_PROC_BEGIN	IS_PROC_END	IS_STATUS
  00000.....	0	12/5/13 6:00:13 PM	2/20/14 1:20:58 PM	2/20/14 1:20:58 PM	2/20/14 1:20:58 PM	0
  CONFIGURATION...	25	12/5/13 6:00:13 PM	2/20/14 1:20:58 PM	2/20/14 1:20:58 PM	2/20/14 1:21:02 PM	1
  87D017......	9	12/5/13 6:00:13 PM	2/20/14 1:20:58 PM	2/20/14 1:21:07 PM	2/20/14 1:21:08 PM	0

 

Users realize that three rows similar to those ones above are inserted in the EM_IS_LAST_UPDATE table. The IS_ITEM_ID column indicates that the load failed during the transfer of the 9th item, DOCUMENT in this case and the IS_STATUS=0 indicates that the load has failed. The row that contains the IS_PROJ_GUID=0 is entered with the start of every migration. The 2nd row seen in the above table for CONFIGURATION indicates the status of the configuration objects' data load. In the above case, since the value of IS_STATUS for this row is 1, it indicates that the configuration objects' migration has been completed.

 

When the next load is started, the recovery mechanism restarts the migration from project A-item 9 and completes the data load by transferring the metadata information for project A followed by the statistics migration for project A. This recovery spans the window of IS_WIN_BEGIN up to IS_WIN_END above. So, assume that the first load after the failure is kicked off at the time '2014-02-20 17:45'. This first portion of the recovery mechanism will try to bring the data from the failed state starting from '12/5/13 6:00:13 PM' until '2/20/14 1:20:58 PM'.

 

Next, the recovery mechanism runs a full data load starting with the Metadata migrations and followed by the statistics migrations. This load spans the IS_WIN_END (will be the IS_WIN_BEGIN of the full load) of the failed data load up to the time the full data load was started. Thus, the EM_IS_LAST_UPDATE table will be similar to the following:

 

 

The recovery mechanism updates the row with IS_STATUS=0 and sets the value of this column to '1' if the recovering load is successful. It also updates the IS_ITEM_ID and IS_PROC_END columns associated with the failed load. The IS_PROC_END (time) value for the row with IS_PROJ_GUID=0 will be slightly later than the same value for the row with the IS_PROJ_GUID=EA6086B… since it covers the whole time of the recovering load.

 

If there is more than one project being tracked (e.g., there is another project B selected) with the order of projects A and B in the configuration window and if the metadata data load fails during the migration of the project A, then the recovering load starts from the item of failure in project A, it finishes the MD migration for A followed by the MD migration for project B. The recovering data load, then, runs a statistics migration for these two projects. Finally, the recovering data load runs a full data load covering the time between the IS_WIN_END of the failed load and the time that the recovering data load was started.

 

Case B: Data load failure during Fact migration

• Assume that the data load process fails during the statistics data load
• Since the statistics data load consists of transactional statements that contain multiple SQL passes (called sequences), assume that the load fails at Transaction 2 and Sequence 13.

   

  IS_PROJ_GUID	IS_ITEM_ID	IS_WIN_BEGIN	IS_WIN_END	IS_PROC_BEGIN	IS_PROC_END	IS_STATUS
  0000.........	3	1/21/13 12:00:01 AM	2/20/14 2:41:09 PM	2/20/14 2:41:12 PM	2/20/14 2:41:12 PM	0
  CONFIGURATION…	24	1/21/13 12:00:01 AM	2/20/14 2:41:09 PM	2/20/14 2:41:14 PM	2/20/14 2:41:24 PM	1
  87D017......	24	1/21/13 12:00:01 AM	2/20/14 2:41:09 PM	2/20/14 2:41:25 PM	2/20/14 2:42:03 PM	1

 

The users will realize that three rows similar to those ones above are inserted in the EM_IS_LAST_UPDATE table. The IS_ITEM_ID=3 indicates that the data load has failed within the fourth transaction (numbering of the transactions starts with 0) after the metadata migration was complete as the value 24 in the second and third rows show. The value of zero in IS_STATUS also indicates that the load failed during the fact migration.

 

When the next data load kicks off, the recovery mechanism restarts the statistics data load from the beginning of the Transaction 3, completes the statistics data load for the failed load. The data load, then runs a full data load (metadata and statistics) to transfer the data from the time of failure until the time the recovery load is kicked off.

 

If the recovering load is successful, the rows above that indicate the failure are overwritten similar to the metadata data load recovery as follows: 

 

 

For this particular example, the recovering data load was kicked off at 14:49 the same day. It is important to know that the recovery process does not rerun the metadata data load for the failed interval, i.e., between the times '1/21/13 12:00:01 AM' and '2/20/14 2:41:09 PM' since metadata data load was successful in the failed load.

 

Unless the MicroStrategy Enterprise Manager data load is in a failed state, the IS_STATUS column should read 1 for all the rows but the first one that always has IS_STATUS=0.

 

Users should consult to the MSTRMigration.log in case of a failure as it contains the failing SQL statement and/or the error message regardless of the metadata data load or the statistics data load.

 

The MSTRMigration.log also includes the information about the recovering load. In the case of a recovery from a failure during a statistics data load, the users will notice that the next load will dump information similar to the following when it is started. In the following case, the recovery mechanism is attempting to re-run a data load which failed during metadata migration.2/20/2014 1:23:44 PM   1 Intelligence Servers listed in Database.
2/20/2014 1:23:45 PM   Previous incomplete migration found.

2/20/2014 1:23:45 PM   Retrieving previous timestamp.

---------------------------------------------------------

Recovering: Window Begin = 12/5/2013 6:00:13 PM; Window End = 2/20/2014 1:20:58 PM.

---------------------------------------------------------

2/20/2014 1:23:46 PM   Recovering previous project migration.

2/20/2014 1:23:46 PM   Begin metadata migrations.

................

 

In the case of a recovery from a failure during fact migration, the logs can be as shown below:2/20/2014 2:47:25 PM   1 Intelligence Servers listed in Database.
2/20/2014 2:49:12 PM   Previous incomplete migration found.

2/20/2014 2:49:12 PM   Retrieving previous timestamp.

---------------------------------------------------------

Recovering: Window Begin = 1/21/2013 12:00:01 AM; Window End = 2/20/2014 2:41:09 PM.

---------------------------------------------------------

2/20/2014 2:49:12 PM   Start fact migrations.
........