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.

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.

Related Topics

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.

Related Topics

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:

Related Topics

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 arehouse (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'). 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;
/

Related Topics

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:

See Also:

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

  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..
  3. Update the parameters of the DWC_ETL_PARAMETER control table for BUILD-MINING-MODELS process.
  4. Create a session by connecting ocdm_user user through SQLPLUS. An intra-ETL process 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);

Related Topics

Running Oracle Data Miner Workflows

Use these steps 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;
/

Related Topics