MicroStrategy provide us the possibility to store statistics about our projects in physical tables. This information is varied and includes access dates, fault recording, runtimes … If we want to see the definition and composition of statistical tables which Microstrategy gathers, we can consult in:
since 463 page, there, we can see the information contained in each table.
In this post, I will combine information from the statistics repository with the information which we have in MicroStrategy metadata, for this reason it is advisable to take a look at the previous post:
where I spoke, in introductory way , about the main tables of this repository.
Playing with this information, I want to get useful information to improve the server management, from Administration point of view.
At the first point, we need to create the statistics repository, if you have not created yet. We must to open the Microstrategy “Configuration Wizard” and select “Create statistics repository, history and metadata”
and, in the next step, select “Statistics”
and we follow our assistant to create the repository.
At this point, we have the tables where they will store the statistics, but now, we must to enable the collection in the projects which we are interested. To get this, we will go to setup our project (Right click on our project & Project configuration), and then go to the statistics section:
Here, we can see the database which we have created in the Configuration Wizard.
Then, we proceed to select the option in checkboxes which we are interested in collect. Once we have saved the changes and restart the Intelligent Server, our project will begin collecting statistics.
In the lines bellow, I will propose a few queries that I have found useful and illustrative to take information from these tables. As I previously said, these queries combine metadata tables and statistics which are two different data base instances , so these queries should be done under administration profile.
In the following examples
MICROSTRATEGY is the database instance corresponding to the Microstrategy Metadata
STATISTICS is the data base instance corresponding to the Statistics tables
In this query, we get a list users which execute reports from a customizable period, for example in the last 24 hours:
SELECT A.OBJECT_NAME AS USER_NAME, SUM(B.NUM_JOBS_EXEC) “JOBS_EXECUTED”
FROM MICROSTRATEGY.DSSMDOBJINFO A
SELECT A1.USERID AS USERID, COUNT(REPORTID) AS NUM_JOBS_EXEC
FROM ESTADISTICA.IS_REPORT_STATS A1
WHERE ROUND(SYSDATE-TRUNC(A1.DAY_ID)) <=1
GROUP BY A1.USERID,A2.SESSIONID
ORDER BY 2 DESC
GROUP BY A.OBJECT_NAME
ORDER BY 2 DESC;
We can make a list of reports / documents with or without faults, executed over one period of time, for example in the last 24 hours:
SELECT TIPO, SUM(OK) AS NUM_EXEC_OK,SUM(KO) AS NUM_EXEC_KO
select ‘INFORME’ AS TIPO,
CASE WHEN A.JOBSTATUS=4 THEN 1
END AS KO,
CASE WHEN JOBSTATUS=3 THEN 1
END AS OK
FROM ESTADISTICA.IS_REPORT_STATS A
WHERE ROUND(SYSDATE-TRUNC(A.DAY_ID)) <=1 AND A.JOBSTATUS IN (3,4)
select ‘DOCUMENTO’ AS TIPO,
CASE WHEN B.EXECSTATUS=4 THEN 1
END AS KO,
CASE WHEN EXECSTATUS=3 THEN 1
END AS OK
FROM ESTADISTICA.IS_DOCUMENT_STATS B
WHERE ROUND(SYSDATE-TRUNC(B.DAY_ID)) <=1 AND B.EXECSTATUS IN (3,4)
GROUP BY TIPO;
Error message in the reports which have failed in a given period, for example, in the last 24 hours:
D.OBJECT_UNAME AS PROYECTO,
C.OBJECT_UNAME AS NOMBRE_INFORME,
B.OBJECT_UNAME AS USUARIO,
A.ERRORMESSAGE AS MENSAJE_ERROR,
ON (B.OBJECT_ID= A.USERID)
ON (C.OBJECT_ID= A.REPORTID)
ON (D.OBJECT_ID= A.PROJECTID)
A.JOBERRORCODE <> 0
AND UPPER(A.ERRORMESSAGE) NOT LIKE ‘%CANCELED%’
AND UPPER(A.ERRORMESSAGE) NOT LIKE ‘%CANCELADO%’
GROUP BY D.OBJECT_UNAME ,
As you can guess and see, all these queries are customizable to get the results which we want and the possibilities are endless, depending the knowledge of “what to looking for” and “where we looking for”