Home News and Information Whitepapers Workbook Usage History, Migrating to Oracle Discoverer 10g
Workbook Usage History, Migrating to Oracle Discoverer 10g  E-mail
User Rating: / 6
PoorBest 
Written by HSYS Staff   
Friday, 16 January 2009 00:00

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

This query answers the question, "how many times has a workbook or report been run?"
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
OWNER WORKBOOK WORKSHEET RUN_COUNT
SYSADMIN Order to Cash Report Sales Online 17773
SYSADMIN Project Analysis Report Project Costing 9982
SYSADMIN Noetix Oracle Projects Report Project Billing 3871
SYSADMIN Oracle Receivables Discoverer Report iReceivables 2268
...

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.

Last Updated on Monday, 07 November 2011 13:03
 

Contact Us Now!

Interest
  • We respect your privacy and absolutely will not send you spam.