Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Metadata Reports

Posted by Venkatakrishnan J on November 21, 2007

We saw earlier here about how Catalog Reports can be created and used. In the same way we also have an option of creating Metadata Reports. Metadata Reports are those reports that shows how the Presentation Layer is related to the Business Model layer and in turn also shows how BM is related to the Physical layer. This report would give all the details like the init blocks used, variables used etc. It is a very comprehensive report that again can be very useful. Lets see how to go about creating this Report. This is available as a utility in the Administration Tool. If you open the Administration tool and go to Tool -> Utilities you would find a utility called as Repository Documentation.

      

      

As soon as you click on Execute it would ask you to save the output into a flat file. Save the report and open the report.

      

If you notice there are quite a few important column information that we can get out of this Report. Following are the list of columns that the report would contain.

Presentation Catalog
Presentation Table
Presentation Column
Business Model
Derived logical table
Derived logical column
Expression
Logical Table
Logical Column
Logical Table Source
Expression (BM Expression)
Initialization Block
Variable
Database
Physical Catalog
Physical Schema
Physical Table
Alias
Physical Column

The above set of columns can be very useful if you quickly want to understand your metadata. Also, if need be you can use the below script to load this file into an external table

CREATE OR REPLACE DIRECTORY METADATAREPO_DIR AS ‘D:\VENKAT\ET’

CREATE TABLE METADATAREPORT_ET (
PRESENTATION_CATALOG VARCHAR(1000),
PRESENTATION_TABLE VARCHAR(1000),
PRESENTATION_COLUMN VARCHAR(1000),
BUSINESS_MODEL VARCHAR(2000),
DERIVED_LOGICAL_TABLE VARCHAR(4000),
DERIVED_LOGICAL_COLUMN VARCHAR(4000),
EXPRESSION VARCHAR(4000),
LOGICAL_TABLE VARCHAR(2000),
LOGICAL_COLUMN VARCHAR(2000),
LOGICAL_TABLE_SOURCE VARCHAR(3000),
LOGICAL_EXPRESSION VARCHAR(4000),
INIT_BLOCK VARCHAR(4000),
PHY_VARIABLE VARCHAR(4000),
PHY_DATABASE VARCHAR(4000),
PHYSICAL_CATALOG VARCHAR(4000),
PHYSICAL_SCHEMA VARCHAR(4000),
PHYSICAL_TABLE VARCHAR(4000),
ALIAS VARCHAR(4000),
PHYSICAL_COLUMN VARCHAR(4000))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY METADATAREPO_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY 0X’09’
MISSING FIELD VALUES ARE NULL
(PRESENTATION_CATALOG CHAR(1000),
PRESENTATION_TABLE CHAR(1000),
PRESENTATION_COLUMN CHAR(1000),
BUSINESS_MODEL CHAR(2000),
DERIVED_LOGICAL_TABLE CHAR(4000),
DERIVED_LOGICAL_COLUMN CHAR(4000),
EXPRESSION CHAR(4000),
LOGICAL_TABLE CHAR(2000),
LOGICAL_COLUMN CHAR(2000),
LOGICAL_TABLE_SOURCE CHAR(3000),
LOGICAL_EXPRESSION CHAR(4000),
INIT_BLOCK CHAR(4000),
PHY_VARIABLE CHAR(4000),
PHY_DATABASE CHAR(4000),
PHYSICAL_CATALOG CHAR(4000),
PHYSICAL_SCHEMA CHAR(400),
PHYSICAL_TABLE CHAR(400),
ALIAS CHAR(4000),
PHYSICAL_COLUMN CHAR(4000)))
LOCATION (‘RepositoryDoc.csv’))
PARALLEL
REJECT LIMIT UNLIMITED

Once this is done, load this table back into the repository and then create a report.

      

      

Simple but very powerful.

Advertisements

2 Responses to “Oracle BI EE 10.1.3.3/2 – Metadata Reports”

  1. Ashish said

    Hi Venkatakrishnan ,

    I am not able to create this metadata report.
    Here i am mentioning the steps which i followed
    (1) Using Repository Documentation utility , i generated the CSV file.
    (2) Instead of using external table , i created a table in database.
    (3) Using sql loader i uploaded data from CSV file into the table.
    (4) Then Using OBIEE 10.1.3.3, i imported this table into Physical layer.
    (5) Then after i created a new subject area at BMM layer and dragged the table
    to the BMM. Then dragged the table to Presentation Layer.

    Beacuse the BMM layer has only this table, No other table is present at BMM layer, RPD beacomes inconsistent. Beacause no logical join is present.
    And when i look this Catalog in BI Answer, It shows only Subject Area , No columns information.

    I also tried to use this table with a consistent RPD. But when i use this table , i makes the RPD inconsistent.Again the same issue comes.

    My question are –
    (A) How are you using this table to create metadata report?
    (B) How are you creating logical join?
    (C) How your RPD is consistent?
    (D) How are you able to use this table with BI Answer?

    Any ideas in this regard.

  2. RamC said

    @Ashish

    You can just duplicate the tables at the Business Model Layer and create a complex join. That should make things easy…

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

 
%d bloggers like this: