6 Metadata Collection and Reports

Overview of Managing Metadata for Oracle Communications Data Model

Metadata is any data about data and, as such, is an important aspect of the data warehouse environment. Metadata allows the end user and the business analyst to navigate through the possibilities at a higher business object level.

Metadata management is a comprehensive, ongoing process of overseeing and actively managing metadata in a central environment which helps an enterprise to identify how data is constructed, what data exists, and what the data means. It is particularly helpful to have good metadata management when customizing Oracle Communications Data Model so that you can do impact analysis to ensure that changes do not adversely impact data integrity anywhere in your data warehouse.

• Metadata Categories and Standards

• Working with a Metadata Repository

Browsing Metadata Reports and Dashboard

To customize the Oracle Communications Data Model model, you must understand the dependencies among Oracle Communications Data Model components, especially how the report KPIs are mapped to the physical tables and columns. Oracle Communications Data Model provides a tool, the OCDM Metadata browser that helps you discover these dependencies. When you install Oracle Communications Data Model with its sample reports, the metadata browser is delivered as a sample Dashboard in the webcat.

There are four tabs (reports) in the Oracle Communications Data Model Metadata browser. To browse the metadata repository:

1. In the browser, open the login page at http://servername:9704/analytics where servername is the server on which the webcat is installed.

2. Login with username of ocdm, and provide the password.

3. Select the Metadata Browser dashboard.

4. Use the tabs in the Metadata browser to explore the metadata.

   • Measure-Entity tab

     On the Measure-Entity tab you can see the business areas (relational, OLAP, mining), the measures description, corresponding formula, responsible entities, and attributes for the measure.

   • Entity-Measure tab

     Using the Entity-Measure tab, you can discover the mappings between entities, attributes, supported measures, and calculations of the measures. You can discover information about particular entities and attributes.

   • Program-Table tab

     Using the Program-Table tab you can browse for information on the intra-ETL mappings and report information. Take the following steps:

   • Table-Program tab

     By default when you go to the Table-Program tab you see all of the tables used for all the reports.

     To discover what reports use a particular table, you must move a particular table from the right pane to the left (Selected) pane.

Collecting and Populating Metadata

The Oracle Communications Data Model metadata browser generation packages generate and update the Oracle Communications Data Model metadata. The metadata generation package contains four main tables and several staging tables and views. The metadata generation tables are:

• MD_ENTY

• MD_PRG

• MD_KPI

• MD_REF_ENTY_KPI

Use the following steps to collect and populate the metadata.

1. Collect LDM Metadata:

   Extract the Logical Data Model repository metadata from Oracle SQL Developer Data Modeler (OSDM) into a database schema. Use manual steps to generate Logical Data Model repository tables in the database with Oracle SQL Developer Data Modeler.

   1. Start Oracle SQL Developer Data Modeler

   2. Open Logical Data Model

   3. Select File.

   4. Select Export.

   5. Select To Reporting Schema.

2. Collect Sample Dashboard Metadata:

   Extract the BIEE dashboard metadata from webcat to csv file.

   Using OBIEE catalog manager open the SQL Developer sample report webcat:

   Tools -> create Report -> Select type to report on -> select dashboard

   Select columns one by one as shown in the md_dashboard.ldr specified in the meta_data folder, then save as a csv format file, md_dashboard.csv.

   Put this file in the meta_data folder.

   Column Sequence:

   1. Name

   2. Description

   3. Path

   4. Folder

   5. Analysis Path

   6. Analysis Name

   7. Analysis Description

   8. Dashboard Page Description

   9. Dashboard Page Name

   10. Dashboard Page Path

   11. Owner

3. Collect Sample Report Metadata:

   Extract BIEE report metadata from webcat to csv file. Use OBIEE catalog manager to open Oracle Communications Data Model sample report webcat.

   • Tools -> create Report -> Select type to report on -> select Analysis -> select columns one by one as shown in the md_dashboard.ldr specified in the meta_data folder.

   • Save the file as csv format, md_dashboard.csv. Put the file under meta_data folder

   Column Sequence:

   1. NAME

   2. DESCRIPTION

   3. TABLE_NAME

   4. COLUMN_NAME

   5. FOLDER

   6. PATH

   7. SUBJECT_AREA

   8. FORMULA

4. Collect Sample RPD Metadata:

   Extract BIEE RPD metadata from RPD to csv file. Use Administrator Tool to open Oracle Communications Data Model sample report RPD:

   • Tools -> Utilities -> Repository Documentation -> Execute -> select location -> set xls file name as md_rpd.

   • Save as csv format md_rpd.csv and put under meta_data folder.

5. Load Naming Convention Information:

   Load Oracle Communications Data Model Physical Data Model naming convention information from csv into a staging table. Use sqlloader to load data from name_conversion.csv into MD_NAME_CONVERSION table. The sqlloader format file: Name_conversion.ldr

   Name_conversion.ldr:
   OPTIONS (SKIP=1)
   LOAD DATA
   INFILE      'name_conversion.csv'
   BADFILE     'name_conversion.csv.bad'
   DISCARDFILE 'name_conversion.csv.dsc'
   truncate
   INTO TABLE MD_NAME_CONVERSION
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
   TRAILING NULLCOLS
   (
   ABBREVIATION    , 
   FULL_NAME      
   )
   

6. Load Sample Dashboard Metadata:

   Load sample dashboard metadata from csv into a staging table. Use sqlloader to load data from md_dashboard.csv into MD_DASHBOARD table. The sqlloader format file: md_dashboard.ldr.

   Md_dashboard.ldr:
    
   OPTIONS (SKIP=1)
   LOAD DATA
   INFILE      'md_dashboard.csv'
   BADFILE     'md_dashboard.csv.bad'
   DISCARDFILE 'md_dashboard.csv.dsc'
   truncate
   INTO TABLE MD_DASHBOARD
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
   TRAILING NULLCOLS
   (
   NAME char(2000),
   DESCRIPTION char(2000),
   PATH char(2000),
   FOLDER char(2000),
   ANALYSIS_PATH char(2000),
   ANALYSIS_NAME char(2000),
   ANALYSIS_DESCRIPTION char(2000),
   DASHBOARD_PAGE_DESCRIPTION char(2000),
   DASHBOARD_PAGE_NAME char(2000),
   DASHBOARD_PAGE_PATH char(2000),
   OWNER char(2000)
   )
   

7. Load Sample Report Metadata

   Load sample report metadata from csv into a staging table. Use sqlloader to load data from md_report.csv into MD_REPORT table. The sqlloader format file: md_report.ldr.

   Md_dashboard.ldr:
    
   OPTIONS (SKIP=1)
   LOAD DATA
   INFILE      'md_dashboard.csv'
   BADFILE     'md_dashboard.csv.bad'
   DISCARDFILE 'md_dashboard.csv.dsc'
   truncate
   INTO TABLE MD_DASHBOARD
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
   TRAILING NULLCOLS
   (
   NAME char(2000),
   DESCRIPTION char(2000),
   PATH char(2000),
   FOLDER char(2000),
   ANALYSIS_PATH char(2000),
   ANALYSIS_NAME char(2000),
   ANALYSIS_DESCRIPTION char(2000),
   DASHBOARD_PAGE_DESCRIPTION char(2000),
   DASHBOARD_PAGE_NAME char(2000),
   DASHBOARD_PAGE_PATH char(2000),
   OWNER char(2000)
   )
   

8. Load Sample RPD Metadata:

   Load sample RPD metadata from csv into a staging table.

   Note:

   If the OLAP part of the RPD is populated by the BIEE native OLAP import. Then the metadata of this part will not be shown in md_rpd.csv. You need to manually populate this part of metadata from the RPD.

   Use sqlloader to load data from md_rpd.csv into MD_RPD table. The sqlloader format file: md_rpd.ldr.

   Md_rpd.ldr:
    
   OPTIONS (SKIP=0)
   LOAD DATA
   INFILE      'md_rpd.csv'
   BADFILE     'md_rpd.csv.bad'
   DISCARDFILE 'md_rpd.csv.dsc'
   truncate
   INTO TABLE MD_RPD
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
   TRAILING NULLCOLS
   (
    SUBJECT_AREA
   ,PRESENTATION_TABLE
   ,PRESENTATION_COLUMN char(500)
   ,DESC_PRESENTATION_COLUMN
   ,BUSINESS_MODEL
   ,DERIVED_LOGICAL_TABLE
   ,DERIVED_LOGICAL_COLUMN
   ,DESC_DERIVED_LOGICAL_COLUMN
   ,EXPRESSION char(1000)
   ,LOGICAL_TABLE
   ,LOGICAL_COLUMN
   ,DESC_LOGICAL_COLUMN
   ,LOGICAL_TABLE_SOURCE
   ,EXPRESSION_1 char(1000)
   ,INITIALIZATION_BLOCK
   ,VARIABLE
   ,DATABASE
   ,PHYSICAL_CATALOG
   ,PHYSICAL_SCHEMA
   ,PHYSICAL_TABLE
   ,ALIAS
   ,PHYSICAL_COLUMN
   ,DESC_PHYSICAL_COLUMN
   )
   

9. Load LDM/PDM Metadata (Table MD_ENTY):

   Load LDM/PDM mapping and related information into table MD_ENTY. For information on this step, see "Load LDM/PDM Metadata (Table MD_ENTY)".

10. Load Program (Intra-ETL) Metadata (Table MD_PRG):

    Load Intra-ETL program input/output and related information into table MD_PRG.

    For information on this step, see "Load Program (Intra-ETL) Metadata (Table MD_PRG)"

11. Load Reports and KPI Metadata (Table - MD_KPI and MD_REF_ENTY_KPI)

    Load sample report metadata into MD_KPI and load report/PDM/LDM mapping related information into table MD_REF_ENTY_KPI.

    For information on this step see "Load Reports and KPI Metadata (Table MD_KPI and MD_REF_ENTY_KPI):".