11 Oracle Communications Data Model Utility Scripts

This chapter describes the Oracle Communications Data Model utility scripts.

This chapter includes the following sections:

• Calendar Population

• Metadata Population

Calendar Population

The Calendar population scripts consist of two one-time installation packages.

Calendar Population Scripts

The Calendar population scripts include the following packages:

• calendar_population_header.sql

• calendar_population_body.sql

Running these packages does the following:

1. Prepares necessary changes for the OCDM_SYS schema.

2. Creates the Calendar_Population package that contains the following procedures:

   • RUN(in_setup_start_date, in_setup_no_years) is the main procedure to populate everything about calendar.

   • RBIW_Base_Time_Tables_ddl creates the base table needed to support multiple hierarchies: Business or Calendar.

   • RBIW_Populate_Time_Hier_Bsns(in_setup_start_date, in_setup_no_years) sets up the data in base table for the Business hierarchy as specified in setup or install section.

   • RBIW_Populate_Time_Hier_Clndr(in_setup_start_date, in_setup_no_years) sets up the data in base table for the Calendar hierarchy as specified in setup or install section.

   • RBIW_Time_hier_Star sets up the Time hierarchy reporting layer tables.

   • RBIW_Time_Views sets up the Time hierarchy reporting layer views, star and hybrid snowflake views.

   • RBIW_Populate_Time_Transform populates the Time transformation tables using the base Time tables or views created above. It populates transformation data for both hierarchies: Business and Calendar.

How to Populate Calendar Data

To populate calendar data:

1. Log in to OCDM_SYS user.

2. Execute the following SQL statement:

   exec Calendar_Population.run(date,num_years);
   

   where, date is the start date with which you want to populate calendar data. It is of type CHAR and should be input in the format 'YYYY-MM-DD' (for example,'2005-05-18'). num_years is the number of years to populate calendar data, which should be INTEGER.

Metadata Population

You can use the pkg_get_metadata.sql scrip to generate the Logical Data Model -> Physical Data Model metadata.

Metadata Population Pre-Requisites

LDM dump should be imported into Oracle SQL Data Modeler repository. Physical schema should exist in the same database (OCDM PDM).

Metadata Population Steps

Use the following steps to generate Oracle Communications Data Model Logical Data Model metadata:

1. Connect to the schema where Oracle Communications Data Model Oracle SQL Data Modeler metadata repository resides, for example, "designer".

2. Run metadata_prepare.sql script to create and populate the following required tables. While running the script (metadata_prepare.sql) it prompts for Oracle Communications Data Model Physical Data Model schema, here you have to give the schema name. Once the script is executed, the following tables are created in the designer schema:

   1. NAME_CONVERSION - It stores the original name and abbreviated name.

   2. METADATA - It stores final metadata information.

   3. LDM - It stores the LDM information like entity, attribute names.

   4. PDM - It stores the PDM information like table, column names.

3. Run the pkg_get_metadata.sql script to create get_metadata package in the same designer schema.

4. Run the following command to populate metadata information into metadata table which created in step2.

   Note: You must provide the exact application system name as in Oracle SQL Data Modeler.

   BEGIN
   get_metadata.run('application system name');
   END;
   /
   

5. Manually verify and update, this is the only manual process we have to do, those rows not matched between LDM and PDM in metadata table as we have used FULL OUTER join in the package.