Refreshing the Data in an Oracle Communications Data Model Warehouse

To load new data into your Oracle Communications Data Model warehouse, you extract the data from one or more operational systems and copy that data into the warehouse.

The section, "Performing an Initial Load of the Access Layer" describes how to perform an initial load of an Oracle Communications Data Model data warehouse. After this initial load, you must load new data into your Oracle Communications Data Model data warehouse regularly so that it can serve its purpose of facilitating business analysis.

The challenge in data warehouse environments is to integrate, rearrange and consolidate large volumes of data over many systems, thereby providing a new unified information base for business intelligence.

The successive loads and transformations must be scheduled and processed in a specific order that is determined by your business needs. Depending on the success or failure of the operation or parts of it, the result must be tracked and subsequent, alternative processes might be started.

You can do a full incremental load of the Oracle Communications Data Model warehouse, or you can refresh the data sequentially, as follows:

  1. Refreshing the Foundation Layer of Oracle Communications Data Model Warehouse
  2. Refreshing the Access Layer of an Oracle Communications Data Model Warehouse

In either case, you can manage errors during the execution of the intra-ETL as described in .

Refreshing the Foundation Layer of Oracle Communications Data Model Warehouse

You can refresh the foundation layer of an Oracle Communications Data Model warehouse (that is, the reference, lookup, and base tables) in the following ways:

  • If an application adapter for Oracle Communications Data Model is available for the system from which you want to refresh the foundation layer of an Oracle Communications Data Model warehouse, you can use that adapter to refresh the foundation layer.

  • You can refresh the foundation layer using source-ETL scripts that you wrote using Oracle Warehouse Builder or another ETL tool. For more information on creating source-ETL, see "Writing Your Own Source-ETL".

Refreshing the Access Layer of an Oracle Communications Data Model Warehouse

Refreshing the access layer of an Oracle Communications Data Model is a multi-step process. You can do a full incremental load of the access layer all at one time, or you can refresh the data sequentially, as follows:

  • Refreshing Oracle Communications Data Model Derived Tables

  • Refreshing Oracle Communications Data Model Aggregate Materialized Views

  • Refreshing Oracle Communications Data Model OLAP Cubes

  • Refreshing Oracle Communications Data Model Data Mining Models

In either case, you can manage errors during the execution of the intra-ETL as described in "Managing Errors During Oracle Communications Data Model Intra-ETL Execution".

Refreshing Oracle Communications Data Model Derived Tables

Refreshing the relational tables in an Oracle Communications Data Model is a multi-step process:

  1. Refresh the foundation layer of the Oracle Communications Data Model warehouse (that is, the reference, lookup, and base tables) with operational system data by executing the source-ETL that you have written.
  2. Update the parameters of the DWC_ETL_PARAMETER control table for three processes('OCDM-INTRA-ETL', 'DWD_CUST_DNA', 'DWD_CNT_DAY'). Please refer to "Performing an Initial Load of an Oracle Communications Data Model Warehouse" for more information on the DWC_ETL_PARAMETER table. For an incremental load of an Oracle Communications Data Model warehouse, specify the values shown in the following table (that is, the beginning and end date of the ETL period) for all three processes

    Columns Value

    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, see Oracle Communications Data Model Reference.

  3. Create a session by connecting ocdm_user user through SQLPLUS, then refresh Oracle Communications Data Model derived tables by executing following command:
    SQL>
    
    BEGIN OCDM_SYS.PKG_INTRA_ETL_PROCESS.run;
    END;
    /
    

Refreshing Oracle Communications Data Model Aggregate Materialized Views

Refreshing the Aggregate Materialized Views in an Oracle Communications Data Model is a multi-step process:

  1. Refresh the foundation layer of the Oracle Communications Data Model warehouse (that is, the reference, lookup, and base tables) with operational system data by executing the source-ETL that you have written.
  2. Refresh Oracle Communications Data Model derived tables as explained in Refreshing Oracle Communications Data Model Derived Tables.
  3. Update the parameters of the DWC_ETL_PARAMETER control table for OCDM-DWA-MV-DATE process. Please refer to Performing an Initial Load of an Oracle Communications Data Model Warehouse section to know how to update DWC_ETL_PARAMETER table. For an incremental load of an Oracle Communications Data Model warehouse, specify the values shown in the following table (that is, the beginning and end date of the ETL period) for OCDM-DWA-MV-DATE process.

    Columns Value

    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, see Oracle Communications Data Model Reference.

  4. Create a session by connecting ocdm_user user through SQLPLUS. An intra-ETL process created in Refreshing Oracle Communications Data Model Derived Tables must be in 'RUNNING' status now:
    sqlplus ocdm_user/ocdm_user@SID
    
  5. Refresh Oracle Communications Data Model aggregate materialized views by executing following commands:
SQL> DECLARE
  p_process_no    NUMBER;
  l_status        VARCHAR2(20);
BEGIN
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_ACCT_DEBT_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_ACCT_PYMT_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_ACCT_STTSTC_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_AGRMNT_ACCT_SBRP_PROD',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_ARPU_BASE_CUST_TYP',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_BER_FER_ERR_RATIO_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CALL_CNTR_CALL_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CALL_CNTR_CASE_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CELL_STTSTC_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CMISN_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CNT_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_COST_CNTR_MO',p_process_no);    
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CUST_ACQSTN_SUMM_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CUST_CHRN_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CUST_COST_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CUST_DEBT_COLLCTN_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CUST_EQPMNT_INSTLTN_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CUST_ORDR_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_DATA_USG_MO',p_process_no);    
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_INVC_ADJ_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_INVC_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_INV_POSN_DEPT_DAY',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_INV_POSN_SBC_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_IN_PLTFRM_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_LYLTY_PROG_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_MKT_SHARE',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_MSC_TRFC_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_NBR_PRT_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_NTWK_AVLBLTY_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_NTWK_TCHPNT_MO',p_process_no);    
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_PRPD_ALWNCE_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_PRTNR_STLMNT_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_RDMPTN_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_RF_NTWK_CPCTY_MO',p_process_no);    
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_RVN_MO',p_process_no);        
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_SBSCBR_STTSTC_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_SL_CMPGN_SUMM_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_SPLMNTR_SRVC_USG_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_STORE_EFFNCY_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_VAS_SBRP_QCK_SUMM_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_VAS_USG_MO',p_process_no);
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_VOI_CALL_MO',p_process_no);
END;
/
 
 
SQL> DECLARE
  p_process_no    NUMBER;
  l_status        VARCHAR2(20);
BEGIN
  l_status := OCDM_SYS.PKG_INTRA_ETL_UTIL.Refresh_MV('DWA_CUST_GROSS_ORDRS_QTR',p_process_no);
END;
/

Refreshing OLAP Cubes

On a scheduled basis you must update the OLAP cube data with the relational data that has been added to the Oracle Communications Data Model data warehouse since the initial load of the OLAP cubes. Refreshing the OLAP Cubes in an Oracle Communications Data Model is a multi-step process:

  1. Refresh the foundation layer of the Oracle Communications Data Model warehouse (that is, the reference, lookup, and base tables) with operational system data by executing the source-ETL that you have written.
  2. Refresh Oracle Communications Data Model derived tables as explained in Refreshing Oracle Communications Data Model Derived Tables.
  3. Refresh Oracle Communications Data Model aggregate materialized views as explained in Refreshing Oracle Communications Data Model Aggregate Materialized Views.
  4. Update the parameters of the DWC_OLAP_ETL_PARAMETER control table. Please refer to Performing an Initial Load of an Oracle Communications Data Model Warehouse section to know how to update DWC_OLAP_ETL_PARAMETER table.

    For more information on DWC_OLAP_ETL_PARAMETER control table, see Oracle Communications Data Model Reference.

  5. Create a session by connecting ocdm_user user through SQLPLUS. An intra-ETL process created in Refreshing Oracle Communications Data Model Derived Tables must be in 'RUNNING' status now:
    sqlplus ocdm_user/ocdm_user@SID
    

    Note:

    Run the following script one time to grant privilege on mining tables generated at mining process to user ocdm_report.

    --Run following statements as OCDM_SYS user 
    GRANT SELECT ON dwd_prpd_chrn_svm_roc TO ocdm_report; 
    GRANT SELECT ON dwd_prpd_chrn_svm_lift TO ocdm_report; 
    GRANT SELECT ON dwd_prpd_chrn_svm_cnf_mtrx TO ocdm_report; 
    GRANT SELECT ON dwd_pstpd_chrn_svm_roc TO ocdm_report; 
    GRANT SELECT ON dwd_pstpd_chrn_svm_lift TO ocdm_report; 
    GRANT SELECT ON dwd_pstpd_chrn_svm_cnf_mtrx TO ocdm_report; 
    GRANT SELECT ON dwd_target_promo_svm_roc TO ocdm_report; 
    GRANT SELECT ON dwd_target_promo_svm_lift TO ocdm_report; 
    GRANT SELECT ON dwd_target_promo_svm_cnf_mtrx TO ocdm_report;
    
    --Run following statements as OCDM_REPORT user 
    CREATE SYNONYM dwd_prpd_chrn_svm_roc FOR ocdm_sys.dwd_prpd_chrn_svm_roc; 
    CREATE SYNONYM dwd_prpd_chrn_svm_lift FOR ocdm_sys.dwd_prpd_chrn_svm_lift; 
    CREATE SYNONYM dwd_prpd_chrn_svm_cnf_mtrx FOR ocdm_sys.dwd_prpd_chrn_svm_cnf_mtrx; 
    CREATE SYNONYM dwd_pstpd_chrn_svm_roc FOR ocdm_sys.dwd_pstpd_chrn_svm_roc; 
    CREATE SYNONYM dwd_pstpd_chrn_svm_lift FOR ocdm_sys.dwd_pstpd_chrn_svm_lift; 
    CREATE SYNONYM dwd_pstpd_chrn_svm_cnf_mtrx FOR ocdm_sys.dwd_pstpd_chrn_svm_cnf_mtrx; 
    CREATE SYNONYM dwd_target_promo_svm_roc FOR ocdm_sys.dwd_target_promo_svm_roc; 
    CREATE SYNONYM dwd_target_promo_svm_lift FOR ocdm_sys.dwd_target_promo_svm_lift; 
    CREATE SYNONYM dwd_target_promo_svm_cnf_mtrx FOR ocdm_sys.dwd_target_promo_svm_cnf_mtrx;
    
  6. Refresh Oracle Communications Data Model OLAP cubes by executing following commands:
    SQL> begin
    OCDM_SYS.PKG_OCDM_OLAP_ETL_AW_LOAD.run;    
    END;
    /
    
  7. If there is requirement to refresh only Oracle Communications Data Model OLAP cubes, the same can be achieved with step 6, but before that make sure an intra-ETL process is already running. If no intra-ETL process is running, start one:
    sqlplus ocdm_user/ocdm_user@SID
     
    SQL> DECLARE
      l_process_type  OCDM_SYS.DWC_INTRA_ETL_PROCESS.PROCESS_TYPE%TYPE;
      l_error_text    OCDM_SYS.DWC_MESSAGE.MESSAGE_TEXT%TYPE;
      l_process_no    NUMBER;
    BEGIN
      l_process_no := OCDM_SYS.PKG_INTRA_ETL_UTIL.Start_Process(l_process_type,l_error_text);
    END;
    /
    

Refreshing Oracle Communications Data Model Data Mining Models

Data mining models get refreshed whenever intra-ETL workflow is executed. Data mining models trained using training data collected based on from_date_etl parameter and scored on apply data collected based on from_date_etl parameter in DWC_ETL_PARAMETER table for BUILD-MINING-MODELS process.

You can also refresh all data mining models together or refresh each data mining model individually.

Refreshing the Data Mining Models in an Oracle Communications Data Model is a multi-step process:

  1. Refresh the foundation layer of the Oracle Communications Data Model warehouse (that is, the reference, lookup, and base tables) with operational system data by executing the source-ETL that you have written.
  2. Refresh Oracle Communications Data Model derived tables as explained in "Refreshing Oracle Communications Data Model Derived Tables".
  3. Update the parameters of the DWC_ETL_PARAMETER control table for BUILD-MINING-MODELS process. See "Performing an Initial Load of an Oracle Communications Data Model Warehouse" for information on updating the DWC_ETL_PARAMETER table.

    For more information on DWC_ETL_PARAMETER control table, see Oracle Communications Data Model Reference.

  4. Create a session by connecting ocdm_user user through SQLPLUS. An intra-ETL process created in "Refreshing Oracle Communications Data Model Derived Tables" must be in 'RUNNING' status now:
    sqlplus ocdm_user/ocdm_user@SID
    
  5. Refresh Oracle Communications Data Model data mining models by executing following commands:
    SQL> BEGIN 
    ocdm_sys.pkg_ocdm_mining.run; 
    END;
    /
    
  6. You can also refresh data mining models individually. To refresh Prepaid SVM Churn model, execute the following command (make sure you are connected as ocdm_user user):
    SQL> exec ocdm_sys.pkg_ocdm_mining.create_prpd_churn_svm_model(training_day_key);
    

Running Oracle Data Miner Workflows

Use to run the Oracle Data Miner workflows.

Configure Oracle Data Miner Repository, as described in Oracle Communications Data Model Installation Guide
  1. Update from_date_etl and to_date_etl columns for BUILD-MINING-MODELS process in DWC_ETL_PARAMETER table:
    1. from_date_etl Training data is selected as of this date
    2. to_date_etl Apply data is selected as of this date
  2. Invoke pkg_ocdm_mining_odmr_util.odmr_wf_ltst_exctn_log procedure to extract Oracle Data Miner workflows status:
    BEGIN
    pkg_ocdm_mining_odmr_util.odmr_wf_ltst_exctn_log;
    END;
    /
    
  3. Check dwc_odmr_wf_exctn table for workflows status:
    SELECT * FROM dwc_odmr_wf_exctn;
    
  4. Invoke pkg_ocdm_mining_odmr_util.odmr_wf_stat_chk_upd_trgt_tab procedure to check the status of each workflow and update mining target tables of the models created in the workflow only if workflow execution finished successfully:
    BEGIN
     pkg_ocdm_mining_odmr_util.odmr_wf_stat_chk_upd_trgt_tab;
    END;
    /