KB32849: Database Based History List - Best Practices and Frequently Asked Questions

Introduction:
Proper procedures must be followed to ensure the database is kept at a reasonable level. The correct way to ensure History List repository does not outgrow itself is to delete older History List messages using an administrative schedule. This process will trigger the deletion of History caches from disk as well as from the history list database repository. It is NOT recommended to delete History List caches by themselves either manually or via a schedule. This will create unreferenced History List messages and further lead to orphaned records in the DSSCSCONTENT table (one of the three History List repository tables).
 
What are the advantages of Database based History list?
Database Based History List offers many advantages over File based History List:

• Allow greater scaling potentially to infinite History List messages.
• Duplicate copy of caches (one on disk and one in the content table) ensures that if the file system is unavailable the cache is always available.
• Scheduled administrative task to delete History List messages for multiple users only logs one user session to Strategy Intelligence Server for the cleanup process unlike the file based History List where each user session must be logged individually.
• History List messages are stored centrally in the database, rather than in multiple file locations.
• Database Based History List comes with a History List System monitor which can be used to monitor, filter and purge any of the History List Messages for any user in a project source.
• Database Based History List enables easier backups and administration
• Provides additional details that can be used more effectively by Strategy Technical Support to troubleshoot an issue.

How can Database Based History List be used as a long term storage medium for History List content?
When a History List message is created, the History List content (cache) is stored on the file system as well as in the History List repository. If users wish to keep the History List content in the History List repository for long term usage, the History list content should be deleted from the file system first so it is not loaded in memory every time Strategy Intelligence Server starts up.
When the administrator wants to access the content of a History List message that no longer has the cache file on disk, the cache will be loaded from the History list repository on demand (as soon as the user clicks on the History List message).
 
To delete the History List cache from the file system, the administrator should create an administrative schedule to delete only "History Caches". This will ensure the History List content is only deleted from the file system but still remains in the DSSCSCONTENT table (one of the three History List repository tables).
 
Note: The process described in the above section should only be used if Database Based History List is needed as long term storage medium otherwise orphaned rows may remain in the DSSCSCONTENT table.
 
How much data can be stored in the History List repository?
Strategy does not enforce any hard limits on the amount of data that can be stored in the History List repository. However, more data is in the History List tables will lead to slow retrieval of the History List content from those tables.
History List tables are indexed so this should not be a huge obstacle.
 
How to avoid orphaned rows in DSSCSCONTENT table?
The way to ensure there are no orphaned rows in the DSSCSCONTENT table is to create an admin task to delete History List message (not History List caches). Deleting History List messages will trigger deletion of History List caches from file and from the database leaving NO orphan rows.
On the other hand, deleting History List caches first (either manually or via an administrative task), will only trigger the deletion of History List caches from the file system and leave unreferenced History List messages on the system. At this point deleting the History List messages will leave orphaned rows in the DSSCSCONTENT table.
 
To delete these orphaned rows from the DSSCSCONTENT table, refer to the Strategy Knowledge Base Document below for the SQL to run against the History List reporsitory:
KB31677: What tables are stored in the History List repository used in Database Based History List in MicroStrategy 9.x - 10.x
 
 
Where should the History List repository reside?
Because History List repository can get quite large based on the usage pattern, Strategy recommends housing History List Repository in a tablespace separate from the metadata, warehouse or statistics. This will also enable easier backup of the repository and faster access to the History List content.
 
For additional details on Database Based History List and how to configure it, refer to the following Strategy Knowledge Base Documents below:
KB20506: What is Database Based History List in MicroStrategy 9.4.x - 10.x
KB20508: How to set up and configure a Database Based History List Repository in MicroStrategy 9.x and newer
KB20507: New Feature in MicroStrategy 9.x: History List Messages Monitor