We recently helped a client, with thousands of workbooks in Oracle Discoverer 4i, migrate to BI Discoverer 10g. It was be prohibitively expensive to blindly convert all of these workbooks, so naturally the staff had some questions. This how-to guide explores finding the workbook usages in Discoverer.

...the IT staff's first question was, "how do we tell which reports the users are running and which ones we can discard prior to upgrading?"

This client was using the seeded version of Discoverer (4i) against the E-Business Suite. They were also using the Noetix Views and were concerned about Noetix's lack of support for the E-Business Suite's BIS end user layer component.

Although this project involved the Oracle Applications, the scripts are valid for any standalone implementation of Discoverer or Oracle Business Intelligence.

Oracle Discoverer keeps some details about each workbook's run statistics. The information is kept in the end user layer.

Workbook Execution Frequency

SELECT 
 a.qs_doc_owner Owner, 
 a.qs_doc_name Workbook, 
 a.qs_doc_details Worksheet, 
 count(*) run_count
FROM  
 eul4_qpp_stats a,
 eul4_documents b
WHERE a.qs_created_date > '01-JAN-2009'  -- only recent reports
AND a.qs_doc_name = b.doc_name
GROUP BY 
 a.qs_doc_owner, 
 a.qs_doc_name, 
 a.qs_doc_details
ORDER BY run_count DESC

Conclusion

Armed with the usage counts, the IT staff was able to meet with their user community and decide that most of the workbooks could be purged and need not be converted. That brought the list from thousands down to a few hundred. Any workbook run less than 10 or 20 times could be dropped.

...an accurate survey of your Discoverer workbook usage count is a critical prerequisite to any upgrade.