Tracking Table Usage with Enterprise Manager
Capping off this week of Aggregate Table talk, one piece of insight you’ll definitely want to have is which tables are being used by which reports. Sometimes it can be handy to know if a particular table is no longer needed and can be discarded, or to identify reports that are hitting detail tables instead of aggregates. MicroStrategy Enterprise Manager comes with lots of statistical analysis for your project, and today I’ll show how to build a quick report that I use all of the time for keeping my Aggregate and Sub-Set tables in check.
MicroStrategy Enterprise Manager is an optional stand alone tool that effectively ETLs the raw statistics data from the Intelligence Server and feeds a schema for project analysis. It also ships with a pre-built Enterprise Manager project that can be duplicated into your metadata for your convenience. The following steps are to be used in the Enterprise Manager project.
Add the following Attributes to the report:
DB Table (SchemaAttributesSchema Objects)
Project (SchemaAttributesConfiguration Objects)
Report (SchemaAttributesApplication Objects)
Add the metric:
RP Number of Jobs accessing DB Table/Column (MetricsReport Processing AnalysisCounts)
Add any filters you want, such as:
A Date filter from FiltersTime Filters. I personally use Today – 7 Days
A Report Filter, such as “Report Name Not Like %DELETED% or %HOC% or anything else you want to ignore”
Now you have a report that you can run to quickly check which (if any) Reports are using a particular table. You do end up with duplicate entries for the same tables between projects, but it’s still very usable. Personally, I drag the DB Table attribute to the Page By and use that as a quick filter to see all reports from all projects.