Oracle BI EE – Catalog Reports
Posted by Venkatakrishnan J on November 19, 2007
One of the very good features of the catalog manager is its ability to export Catalog related reports. For example, if you would like to have a detailed report containing the frequently accessed reports, last accessed time etc of the reports within a folder you have in the catalog, Catalog Reports are the way to go. This option is available for all the folders that you have in your catalog. So, if you log into Catalog Manager and right click any of the folders that you have in your catalog you would get the “Create Report” option.
So, if you click on Create Report, you would get multiple options.
So, the list of columns that you see on the left are the columns that are available for the report. For example, one can create a report containing the Created Time, Last Accessed Time, Owner, Request Folder, Request Name. So, what this would basically show is the list of the reports that have been accessed in each folder and their corresponding created times. Also, one has an option to save these reports in a file. Lets create a simple report containing the above said columns and see how the report actually looks like
There can be multiple types of reports. They are
4. Segment Trees
5. List of Formats
The first 2 are of importance since they provide catalog related reports for the individual reports and dashboards respectively. I think the remaining 3 formats are related to Siebel CRM but not sure. One other good option is to export these reports into flat files and load them into the database so that you can use this in your dashboard reports. You can use APEX to load these reports into a database table. But the main issue that i found is that if you include columns like RequestXML/ Dashboard Page XML then it becomes almost impossible to load these reports into the database(because of these XML fields). So, while creating the reports (if you do not have any custom load scripts), just exclude the RequestXML column. Also, i have included the following external table script below. You can just create the external table and use that to report in BI EE.
CREATE OR REPLACE DIRECTORY CATALOGREPORT AS ‘D:\VENKAT\ET’
CREATE TABLE CATALOGREPORT_ET (
DEFAULT DIRECTORY CATALOGREPORT
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY 0X’09’
MISSING FIELD VALUES ARE NULL
REJECT LIMIT UNLIMITED