EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB330665: How to manually purge Statistics and Enterprise Manager warehouse data in MicroStrategy 2021

David Currin

Quality Engineer, Senior • MicroStrategy

In some scenarios, it may be desirable to manually purge historical Statistics and Enterprise Manager data from the warehouse database. This KB article will outline the possible scenarios and corresponding SQL queries to do so.

WARNING:
By design, the SQL queries provided in this KB article WILL RESULT IN LOSS OF DATA.  It is recommended to make a backup of the warehouse before proceeding to safeguard against accidental data loss.
 
NOTES:
All queries provided are for SQL Server database only.  Syntax must be modifed based on the database type.
All queries provided purge data for an example time period of 01-01-2015 to 12-31-2015.  Dates and times must be modified based on the deletion requirement.
 
SCENARIOS:

  1. Purge data between two dates
  2. Purge data between two dates for specific Intelligence Server machines
  3. Purge data between two dates for specific Strategy projects
  4. Purge data between two dates for specific Intelligence Server machines and specific Strategy projects
  5. Purge data for specific Strategy projects (not deleted from the metadata)
  6. Purge data for specific Strategy projects which have already been deleted from the metadata

 

  1. Purge data between two dates
    • Statistics Tables:


DELETE FROM IS_SESSION_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_SCHEDULE_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_REP_SEC_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_REP_STEP_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_REP_SQL_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_DOC_STEP_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_DOCUMENT_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_REPORT_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_PROJ_SESS_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_REP_COL_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_CACHE_HIT_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_PR_ANS_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_MESSAGE_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_PERF_MON_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_CUBE_REP_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_INBOX_ACT_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM CT_DEVICE_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM CT_EXEC_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM CT_MANIP_STATS WHERE RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';

 

  • Enterprise Manager Tables:
    1. The table IS_PROJECT_FACT_1 should be purged last. Hence, run the queries must be run in the order below.


DELETE FROM IS_SESSION WHERE EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_REP_FACT WHERE EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_DOC_FACT WHERE EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_PROJECT_FACT_1 WHERE EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';

 

  1. Purge data between two dates for specific Intelligence Server machines
    • Statistics Tables:
      1. In the queries below, replace the text ‘MACHINE_NAME’ with the name of the Intelligence Server Machine to purge data for and the text ‘34592’ with the port number for that Intelligence Server. (Hint: You can always query one of the tables and copy the exact string from the SERVERMACHINE column)


DELETE FROM IS_SESSION_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_SCHEDULE_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_SEC_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_STEP_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_SQL_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_DOC_STEP_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_DOCUMENT_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REPORT_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_PROJ_SESS_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_COL_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_CACHE_HIT_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_PR_ANS_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_MESSAGE_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_PERF_MON_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_CUBE_REP_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_INBOX_ACT_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM CT_EXEC_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM CT_MANIP_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952' OR SERVERMACHINE='MACHINE_NAME') AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));

 

  • Enterprise Manager Tables:
    1. In the queries below, replace the text ‘MACHINE_NAME’ with the name of the Intelligence Server Machine to purge data for and the text ‘34592’ with the port number for that Intelligence Server. (Hint: You can always query one of the tables and copy the exact string from the EM_APP_SRV_MACHINE column)
    2. The table IS_PROJECT_FACT_1 should be purged last. Hence, the queries must be run in the order below.


DELETE FROM IS_SESSION WHERE ((EM_APP_SRV_MACHINE='MACHINE_NAME:34952') AND (EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_FACT WHERE ((EM_APP_SRV_MACHINE='MACHINE_NAME:34952') AND (EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_DOC_FACT WHERE ((EM_APP_SRV_MACHINE='MACHINE_NAME:34952') AND (EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_PROJECT_FACT_1 WHERE ((EM_APP_SRV_MACHINE='MACHINE_NAME:34952') AND (EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));

 

  1. Purge data between two dates for specific Strategy Projects
    • Statistics Tables:
      1. In the queries below, replace the text '<PROJECTID>' with the ID of the Strategy Project to purge data for.


DELETE FROM IS_SCHEDULE_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_SEC_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_STEP_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_SQL_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_DOC_STEP_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_DOCUMENT_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REPORT_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_PROJ_SESS_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_COL_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_CACHE_HIT_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_PR_ANS_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_MESSAGE_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_PERF_MON_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_CUBE_REP_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_INBOX_ACT_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM CT_EXEC_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM CT_MANIP_STATS WHERE ((PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));

 

  • Enterprise Manager Tables:
    1. In the queries below, replace the text '<PROJECTID>' with the ID of the Strategy Project to purge data for.
    2. The table IS_PROJECT_FACT_1 should be purged last. Hence, the queries must be run in the order below.


DELETE FROM IS_DOC_FACT WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>')) AND EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_REP_FACT WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>')) AND EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_SESSION WHERE IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>'))) AND EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';
DELETE FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>')) AND EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00';

 

  1. Purge data between two dates for specific Intelligence Server machines and specific Strategy projects
    1. Statistics Tables:
      1. In the queries below, replace the text ‘MACHINE_NAME’ with the name of the Intelligence Server Machine to purge data for and the text ‘34592’ with the port number for that Intelligence Server. (Hint: You can always query one of the tables and copy the exact string from the SERVERMACHINE column)
      2. In the queries below, replace the text '<PROJECTID>' with the ID of the Strategy Project to purge data for.


DELETE FROM IS_SCHEDULE_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_SEC_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_STEP_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_SQL_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_DOC_STEP_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_DOCUMENT_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REPORT_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_PROJ_SESS_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_REP_COL_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_CACHE_HIT_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_PR_ANS_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_MESSAGE_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_PERF_MON_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_CUBE_REP_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM IS_INBOX_ACT_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));
DELETE FROM CT_EXEC_STATS WHERE ((SERVERMACHINE='MACHINE_NAME:34952') AND (PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00'));

 

  • Enterprise Manager Tables:
    1. In the queries below, replace the text ‘MACHINE_NAME’ with the name of the Intelligence Server Machine to purge data for and the text ‘34592’ with the port number for that Intelligence Server. (Hint: You can always query one of the tables and copy the exact string from the EM_APP_SRV_MACHINE column)
    2. In the queries below, replace the text '<PROJECTID>' with the ID of the Strategy Project to purge data for.
    3. The table IS_PROJECT_FACT_1 should be purged last. Hence, run the queries in the order below.


DELETE FROM IS_DOC_FACT WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>')) AND EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00' AND EM_APP_SRV_MACHINE='MACHINE_NAME:34952';
DELETE FROM IS_REP_FACT WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>')) AND EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00' AND EM_APP_SRV_MACHINE='MACHINE_NAME:34952';
DELETE FROM IS_SESSION WHERE IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>'))) AND EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00' AND EM_APP_SRV_MACHINE='MACHINE_NAME:34952';
DELETE FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>')) AND EM_RECORD_TS BETWEEN '01-01-2015 00:00:00' AND '12-31-2015 00:00:00' AND EM_APP_SRV_MACHINE='MACHINE_NAME:34952';

 

  1. Purge data for specific Strategy projects (not deleted from the metadata)
    • Statistics Tables:
      1. In the queries below, replace the text '<PROJECTID>' with the ID of the Strategy Project to purge data for.


DELETE FROM IS_SCHEDULE_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_REP_SEC_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_REP_STEP_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_REP_SQL_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_DOC_STEP_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_DOCUMENT_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_REPORT_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_PROJ_SESS_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_REP_COL_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_CACHE_HIT_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_PR_ANS_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_MESSAGE_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_PERF_MON_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_CUBE_REP_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_INBOX_ACT_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM CT_EXEC_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM CT_MANIP_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;

 

  • Enterprise Manager Tables:
    1. In the queries below, replace the text '<PROJECTID>' with the ID of the Strategy Project to purge data for.
    2. The table IS_PROJECT_FACT_1 should be purged last. Hence, the queries must be run in the order below.


DELETE FROM IS_DOC_FACT WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>'));
DELETE FROM IS_REP_FACT WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>'));
DELETE FROM IS_SESSION WHERE IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>')));
DELETE FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>'));

 

  1. Purge data for specific Strategy projects which have already been deleted from the metadata
    • Statistics Tables:
      1. In order to purge data when you DO NOT know the IDs of the Projects (as they may have been deleted), you can retrieve the IDs from the Enterprise Manager warehouse as follows:
        1. In the following query, the IS_PROJ_NAME field represents the name of a Strategy Project. Replace the text ‘Strategy Tutorial’ with the name of the Strategy Project to purge data for.
          SELECT ‘IS_PROJ_GUID’ FROM IS_PROJ WHERE IS_PROJ_NAME=‘Strategy Tutorial’;
        2. The returned value of the column IS_PROJ_GUID (32 character GUID) is the ID of the Strategy Project to purge data for. Use this value to purge data ONLY from the Statistics Tables.
      2. In the queries below, replace the text '<PROJECTID>' with the ID of the Strategy Project to purge data for.
      3. The table IS_PROJECT_FACT_1 should be purged last. Hence, the queries must be run in the order below.


DELETE FROM IS_SCHEDULE_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_REP_SEC_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_REP_STEP_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_REP_SQL_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_DOC_STEP_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_DOCUMENT_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_REPORT_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_PROJ_SESS_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_REP_COL_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_CACHE_HIT_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_PR_ANS_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_MESSAGE_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_PERF_MON_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_CUBE_REP_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM IS_INBOX_ACT_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM CT_EXEC_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;
DELETE FROM CT_MANIP_STATS WHERE PROJECTID ='<PROJECTID>' OR PROJECTID IS NULL;

 

  • Enterprise Manager Tables:
    1. In order to purge data from Enterprise Manager Tables when you DO NOT know the IDs of the Projects (as they may have been deleted), you can retrieve the IDs from the Enterprise Manager warehouse as follows:
      1. In the following query, the IS_PROJ_NAME field represents the name of a Strategy Project. Replace the text ‘Strategy Tutorial’ with the name of the Strategy Project you would like to purge data for.
        SELECT ‘IS_PROJ_GUID’ FROM IS_PROJ WHERE IS_PROJ_NAME=‘Strategy Tutorial’;
      2. The returned value of the column IS_PROJ_GUID (32 character GUID) is the ID of the Strategy Project you would like to purge data for. Use this value to purge data ONLY from the Statistics Tables.
    2. In the queries below, replace the text '<PROJECTID>' with the ID of the Strategy Project to purge data for.
    3. The table IS_PROJECT_FACT_1 should be purged last. Hence, the queries must be run in the order below.


DELETE FROM IS_DOC_FACT WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>'));
DELETE FROM IS_REP_FACT WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>'));
DELETE FROM IS_SESSION WHERE IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>')));
DELETE FROM IS_PROJECT_FACT_1 WHERE IS_PROJ_ID IN (SELECT IS_PROJ_ID FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJECTID>'));

Comment

0 comments

Details

Knowledge Article

Published:

June 21, 2017

Last Updated:

February 27, 2024