KB11295: Different levels of purging Statistics data in MicroStrategy Intelligence Server 9.x

• Strategy

Purge Statistics allows administrators to selectively delete obsolete data from their Statistics tables. In MicroStrategy Intelligence Server 9.x, statistics purging can be accomplished selectively at the server and project levels.

Purge Statistics allows administrators to selectively delete obsolete data from their Statistics tables.

In Strategy Intelligence Server 9.x, statistics purging can be accomplished selectively at the server and project levels. Consider a situation involving two Intelligence Servers, S1 and S2, sharing the same server definition (they may be in a cluster). There are two projects, P1 and P2, within both servers that are setup to log statistics to the same statistics location. An administrator may wish to selectively purge statistics for Project P1 only belonging to Intelligence Server S1 from the statistics tables.

Purge Statistics at Server Level
This option is located at Strategy Intelligence Server Configuration > Statistics > Purge, as shown below:

Upon clicking the 'Purge now' button, this option purges Statistics for all projects within that server. The purge operation involves a delete statement against the IS_SESSION_STATS table based on the specific server. This is achieved using the 'SERVERMACHINE' field, as can be seen from the example delete statement below:


Delete from IS_SESSION_STATS where (SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN To_Date('09-09-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND To_Date('10-09-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

MACHINE_NAME is the name of the server machine. The SERVERMACHINE field in IS_SESSION_STATS table in Strategy 9.x is populated with the machine name and port number in the following way:
MACHINE_NAME:34952, where 34952 is the port number.

Delete statements are also issued against the other 11 statistics tables based on the SERVERMACHINE. The following is an example of such a delete statement:


Delete from IS_DOCUMENT_STATS where (SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN To_Date('03-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND To_Date('05-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

Purge Statistics at Project Level
This option is located at Project Configuration > Statistics > Purge, as shown below:

Upon clicking the 'Purge now' button, this option purges statistics for the specific project across all servers. There is no delete statement issued against IS_SESSION_STATS table because this table does not contain any project-level information. Delete statements are issued against the other 11 statistics tables based on the ProjectID (Project GUID) field.

The following is an example of such a delete statement:


Delete from IS_SCHEDULE_STATS where ProjectID ='7E979C524B9933903BF3ACB0F10601F8' OR PROJECTID IS NULL AND RECORDTIME BETWEEN {ts'2013-11-18 00:00:00'} AND {ts'2013-12-18 00:00:00'}

Purge Statistics by Intelligence Server - Project Combination
This option is available at the project level by clicking on the 'Advanced' button, as shown below. Checking the box, 'Only purge statistics logged from the current Intelligence Server machine' and selecting the tables from which Statistics should be purged, results in purging of statistics for the specific project and the specific server. As before, there is no delete statement issued against IS_SESSION_STATS table because this table does not contain any project-level information. Delete statements are issued against the required tables based on what tables are selected.

The following is an example of such a delete statement:


Delete from IS_SCHEDULE_STATS where (SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (PROJECTID ='87D017124E099D2F78CFE7BAF3DDAB2C' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN To_Date('03-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND To_Date('05-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

Comment

0 comments

Details

Knowledge Article

Published:

April 25, 2017

Last Updated:

April 25, 2017

Delete from IS_SESSION_STATS where (SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN To_Date('09-09-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND To_Date('10-09-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

Delete from IS_DOCUMENT_STATS where (SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN To_Date('03-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND To_Date('05-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

Delete from IS_SCHEDULE_STATS where (SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (PROJECTID ='87D017124E099D2F78CFE7BAF3DDAB2C' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN To_Date('03-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND To_Date('05-10-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))