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
1. Requests
2. Dashboards
3. Segments
4. Segment Trees
5. List of Formats
2. Dashboards
3. Segments
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 (
CREATED_TIME VARCHAR(100),
LAST_ACCESSED_TIME VARCHAR(100),
LAST_MODIFIED_TIME VARCHAR(100),
OWNER_REPORT VARCHAR(20),
REQUEST_CRITERIA_COLUMN VARCHAR(40),
REQUEST_CRITERIA_FORMULA VARCHAR(40),
REQUEST_CRITERIA_TABLE VARCHAR(40),
REQUEST_DESC VARCHAR(2000),
REQUEST_FOLDER VARCHAR(200),
REQUEST_NAME VARCHAR(300),
REQUEST_PATH VARCHAR(100),
REQUEST_SQL VARCHAR(400),
REQUEST_SA VARCHAR(40))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY CATALOGREPORT
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY 0X’09′
MISSING FIELD VALUES ARE NULL
(CREATED_TIME CHAR(100),
LAST_ACCESSED_TIME CHAR(100),
LAST_MODIFIED_TIME CHAR(100),
OWNER_REPORT CHAR(20),
REQUEST_CRITERIA_COLUMN CHAR(40),
REQUEST_CRITERIA_FORMULA CHAR(40),
REQUEST_CRITERIA_TABLE CHAR(40),
REQUEST_DESC CHAR(2000),
REQUEST_FOLDER CHAR(200),
REQUEST_NAME CHAR(300),
REQUEST_PATH CHAR(100),
REQUEST_SQL CHAR(400),
REQUEST_SA CHAR(40)))
LOCATION (‘Test.Txt’))
PARALLEL
REJECT LIMIT UNLIMITED
Posts
