Oracle Discoverer Query to find out Workbook, last run and number of time executed query

Oracle Discoverer Query to find out Workbook, last run and number of time executed query

SELECT A.* , B.TOTCOUNT FROM
(SELECT CASE WHEN  QS_DOC_OWNER IS NULL THEN 'E' ELSE QS_DOC_OWNER END  AS USERNAME , QS_DOC_NAME, QS_DOC_DETAILS, QS_CREATED_DATE LAST_USED, QS_NUM_ROWS
FROM MPEADEVL.EUL5_QPP_STATS
WHERE QS_ID IN(
  SELECT  MAX(QS.QS_ID)
    FROM MPEADEVL.EUL5_QPP_STATS QS, MPEADEVL.EUL5_DOCUMENTS DC
    WHERE QS.QS_DOC_NAME=DC.DOC_NAME
    GROUP BY QS_DOC_OWNER, QS.QS_DOC_NAME, QS.QS_DOC_DETAILS)
ORDER BY LAST_USED DESC) A,
(SELECT CASE WHEN  QS_DOC_OWNER IS NULL THEN 'E' ELSE QS_DOC_OWNER END AS USERNAME, QS_DOC_NAME, QS_DOC_DETAILS, COUNT(*) TOTCOUNT
FROM MPEADEVL.EUL5_QPP_STATS QS, MPEADEVL.EUL5_DOCUMENTS DC
    WHERE QS.QS_DOC_NAME=DC.DOC_NAME
    GROUP BY QS.QS_DOC_OWNER, QS.QS_DOC_NAME, QS.QS_DOC_DETAILS) B
WHERE A.QS_DOC_NAME=B.QS_DOC_NAME AND A.QS_DOC_DETAILS=B.QS_DOC_DETAILS AND A.USERNAME=B.USERNAME;

RPD deployment in OBIEE 12c

1. Open the command prompt go to the below path

Oracle_Home\user_projects\domains\bi\bitools\bin

2. Enter the below command

Data-model-cmd.cmd uploadrpd -I .rpd -W -U -P -SI

I : specifies name of the repository (give complete path)
W :  specifies the repository’s password.
U :  specifies a valid user’s name to be used for OBIEE authentication
P : specifies the password corresponding to the user’s name that you specified for U.
SI : specifies the name of the service instance.