The following are some queries which Strategy Technical Support has found valuable in obtaining troubleshooting information for cases of missing or incorrect data in statistics logging or Strategy Enterprise Manager reporting.
One of the key troubleshooting steps in Enterprise Manager data availability issues is determining where the information flow may be breaking down. The normal information flow is that first the Strategy Intelligence Server writes statistics into statistics tables in the Enterprise Manager warehouse. The statistics are not used for reporting directly, but must undergo an ETL process (namely, the data load configured using the Enterprise Manager console).
If the statistics logging process is not working, both the statistics queries and the fact table queries will return similar answers. If statistics logging is functioning normally, but the data load process is failing, then the statistics queries will return up-to-date timestamps while the fact table queries will return older timestamps.
Statistics queries:
These are relevant to statistics logged by the Intelligence Server into the statistics tables.
To obtain the latest time when session statistics were recorded by the Intelligence Server:
select max(recordtime) from is_session_stats
To obtain the latest time when report statistics were recorded by the Intelligence Server:
select max(recordtime) from is_report_stats
To obtain the latest time when session statistics were recorded by the Intelligence Server, per server and project. This is useful when statistics are working for some projects but not others. Note that this query can identify the projects associated with sessions only if the projects are configured to record project session statistics. If project sessions are disabled, then the project ID and name for each session will be NULL.
select a1.serverid, a1.servername, a2.projectid, a3.is_proj_name, max(a1.recordtime)
from is_session_stats a1
left outer join is_proj_sess_stats a2
on (a1.sessionid = a2.sessionid)
left outer join is_proj a3
on (a2.projectid = a3.is_proj_guid)
group by a1.serverid, a1.servername, a2.projectid, a3.is_proj_name
NOTE:
Starting in version 10.x the column name of the "servername" column has been changed to "servermachine". The above query needs to be modified accordingly if executing against a 10.x statististics repository.
Statistics repositories in Oracle databases prior to Oracle 9i do not support "left outer join" syntax. If the above query does not run in Oracle because of the "left outer join" keywords, the following alternative may be used.
select a1.serverid, a1.servername, a2.projectid, a3.is_proj_name, max(a1.recordtime)
from is_session_stats a1,
is_proj_sess_stats a2,
is_proj a3
where a1.sessionid = a2.sessionid (+) and
a2.projectid = a3.is_proj_guid (+)
group by a1.serverid, a1.servername, a2.projectid, a3.is_proj_name
To obtain the latest time when report statistics were recorded by the Intelligence Server, per server and project:
select a1.serverid, a1.servername, a1.projectid, a2.is_proj_name, max(a1.recordtime)
from is_report_stats a1, is_proj a2
where a1.projectid = a2.is_proj_guid
group by a1.serverid, a1.servername, a1.projectid, a2.is_proj_name
Fact table queries:
These are relevant to data used directly in Strategy Enterprise Manager reporting. These data are populated into the fact tables by the Enterprise Manager data load process.
To obtain the latest time for which session facts exist, per Intelligence Server machine:
select em_app_srv_machine, max(em_record_ts)
from is_session
group by em_app_srv_machine
To obtain the latest time for which report facts exist, per Intelligence Server machine and project:
select a2.em_app_srv_machine, a3.is_proj_name, max(a1.em_record_ts)
from is_rep_fact a1, is_session a2, is_proj a3
where a1.is_session_id = a2.is_session_id and
a1.is_proj_id = a3.is_proj_id
group by a2.em_app_srv_machine, a3.is_proj_name
