KB47468: How to delete Enterprise Manager Warehouse data

OVERVIEW
In Strategy version 9.x, it was possible to delete the Enterprise Manager Warehouse data by specifying the time frame and clicking the “WH data deletion” button in the Strategy Enterprise Manager Console as explained in  KB10191 .
 
In Strategy Secure Enterprise 10.x, Enterprise Manager has received a significant redesign and it is no longer possible to delete the warehouse data in a similar manner. An enhancement request has been logged to include the warehouse data deletion feature in a future release of Enterprise Manager. Strategy will continue to investigate and post updates as soon as they are available.
 
This technical note describes a workaround to delete the warehouse data in version 10.x. 
 
NOTES:

• “IS_SERVER_ID” in the WHERE clause is surrounded by square brackets because this can be omitted unless the requirement is to delete a specific server definition. The server definition can be found in the IS_SERVER table.
• The following queries are for a SQL Server database. It will be necessary to adjust the syntax if using against other types of databases.
• IS_PROJECT_FACT_1 should be last purged among the four queries

 
WAREHOUSE DATA DELETION
In Strategy 10.x, it is necessary to execute multiple DELETE statements directly against the following four fact tables in the Enterprise Manager Statistics Repository database.
- IS_DOC_FACT
- IS_REP_FACT
- IS_SESSION
- IS_PROJECT_FACT_1
 
 IS_DOC_FACT

DELETE FROM IS_DOC_FACT
WHERE
IS_PROJ_ID IN
 (
 SELECT IS_PROJ_ID
 FROM IS_PROJ
 WHERE IS_PROJ_GUID IN ('<PROJECT GUID TO DELETE 1>','<PROJECT GUID TO DELETE 2>')
 )
AND EM_RECORD_TS BETWEEN '<STARTING DATE FOR DELETE PERIOD>' AND '<FINISH DATE FOR DELETE PERIOD>'
[AND IS_SERVER_ID =<SERVER ID>]; 

 
 IS_REP_FACT

DELETE FROM IS_REP_FACT
WHERE
IS_PROJ_ID IN
 (
 SELECT IS_PROJ_ID
 FROM IS_PROJ
 WHERE IS_PROJ_GUID IN ('<PROJECT GUID TO DELETE 1>','<PROJECT GUID TO DELETE 2>')
 )
AND EM_RECORD_TS BETWEEN '<STARTING DATE FOR DELETE PERIOD>' AND '<FINISH DATE FOR DELETE PERIOD>'
[AND IS_SERVER_ID =<SERVER ID>];

 
 IS_SESSION

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 ('<PROJECT GUID TO DELETE 1>','<PROJECT GUID TO DELETE 2>')
 )
 )
AND EM_RECORD_TS BETWEEN '<STARTING DATE FOR DELETE PERIOD>' AND '<FINISH DATE FOR DELETE PERIOD>';
[AND IS_SERVER_ID =<SERVER ID>]; 

 
 IS_PROJECT_FACT_1

DELETE FROM IS_PROJECT_FACT_1
WHERE
IS_PROJ_ID IN
 (
 SELECT IS_PROJ_ID
 FROM IS_PROJ
 WHERE IS_PROJ_GUID IN ('<PROJECT GUID TO DELETE 1>','<PROJECT GUID TO DELETE 2>')
 )
AND EM_RECORD_TS BETWEEN '<STARTING DATE FOR DELETE PERIOD>' AND '<FINISH DATE FOR DELETE PERIOD>'
[AND IS_SERVER_ID =<SERVER ID>]; 

KB47468