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

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

  • If you are using an application adapter for Oracle Communications Data Model, then you use that adapter to load the foundation layer. For example, you can use the NCC Adapter for Oracle Communications Data Model to populate the foundation layer of an Oracle Communications Data Model warehouse with data from an Oracle Communications Network Charging and Control system.

  • 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) take 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.


    For more information on DWC_ETL_PARAMETER control table.

  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 Intra-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.