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:
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.
Start Oracle SQL Developer Data Modeler
Open Logical Data Model
Select File.
Select Export.
Select To Reporting Schema.
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:
Name
Description
Path
Folder
Analysis Path
Analysis Name
Analysis Description
Dashboard Page Description
Dashboard Page Name
Dashboard Page Path
Owner
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:
NAME
DESCRIPTION
TABLE_NAME
COLUMN_NAME
FOLDER
PATH
SUBJECT_AREA
FORMULA
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.
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 )
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) )
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) )
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 )
Load LDM/PDM Metadata (Table MD_ENTY):
Load LDM/PDM mapping and related information into table MD_ENTY..
Load Program (Intra-ETL) Metadata (Table MD_PRG):
Load Intra-ETL program input/output and related information into table MD_PRG.
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.
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 |
Describes the table names of the tables that generate and store program metadata.
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 |
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 |