D Using the Oracle Communications Billing and Revenue Management Adapter

BRM Adapter for Oracle Communications Data Model Staging Schema Objects

After the installation of BRM Adapter for Oracle Communications Data Model, a staging schema is created, (brm_stg). The staging schema contains the following tables, views, functions, and procedures.

Verify that these objects are in the respective BRM staging schema (brm_stg).

Staging Schema Tables

Corresponding to each Oracle Communications Billing and Revenue Management source table to be loaded, each of the following tables are created or updated in the staging schema:

• Normal Staging Tables (XXX) (the table name is same as source table name)

• Previous Day Tables (XXX_LD)

• Delta Tables (XXX_D)

• Delta History Tables (XXX_D_H)

• Golden Gate Option Input Parameter Table (BRM_MAPPING_TAB)

• Loading Parameter Table BRM_ETL_PARAMETER (having mainly From Date, To Date and Loading date)

Staging Schema Views

For each Oracle Communications Billing and Revenue Management source system view used in the BRM Adapter for Oracle Communications Data Model, two views are created in staging schema (brm_stg), (XXX_ vw), with the names:

• EVENT_BILLING_PAYMENT_T_MAP_VW

• SERVICE_T_MAP_VW

Staging Schema Functions

For each Oracle Communications Billing and Revenue Management source system function used in the BRM Adapter for Oracle Communications Data Model (having name UTC_TO_ORACLE) is created in staging schema (brm_stg).

Staging Schema Procedures

To maintain the everyday source data changes and history of the data changes three procedures are created in staging schema (brm_stg) for both Golden Gate and Non-Golden Gate options:

• PRE_STAGING_LOAD (For Oracle Golden Gate Option)

• PRE_OCDM_LOAD (For Non - Oracle Golden Gate Option)

• POST_STAGING_LOAD (For Oracle Golden Gate Option)

BRM Adapter for Oracle Communications Data Model OCDM_SYS Objects

Installing and configuring the BRM Adapter for Oracle Communications Data Model adds the following procedure definitions to the ocdm_sys schema delivered with Oracle Communications Data Model creates procedures in ocdm_sys schema to populate the integrated BRM data.

Verify that these objects are in the Oracle Communications Data Model schema (ocdm_sys).

Procedures

• DISB_CONS

• ENAB_CONS

• INSERT_REF

• SCD2_UPDATE

Refreshing the Foundation Layer on a Scheduled Basis

If you are not using the BRM Adapter for Oracle Communications Data Model with Oracle GoldenGate to populate the foundation layer of an Oracle Communications Data Model warehouse, then you refresh the foundation layer on a scheduled basis using Oracle Data Integrator (ODI).

To refresh the data in the foundation layer using only ODI, take the following steps:

1. Verify that the installation and configuration created the schema objects described in "Schema Definitions Added for the BRM Adapter for Oracle Communications Data Model".

   See:

   Oracle Communications Data Model Installation Guide for instructions on installing and configuring the BRM Adapter for Oracle Communications Data Model for use by ODI.

2. Reset the BRM_ETL_PARAMETER table parameter values:

   1. Connect to the staging schema (for example: brm_stg).

   2. Delete data from the table BRM_ETL_PARAMETER, then set the values:

      FROM_DATE_ETL=TO_DATE_ETL(last time loading)+ 1 second;
      TO_DATE_ETL=current time of BRM system;
      LOAD_DT = time when loading
      

      For example:

      delete from brm_etl_parameter;
      commit;
      Insert into BRM_ETL_PARAMETER
      (PROCESS_NAME, FROM_DATE_ETL, TO_DATE_ETL, LOAD_DT)
      Values ('BRM-ADAPTER', TO_DATE('05/22/2012 10:28:56', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/24/2012 10:28:55', 'MM/DD/YYYY HH24:MI:SS'), sysdate);
      commit;
      

      Note: you can obtain the current Oracle Communications Billing and Revenue Management time by pvt (pin_virtual_time).

3. From ODI Studio Designer, go to the BRM_OCDM project, then take the following steps:

   1. Select SRC_STG_NONOGG, then Packages, and then, select and execute the following package:

      • SRC_STG_LOAD_NON_OGG_PKG

   2. Select STG_OCDM, then Packages, and then, select and execute the following package:

      • STG_OCDM_INITIAL_LOAD_PKG

Refreshing the Foundation Layer with Real-Time Data

After performing an initial load of the Oracle Communications Data Model warehouse as described in "Initial Loading Using the BRM Adapter for Oracle Communications Data Model", you can refresh the data in the foundation layer of an Oracle Communications Data Model warehouse on a real-time basis.

Take the followings steps to ensure that to the data in the foundation layer of an Oracle Communications Data Model warehouse in this manner:

1. Install the BRM Adapter for Oracle Communications Data Model and configure it for use by both Oracle GoldenGate and Oracle Data Integrator (ODI) and as described in Oracle Communications Data Model Installation Guide.

   Verify that the installation and configuration created the schema objects described in "Schema Definitions Added for the BRM Adapter for Oracle Communications Data Model".

2. Oracle GoldenGate handles step 1, as shown in Figure D-2. All you need to do is check whether the Oracle GoldenGate processes in the source and staging systems are in RUNNING mode (all GoldenGate processes keep checkpoint information and restart from the point of crash without any loss of data, provided nobody alters the process to restart from a different position).

   From the GGSCI prompt, issue info all commands to verify that the Oracle GoldenGate processes needed by the BRM Adapter for Oracle Communications Data Model are running.

   Source System Processes	Staging System Processes
   Manager process Extract process (EXTBRM) Extract Pump process (EXTPBRM)	Manager process Replicate process (REPBRM)

   
   

   The following code shows the issuing of these commands with successful results.

   GGSCI>  (mypc1)  5> info all
   

   Program    Status   Group    Lag        Time Since Chkpt
   
   MANAGER    RUNNING
   EXTRACT    RUNNING  EXTBRM   47:29:00   00:00:20
   EXTRACT    RUNNING  EXTPBRM  00:00:00   47:29:06
   

   GGSCI>  (ocdm01)  2> info all
   

   Program    Status   Group    Lag        Time Since Chkpt
   
   MANAGER    RUNNING
   REPLICAT   RUNNING  REPBRM   00:00:00   00:03:09
   

   Tips:

   If you have two source systems, check process status on both source systems.

   For commands to manage Oracle GoldenGate processes, see Oracle Communications Data Model Installation Guide.

3. To perform Step 2, 3, 4 as shown in Figure D-2, using ODI run the following package: STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG.

   From ODI Studio Designer, go to the BRM_OCDM project, then take the following steps:

   1. Select STG_OCDM, then select Packages, and then, select and execute the STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG.

   See:

   See "Execution Flow Using BRM Adapter: with Oracle GoldenGate and ODI" for an explanation of the execution flow.

Objects in SRC_STG_NONOGG Folder of BRM_OCDM Project

SRC_STG_LOAD_NON_OGG_PKG Package

The SRC_STG_LOAD_NON_OGG_PKG package in SRC_STG_NONOGG folder of BRM_OCDM project is using to load data into BRM staging database from BRM source database. Following steps were included in this process of loading staging database.

1. Execute PRE_STAGING_LOAD procedure to load previous day tables(XXX_LD). This procedure performs following operations:

   1. Drop Previous Day Tables (XXX_LD)

   2. Rename normal staging tables (XXX) to previous day tables (XXX_LD)

   3. Create normal staging tables (XXX) structure from previous day tables (XXX_LD)

   (Drop XXX_LD -> rename XXX to XXX_LD ->Create XXX from XXX_LD).

2. Execute Non Golden Gate Interfaces (XXX_NONGG) one by one to load normal staging tables (XXX).

   There are 101 Non Golden Gate Interfaces out of which 51 have the naming convention (XXX_NONGG) and the other 51 use the naming convention (XXX_NONGG_IU); these are executed in sequence to load Staging tables form Source tables (src.XXX to stg.XXX).

   SRC_STG_NONGG Interfaces:

   1.     ACCOUNT_NAMEINFO_T_NONGG_IU
   2.     ACCOUNT_NAMEINFO_T _NONGG
   3.     ACCOUNT_T_NONGG_IU
   4.     ACCOUNT_T_NONGG
   5.     BAL_GRP_T_NONGG_IU
   6.     BAL_GRP_T_NONGG
   7.     BILLINFO_T_NONGG_IU
   8.     BILLINFO_T_NONGG
   9.     BILL_T_NONGG_IU
   10.     BILL_T_NONGG
   11.     CONFIG_T_NONGG_IU
   12.     CONFIG_T_NONGG
   13.     CONFIG_BEID_BALANCES_T_NONGG_IU
   14.     CONFIG_BEID_BALANCES_T_NONGG
   15.     CONFIG_BUSINESS_TYPE_T_NONGG_IU
   16.     CONFIG_BUSINESS_TYPE_T_NONGG
   17.     CONFIG_CUR_CONV_RATES_T_NONGG_IU
   18.     CONFIG_CUR_CONV_RATES_T_NONGG
   19.     DD_OBJECTS_T_NONGG_IU
   20.     DD_OBJECTS_T_NONGG
   21.     DEAL_PRODUCTS_T_NONGG_IU
   22.     DEAL_PRODUCTS_T_NONGG
   23.     DEAL_T_NONGG_IU
   24.     DEAL_T_NONGG
   25.     DISCOUNT_T_NONGG_IU
   26.     DISCOUNT_T_NONGG
   27.     EVENT_BAL_IMPACTS_T_NONGG_IU
   28.     EVENT_BAL_IMPACTS_T_NONGG
   29.     EVENT_BILLING_PAYMENT_CASH_T_NONGG_IU
   30.     EVENT_BILLING_PAYMENT_CASH_T_NONGG
   31.     EVENT_BILLING_PAYMENT_CC_T_NONGG_IU
   32.     EVENT_BILLING_PAYMENT_CC_T_NONGG
   33.     EVENT_BILLING_PAYMENT_CHECK_T_NONGG_IU
   34.     EVENT_BILLING_PAYMENT_CHECK_T_NONGG
   35.     EVENT_BILLING_PAYMENT_DD_T_NONGG_IU
   36.     EVENT_BILLING_PAYMENT_DD_T_NONGG
   37.     EVENT_BILLING_PAYMENT_FAILED_T_NONGG_IU
   38.     EVENT_BILLING_PAYMENT_FAILED_T_NONGG
   39.     EVENT_BILLING_PAYMENT_PAYORD_T_NONGG_IU
   40.     EVENT_BILLING_PAYMENT_PAYORD_T_NONGG
   41.     EVENT_BILLING_PAYMENT_POST_T_NONGG_IU
   42.     EVENT_BILLING_PAYMENT_POST_T_NONGG
   43.     EVENT_BILLING_PAYMENT_T_NONGG_IU
   44.     EVENT_BILLING_PAYMENT_T_NONGG
   45.     EVENT_BILLING_PAYMENT_WTRAN_T_NONGG_IU
   46.     EVENT_BILLING_PAYMENT_WTRAN_T_NONGG
   47.     EVENT_BROADBAND_USAGE_T_NONGG_IU
   48.     EVENT_BROADBAND_USAGE_T_NONGG
   49.     EVENT_SESSION_DIALUP_T_NONGG_IU
   50.     EVENT_SESSION_DIALUP_T_NONGG
   51.     EVENT_SESSION_TELCO_GPRS_T_NONGG_IU
   52.     EVENT_SESSION_TELCO_GPRS_T_NONGG
   53.     EVENT_SESSION_TLCO_GSM_T_NONGG_IU
   54.     EVENT_SESSION_TLCO_GSM_T_NONGG
   55.     EVENT_SESSION_TLCS_T_NONGG_IU
   56.     EVENT_SESSION_TLCS_T_NONGG
   57.     EVENT_SESS_TLCS_SVC_CODES_T_NONGG_IU
   58.     EVENT_SESS_TLCS_SVC_CODES_T_NONGG
   59.     EVENT_T_NONGG_IU
   60.     EVENT_T_NONGG
   61.     IFW_CURRENCY_NONGG_IU
   62.     IFW_CURRENCY_NONGG
   63.     IFW_TIMEZONE_NONGG_IU
   64.     IFW_TIMEZONE_NONGG
   65.     IFW_USAGETYPE_NONGG_IU
   66.     IFW_USAGETYPE_NONGG
   67.     INVOICE_T_NONGG_IU
   68.     INVOICE_T_NONGG
   69.     ITEM_T_NONGG_IU
   70.     ITEM_T_NONGG
   71.     NOTE_T_NONGG_IU
   72.     NOTE_T_NONGG
   73.     PLAN_SERVICES_T_NONGG_IU
   74.     PLAN_SERVICES_T_NONGG
   75.     PLAN_T_NONGG_IU
   76.     PLAN_T_NONGG
   77.     PRODUCT_T_NONGG_IU
   78.     PRODUCT_T_NONGG
   79.     PURCHASED_DISCOUNT_T_NONGG_IU
   80.     PURCHASED_DISCOUNT_T_NONGG
   81.     PURCHASED_PRODUCT_T_NONGG_IU
   82.     PURCHASED_PRODUCT_T_NONGG
   83.     RATE_PLAN_T_NONGG_IU
   84.     RATE_PLAN_T_NONGG
   85.     SERVICE_EMAIL_T_NONGG_IU
   86.     SERVICE_EMAIL_T_NONGG
   87.     SERVICE_TELCO_FEATURES_T_NONGG_IU
   88.     SERVICE_TELCO_FEATURES_T_NONGG
   89.     SERVICE_TELCO_GPRS_T_NONGG_IU
   90.     SERVICE_TELCO_GPRS_T_NONGG
   91.     SERVICE_TELCO_GSM_T_NONGG_IU
   92.     SERVICE_TELCO_GSM_T_NONGG
   93.     SERVICE_T_NONGG_IU
   94.     SERVICE_T_NONGG
   95.     PAYINFO_T_NONGG_IU
   96.     PAYINFO_T_NONGG
   97.     PAYINFO_CC_T_NONGG_IU
   98.     PAYINFO_CC_T_NONGG
   99.     PAYINFO_DD_T_NONGG_IU
   100.     PAYINFO_DD_T_NONGG
   101.     PAYINFO_INV_T_NONGG_IU
   102.     PAYINFO_INV_T_NONGG
   

3. Execute POST_STAGING_LOAD procedure to load the data into Delta Tables (XXX _DELTA) and Delta History Tables (XXX_DELTA_H).

   1. Truncate delta tables (XXX _DELTA)

   2. Insert data into delta tables (XXX _DELTA) from normal staging tables (XXX) and previous day tables (XXX_LD).

   3. Update delta tables (XXX _DELTA) columns as per the source changes (OPTYPE, LAST_UPDATE_TIME & CHANGE_DATE)

   4. Insert data into delta history tables(XXX_DELTA_H) from delta tables (XXX _DELTA)

   (Truncate XXX_DELTA -> Insert into XXX_DELTA from (XXX minus XXX_LD) -> Insert into XXX_DELTA_H from XXX_DELTA)

SRC_STG_LOAD_NON_OGG_PKG Package

The SRC_STG_LOAD_NON_OGG_PKG package in SRC_STG_NONOGG folder of BRM_OCDM project is using to load the staging delta and delta history tables. This package will call the PRE_OCDM_LOAD procedure and execute in an infinite loop. In each loop of iteration, it performs the following steps and loads the data into Delta Tables (XXX _DELTA) and Delta History Tables (XXX_DELTA_H).

1. Insert data into delta history tables(XXX_DELTA_H) from delta tables (XXX _DELTA)

2. Truncate delta tables (XXX _DELTA)

3. Insert data into delta tables (XXX _DELTA) from normal staging tables (XXX)

4. Wait for new data in any XXX _DELTA tables, ODI uses an tool called ODI WAIT FOR DATA which will be in the waiting state; the job of this Tool (ODI WAIT FOR DATA) will be waiting for any data changes in staging layer delta tables (XXX _DELTA).Once it finds any changes it will execute the concerned interface where the data will be populated into related OCDM foundation layer tables

(Insert into XXX_DELTA_H from XXX_DELTA and Truncate XXX_DELTA and Insert into XXX_DELTA from XXX)

Objects in STG_OCDM Folder of BRM_OCDM

STG_OCDM_INITIAL_LOAD_PKG Package

The STG_OCDM_INITIAL_LOAD_PKG package in STG_OCDM folder of BRM_OCDM project loads the OCDM foundation layer tables from BRM Staging Database delta tables (XXX _DELTA). The package STG_OCDM_INITIAL_LOAD_PKG executes 46 STG-OCDM interfaces (XXX_MAP) which are executed in sequence, where the data is populated into related OCDM foundation layer tables form staging delta tables (XXX _DELTA).

STG-OCDM Interfaces:

1.     ACCOUNT_ACCT_MAP
2.     ACCOUNT_ADDR_LOC_MAP
3.     ACCOUNT_CUST_MAP
4.     ACCOUNT_GEO_CITY_MAP
5.     ACCOUNT_GEO_CNTRY_MAP
6.     ACCOUNT_GEO_STATE_MAP
7.     ACCOUNT_INVC_DLVRY_MAP
8.     ACCOUNT_POSTCD_MAP
9.     ACCOUNT_PRTY_CNCT_INFO_MAP
10.     ACCOUNT_PRTY_MAP
11.     BAL_GRP_GRP_MAP
12.     BILLINFO_ACCT_MAP
13.     BILLINFO_PYMT_MTHD_TYP_MAP
14.     BILL_INVC_MAP
15.     CONFIG_BEID_BAL_TYP_MAP
16.     CONFIG_BUSINESS_TYPE_T_MAP
17.     CONFIG_CUR_CRNCY_EXCHNG_RATE_MAP
18.     DD_OBJECTS_SRVC_SPEC_MAP
19.     DEAL_PKG_ASGN_MAP
20.     DEAL_PROD_MAP
21.     EVENT_BAL_IMPACTS_NTWK_EVT_ACCT_BAL_IMPC_MAP
22.     EVENT_BILLING_ACCT_RCHRG_MAP
23.     EVENT_BROADBAND_USAGE_BRDBND_USG_EVT_MAP
24.     EVENT_CALL_EVT_MAP
25.     EVENT_DATA_SRVC_EVT_MAP
26.     EVENT_SESSION_GPRS_USG_EVT_MAP
27.     IFW_CRNCY_MAP
28.     IFW_TIMWZONE_TIME_MAP
29.     IFW_USAGETYPE_SRVC_TYP_MAP
30.     INVOICE_INVC_DLVRY_MAP
31.     ITEM_INVC_ITEM_MAP
32.     NOTE_EVT_PRTY_INTRACN_MAP
33.     PAYINFO_ACCT_PREF_PYMT_MTHD_MAP
34.     PAYINFO_CC_PRTY_MAP
35.     PAYINFO_DD_PRTY_MAP
36.     PAYINFO_INV_T_PRTY_MAP
37.     PLAN_MKT_PLN_MAP
38.     PLAN_SERVICES_PROD_MKT_PLN_ASGN_MAP
39.     PRODUCT_DISCOUNT_PROD_MAP
40.     PRODUCT_DISCOUNT_PROD_RLTN_MAP
41.     PURCHASED_PRODUCT_PRICE_CHRG_MAP
42.     PURCHASED_PRODUCT_SBRP_MAP
43.     RATE_PLAN_PROD_RTNG_PLN_MAP
44.     SERVICE_CUST_FCNG_SRVC_MAP
45.     SERVICE_SRVC_STAT_HIST_MAP
46.     SERVICE_TELCO_FEATURES_SRVC_CHTRSTC_MAP 

STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG Package

The following mentioned STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG package in STG_OCDM folder of BRM_OCDM project executes the OCDM foundation layer tables population interfaces and these packages needs to be executed and should be in running mode.

Note: STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG is a package which is having a package STG_OCDM_OGG_CDC_LOAD_PKG and a SCD2_UPDATE procedure, the scenario package is based on STG_OCDM_OGG_CDC_LOAD_PKG which consist of 46 interfaces and a procedure PRE_OCDM_LOAD. The STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG will be in an Infinite loop process which will wait for data to perform data load from stage(BRM_STG) to target (OCDM_SYS)