KB442219: Best practices for maintaining the MicroStrategy Change journal Tables and Managed objects for optimal Metadata health

• Strategy

This document provides best practices on maintaining the MicroStrategy metadata health by controlling the size of the Change Journaling Tables and cleaning up unused managed Metadata objects

Introduction
The Strategy Metadata (MD) is usually stored in an RDBMS and contains 3 sets of tables, namely:

DSSMD* tables (this set of tables that start with “DSSMDxxx” contain Strategy objects or often known as DSS objects)
DSSCS* tables (this set of tables that start with “DSSCSxxx” contain information about history list, distribution service, content server information)
DSSMDJRN* tables (this set of tables that start with “DSSMDJRNxxx” contain information about journal entry)

The Strategy Metadata size can increase even in a case when new projects are not added to the metadata because of Change Journaling. In projects which have self-service BI, where users create their own objects from Strategy Web via Data Import or in an active project where a lot of changes are continually made, it will not only cause the metadata object tables’ size to grow, it also contributes to a significant increase in the size of the Change Journaling tables.
Change Journaling is enabled by default since Strategy version 10.8 to help improve the performance of Quick Search on Strategy Web. Since the Quick Search indices make use of the Change Journaling data, they can easily update their existing indexes based on this data instead of having to do a full table scan. At the time of this writing, it is not possible to disable Change Journaling on a Strategy Project. Because of this, in a project where many changes are continuously made to a given project, the size of the Change Journaling tables (DSSMDJRNXXXX tables) can quickly increase.
To prevent a situation where Metadata performance gets affected because of the size of the tables, it is required that the Change Journaling tables are cleaned up on a periodic basis along with performing a cleanup of unused managed objects. However, before an attempt to cleanup these tables is made, Administrators should verify the current state of the metadata and analyze which tables are the largest.
How to Analyze the Size of Metadata Tables
The Strategy Administrator should work with the DBA to find the tables which consume the largest amount of memory. The example below shows the analysis for a MySQL Metadata:
SQL to find the tables in a given schema


SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = "TEST_md"

SQL to run the ANALYZE TABLE command on all the table retrieved from the above command


ANALYZE TABLE DSSCSCONTACT, DSSCSADDRESS, DSSCSBADGETB, …, DSSCSSYSPROP, DSSMDJRNINFO, 
DSSMDJRNLNKS, …, DSSMDACCPROP, DSSMDLNKITEM, DSSMDLNKPROP, …

Once all tables have been analyzed, the following SQL can be used to get an idea about the Size and Number of Records
Note: The attachment at the end of this article provides SQL that can be used for other RDBMS such as Microsoft SQL Server, Oracle, DB2 and Teradata


SELECT table_name AS "Table", 
round(((data_length + index_length) / 1024 / 1024), 2) as Size, 
TABLE_ROWS as Number_of_Records
FROM	information_schema.TABLES
WHERE table_schema = "TEST_md" 
ORDER BY Size DESC

Note that the Size is in megabytes (MBs), and it is an approximation only. Similarly, since Information Schema is used, the “Number of Records” is NOT as accurate as using COUNT(*). But in general, this serves as a good benchmark to give an idea about where the growth of the MD coming from.
Most commonly, the following sets of tables will be top ranked in terms of memory consumption and number of rows

If DSSMDJRN* (very likely DSSMDJRNINFO) appears dominantly on the top, then this Metadata has a lot of changes (very likely from Project that is actively being developed or users can freely build their own object, e.g., Self Service type of project) & Change Journal is not purged periodically.
If DSSMDOBJDEPN (or other DSMDOBJxxx tables) appears dominantly on the top, then this MD has large numbers of objects that are dependent to each other.

Strategy cannot provide a recommendation on the amount of data to retain in the Change Journaling tables and the amount of data retained in these tables should be dependent on the actual requirements of data usage from these tables. It is recommended for the BI Administrator to establish a standard way (policy) to back-up the entries in the Change Journaling tables since these could be needed for SOX compliance and other investigations.
How to Purge Change Journal Entries
***************************************************************************************************************************************************************************************************************************
IMPORTANT NOTE: Administrators should analyze the data distribution per day and project in the Change Journaling tables before attempting to purge any old data. This is because the process of purging Change Journal entries could slow down the entire BI system in a case where is a large amount of data in the Change Journaling tables.
***************************************************************************************************************************************************************************************************************************
When the number of Change Journal Entries (i.e., the number of records in DSSMDJRNINFO) is relatively large (e.g., > 100K records), the purging of tables should be done in multiple batches. To do this, the Administrator can get an idea how the records are distributed in the DSSMDJRNINFO across various project in different date with the following SQL:


SELECT transaction_project_id, date_format(transaction_timestamp, ‘%m/%Y’), count(*) from DSSMDJRNINFO group by 1, 2

(or)

SELECT transaction_project_id, date(transaction_timestamp), count(*) from DSSMDJRNINFO group by 1, 2

Once the Administrator has information about the data distribution in the Change Journaling tables by project and date, the Change Journaling tables can be purged from MSTR Developer for every Project by logging into a project, and then going to the Project Configuration, and then Change Journaling as shown below. As advised above, if there are a lot of entries in the Change Journaling tables, it is recommended to purge Change Journal entries multiple times to avoid the MD RDBMS to be locked by delete operation. Strategy internal testing has shown that up to 100K rows can be deleted in one batch without locking the database for extended periods of time. However, this number needs to be validated by working with the DBA and confirming that the DB is able to successfully delete this amount of data in one go.

Scheduled Purge of Change Journaling Tables
Once the tables are initially cleaned up to a state as required by the business, to make sure that the tables don’t continue to grow, Command Manager can be used to schedule the cleanup task.
Note: It is not currently possible to schedule an administrative task from Developer to purge Change Journaling tables.

Create a Command Manager script file with content like below (customize the script to include the commands for required projects and required dates) and save the file with a name such CJ_PURGE.scp
```
PURGE CHANGE JOURNALING BEFORE "11/30/2012 11:22:23 AM" COMMENTS "Purging project" FOR PROJECT "Strategy Tutorial";
```
Create a batch file with content similar to below that can kick off the above Command Manager script by executing Command Manager through Command Line
```
C:\Program Files (x86)\Strategy\Command Manager>cmdmgr -connlessmstr -f CJ_PURGE.scp -o log.txt
```
Windows scheduler can then be used to schedule the execution of this Batch file on a periodic basis. However, it would still be necessary to adjust the dates in the script file in Step 1 for each scheduled run.

How to cleanup Unused Managed Objects
If the count in the DSSMDXXX objects is also high as per the Metadata Tables analysis, and in a case where many net new objects (such as reports and dashboards) are not present in the project, it would also suggest that there is a high count of unused managed metadata objects. This is particularly applicable to the self-service projects where users can use data import to create new dashboards and cubes.
For information about what managed objects in the metadata are, refer to the documentation here .
For a MySQL MD, the following SQL can be used to get a count of the unused managed objects:


SELECT count(*) from DSSMDOBJINFO a
WHERE	a.PROJECT_ID in ('xxx') and
a.PARENT_ID = 'EE9BD70043F0F571B38E43BF19B9301A' and 
	a.OBJECT_ID NOT IN (
SELECT b.DEPN_OBJID
FROM DSSMDOBJDEPN b)

The above SQL calculates the first order number of Strategy (DSS) objects that are stored in managed object folder (GUID = EE9BD70043F0F571B38E43BF19B9301A) and the object is NOT referred by any other object in DSSMDOBJDEPN table. The italicized part of the above SQL (is optional and) can be omitted if we want to analyze the entire MD without regards to any specific Project.
Important Note: The above query does not account for dependencies of managed objects amongst themselves i.e., managed objects depending on other managed objects.
If the count of unused managed objects is in the order of 100s of thousands, it is an indicator that they are many unused objects in the metadata and that the these should be cleaned up.
The administrative task Delete unused metadata objects, shown below, should be used to achieve this.

Note that the task needs to be run multiple times because the operation only deletes 1000 objects at a time. For additional details about this behavior, refer to the following Strategy Community Article:
KB46453: Certain unused managed objects are not deleted after performing ‘delete unused managed objects’ in MicroStrategy Developer 9.x and 10.x. KB442219

Comment

0 comments

Details

Knowledge Article

Published:

November 8, 2018

Last Updated:

August 11, 2022

SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as Size, TABLE_ROWS as Number_of_Records FROM information_schema.TABLES WHERE table_schema = "TEST_md" ORDER BY Size DESC

SELECT transaction_project_id, date_format(transaction_timestamp, ‘%m/%Y’), count(*) from DSSMDJRNINFO group by 1, 2 (or) SELECT transaction_project_id, date(transaction_timestamp), count(*) from DSSMDJRNINFO group by 1, 2