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.
Posts
