Derived Tables in Oracle Communications Data Model

Derived tables have a DWD_ prefix.

Derived tables are tables where one of the following apply:

  • Have as values the result of a non-aggregate calculation against the data in the foundation layer tables.

  • Have some minimal level of aggregation, typically at the day level (for example, DWD_CNT_DAY).

  • Summarize, at day or month level, all activities over the period of specific processes, split in various attributes (for example, DWD_ACCT_BAL_MO).

Some derived tables leverage, and hence are dependent on, other derived tables (for example DWD_CUST_MNNG).

Depending on the type of derived table, you customize the table as follows:

  • Tables that hold the results of a calculation such as the DWD_BER_FER_ERR_RATIO_DAY table that contains values that are the daily BER (Bit Error Rate) and FER (Frame Error Rate) statistics about the network elements.

  • Result tables for the data mining models (for example, DWD_CUST_MNNG).

See Also:

The Derived Tables topic in Oracle Communications Data Model Reference for a list of all of the derived tables in the default Oracle Communications Data Model

For a list of only those derived tables that are results tables for the data mining models, see the chapter on Data Mining Models in Oracle Communications Data Model Reference

Creating New Derived Tables for Calculated Data

If, during fit-gap analysis, you identified a need for calculated data that is not provided by the default derived tables, you can meet this need by defining new tables or, alternatively, by adding missing dimensions and measures to existing derived tables.

When designing these tables, name the tables following the convention of using the CWD_ prefix (for Customized Warehouse Derived) or DWD_ (for Data Warehouse Derived). Make sure all the main dimensions are put first and have Foreign Keys to their corresponding reference or lookup tables. Attributes that add information only, avoiding costly joins, should not be part of the Primary Key of the derived table. Some dimensions that are part of a hierarchy do not necessarily need to be part of the Primary Key. All measures should be put afterward, grouped if possible by similar meaning. Make sure that all monetary measures should have the LOCAL and REPORTING amount associated, and if possible the currency (as required in the TM Forum SID).

See Also:

Oracle Communications Data Model Reference for details on dimensions that are part of a hierarchy

Customizing Oracle Communications Data Model Data Mining Models

Some derived (DWD_) tables in the default ocdm_sys schema are the results of data mining models defined in the default Oracle Communications Data Model. Those models are defined in the default ocdm_sys schema that also comes with Oracle Communications Data Model.

Oracle Communications Data Model data mining models get source data from views defined on two derived tables (DWD_) and a base table (DWB_). These tables are:

  • DWD_CUST_DNA

  • DWD_VAS_SBRP_QCK_SUMM

  • DWB_EVT_PRTY_INTRACN

Derived tables store data mining models prediction results and model rules, in (DWD_), and use reference tables (DWR_), and look up tables (DWL_). These tables are:

  • DWD_CUST_PROD_AFFLTN

  • DWD_CUST_DNA

  • DWR_CUST_DT_NODE

  • DWD_CHRN_SVM_FACTOR

  • DWD_CHRN_SVM_ROC

  • DWD_PROMO_SVM_FACTOR

  • DWD_PROMO_SVM_ROC

  • DWR_CUST_SGMNT

  • DWR_CUST_SGMNT_DTL

  • DWL_MNNG_LTV_BAND

  • DWL_MNNG_LT_SRVVL_BAND

When Oracle Communications Data Model is installed the installer copies the mining source scripts to $ORACLE_HOME/ocdm/pdm/mining/src.

The following table lists the mining scripts:

Table 3-1 Oracle Communications Data Model Mining Scripts

Script Name Description

cust_sntmnt_manual_score.sql

Predefined dictionary for customer comment scoring.

ocdm_mining_init.sql

Initializes mining environment and executes the other three mining scripts.

pkg_mining_etl.sql

Defines views, which have training/apply data, on source tables.

pkg_ocdm_mining.sql

Core mining package that has a procedure for each model. Each procedure drops, creates mining model and scores mining model.

When you create a customized Oracle Communications Data Model warehouse, you can customize the data mining models in the following ways:

Creating a New Data Mining Model for Oracle Communications Data Model

Describes the steps to create a data mining model.

When you need to create new results tables, Follow the naming conventions outlined in Conventions When Customizing the Physical Model and use a DWD_ prefix for new results tables.
  1. Define the problem and identify input attributes. Also identify target attribute if the mining problem is supervised.
  2. Check if the existing mining source views defined in pkg_mining_etl.sql script support the requirement of your problem. Modify the definition of views to support your requirement. Do not remove any columns from view definition unless you are sure that those columns do not make any sense.
  3. If the existing mining source views do not support required fields, create a new table or view to support your requirements. Add the new table to pkg_mining_etl.sql PL/SQL package. Modify the intra-ETL programs to support your mining problem requirements.
  4. For each mining problem that Oracle Data Mining supports, there is more than one algorithm. Create a setting table for your mining problem and follow the naming convention. The prefix for a setting table is "DM_". Add the definition of new setting table to ocdm_mining_init.sql script.
  5. Add a procedure for your mining problem to the pkg_ocdm_mining PL/SQL package. This procedure creates the mining model and scores the trained mining model on the apply data. Compile the package. To create the mining model for your problem, invoke the newly added procedure. Make sure your new procedure works according to your expectations. Check user_mining_models data dictionary view for trained model. There are more data dictionary views that give information on the trained models.

Related Topics

Modifying Oracle Communications Data Model Data Mining Models

Describes the steps to customize Oracle Communications Data Model mining models.

  1. Change the definition of source views used as input to the mining model.

  2. If required, change the definition of source derived table, DWD_CUST_DNA. Do not remove any existing columns. Only add new columns with NULL enable.

  3. Modify the intra-ETL package of DWD_CUST_DNA table. Execute the intra-ETL package to load data into DWD_CUST_DNA table.

  4. Refresh mining views by executing following statement. You need to pass training day and apply day key:

    SQL> exec PKG_MINING_ETL.refresh_mining_views(l_trnng_day_key,l_apply_day_key);

  5. Train the model again by calling Oracle Communications Data Model mining package.

  6. Ensure that the model reflects the new definition (for example, that a new column has been added).

To add a new column to create_prpd_churn_svm_model, perform the following steps:

  1. Add the new column to views that are used in create_prpd_churn_svm_model.

    • DWV_PRPD_CUST_CHRN_SRC

    • DWV_PRPD_CUST_CHRN_TST

    • DWV_PRPD_CUST_CHRN_APPLY

  2. Train the model by issuing the following statement: 
    pkg_ocdm_mining.create_prpd_churn_svm_model(training_day_key);
  3. Execute the following statement to query model details table and ensure the new column name is included in the query result: 
    SQL> SELECT attribute_name
        FROM TABLE(SELECT ATTRIBUTE_SET FROM 
        TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_SVM('OCDM_PRPD_CHURN_SVM'))
     WHERE CLASS=1);

Related Topics

How to Customize the Churn Prediction Model

After you have populated Oracle Communications Data Model foundation layer and the derived tables, you can leverage the prebuilt Oracle Communications Data Model mining model for some more advanced analysis and predictions.

This tutorial shows you how to predict the prepaid customers who will terminate the service in next three months (churners) based on the populated Oracle Communications Data Model warehouse. Using prebuilt Oracle Communications Data Model Mining models, you can easily and very quickly see the prediction result of your customers, without having to go through all of the data preparation, training, testing and applying process that you must perform in a traditional from-scratch mining project.

After the initially generated mining model, as time goes by, the customer information and their behavior change. Consequently, you must refresh the previous trained mining models based on the latest customer and usage data. You can follow the process in this tutorial to refresh the mining models to acquire predictions on latest customer information.

This tutorial shows you how to generate the Churn Prediction model through Oracle Communications Data Model Mining APIs. To use different parameters in the training process, or customize the model in more advanced fashion, you can also use Oracle Data Miner to do the same work.

Related Topics

Tutorial Prerequisites

Before starting this tutorial:

Review the Oracle by Example (OBE) tutorial "Using Oracle Data Miner 11g Release 2." To access this tutorial, open the Oracle Learning Library in your browser by following the instructions in Oracle Technology Network; and, then, search for the tutorial by name.

  1. Install Oracle Communications Data Model.

  2. Populate the base, reference, and lookup tables.

  3. Execute the intra-ETL.

Ensure that the following tables contain valid data:

DWB_ACCT_STAT_HIST
DWB_BRDBND_USG_EVT
DWB_FIXED_LN_CALL_EVT
DWB_ISP_USG_EVT
DWB_MMS_EVT
DWB_NP_RQST_HDR
DWB_SMS_EVT
DWB_WRLS_CALL_EVT
DWB_WRLS_CNTNT_DNLDG_EVT
DWD_ACCT_DEBT_MO
DWD_ACCT_PYMT_DAY
DWD_CNTCT_CNTR_DAY
DWD_CUST_DNA
DWD_PRPD_ACCT_STTSTC_DAY
DWL_AGE_BND
DWL_AGE_ON_NET_BND
DWL_DEBT_AGNG_BND
DWR_ACCT
DWR_ADDR_LOC
DWR_AGRMNT
DWR_CUST
DWR_BSNS_MO
DWR_CUST_SCR
DWR_DEMOG_ATRIB
DWR_EXTRNL_OPRTR
DWR_HH
DWR_INDVL_DEMOG_VAL
DWR_PROD_SBRPDWR_JB
DWR_SOC_JB

Note:

If you have not populated the real customer data and you only want to learn the Oracle Communications Data Model mining model, you can use the sample data by taking the following steps:

  1. Ensure that during the install you generated the calendar data covering the range of 2011 through 2012. For example, by setting the parameters of starting from "20070101" for 10 years satisfy this condition.

  2. Download the sample data (ocdm_for_mining.dmp.zip):

    http://download.oracle.com/technetwork/database/options/comm-data-model/ocdm_for_mining.dmp.zip

    Then unzip the sample data and import that data into your new ocdm_sys schema using the following command:

    imp system/<password> file=ocdm_for_mining.dmp fromuser=ocdm_sample touser=ocdm_sys ignore=y log=mining_dump_import.log

Preparing Your Environment

Provides a tutorial for preparing your environment.

This tutorial requires a valid, populated Oracle Communications Data Model warehouse.

Note:

For more information about using SQL Developer, refer to tutorial "Getting Started with Oracle SQL Developer 3.0". To access this tutorial, open the Oracle Learning Library in your browser by following the instructions in Oracle Technology Network; and, then, search for the tutorial by name.

To prepare the environment, perform the following steps:

  1. In SQL Developer, connect to the ocdm_sys schema.

    Tip:

    SQL Developer can be found on any Oracle Database Installation under $ORACLE_HOME/sqldeveloper.

  2. After you connect to the ocdm_sys schema, you can see all the tables in that schema. You can narrow down the list by right clicking the "table" and then applying filters:

    Description of GUID-555A8ACF-1E0F-4AAF-9C23-F875556A9BBF-print.png follows
    Description of the illustration GUID-555A8ACF-1E0F-4AAF-9C23-F875556A9BBF-print.png

  3. (Optional) As mentioned in the Preparing Your Environment, if you have not populated tables with your data you can try using sample data. After you download the sample data, follow these steps to import the sample data:

    1. Connect to sqlplus as sysdba:

      sqlplus / as sysdba

    2. Execute following commands to generate disable constraint script:

      spool disable_constraints.sql
SET PAGESIZE 15000
SET LINESIZE 1000
conn ocdm_sys/ocdm_sys
SELECT 'ALTER TABLE ' || table_name || ' disable constraint ' || constraint_name || ';'
FROM user_constraints
WHERE STATUS='ENABLED'
AND constraint_type = 'R';
 
 
SELECT 'ALTER TABLE ' || table_name || ' disable constraint ' || constraint_name || ';'
FROM user_constraints
WHERE STATUS='ENABLED'
AND constraint_type = 'U';
 
SELECT 'ALTER TABLE ' || table_name || ' disable constraint ' || constraint_name || ';'
FROM user_constraints
WHERE STATUS='ENABLED'
AND constraint_type = 'C';
 
SELECT 'ALTER TABLE ' || table_name || ' disable constraint ' || constraint_name || ';'
FROM user_constraints
WHERE STATUS='ENABLED'
AND constraint_type = 'P';
 
spool off

    3. Execute disable_constraints.sql script as ocdm_sys user:

      sqlplus ocdm_sys/ocdm_sys @disable_constraints.sql

    4. Import the sample dump into ocdm_sys schema by executing the following commands:

      imp system/<password> file=ocdm_for_mining.dmp fromuser=ocdm_sample touser=ocdm_sys ignore=y log=mining_dump_import.log

    5. Execute the following commands to generate enable constraint script:

      spool enable_constraints.sql
SET PAGESIZE 15000
SET LINESIZE 1000
conn ocdm_sys/ocdm_sys
SELECT 'ALTER TABLE ' || table_name || ' enable constraint ' || constraint_name || ';'
FROM user_constraints
WHERE STATUS='DISABLED'
AND constraint_type = 'R';
 
 
SELECT 'ALTER TABLE ' || table_name || ' enable constraint ' || constraint_name || ';'
FROM user_constraints
WHERE STATUS='DISABLED'
AND constraint_type = 'U';
 
SELECT 'ALTER TABLE ' || table_name || ' enable constraint ' || constraint_name || ';'
FROM user_constraints
WHERE STATUS='DISABLED'
AND constraint_type = 'C';
 
SELECT 'ALTER TABLE ' || table_name || ' enable constraint ' || constraint_name || ';'
FROM user_constraints
WHERE STATUS='DISABLED'
AND constraint_type = 'P';
 
spool off

    6. Execute enable_constraints.sql script as ocdm_sys user:

      sqlplus ocdm_sys/ocdm_sys @enable_constraints.sql

  4. Review tables to ensure they contain valid data, either from your own customer data, or from the sample data:

    Description of GUID-C60F7E7B-AB85-4EA8-9EF7-659A308DFF42-default.png follows
    Description of the illustration GUID-C60F7E7B-AB85-4EA8-9EF7-659A308DFF42-default.png

  5. Review the DWD_CUST_DNA table:

    Description of GUID-69ED1D53-903B-4332-AD3B-39D3DFCB9600-default.png follows
    Description of the illustration GUID-69ED1D53-903B-4332-AD3B-39D3DFCB9600-default.png

  6. Select and check each of the following tables to ensure that the table is properly populated:

    DWR_CUST

    DWB_EVT_PRTY_INTRACN

    DWB_ACCT_PYMT

    DWD_CUST_DNA

    DWD_VAS_SBRP_QCK_SUMM

Generating the Prepaid Churn Prediction Model

For generating prepaid churn prediction model, refresh the mining views defined on mining source tables DWD_CUST_DNA and DWD_VAS_SBRP_QCK_SUMM.

  1. Refresh mining views by passing training day key and apply day key. If you are using mining sample dump provided, then pass 20110201 as training day key and 20110301 as apply day key: 
    sqlplus  ocdm_sys/ocdm_sys
SQL> exec PKG_MINING_ETL.refresh_mining_views(l_trnng_day_key,l_apply_day_key);
  2. Check user_mining_models data dictionary view for mining models. It should return no data if you have not previously trained any models: 
    SQL> SELECT * FROM user_mining_models;
  3. Train the prepaid churn prediction model by executing following: 
    sqlplus  ocdm_sys/ocdm_sys
SQL> exec PKG_OCDM_MINING.create_prpd_churn_svm_model (l_trnng_day_key);
  4. Check user_mining_models data dictionary view for trained prepaid churn prediction mining model: 
    SQL> SELECT * FROM user_mining_models;
  5. Check DWD_CHRN_SVM_FACTOR table for prepaid churn SVM factors: 
    SQL> SELECT * FROM DWD_CHRN_SVM_FACTOR WHERE ACCT_TYP_CD = 'PRPD';

Note:

This tutorial does not refresh all models. It only refreshes one prepaid churn prediction model. To refresh all of the default mining models based on latest customer data, follow the instructions in Refreshing the Data in an Oracle Communications Data Model Warehouse.

Checking the Result

Describes the steps to perform after you refresh, train, and generate the data mining model, to check the results with the DWD_CUST_DNA results table in ocdm_sys schema.

  1. Issue the following query: 
    SQL> SELECT CUST_CD, PRDCT_CHURN_SVM_IND, PRDCT_CHURN_SVM_PROB 
FROM DWD_CUST_DNA 
WHERE CHRN_IND = 0 --Apply it only on active customers, hence checking their data
AND   DAY_KEY = 20110301 --Apply day Key
AND ROWNUM < 10
;

    This provides results:

    Description of GUID-BA1ACF1B-3B88-4AF4-AEB7-A7512607769E-default.png follows
    Description of the illustration GUID-BA1ACF1B-3B88-4AF4-AEB7-A7512607769E-default.png

    For each customer identified by CUST_CD, the PRDCT_CHRN_SVM_IND column gives a Boolean prediction of whether a customer will churn in next three months. Zero (0) stands for non-churner, while one (1) stands for churner. The PRDCT_CHURN_SVM_PROB column provides a more detailed probability (0~1) that specifies how likely a customer is going to churn.

  2. (Optional) If you have also installed the Oracle Communications Data Model sample reports in Oracle Business Intelligence Suite Extended Edition you can also view the results as an Oracle Business Intelligence Suite Extended Edition report.