Performing an Initial Load of an Oracle Communications Data Model Warehouse

Performing an initial load of an Oracle Communications Data Model is a multistep process.

1. Load the foundation layer of the Oracle Communications Data Model warehouse (that is, the reference, lookup, and base tables) .
2. Load the access layer of the Oracle Communications Data Model warehouse (that is, the derived and aggregate tables, materialized views, OLAP cubes, and data mining models).

• Performing an Initial Load of the Foundation Layer
  Describes how the manner in which you perform an initial load of the foundation layer of Oracle Communications Data Model tables, (that is, the reference, lookup, and base tables) varies depending on whether you are using an application adapter.
• Performing an Initial Load of the Access Layer
  To perform an initial load of access layer of the Oracle Communications Data Model warehouse (that is, the derived and aggregate tables, materialized views, OLAP cubes, and data mining models) perform the following steps:
• Executing the Default Oracle Communications Data Model ntra-ETL
  The intra-ETL workflow is implemented using PL/SQL package, PKG_INTRA_ETL_PROCESS. This package has a public procedure, Run, and also has private procedures for executing derived intra-ETL programs, refreshing aggregate materialized views, refreshing data mining models, and refreshing OLAP cubes. The public procedure, Run, invokes all the private procedures.

Performing an Initial Load of the Foundation Layer

Describes how the manner in which you perform an initial load of the foundation layer of Oracle Communications Data Model tables, (that is, the reference, lookup, and base tables) varies depending on whether you are using an application adapter.

You perform an initial load of the foundation layer as follows:

• If you are using an application adapter for Oracle Communications Data Model, then you use that adapter to load the foundation layer.

• If you are not using an application adapter, then you perform the initial load of the foundation layer using source-ETL that you create.

Performing an Initial Load of the Access Layer

To perform an initial load of access layer of the Oracle Communications Data Model warehouse (that is, the derived and aggregate tables, materialized views, OLAP cubes, and data mining models) perform the following steps:

1. Update the parameters in DWC_ETL_PARAMETER control table in the ocdm_sys schema for different processes so that the ETL can use this information (that is, the beginning and end date of the ETL period) when loading the derived and aggregate tables and views.

   For an initial load of an Oracle Communications Data Model warehouse, specify the values shown in the following tables:

   For OCDM-INTRA-ETL process:

   Columns	Value
   PROCESS_NAME	'OCDM-INTRA-ETL'
   FROM_DATE_ETL	The beginning date of the ETL period.
   TO_DATE_ETL	The ending date of the ETL period.

   For DWD_CUST_DNA process:

   Columns	Value
   PROCESS_NAME	'DWD_CUST_DNA'
   FROM_DATE_ETL	The beginning date of the ETL period.
   TO_DATE_ETL	The ending date of the ETL period.

   For DWD_CNT_DAY process:

   Columns	Value
   PROCESS_NAME	'DWD_CNT_DAY'
   FROM_DATE_ETL	The beginning date of the ETL period.
   TO_DATE_ETL	The ending date of the ETL period.

   For OCDM-DWA-MV-DATE process:

   Columns	Value
   PROCESS_NAME	' OCDM-DWA-MV-DATE'
   FROM_DATE_ETL	The beginning date of the ETL period.
   TO_DATE_ETL	The ending date of the ETL period.

   For BUILD-MINING-MODELS process:

   Columns	Value
   PROCESS_NAME	'BUILD-MINING-MODELS'
   FROM_DATE_ETL	The beginning date of the ETL period.
   TO_DATE_ETL	The ending date of the ETL period.

2. Update the Oracle Communications Data Model OLAP ETL parameters in DWC_OLAP_ETL_PARAMETER control table in the ocdm_sys schema to specify the build method and other build characteristics so that the ETL can use this information when loading the OLAP cube data.

   Table 4-1 Values of OLAP ETL Parameters in the DWC_OLAP_ETL_PARAMETER table for Initial Load

   Column Name	Value
   PROCESS_NAME	' OCDM-OLAP-ETL'
   BUILD_METHOD	C which specifies a complete refresh which clears all dimension values before loading.
   CUBENAME	One of the following values that specifies the cubes you want to build: ALL specifies a build of the cubes in the Oracle Communications Data Model analytic workspace. cubename[[|cubename]...] specifies one or more cubes to build.
   MAXJOBQUEUES	A decimal value that specifies the number of parallel processes to allocate to this job. (Default value is 4.) The value that you specify varies depending on the setting of the JOB_QUEUE_PROCESSES database initialization parameter.
   CALC_FCST	One of the following values depending on whether you want to calculate forecast cubes: Y specifies calculate forecast cubes. N specifies do not calculate forecast cubes.
   NO_FCST_YRS	If the value for the CALC_FCST column is Y, specify a decimal value that specifies how many years forecast data you want to calculate; otherwise, specify NULL.
   FCST_MTHD	If the value for the CALC_FCST column is Y, then specify AUTO; otherwise, specify NULL.
   FCST_ST_YR	If the value for the CALC_FCST column is Y, then specify value specified as 'BY YYYY' which is the "start business year" of a historical period; otherwise, specify NULL.
   FCST_END_YR	If the value for the CALC_FCST column is Y, then specify value specified as 'BY YYYY' which is the "end business year" of a historical period; otherwise, specify NULL.
   OTHER1	Specify NULL.
   OTHER2	Specify NULL.

3. Execute the intra-ETL.

Executing the Default Oracle Communications Data Model ntra-ETL

The intra-ETL workflow is implemented using PL/SQL package, PKG_INTRA_ETL_PROCESS. This package has a public procedure, Run, and also has private procedures for executing derived intra-ETL programs, refreshing aggregate materialized views, refreshing data mining models, and refreshing OLAP cubes. The public procedure, Run, invokes all the private procedures.

Before executing intra-ETL workflow, update ETL parameters in DWC_ETL_PARAMETER and DWC_OLAP_ETL_PARAMETER tables. It is suggested to not use ocdm_sys user to update ETL parameter tables and executing intra-ETL workflow. Ask your DBA to unlock ocdm_user for performing these tasks using the following commands:

ALTER USER ocdm_user account unlock;

Use ocdm_user user to update ETL parameter tables and execute intra-ETL workflow. In a SQLPLUS session, connect to ocdm_user user:

sqlplus ocdm_user/ocdm_user@SID

Update ETL parameter tables:

SQL> UPDATE OCDM_SYS.DWC_ETL_PARAMETER
SET from_date_etl = < The beginning date of the ETL period >,
    to_date_etl   = < The ending date of the ETL period >
WHERE process_name = 'OCDM-INTRA-ETL'
;
/
SQL> commit;
 
SQL> UPDATE OCDM_SYS.DWC_ETL_PARAMETER
SET from_date_etl = < The beginning date of the ETL period >,
    to_date_etl   = < The ending date of the ETL period >
WHERE process_name = 'DWD_CUST_DNA'
;
/
SQL> commit;
 
SQL> UPDATE OCDM_SYS.DWC_ETL_PARAMETER
SET from_date_etl = < The beginning date of the ETL period >,
    to_date_etl   = < The ending date of the ETL period >
WHERE process_name = 'DWD_CNT_DAY'
;
/
SQL> commit;
 
SQL> UPDATE OCDM_SYS.DWC_ETL_PARAMETER
SET from_date_etl = < The beginning date of the ETL period >,
    to_date_etl   = < The ending date of the ETL period >
WHERE process_name = 'OCDM-DWA-MV-DATE'
;
/
SQL> commit;
 
SQL> UPDATE OCDM_SYS.DWC_ETL_PARAMETER
SET from_date_etl = < The beginning date of the ETL period >,
    to_date_etl   = < The ending date of the ETL period >
WHERE process_name = 'BUILD-MINING-MODELS'
;
/
SQL> commit;
 
SQL> UPDATE OCDM_SYS.DWC_ETL_PARAMETER
SET build_method = <>,
    cubename   = <>,
  .
  .
  .
  .
fcst_st_yr = <>,
fcst_end_yr = <>
;
/
SQL> commit;

Run the following command to execute intra-ETL workflow:

SQL> BEGIN 
OCDM_SYS.PKG_INTRA_ETL_PROCESS.Run; 
END; 
/

The status of each activity is tracked using DWC_INTRA_ETL_ACTIVITY table. The status of each cube data loading is tracked using DWC_OLAP_ACTIVITY table. The status of the entire intra-ETL workflow process is tracked using DWC_INTRA_ETL_PROCESS table.