SUMMARY:
This tech note is an explanation of how to check the size (row count) of a table in Strategy 9.x and later. specifically focusing on statistics tables for the Enterprise Manager project, but the SQL used to query for the row count can be used on metadata and history list tables as well. The scope will be limited to Windows operating systems through the use of Strategy DB Query Tool, although any other query tool can be used to execute the suggested SQL. Information on obtaining the physical size of a table in an environment should be addressed by the DBA.
DESCRIPTION
In order to stay informed about the size of the data or troubleshoot issues with memory or performance, a user may want to check the size of the tables in their environment. This can be achieved by querying for the row count of a specific table. Any user with access to the database can run this query.
ACTION STEPS
The following procedure describes how to check the size (row count) of a table in Strategy:
Step 1: Open DB Query Tool and connect to the Statistics Warehouse data.
Step 2.: Use the following query: SELECT COUNT (*) FROM <table name>.
Step 3: Retrieve the results at the bottom of the page. The results should look like this:
ADDITIONAL TROUBLESHOOTING
This is a list of the statistics tables that can be found in Strategy Enterprise Manager. For situations where an Enterprise Manager table would need to be queried to determine how much data it was holding, these would be the most likely to used:
IS_SESSION_STATS: Logs every Intelligence Server user session.
IS_PROJ_SESS_STATS: Records statistics related to the project session.
IS_REPORT_STATS: Tracks job level statistics information about every completed report execution that occurs in the Intelligence Server.
IS_REP_STEP_STATS: Tracks each step in the report execution process.
IS_DOCUMENT_STATS: Tracks document executions that pass through Intelligence Server.
IS_DOC_STEP_STATS: Tracks each step in the document execution process.
IS_CACHE_HIT_STATS: Tracks job executions that hit the report cache.
IS_REP_SQL_STATS: Enables access to the SQL for a given report execution.
IS_SCHEDULE_STATS: Tracks which reports have been run as the result of a subscription.
IS_REP_SEC_STATS: Tracks executions that used security filters.
IS_REP_COL_STATS: Tracks the column-table combinations used in the SQL during report executions.
IS_REP_MANIP_STATS: Records report manipulation statistics related to user actions after a report has been executed.
IS_MESSAGE_STATS: Records statistics related to sending messages.
IS_PR_ANS_STATS: Records statistics related to prompt answers.
IS_PERF_MON_STATS: Records statistics related to notification, diagnostics, and performance counters logged by Intelligence Server.
IS_INBOX_STATS: Records statistics related to History List manipulations.
IS_CUBE_REP_STATS: Records statistics related to Intelligent Cube manipulations.
TO LEARN MORE
More information about statistics tables can be found in the "Supplemental Reference For System Administration" guide in the Strategy Product Manual. The manual can be found in your installation of Strategy on Windows by clicking Start>All Programs>Strategy>Product Manuals.
Additional tech notes concerning Enterprise Manager tables:
KB36020 "New Statistics and Enterprise Manager tables designed to track new Intelligent Cube Operations in Strategy 9.2.0"
KB11981 "How to determine if data from the statistics tables was purged successfully in Strategy Enterprise Manager"
KB7022 "How to correctly clean data in the Strategy Enterprise Manager warehouse"
