C Refreshing the Data in an Oracle Communications Data Model Warehouse

Introduction to Refreshing the Data in an Oracle Communications Data Model Warehouse

"Performing an Initial Load of an Oracle Communications Data Model Warehouse" describes how to perform an intial 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.

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 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 and are 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 relational tables and views, OLAP cubes, and data mining models all at one time, or you can refresh the data sequentially:

1. Refreshing Oracle Communications Data Model Relational Tables and Views

2. Refreshing Oracle Communications Data Model OLAP Cubes

3. 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 Relational Tables and Views

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

1. Refresh the reference, lookup, and base tables in the Oracle Communications Data Model warehouse with OLTP data by executing the source-ETL that you have written.

2. Update the parameters of the DWC_ETL_PARAMETER control table in the ocdm_sys schema. 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).

   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.

   
   

   See:

   Oracle Communications Data Model Reference for more information on the DWC_ETL_PARAMETER control table.

3. Refresh the derived tables and aggregate tables which are materialized views in Oracle Communications Data Model by executing the DRVD_FLOW and AGGR_FLOW subprocess of the INTRA_ETL_FLW process flow. See "Executing the Default Oracle Communications Data Model Intra-ETL " for more information.

Refreshing Oracle Communications Data Model 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.

You can execute the Oracle Communications Data Model ETL to update the OLAP cubes in the following ways

• Refresh all of the data in the warehouse by executing the Oracle Warehouse Builder Workflow INTRA_ETL_FLW in one of the ways that are described in "Executing the Default Oracle Communications Data Model Intra-ETL ".

  The OLAP Cubes are populated through OLAP_MAP which is a part of Oracle Communications Data Model intra-ETL main workflow INTRA_ETL_FLW.

• Refresh only the OLAP cube data by executing the OLAP_MAP Oracle Warehouse Builder mapping in the Oracle Warehouse Builder control center.

  Note:

  You must refresh the corresponding materialized view of the OLAP cubes you are refreshing before you execute OLAP_MAP. (For the mapping between OLAP cube and materialized views, refer to Oracle Communications Data Model Reference.

Take these steps to perform an incremental load of the analytic workspace that is part of the Oracle Communications Data Model warehouse:

1. Update the aggregate tables which are materialized views in Oracle Communications Data Model. See "Refreshing Oracle Communications Data Model Relational Tables and Views" for more information.

2. Execute the intra-ETL to load the cube data in one of the ways described in "Executing the Default Oracle Communications Data Model Intra-ETL ".

3. If necessary, recover from errors that happen during the execution of OLAP_MAP take the following steps.

   1. Change the value of the BUILD_METHOD column of the ocdm_sys.DWC_OLAP_ETL_PARAMETER table to "C".

   2. In Oracle Warehouse Builder, rerun the OLAP_MAP map.

Refreshing Oracle Communications Data Model Data Mining Models

The MINING_FLW sub-process flow of the INTRA_ETL_FLW process flow triggers the data mining model refreshment. After the initial load of the warehouse, it is recommended to refresh the data mining models monthly. Refreshing the data models is integrated into the MINING_FLW sub-process flow. You can also manually refresh the data models.

The way you refresh a data mining model varies depending on whether you want to refresh all of the models or only one model:

• To manually refresh all mining models, call the following procedure.

  ocdm_mining.PKG_OCDM_MINING.REFRESH_MODEL( MONTH_CODE,P_PROCESS_NO)
  

  This procedure performs the following tasks for each model:

  1. Refreshes the mining source materialized views based on the latest data from ocdm_sys schema.

  2. Trains each model on the new training data.

  3. Applies each model onto the new apply data set.

• To manually re-create only one mining model, you can call the corresponding procedure. For example, to re-create ther churn SVM model, you can call the following procedure.

  ocdm_mining.create_churn_svm_model( MONTH_CODE );
  

  "Tutorial: Customizing the Churn Prediction Mining Model" provides detailed instructions for refreshing a single data mining model.