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 "Loading 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 "Loading 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 "Loading Reports and KPI Metadata (Table MD_KPI and MD_REF_ENTY_KPI):".

Loading LDM/PDM Metadata (Table MD_ENTY)

If you want to get the mapping between a business area and an entity, you have to manually populate this information. You can only get this information from the metadata report for those entities which are used in the report, for those entities which are not used in report, you have to manually map them to the correct business area.

Source Tables Required


Source Table Name Description

DMRS_ATTRIBUTES

Containing attributes of the particular entity

DMRS_ENTITIES

Containing entity name with unique id

MD_NAME_CONVERSION

Containing full name and abbreviation of the distinct word used in the LDM


Staging Tables/Views


Staging Table/View Name Description

MD_OIDM_ATTR_COL_NAME_MAP

Used to store abbreviate the column names based on the standard abbreviation used in the project.

MD_DM_ALL_ENT_ATTR

Used to generate and keep the entity description.


Loading MD_ENTY (MD_ENTY_POP.SQL)

GIVE_ABBRV

Type: Function

This database function GIVE_ABBRV provides the abbreviation for a named token from the table MD_NAME_CONVERSION.

Source Table

MD_NAME_CONVERSION

Columns: ABBREVIATION

Target

Table: MD_OIDM_ATTR_COL_NAME_MAP

Columns: column_name_abbr

MD_DM_ALL_ENT_ATTR

Type: View

This database view provides the description of each entity.


Source Table Target View

DMRS_ENTITIES

MD_DM_ALL_ENT_ATTR


PL/SQL Program to Update Column Name

Type: PL/SQL Program

This program updates the column name based on the result of function GIVE_ABBRV.


Source Tables Target Table

MD_OIDM_ATTR_COL_NAME_MAP

DMRS_ATTRIBUTES

MD_OIDM_ATTR_COL_NAME_MAP

Column: column_name_abbr


PL/SQL program to insert initial data into MD_OIDM_ATTR_COL_NAM

Type: PL/SQL Program

Provides initial loading for table MD_OIDM_ATTR_COL_NAME_MAP


Source Tables Target Table

MD_DM_ALL_ENT_ATTR

DMRS_ENTITIES

MD_OIDM_ATTR_COL_NAME_MAP


PL/SQL program to load data into MD_ENTY

Type: PL/SQL Program

Loads data into MD_ENTY from all the staging tables.


Source Table Target Table

MD_OIDM_ATTR_COL_NAME_MAP

MD_ENTY


Loading Program (Intra-ETL) Metadata (Table MD_PRG)

Source Tables Required


Source Table Name Description

USER_DEPENDENCIES

This database view describes dependencies between procedures, packages, functions, package bodies, and triggers owned by the current user, including dependencies on views created without any database links.

MD_RPD_RPT

This table contains the sample report related information.


Staging Tables/Views


Staging Table/View Name Description

MD_INTRA_ETL

Used to generate and keep the relational/OLAP ETL program metadata information.

MD_MINING

Used to generate and keep the data mining ETL program metadata information.


Loading MD_PRG (MD_PRG_POP.SQL, MD_MIN_PRG_POP.SQL)

Program: MD_INTRA_ETL

Type: View

This view extracts information for relational and OLAP Intra-ETL packages. The structure is the same as MD_PRG.


Source View Target View

USER_DEPENDENCIES

MD_INTRA_ETL


Program: MD_MINING

Type: View

This view extracts information for the data mining Intra-ETL packages. The structure of the view same as MD_PRG.


Source View Target View

USER_DEPENDENCIES

MD_MINING


Program: PL/SQL program to load ETL mapping data into MD_PRG.

Type: PL/SQL Program

Load ETL program data into MD_PRG from all the staging views


Source Views Target Table

MD_INTRA_ETL

MD_MINING

MD_PRG


Program: PL/SQL program insert report data into MD_PRG

Type: PL/SQL Program

Load report data into MD_PRG from report staging table.


Source Table Target Table

MD_RPD_RPT

MD_PRG


Loading Reports and KPI Metadata (Table MD_KPI and MD_REF_ENTY_KPI):

Source Tables Required


Source Table Name Description

MD_RPD

This tables stores all the RPD metadata information, it is directly loaded from md_rpd.csv

MD_REPORT

This tables stores all the report (analysis) metadata information, it is directly loaded from md_report.csv

MD_DASHBOARD

This tables stores all the sample report dashboard metadata information, it's directly loaded from md_dashboard.csv


Staging Tables/Views


Staging Table/View Name Description

MD_RPD_CALC_PHY

Stores the missing physical tables and columns for derived measures. Wrote a query to find out missing Physical tables and columns for derived measures.

MD_REPORT1

MD_REPORT1 has the same structure of MD_RPT, it is used to store comma separated tables and columns to the new row, by that it can directly join with physical tables and columns from MD_RPD_CALC_PHY.

MD_RPT_DASH

Contains all mappings information between RPD and reports.

MD_RPD_RPT_DASH

Stores all the mappings information of Report, RPD and Dashboard.


Loading MD_KPI and MD_REF_ENTY_KPI (SAMPLE_REP_POP.SQL)

Program: PL/SQL program Insert non calculated columns Data Into MD_RPD_CALC_PHY

Type: PL/SQL Program

This program extracts those base KPIs or non calculated column information and inserts into MD_RPD_CALC_PHY.


Source Table Target Table

MD_RPD

MD_RPD_CALC_PHY


Program: PROCEDURE Proc_DelmValuePopulate2

Type: Procedure

This procedure loads comma separated data to new row of the MD_REPORT1 table.


Source Table Target Table

MD_REPORT

MD_REPORT1


Program: PL/SQL program to create and perform initial load of data into MD_RPD_RPT

Type: PL/SQL Program

This program creates and performs initial load of data for the table MD_RPD_RPT.


Source Tables Target Table

MD_RPD_CALC_PHY

MD_REPORT1

MD_RPD_RPT


Program: PL/SQL program to create and initial load data into MD_RPD_RPT_DASH.

Type: PL/SQL Program

This program creates and performs initial load of data for table MD_RPD_RPT_DASH.


Source Tables Target Table

MD_RPD_CALC_PHY

MD_RPT_DASH

MD_RPD_RPT_DASH

MD_RPD_RPT_DASH


Program: PL/SQL program to create and initial load data into MD_RPD_RPT.

Type: PL/SQL Program

This program creates performs initial load of data for table MD_RPD_RPT.


Source Tables Target Table

MD_RPD_CALC_PHY

MD_REPORT1

MD_RPD_RPT


Program: MD_DRVD_KP

Type: View

This view extracts and keeps the information for all the calculated KPIs.


Source Table Target Table

MD_RPD_RPT_DASH

MD_DRVD_KPI


Program: PL/SQL program to create and performs initial load of data into MD_KPI.

Type: PL/SQL Program

This program creates and performs initial load of data for table MD_KPI.


Source Table Target Table

MD_RPD_RPT_DASH

MD_KPI


Program: PL/SQL program to create and initial load data into MD_REF_ENTY_KPI.

Type: PL/SQL Program

This program creates and performs the initial load of data for table MD_REF_ENTY_KPI.


Source Table Target Table

MD_RPD_RPT_DASHI

MD_REF_ENTY_KPI