KB214848: Data Manipulation Language (DML) Upgrade for Statistics Repository in an Oracle database with Partitioning in MicroStrategy 10.0

The DML upgrade of the Statistics Repository must be performed after the DDL upgrade i.e. upgrade via Configuration Wizard 10.0. The information provided in this Technical Note pertains to Step 2 as shown below:

 
Pre-Requisites for running the DML script:

1. Users are upgrading the Statistics Repository from version 9.x to 10.0. 
2. If administrators plan to run the entire script in one go, then the amount of free space available on the database should be at least the size of the current Statistics tables. For example, if the Statistics tables currently take up 10 GB of space, then  make sure that there is at least 10 GB of free space available. This is because data will be moved from the old (9.x) statistics tables to the new (10.0) statistics tables. This will essentially create a copy of all Statistics tables. After this script is successfully completed, the old Statistics tables can be deleted.
   
   Note: If the DML script is run as one transaction at a time, the old Statistics table can be deleted after the corresponding transaction. 
3. The Statistics Repository is located in an Oracle database with partitioning only. If the Statistics Repository is set up in any other database, refer to the following Technical Note: KB214847: Data Manipulation Language (DML) Upgrade for Statistics Repository in Strategy 10.0.
4. DDL upgrade of the Statistics and Enterprise Manager Repository via Configuration Wizard 10.0 is completed.
5. Enterprise Manager Warehouse tables should be present in the same repository as the Statistics Tables.
6. A a backup of the pre-upgrade Statistics repository should be created before running the DML scripts.

 
Purpose of the DML script:
In Strategy 10.0, a number of improvements have been made to the Statistics tables such as removing redundant columns, optimizing data types, new index strategy, optimized view definitions and so on. Hence, all existing statistics tables are renamed to <table_name>_DML_RUN during the DDL upgrade (via Configuration Wizard) and new, optimized tables are created. The primary purpose of the DML script is to move data from the old tables to the newly created tables.
 
The DML script contains the following work flow at a high level:

1. Create a temporary table called REPOSITORY_PROJECT_REL_TEMP.
2. Insert into the temporary table, data from the Enterprise Manager Warehouse tables (EM_MD, IS_PROJ).
3. Move data from old tables to new tables:
   
• Also joins to the temporary table mentioned above to populate the REPOSITORYID column in case it is NULL.
• Populates the DAY_ID, HOUR_ID, MINUTE_ID columns in case they are NULL.
• Renames old tables to DELETE_10_ <table_name> so that all old tables are grouped together and are easy to find for deletion purposes once the DML is successfully run.
4. Create indexes on the new tables.

 
Location of the DML Script:
The DML scripts can be found at the following two locations:

1. At the end of this Technical Note
2. On the Strategy Enterprise Manager machine: <Installation Folder> \MicroStrategy\StatisticsEnterpriseManagerScripts\DMLScripts

Important Note:

• If users are upgrading from Strategy  9.4.1, then the DML script: 'statistics_upgrade_partitioned_93_94to10_DML_Oracle.sql' located at the end of this technical note, should be used.

 
Best Practices:

• Before running the DML script, purge all unnecessary data from the statistics tables. Ensure that only the most recent data is kept if possible. This will also improve the overall performance. The duration of execution of the DML scripts will depend on the amount of data in the Statistics tables and this is the reason for not including the DML script as part of the DDL upgrade process of the Statistics Repository via Configuration Wizard
• Ensure that all 10.0 Strategy Intelligence Servers logging data to the Statistics tables are shutdown.
• For best performance, run the DML scripts when the load on the database server is low.
• The DML scripts against each table should be run as transactions as indicated in the script file. It is necessary to run each SQL statement as a transaction in case errors are encountered during SQL execution. Users should look for the begin and end <TRANSACTION NAME> text in the script files which indicate the necessity to run that SQL statement as a transaction, as shown below:
   
  

  
  /* BEGIN <TRANSACTION NAME> */
  ....
  script to be executed
  ....
  /* END <TRANSACTION NAME>*/

  
  
• Run the DML script against the statistics database from a database client application such as Strategy DB Query Tool or any native SQL execution tool provided by the database. 
  

 
 
KB214848