Prerequisites for running a DML script:
- User is upgrading the Statistics Repository from version 9.x to 9.3.0 - 9.4.x.
Note: The DML scripts need not be executed if users are upgrading Statistics tables from version 9.3.x to version 9.4.1 and when the DML scripts have already been run when users upgraded from a pre-9.3.0 version to 9.3.x. If the DML scripts were not executed when users upgraded to version 9.3.0 from a previous version, users can run the DML scripts when upgrading to version 9.4.x. In other words, users should NOT execute the DML scripts multiple times. - The Statistics Repository is located in either of the following databases:
- Microsoft SQL Server
- Teradata
- IBM DB2
- Sybase
- Oracle (without partitioning only).
- Oracle (with partitioning), if the Statistics Repository is in this configuration, refer to the following Strategy Knowledge Base technical note:
KB41180: Data Manipulation Language (DML) upgrade for statistics repository located in an Oracle database with partitioning in MicroStrategy 9.3.0
- DDL upgrade of the Statistics Repository i.e. upgrade via Configuration Wizard 9.3.0 - 9.4.x or Enterprise Manager Console 9.3.0 - 9.4.x is completed.
- Enterprise Manager warehouse tables should be present in the same repository as the Statistics Tables.
- Create a backup of the Statistics Repository before running the DML scripts.
Purpose of the DML script:
In Strategy 9.3.0, a new column named 'REPOSITORYID' is created during the DDL upgrade of the Statistics Repository and has been added to the following tables:
- IS_REPORT_STATS
- IS_DOCUMENT_STATS
- IS_PROJ_SESS_STATS
- IS_REP_STEP_STATS
- IS_DOC_STEP_STATS
- IS_REP_SQL_STATS
- IS_REP_COL_STATS
- IS_REP_MANIP_STATS
- IS_CACHE_HIT_STATS
- IS_REP_SEC_STATS
- IS_SCHEDULE_STATS
- IS_PR_ANS_STATS
- IS_MESSAGE_STATS
- IS_INBOX_ACT_STATS
- IS_CUBE_REP_STATS
The DML script populates the newly created REPOSITORYID column in each table listed above.
The DML script contains the following workflow at a high level:
- Creates a temporary table.
- Inserts data from the Enterprise Manager warehouse tables (e.g., EM_MD, IS_PROJ) into the temporary table.
- Populates the REPOSITORYID column in the tables listed above by joining each table with the temporary table.
- Drops the temporary table.
Location of the DML script:
DML scripts for all databases (except an Oracle Database with Partitioning) can be found at the following two locations:
- At the end of this Technical Note
- <Installation Folder>\Strategy\Enterprise Manager\DML Scripts
Users should download the appropriate zip file based on the type of database where the Statistics Repository is located. As an example, for Microsoft SQL Server, download the zip file SQL_Server_DML_Statistics.zip.
Note: Except Oracle, there is only a single DML script for each of the other databases. The Oracle DML scripts are categorized into with or without partitioning. All other database DML scripts work independent of partitioning.
Requirements and 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 overall performance.
- Ensure that all 9.3.0 - 9.4.x Strategy Intelligence Servers logging data to the statistics tables are shutdown.
- The duration of execution for 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 9.3.0 - 9.4.x or Enterprise Manager Console 9.3.0 - 9.4.x.
- 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:
Sample Code/Error
/* 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. Make sure the Intelligence Server is stopped while performing this DML upgrade.
