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

Part Number E20363-03
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

You use the Oracle Retail Data Model metadata browser generation packages to generate and update the Oracle Retail Data Model metadata browser.

There are four main tables and other staging tables and views in the metadata generation package. The tables are: MD_ENTY, MD_PRG, MD_KPI, and MD_REF_ENTY_KPI; these are the tables that support metadata browser reports.

This chapter includes the following sections:

Metadata Collection and Population

Use the following steps to generate Oracle Retail Data Model Logical Data Model metadata, and use the following sequence to ensure proper metadata collection and loading.

  1. Collect LDM Metadata

    In step you 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.

    • Start Oracle SQL Developer Modeler

    • Open LDM -> File -> Export -> to Report schema.

  2. Collect Sample Dashboard Metadata

    In this step you extract BIEE dashboard metadata from webcat to csv file.

    Use OBIEE catalog manager to open SQL Developer sample report webcat.

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

    Select columns one by one as per 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 Retail Data Model sample report webcat.

    • Tools -> create Report -> Select type to report on -> select Analysis -> select columns one by one as per 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 Retail 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 Retail Data Model PDM naming convention information from csv into a staging table.Use sqlloader to load data from name_conversion.csv into MD_NAME_CONVERSION table. 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. 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. 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.

    Use sqlloader to load data from md_rpd.csv into MD_RPD table. 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)

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

Metadata Reports and Dashboard

To customize the Oracle Retail Data Model reports, you must understand the dependencies among Oracle Retail Data Model objects, especially how the report KPIs are mapped to the physical tables and columns.

The "Oracle Retail Data Model Metadata" browser that helps you discover these dependencies. When you install Oracle Retail Data Model with its sample reports, the metadata browser is delivered as a sample Dashboard.

See:

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

There are four tabs (reports) in the Oracle Retail Data Model Metadata browser:

Details: Measure-Entity Tab Business Areas and Measures Attributes and Entities

On the Measure-Entity tab the measure descriptions, computational formulas with physical columns, physical tables, and corresponding entities can be viewed by Business Area.

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

Report table uses and column used details:

Table Associated Columns
MD_ENTY_VIEW ENTY_NAME1, ATTRBT_NAME
MD_KPI KPI_NAME, KPI_DESC, CMPUT_LOGIC, BSNS_AREA
MD_PRG_VIEW PHY_TAB_NAME

Details: 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.

For example, take the following steps to learn more about an entity:

  1. Select the entity.

  2. Click GO.

Report table uses and column used details:

Table Associated Columns
MD_ENTY ENTY_NAME, ATTRBT_NAME
MD_KPI KPI_NAME, CMPUT_LOGIC

Details Program-Table Tab

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

For example, take 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.

Report table uses and column used details:

Table Associated Columns
MD_PRG PRG_TYP, PRG_NAME, PHY_TAB_NAME, SB_PRG_TYP, PRG_MODEL, SB_PRG_DESC

Details: 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, you must move a particular table from the right pane to the left (Selected) pane.

For example, to see the reports that use a particular table, take 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.

Report table uses and column used details:

Table Associated Columns
MD_PRG PRG_NAME, PHY_TAB_NAME, SB_PRG_TYP, PRG_MODEL, SB_PRG_DESC