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”

Finished!

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.

You may also like...

19 Responses

  1. ekvaatEk says:

    hey nice post, some real good things to learn from you.

    I tried creating a report like you mentioned but it returns an error. fact does not exists at a the level ….

  2. Jason says:

    Can we do something like this for free form sql reports? I have a table say ‘schema.table’ and would want to know in how many freeform sql reports this is being used.

  3. Bryan says:

    This method works by using Enterprise Manager Statistics, which aren’t going to be available at this level for FFSQL reports. The only way to report on FFSQL would be to do a like ‘%schema.table%’ out of the IS_REP_SQL table (not sure if that’s the exact name) where you’re basically querying the full SQL Query of every report. That’ll be much slower, but it’ll do it.

  4. Question: what does it mean if the report returns no data ? Thank you!

    • Bryan says:

      Probably that your EM Data Loader isn’t running or there’s a problem in your query. Take the SQL from the report to your DB Query Tool and debug it to see where data is being dropped or missing.

  5. Thank you for the quick answer.
    I don’t have enough rights to apply your advice. However, my curiosity is why EM reports that use tables like IS_REP_FACT or IS_DOC_FACT return data and reports (like yours or some of the pre-defined) that use IS_REP_COL_FACT don’t. Any idea on where to proceed with my research ?
    Thanks a lot!

  6. Killian says:

    This sounds to me like the statistics on the project you want to look at are not collected a the lower level at which you want to report. When you configure statistics collection on a project you also select the level of detail. Having IS_REP_FACT populated but not IS_REP_COL_FACT means you can check what report ran when, but you can not check what the objects in the report where or the SQL that was executed.

  7. Hi ,I want to check which projects are configured under enterprise manager.Could you please let me know how to check that

    • Bryan says:

      Open up Enterprise Manager Console, go to Configure (click Next if you get the splash screen), expand the list on the right. Those are your configured projects.

  8. Raj says:

    EM is not available for Unix, how can we handle this situation?

    • Bryan says:

      You can get the same information out of the raw statistics which are logged from the IServer if you aren’t using EM. This will be the tables that start with IS_* if you have Statistics logging enabled. Otherwise, EM currently only runs on Windows, so you’d have to have a separate Windows box setup just to process the EM Data Loads. No way around that.

  9. Tiehu Piao says:

    HI MSTR did not save the project log records in logDB, But the other project’s access user , runtime were saved success.BTW Project statistics properties set as the same.
    Could you give some advise?

  10. murali says:

    Hi Bryan I want to see the selectors data what each user was selecting for a report is it possible in enterprise manager?

    • Bryan says:

      No, I don’t believe that information is logged anywhere since it occurs client side, and EM is based on IServer logged stats. Sounds like a great enhancement request for MSTR though.

  11. Dennis says:

    We have a multi-tenant environment. Is there a way to get this information broken out by DB Connection?

  12. sara says:

    Hi,

    Statistics are not logging .the is_rep_fact is have stopped updating . tried by changing the timeout setting to 60 n then 120 from 10 secs. it dint worked . Shows ODBC error Timedout .

Leave a Reply

Your email address will not be published. Required fields are marked *