Business Intelligence – Oracle

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

1.    Requests
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

About these ads

10 Responses to “Oracle BI EE – Catalog Reports”

  1. majendi said

    This is a great way of keeping the documentation up to date. Using the table is a grat idea, we even use an OBIEE repository to report on what is in production!

  2. [...] saw earlier here about how Catalog Reports can be created and used. In the same way we also have an option of [...]

  3. Rama said

    Catlog manager is having problem in taking URL with ‘-’.
    My server name contains ‘-’ and catlog manager is throwing exception saying invalid URL when I try to give the URL of my server in Open Catolog screen in online mode.

    The URL I am giving is
    http://obi-dev01:9704/analytics/saw.dll?Dashboard

    Error I am getting is URL must be in correct format.

  4. Venkatakrishnan J said

    The URL that you need to give is http://obi-dev01:9704/analytics/saw.dll instead of http://obi-dev01:9704/analytics/saw.dll?Dashboard

  5. Thomas Dodds said

    We are running our OBIEE under OAS and when I supply http:///analytics/saw.dll I get a Unknown Connection Error: (500)Internal Server Error.

    Any help appreciated.

  6. Thomas Dodds said

    My host didn’t come out clear:

    http://bidevapp1:7777/analytics/saw.dll

  7. I played with this in the past : we created an analytics repository using metadata from analytics repository, information from catalog report, informatica metadata, siebel metadata and dac medatada !
    the result was really interesting and really helpful for the development team. It was for an old version of Analytics and I should recreate it for v10.1.3…may be I’ll do it when I will create my own company !

  8. john said

    This website is a life-saver. Thank you for taking the time to update it!!!

  9. John doe said

    This functionality can be used in-tandem with

    Administration> Tools > Utilities > Repository Documentation

    to allow one to view lineage from dashboard all the way to physical columns.

  10. Будьте смелее – позволяйте себе неожиданные решения в интерьере
    http://paradisepropertiestci.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: