Skip Headers
Oracle® Communications Data Model Implementation and Operations Guide
Release 11.3.2

E28442-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 Metadata Collection and Reports

This chapter includes the following sections:

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

Metadata is organized into three major categories:

  • Business metadata describes the meaning of data in a business sense. The business interpretation of data elements in the data warehouse is based on the actual table and column names in the database. Business metadata gathers this mapping information, business definitions, and rules information.

  • Technical metadata represents the technical aspects of data, including attributes such as data types, lengths, lineage, results from data profiling, and so on.

  • Process execution metadata presents statistics on the results of running the ETL process itself, including measures such as rows loaded successfully, rows rejected, amount of time to load, and so on.

Since metadata is so important in information management, many organizations attempt to standardize metadata at various levels, such as:

  • Metadata Encoding and Transmission Standard (METS). A standard for encoding descriptive, administrative, and structural metadata regarding objects within a digital library.

  • American National Standards Institute (ANSI). The organization that coordinates the U.S. voluntary standardization and conformity-assessment systems.

  • International Organization for Standardization (ISO). The body that establishes, develops, and promotes standards for international exchange.

  • Common Warehouse Metamodel (CWM). A specification, released and owned by the Object Management Group, for modeling metadata for relational, non-relational, multi-dimensional, and most other objects found in a data warehousing environment.

When you implement your metadata management solution, reference your data warehouse infrastructure environment and make the decision which standard to follow.

Working with a Metadata Repository

You manage metadata using a Metadata Repository. At the highest level, a Metadata Repository includes three layers of information. The layers are defined in the following order:

  1. Physical layer: this metadata layer identifies the source data.

  2. Business Model and Mapping layer: this metadata layer organizes the physical layer into logical categories and records the appropriate metadata for access to the source data.

  3. Presentation layer: this metadata layer exposes the business model entities for end-user access.

The first step in creating a Metadata Repository is to scope your metadata management needs by:

  • Identifying the metadata consumers. Typically, there are business consumers and technical consumers.

  • Determine the business and technical metadata requirements.

  • Aligning metadata requirements to specific data elements and logical data flows.

Then:

  • Decide how important each part is.

  • Assign responsibility to someone for each piece.

  • Decide what constitutes a consistent and working set of metadata

  • Where to store, backup, and recover the metadata.

  • Ensure that each piece of metadata is available only to those people who need it.

  • Quality-assure the metadata and ensure that it is complete and up to date.

  • Identify the Metadata Repository to use and how to control that repository from one place

After creating the metadata definitions, review your data architecture to ensure you can acquire, integrate, and maintain the metadata.

As the data keeps on changing in your data warehouse day by day, update the Metadata Repository. When you want to change business rules, definitions, formulas or process (especially when customizing the Oracle Communications Data Model), your first step is to survey the metadata and do an impact analysis to list all of the attributes in the data warehouse environment that would be affected by a proposed change.

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.

See:

Oracle Communications Data Model Installation Guide for more information on installing the sample reports and deploying the Oracle Communications Data Model RPD and webcat on the Business Intelligence Suite Enterprise Edition instance.

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.

Using the Measure-Entity Tab Business Areas and Measures Attributes and Entities

The Measure-Entity tab provides information on the measure descriptions, computational formulas with physical columns, physical tables, and corresponding entities by Business Area.

To browse the Measure-Entity data, select the business area and measure description that you are interested in.

Using the Entity-Measure Tab Entity to Attribute Measures

The Entity-Measure tab displays the measures supported by the entities and how they are calculated. You can discover information about particular entities and attributes.

To view the Entity-Measure tab perform the following steps to learn more about an entity:

  1. Select the entity.

  2. Click GO.

Using the Program-Table Tab

The Program-Table tab displays the input and output tables used in the selected programs.

To use the Program-Table tab, perform the following steps to learn more about intra-ETL mappings:

  1. Select the program type (that is, intra-ETL or report) and program name for showing particular report or intra-ETL information.

  2. Select GO.

Using the Table-Program Tab

The Table-Program tab lists the Programs used by a given table and whether that table is an input or output, or both, of that program. To discover what reports use a particular table, move a particular table from the right pane to the left (Selected) pane.

To see the reports that use a particular table, perform the following steps:

  1. In the right pane of the Table-Program tab, select the table.

  2. Move the table to the Selected list on the left by clicking on < (left arrow), and click OK.

  3. Select GO.

    The reports for the selected table are displayed.

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):".

Load 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

Load 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

Load 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