Optimizing and Maintaining Your Project » Using summary tables to store data: Aggregate tables » Determining the frequency of queries at a specific level

Determining the frequency of queries at a specific level

Build aggregate tables only if they can be useful to your users. If aggregate tables are never accessed, they consume disk space and impose unnecessary burdens on the extraction, translation, and loading process, as well as the database backup routines.

However, usefulness is not always easy to quantify. For example, consider the following hierarchy:

A summary of data at the department level seems to be a good candidate for an aggregate table. However, if users frequently want to exclude inactive items, the query must use item-level data and summarize the department data dynamically. Therefore, the department aggregate tables would not be used in this situation.

Once your warehouse is in production, trace the usage of any aggregate tables to determine how frequently they are used in a day-to-day business environment. If any table is not used, eliminate it from the warehouse.

MicroStrategy Enterprise Manager allows you to easily track table usage. For more information on Enterprise Manager, see the System Administration Guide.