6 Metadata Collection and Reports

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

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.

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

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

• Details: Entity-Measure Tab Entity to Attribute Measures

• Details Program-Table Tab

• Details: Table-Program Tab

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:

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:

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:

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: