7 Oracle Communications Data Model Intra-ETL

This chapter includes the following sections:

Introduction to Oracle Communications Data Model Intra-ETL
Value Lookup Models for ETL Mappings
Intra-ETL Oracle Warehouse Builder Mapping Source and Target Tables
Intra-ETL PL/SQL Mapping Source and Target Tables
Intra-ETL Process Flows
Executing the Intra-ETL

Introduction to Oracle Communications Data Model Intra-ETL

In Oracle Communications Data Model, reference and lookup tables store master, reference, and dimensional data; and the base, derived, and aggregate tables store transaction and fact data at different granularities. The base tables store the transaction data at the lowest level of granularity, while the derived and aggregate tables store consolidated and summary transaction data.

Two types of Extract, Transform, and Load (ETL) operations populate the tables with data. The source-ETL operations populate the reference, lookup, and base tables with data from the source On-Line Transaction Processing (OTLP) applications. Additional Intra-ETL operations populate the derived and aggregate tables with the data in the base, reference, and lookup tables. While the source ETL operations are not a part of Oracle Communications Data Model, the Intra-ETL operations are.

There are two categories of Intra-ETL operations (scripts):

Derived Population: A database package containing scripts that populate the derived tables based on the content of the base, reference, and lookup tables.
Aggregate Population: A database package containing scripts to refresh the Oracle Communications Data Model aggregate tables, mostly Materialized Views, based on the content of the derived tables and some reference tables.

Derived tables are implemented using Oracle tables, while the Aggregate tables are implemented using Materialized Views.

Note:

Do not make changes to the ETL as such changes are not supported.

The INTRA_ETL_FLW is actually a process flow designed using the Oracle Warehouse Builder Workflow component which includes the dependency of each individual sub process flow and executes each process flow in the proper order. The result of each table loading is tracked in DWC_ control tables.

For more information, see "Intra-ETL Process Flows" and "Executing the Intra-ETL" and Oracle Communications Data Model Operations Guide.

Value Lookup Models for ETL Mappings

Oracle Communications Data Model Value_Lookup values contains the Lookup tables and its values which are used in Intra-ETL mapping. Hardcoded values contains the list of tables and values which are used in Join conditions & Filter conditions in Intra-ETL mapping.

Table 7-1 Shows the lookup tables and values which are used in Intra-ETL mapping.

Table 7-1 Value Lookup Values for Intra-ETL Mapping

SI No.	Hard Coded Value Table Name	Hard Coded Value Column	Value used	ETL Program Name	ETL Usage Type
1	`DWB_ACCT_DEBT_DT`L	`ACCT_DEBT_DTL_TYP_CD`	`BILL, PNLTY`	`DWD_ACCT_DEBT_DAY`	Hardcoded
2	`DWB_ACCT_PYMT`	`PYMT_MTHD_TYP_CD`	`BNK`	`DWD_ACCT_PYMT_DAY`	Hardcoded
3	`DWB_ACCT_PYMT`	`PYMT_TRX_TYP_CD`	`LTPAY, DPST, PNLTY, INVC`	`DWD_ACCT_PYMT_DAY`	Hardcoded
4	`DWB_EVT_ACCT`	`ACCT_EVT_TYP_CD`	`CRT, VOLDEACT, VOLSUSP, RECNCT, RFSUS, TMNT, INDEACT, DISCNCTN, INSUSP, ACTVTN`	`DWD_ACCT_STAT`	Hardcoded
5	`DWR_PROD`	`PROD_CD`	`PAYTV, HOMTEL, IDD, WRLS, BRDBND`	`DWD_ACCT _STTSTC`	Hardcoded
6	`DWB_ACCT_STAT_HIST`	`ACCT_STAT_TYP_CD`	`CHRN`	`DWD_ACCT _STTSTC`	Hardcoded
7	`DWB_EVT_LYLTY_PROG`	`LYLTY_PROG_EVT_TYP_CD`	`ACMLTN, RDMPTN`	`DWD_ACCT _STTSTC`	Hardcoded
8	`DWB_INVC_ITEM`	`PROD_CHRG_TYP_CD`	`SRVC, SLPROD, AIRTM`	`DWD_ARPU_BASE`	Hardcoded
9	`DWB_COST`	`COST_SUBTYP_CD`	`AQSNCOST, RETNCOST, CCNTCOST, OPRNCOST`	`DWD_ARPU_BASE`	Hardcoded
10	`DWB_EVT_PRTY_INTRACN_CALL`	`INTRACN_RSN_CD`	`CUSTCOMP`	`DWD_CALL_CNTR_CALL_DAY`	Hardcoded
	`DWB_EVT_PRTY_INTRACN_CALL`	`INTRACN_RSLT_TYP_CD`	`RESLVD, PNDNG`	`DWD_CALL_CNTR_CALL_DAY`	Hardcoded
11	`DWR_CNRT_ASGN`	`CNRT_ASGN_RSN_CD`	`OPINIT, CUSTCHNG`	`DWD_CANBLZTN_DTL_DAY`	Hardcoded
14	`DWD_CNRT_CHNG_MO`	`CNRT_CHNG_TYP_CD`	`RPLC, TMNT`	`DWD_CNRT`	Hardcoded
15	`DWB_INVC_ITEM`	`INVC_ITEM_TYP_CD`	`MTHLYFEE, PNLTY`	`DWD_CNRT`	Hardcoded
16	`DWR_CMPGN`	`CMPGN_PRPS`	`ACQR, RTNTN`	`DWD_CNRT`	Hardcoded
17	`DWR_PRMTN`	`PRMTN_TYP_CD`	`PRMM`	`DWD_CNRT`	Hardcoded
18	`DWB_CNRT_TERM_VAL`	`CNRT_TERM_TYP_CD`	`MONAMT`	`DWD_CNRT_CHNG`	Hardcoded
19	`DWR_CNRT_ASGN`	`CNRT_ASGN_TYP_CD`	`RPLC`	`DWD_CNRT_CHNG`	Hardcoded
20	`DWB_CNRT_STAT`	`CNRT_STAT_TYP_CD`	`TMNT`	`DWD_CNRT_CHNG`	Hardcoded
	`DWB_CNRT_STAT`	`CNRT_STAT_RSN_CD`	`PRMTN, PRODUPGD, CMPLN`	`DWD_CNRT_CHNG`	Hardcoded
21	`DWB_CUST_COST`	`COST_SUBTYP_CD`	`AQSNCOST, RETNCOST, CMSN, NTWKCOST, CCCOST, RHCOGS, AHCOGS, ACMSNP, ACMSNC, DMREPY, DMCNRT, OTRCOST, SCCPREPY, SCCCNRT, SLNGCOST, OPRNCOST`	`DWD_COST_CUST`	Hardcoded
22	`DWB_BSNS_UNIT_COST`	`COST_SUBTYP_CD`	`OTRCOST, OPRNCOST, INSTCOST, ADVRCOST, CBUDGET, CATNCOST`	`DWD_COST_ORG`	Hardcoded
23	`DWB_CNRT_TERM_VAL`	`CNRT_TERM_TYP_CD`	`CNRTVAL`	`DWD_CUST_ACQSTN_SUMM_DAY`	Hardcoded
24	`DWB_EVT_SBRP`	`EVT_TYP_CD`	`ACTV, TMNT`	`DWD_CUST_ACQSTN_SUMM_DAY`	Hardcoded
25	`DWB_ACCT_DEBT_DTL`	`ACCT_DEBT_DTL_TYP_CD`	`PNLTY`	`DWD_EXTRNL_DEBT_COLLCTN_DAY`	Hardcoded
26	`DWR_ITEM`	`ITEM_TYPE_CD`	`HNDST`	`DWD_HNDST_SUBSDY_DAY`	Hardcoded
27	`DWR_PROD`	`PROD_NAME`	`HANDSET`	`DWD_HNDST_SUBSDY_DAY`	Hardcoded
28	`DWB_ACCT_DEBT_DTL`	`ACCT_DEBT_DTL_TYP_CD`	`PNLTY`	`DWD_INTRNL_DEBT_COLLCTN_DAY`	Hardcoded
29	`DWB_EVT_ACCT`	`ACCT_EVT_TYP_CD`	`CRT, TMNATMPT, TMNT`	`DWD_LN_ACTVTN_TMNT_DAY`	Hardcoded
30	`DWR_CMPGN`	`CMPGN_PRPS_TYP_CD`	`RTNTN, CONDATE`	`DWD_LN_ACTVTN_TMNT_DAY`	Hardcoded
31	`DWB_MNT_ALLWNC`	`PPA_CTGRY_CD`	`FLANSWER, FLCALL`	`DWD_PRPD_ALWNCE_DAY`	Hardcoded
32	`DWR_PROD_MKT_PLN`	`PROD_MKT_PLN_TYP_CD`	`PRPD`	`DWD_PRPD_CALL_SUMM_DAY`	Hardcoded
33	`DWR_EVT_PRTY_RL`	`EVT_PRTY_RL_CD`	`OPRT`	`DWD_RDMPTN_DAY`	Hardcoded
34	`DWB_UMS_EVT`	`UMS_EVT_TYP_CD`	`RCVD, DEL`	`DWD_VAS_USAGE_DAY`	Hardcoded
35	`DWB_WRLS_CALL_EVT`	`DVRT_RTRV_TYP_CD`	`RTRV, DVRT`	`DWD_VAS_USAGE_DAY`	Hardcoded
36	`DWB_EVT_LYLTY_PROG`	`LYLTY_PROG_EVT_TYP_CD`	`ACMLTN`	`DWD_SBCRBR_CHRN_STTSTC`	Hardcoded
37	`DWB_WRLS_CALL_EVT`	`CALL_TMNT_RSN_CD`	`DRPD, CNCL`		Hardcoded
38	`DWB_CNRT_TERM_VAL`	`CNRT_TERM_TYP_CD`	`NBRLNS`	`DWD_SHRD_PKG_USG_STTSTC_DAY`	Hardcoded
39	`DWR_PROD_CAPBLTY`	`PROD_CAPBLTY_CD`	`NBRLNS`	`DWD_SHRD_PKG_USG_STTSTC_DAY`	Hardcoded
40	`DWB_EVT`	`EVT_RSLT_CD`	`SUCC, FAIL`	`DWD_SL_RPRSTV_STTSTC_MO`	Hardcoded
41	`DWR_PROD_MKT_PLN_ASGN`	`PROD_MKT_PLN_ASGN_CD`	`GIFT`	`DWD_SUBSDY_AMT`	Hardcoded
44	`DWR_PROD`	`PROD_NAME`	`CALL, SMS, MMS`	`DWD_SUBSDY_AMT`	Hardcoded
1	`DWL_DEBT_AGNG_BND`	`DEBT_AGNG_BND_CD`	`DAB1, DAB3, DAB2, DAB4`,	`DWD_ACCT_DEBT_DAY, DWD_ACCT_STTSTC, DWD_CRDT_CTGRY_MO, DWD_PYMT_AGNG_DAY, DWD_SBCRBR_CHRN_STTSTC`	Value_Lookup
2	`DWL_AGE_ON_NET_BND`	`AGE_ON_NET_BND_CD`	`M1 , M3 , M6 , M12 , M24 , M36 , M60 , M96 , M120 , M240 , M240+ ,`	`DWD_ACCT_PYMT_DAY, DWD_CRDT_CTGRY_MO, DWD_HNDST_SUBSDY_DAY, DWD_INVC, DWD_ACCT_STTSTC, DWD_PRPD_ACCT_STTSTC, DWD_PYMT_AGNG_DAY, DWD_RDMPTN_DAY, DWD_LYLTY_PROG_DAY, DWD_SBCRBR_CHRN_STTSTC`	Value_Lookup
3	`DWL_AGE_ON_NET_BND`	`AGE_ON_NET_BND_FROM`	`0 , 3 , 6 , 12, 25 , 36 , 51 , 101, 201 , 271 , 401` ,	`DWD_ACCT_PYMT_DAY`	Value_Lookup
4	`DWL_AGE_ON_NET_BND`	`AGE_ON_NET_BND_TO`	`2 , 5 , 11, 24 , 35 , 50 , 100, 200 , 270 , 400 , 9999999999,`	`DWD_ACCT_PYMT_DAY`	Value_Lookup
6	`DWL_AGE_BND`	`AGE_BND_CD`	`AGBND1 , AGBND2 , AGBND3 , AGBND4 , AGBND5`	`DWD_ACCT_STTSTC, DWD_SBCRBR_CHRN_STTSTC`	Value_Lookup
7	`DWL_CHRN_RSN`	`CHRN_RSN_CD`	`NORSN , SVFLR , CRELOC , CHRNDFLT, CHRNSRVC , RLCTN, DISSAT , SVCTMNT , NONPAID`	`DWD_ACCT _STTSTC`	Value_Lookup
8	`DWL_CUST_RVN_BND`	`CUST_RVN_BND_CD`	`BAND100, BAND200 , BAND300 , BAND400 , BAND500 , BAND600 , BAND700 , BAND800 , BAND900 , BAND1000, BAND1100 , BAND1600 , BAND1500 , BAND1400 , BAND1300 , BAND1200, BAND1700 , BAND1800 , BAND1900 , BAND2000 , BND2000+`	`DWD_ACCT _STTSTC, DWD_SBCRBR_CHRN_STTSTC`	Value_Lookup
9	`DWL_ARPU_BAND`	`ARPU_BND_CD`	`ARPU7500+ , ARPU1000 , ARPU2500 , ARPU5000 , ARPU7500`	`DWD_ACCT_STTSTC, DWD_SBCRBR_CHRN_STTSTC`	Value_Lookup
10	`DWL_PK_OFPK_TIME`	`PK_OFPK_TIME_CD`	`PK , OFPK`	`DWD_CDR_WRLS_DAY, DWD_PRPD_CALL_SUMM_DAY, DWD_VAS_USAGE_DAY`	Value_Lookup
16	`DWL_RECHRG_RVN_SLB`	`RECHRG_RVN_SLB_CD`	`$0-25, $25-50, $50-100, $100+`	`DWD_PRPD_ACCT_STTSTC`	Value_Lookup
17	`DWL_INTRACN_RSN`	`INTRACN_RSN_CD`	`CMPLN, DBCOLL, SRVC, IBMKTG, OBMKTG, CUSTCOMP`	`DWD_PRPD_ACCT_STTSTC`	Value_Lookup
18	`DWL_PRMTN_RSLT_TYP`	`PRMTN_RSLT_TYP_CD`	`OFRACPT, PREVENT`	`DWD_SL_CMPGN_DTL_DAY`	Value_Lookup

Intra-ETL Oracle Warehouse Builder Mapping Source and Target Tables

Shows the Oracle Warehouse Builder mapping to populate derived tables.

DWD_ACCT_DEBT_DAY__MAP Mapping

Populate target table DWD_ACCT_DEBT_DAY. For more information, see ACCOUNT DEBT DAY DRVD.

Table 7-2 DWD_ACCT_DEBT_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_BAL_ADJ
DWB_ACCT_PYMT
DWB_DEBT_COLLCTN_ASGN
DWL_DEBT_AGNG_BND
DWR_ACCT
DWR_ADDR_LOC
DWR_CUST
DWR_DAY

DWD_ACCT_PYMT_DAY__MAP Mapping

Populate target table DWD_ACCT_PYMT_DAY. For more information, see ACCOUNT PAYMENT DAY DRVD.

Table 7-3 DWD_ACCT_PYMT_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_PYMT
DWB_DEBT_COLLCTN_ASGN
DWB_INV_PYMT_ASGN
DWB_INVC
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_BSNS_MO
DWR_ADDR_LOC
DWR_CUST

DWD_ACCT_PYMT_MTHD_STAT_HIST__MAP Mapping

Populate target table DWD_ACCT_PYMT_MTHD_STAT_HIST. For more information, see ACCOUNT PAYMENT METHOD STATUS HIST DRVD.

Table 7-4 DWD_ACCT_PYMT_MTHD_STAT_HIST__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWB_ACCT_PYMT_MTHD_STAT
DWB_CNRT_TERM_VAL
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_ACCT_PREF_PYMT_MTHD
DWR_BSNS_MO
DWR_CNRT
DWR_CUST

DWD_ACCT_RFND_DAY__MAP Mapping

Populate target table DWD_ACCT_RFND_DAY. For more information, see ACCOUNT REFUND DAY DRVD.

Table 7-5 DWD_ACCT_RFND_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_PYMT
DWB_ACCT_RFND
DWB_INVC
DWB_INVC_ADJ
DWB_INVC_ITEM
DWR_ADDR_LOC
DWR_CUST
DWR_DAY

DWD_ACCT_STAT__MAP Mapping

Populate target table DWD_ACCT_STAT. For more information, see ACCOUNT STATUS DRVD.

Table 7-6 DWD_ACCT_STAT__MAP ETL Mapping

Source Table Name
DWB_ACCT_RFND
DWB_EVT_ACCT
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CUST
DWR_PRTY_LYLTY_PROG_PRTCPTN
DWR_SBRP

DWD_ACCT_STTSTC__MAP Mapping

Populate target table DWD_ACCT_STTSTC. For more information, see ACCOUNT STATISTIC DRVD.

Table 7-7 DWD_ACCT_STTSTC__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_CRDT_LMT
DWB_ACCT_PYMT
DWB_ACCT_STAT_HIST
DWB_EVT_LYLTY_PROG
DWB_PRTY_INTRACN_THRD
DWL_AGE_BND
DWL_AGE_ON_NET_BND
DWL_ARPU_BAND
DWL_CHRN_RSN
DWL_CUST_RVN_BND
DWL_DEBT_AGNG_BND
DWR_ACCT
DWR_ACCT_1
DWR_ACCT_PREF_PYMT_MTHD
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CNRT
DWR_CUST
DWR_DAY
DWR_PROD
DWR_PROD_MKT_PLN
DWR_PRTY
DWR_PRTY_LYLTY_PROG_PRTCPTN
DWR_SBRP
DWR_SL_CHNL_RPRSTV

DWD_ARPU_BASE__MAP Mapping

Populate target table DWD_ARPU_BASE. For more information, see ARPU BASE DRVD.

Table 7-8 DWD_ARPU_BASE__MAP ETL Mapping

Source Table Name
DWB_ACCT_COST
DWB_INVC
DWB_INVC_ITEM
DWB_PROD_COST
DWB_SL_CMISN_DTL
DWD_VOI_CALL_DAY
DWL_RECHRG_RVN_SLB
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CUST
DWR_SBRP
DWR_SL_CHNL_RPRSTV

DWD_CALL_CNTR_CALL_DAY__MAP Mapping

Populate target table DWD_CALL_CNTR_CALL_DAY. For more information, see CALL CENTER CALL DAY DRVD.

Table 7-9 DWD_CALL_CNTR_CALL_DAY__MAP ETL Mapping

Source Table Name
DWB_EVT_PRTY_INTRACN_CALL
DWR_ACCT
DWR_DAY
DWR_TIME_SLT

DWD_CALL_CNTR_CASE_DAY__MAP Mapping

Populate target table DWD_CALL_CNTR_CASE_DAY. For more information, see CALL CENTER CASE DAY DRVD.

Table 7-10 DWD_CALL_CNTR_CASE_DAY__MAP ETL Mapping

Source Table Name
DWB_PRTY_INTRACN_THRD
DWR_CUST
DWR_DAY

DWD_CANBLZTN_DTL_DAY__MAP Mapping

Populate target table DWD_CANBLZTN_DTL_DAY. For more information, see CANNIBALIZATION DETAIL DAY DRVD.

Table 7-11 DWD_CANBLZTN_DTL_DAY__MAP ETL Mapping

Source Table Name
DWR_CHNL
DWR_CNRT_ASGN
DWR_CNRT_NEW
DWR_CNRT_OLD
DWR_DAY
DWR_PROD_MKT_PLN1
DWR_PROD_MKT_PLN2

DWD_CMISN_DAY__MAP Mapping

Populate target table DWD_CMISN_DAY. For more information, see COMMISSION DAY DRVD.

Table 7-12 DWD_CMISN_DAY__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWB_INVC_ITEM
DWB_SL_CMISN_DTL
DWR_CNRT
DWR_DAY
DWR_SBRP

DWD_CNCT_DSCNCT_DAY__MAP Mapping

Populate target table DWD_CNCT_DSCNCT_DAY. For more information, see CONNECT DISCONNECT DAY DRVD.

Contains connect and disconnect information of particular subscriber.

Table 7-13 DWD_CNCT_DSCNCT_DAY__MAP ETL Mapping

Source Table Name
DWB_EVT
DWB_EVT_ACCS_MTHD_ACTVTY
DWR_ACCS_MTHD
DWR_ADDR_LOC
DWR_DAY
DWR_EVT_LOC

DWD_CNRT__MAP Mapping

Populate target table DWD_CNRT. For more information, see CONTRACT DRVD.

Table 7-14 DWD_CNRT__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWB_INVC
DWB_INVC_ITEM
DWD_CNRT_CHNG
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CMPGN
DWR_CNRT
DWR_CUST
DWR_PRMTN
DWR_SBRP

DWD_CNRT_CHNG__MAP Mapping

Populate target table DWD_CNRT_CHNG. For more information, see CONTRACT CHANGED DRVD.

Table 7-15 DWD_CNRT_CHNG__MAP ETL Mapping

Source Table Name
DWB_CNRT_STAT
DWB_CNRT_TERM_VAL
DWB_CNRT_TERM_VAL_NEW
DWB_CNRT_TERM_VAL_OLD
DWR_BSNS_MO
DWR_CNRT
DWR_CNRT_ASGN
DWR_CNRT_NEW
DWR_CNRT_OLD
DWR_DAY
DWR_DAY_1
DWR_DAY_OLD_END_DAY
DWR_DAY_OLD_STRT_DAY

DWD_COST_CUST__MAP Mapping

Populate target table DWD_COST_CUST. For more information, see COST CUSTOMER DRVD.

Table 7-16 DWD_COST_CUST__MAP ETL Mapping

Source Table Name
DWB_CUST_COST
DWR_BSNS_MO
DWR_CUST
DWR_SL_CHNL_RPRSTV

DWD_COST_ORG__Map Mapping

Populate target table DWD_COST_ORG. For more information, see COST ORGANIZATIONAL DRVD.

Table 7-17 DWD_COST_ORG__MAP ETL Mapping

Source Table Name
DWB_BSNS_UNIT_COST
DWR_BSNS_MO
DWR_ORG_BSNS_UNIT

DWD_CRDT_CTGRY__MAP Mapping

Populate target table DWD_CRDT_CTGRY. For more information, see CREDIT CATEGORY DRVD.

Table 7-18 DWD_CRDT_CTGRY__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWL_AGE_ON_NET_BND
DWR_BSNS_MO
DWR_CNRT
DWR_SBRP

DWD_CUST_ACQSTN_SUMM_DAY__MAP Mapping

Populate target table DWD_CUST_ACQSTN_SUMM_DAY. For more information, see CUSTOMER ACQUISITION SUMMARY DAY DRVD.

Table 7-19 DWD_CUST_ACQSTN_SUMM_DAY__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWB_EVT_SBRP
DWR_ADDR_LOC
DWR_CNRT
DWR_CUST
DWR_DAY
DWR_PRMTN
DWR_SBRP
DWR_SBRP_1
DWR_SL_CHNL_RPRSTV

DWD_CUST_DEBT_COLLCTN__MAP Mapping

Populate target table DWD_CUST_DEBT_COLLCTN. For more information, see CUSTOMER DEBT COLLECTION DRVD.

Table 7-20 DWD_CUST_DEBT_COLLCTN__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_PYMT
DWB_INVC
DWR_ACCT
DWR_ADDR_LOC
DWR_CUST
DWR_DAY
DWR_PRTY_ASGN
DWR_SL_CHNL_RPRSTV

DWD_EXTRNL_DEBT_COLLCTN_DAY__MAP Mapping

Populate target table DWD_EXTRNL_DEBT_COLLCTN_DAY. For more information, see EXTERNAL DEBT COLLECTION DAY DRVD.

Table 7-21 DWD_EXTRNL_DEBT_COLLCTN_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_ADJ
DWB_ACCT_BAL_HIST
DWB_ACCT_PYMT
DWB_DEBT_COLLCTN_ASGN
DWR_ACCT
DWR_DAY

DWD_GIVE_AWAY_ITEM_DAY__MAP Mapping

Populate target table DWD_GIVE_AWAY_ITEM_DAY. For more information see GIVE AWAY ITEM DAY DRVD.

Table 7-22 DWD_GIVE_AWAY_ITEM_DAY__MAP ETL Mapping

Source Table Name
DWB_EVT_LYLTY_PROG
DWB_EVT_LYLTY_PROG_RDMPTN
DWR_CNRT
DWR_DAY
DWR_PROD_MKT_PLN
DWR_PROD_RTNG_PLN_DTL

DWD_HNDST_SUBSDY_DAY__MAP Mapping

Populate target table DWD_HNDST_SUBSDY_DAY. For more information, see HANDSET SUBSIDY DAY DRVD.

Table 7-23 DWD_HNDST_SUBSDY_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWB_CUST_ORDR
DWB_CUST_ORDR_LN_ITEM
DWB_CUST_ORDR_PYMT
DWB_EVT_GFT_RDMPTN
DWB_EVT_LYLTY_PROG_RDMPTN
DWL_AGE_ON_NET_BND
DWR_CNRT
DWR_CUST
DWR_DAY
DWR_HNDST_MDL
DWR_ITEM
DWR_ORG_ITEM_SLNG_PRICE
DWR_PROD
DWR_PROD_MKT_PLN
DWR_SBRP

DWD_INTRNL_DEBT_COLLCTN_DAY__MAP Mapping

Populate target table DWD_INTRNL_DEBT_COLLCTN_DAY. For more information, see INTERNAL DEBT COLLECTION DAY DRVD.

Table 7-24 DWD_INTRNL_DEBT_COLLCTN_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_ADJ
DWB_ACCT_BAL_HIST
DWB_ACCT_PYMT
DWB_DEBT_COLLCTN
DWR_ACCT
DWR_DAY

DWD_INVC__MAP Mapping

Populate target table DWD_INVC. For more information, see INVOICE DRVD.

Table 7-25 DWD_INVC__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWB_INVC
DWB_INVC_ADJ
DWB_INVC_ITEM
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CNRT
DWR_CUST
DWR_SBRP

DWD_INVC_ADJ__MAP Mapping

Populate target table DWD_INVC_ADJ. For more information, see INVOICE ADJUSTMENT DRVD.

Table 7-26 DWD_INVC_ADJ__MAP ETL Mapping

Source Table Name
DWB_INVC
DWB_INVC_ADJ
DWB_INVC_ITEM
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CUST
DWR_SBRP

DWD_LN_ACTVTN_TMNT_DAY__MAP Mapping

Populate target table DWD_LN_ACTVTN_TMNT_DAY. For more information, see LINE ACTIVATION TERMINATION DAY DRVD.

Table 7-27 DWD_LN_ACTVTN_TMNT_DAY__MAP ETL Mapping

Source Table Name
DWB_EVT_ACCT
DWR_ACCT
DWR_CMPGN
DWR_CNRT
DWR_DAY
DWR_PRMTN
DWR_SBRP
DWR_SL_CHNL_RPRSTV

DWD_LYLTY_PROG_DAY__MAP Mapping

Populate target table DWD_LYLTY_PROG_DAY. For more information, see LOYALTY PROGRAM DAY DRVD.

Table 7-28 DWD_LYLTY_PROG_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWB_EVT
DWB_EVT_LYLTY_PROG
DWB_LYLTY_PROG_PTS_BAL
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_CNRT
DWR_CUST
DWR_DAY
DWR_SBRP

DWD_MKT_SHARE__MAP Mapping

Populate target table DWD_MKT_SHARE. For more information, see MARKET SHARE DRVD.

Table 7-29 DWD_MKT_SHARE__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CNRT
DWR_CUST
DWR_PROD
DWR_SBRP

DWD_MKT_OPRTR_PRTNG__MAP Mapping

Populate target table DWD_MKT_OPRTR_PRTNG. For more information, see MARKET OPERATOR PORTING DERIVED.

The summary information about succeeded Number Porting between operators.

Table 7-30 DWD_MKT_OPRTR_PRTNG__MAP ETL Mapping

Source Table Name
DWB_ACCS_MTHD_PORT_HIST
DWR_BSNS_MO

DWD_PRPD_ACCT_STTSTC__MAP Mapping

Populate target table DWD_PRPD_ACCT_STTSTC. For more information, see PREPAID ACCOUNT STATISTIC DRVD.

Table 7-31 DWD_PRPD_ACCT_STTSTC__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_HIST
DWB_EVT_PRTY_INTRACN
DWB_PRPD_RCHRG
DWL_AGE_ON_NET_BND
DWL_INTRACN_RSN
DWL_RECHRG_RVN_SLB
DWR_ACCT
DWR_BSNS_MO
DWR_CNRT
DWR_PROD_MKT_PLN

DWD_PRPD_ALWNCE_DAY__MAP Mapping

Populate target table DWD_PRPD_ALWNCE_DAY. For more information, see PREPAID ALLOWANCE DAY DRVD.

Table 7-32 DWD_PRPD_ALWNCE_DAY__MAP Mapping

Source Table Name
DWB_MNT_ALLWNC
DWR_DAY
DWR_SL_CHNL_RPRSTV

DWD_PRPD_CALL_SUMM_DAY__MAP Mapping

Populate target table DWD_PRPD_CALL_SUMM_DAY. For more information, see PREPAID CALL SUMMARY DAY DRVD.

Table 7-33 DWD_PRPD_CALL_SUMM_DAY__MAP ETL Mapping

Source Table Name
DWB_WRLS_CALL_EVT
DWL_PK_OFPK_TIME
DWR_ACCT
DWR_CUST
DWR_DAY
DWR_PROD_MKT_PLN

DWD_PYMT_AGNG_DAY__MAP Mapping

Populate target table DWD_PYMT_AGNG_DAY. For more information, see PAYMENT AGING DAY DRVD.

Table 7-34 DWD_PYMT_AGNG_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_PYMT
DWB_INV_PYMT_ASGN
DWB_INVC
DWL_AGE_ON_NET_BND
DWL_DEBT_AGNG_BND
DWR_ACCT
DWR_CUST
DWR_DAY
DWR_PRTY_ASGN
DWR_SL_CHNL_RPRSTV

DWD_RDMPTN_DAY__MAP Mapping

Populate target table DWD_RDMPTN_DAY. For more information, see REDEMPTION DAY DRVD.

Table 7-35 DWD_RDMPTN_DAY__MAP ETL Mapping

Source Table Name
DWB_ACCT_CRDT_LMT
DWB_EVT
DWB_EVT_LYLTY_PROG
DWB_EVT_LYLTY_PROG_RDMPTN
DWB_EVT_PRTY_ASGN
DWB_EVT_PRTY_INTRACN
DWL_AGE_ON_NET_BND
DWR_ACCT
DWR_CNRT
DWR_DAY
DWR_EVT_PRTY_RL

DWD_SBCRBR_CHRN_STTSTC__MAP Mapping

Populate target table DWD_SBCRBR_CHRN_STTSTC. For more information, see SUBSCRIBER CHURN STATISTIC DRVD.

Table 7-36 DWD_SBCRBR_CHRN_STTSTC__MAP

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_CRDT_LMT
DWB_ACCT_PYMT
DWB_EVT_LYLTY_PROG
DWB_PRTY_INTRACN_THRD
DWL_AGE_BND
DWL_AGE_ON_NET_BND
DWL_ARPU_BAND
DWL_CUST_RVN_BND
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CNRT
DWR_CUST
DWR_DAY
DWR_HNDST_INSTNC
DWR_PROD_MKT_PLN
DWR_PRTY
DWR_PRTY_LYLTY_PROG_PRTCPTN
DWR_SBRP
DWR_SBRP_ASGN
DWR_SL_CHNL_RPRSTV

DWD_SHOP_EFFNCY_DAY__MAP Mapping

Populate target table DWD_SHOP_EFFNCY_DAY. For more information, see SHOP EFFICIENCY DAY DRVD.

Table 7-37 DWD_SHOP_EFFNCY_DAY__MAP

Source Table Name
DWB_EVT
DWB_EVT_ASGN
DWB_EVT_PRTY_INTRACN_VST
DWR_ADDR_LOC
DWR_DAY
DWR_ORG_BSNS_UNIT

DWD_SHRD_PKG_USG_STTSTC_DAY__MAP Mapping

Populate target table DWD_SHRD_PKG_USG_STTSTC_DAY. For more information, see SHARED PACKAGE USAGE STATISTICS DAY DRVD.

Table 7-38 DWD_SHRD_PKG_USG_STTSTC_DAY__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWR_ACCT
DWR_CNRT
DWR_DAY
DWR_PROD_CAPBLTY
DWR_PROD_CAPBLTY_VAL
DWR_PROD_MKT_PLN

DWD_SL_DAY__MAP ETL Mapping

Populate target table DWD_SL_DAY. For more information, see SALES DAY DRVD.

Table 7-39 DWD_SHOP_EFFNCY_DAY__MAP

Source Table Name
DWB_CUST_ORDR
DWB_CUST_ORDR_LN_ITEM
DWB_CUST_ORDR_PYMT
DWB_PRTY_ORDR_ASGN
DWR_CNRT
DWR_DAY

DWD_SL_RPRSTV_STTSTC_MO__MAP Mapping

Populate target table DWD_SL_RPRSTV_STTSTC_MO. For more information, see SALES REPRESENTATIVE STATISTICS DRVD.

Table 7-40 DWD_SL_RPRSTV_STTSTC_MO__MAP ETL Mapping

Source Table Name
DWB_CNRT_TERM_VAL
DWB_EVT
DWB_EVT_EQPMNT_INSTNC
DWB_SL_CMISN_DTL
DWR_BSNS_MO
DWR_CNRT
DWR_SBRP
DWR_SL_CHNL_RPRSTV
DWR_SL_CMISN_PLN_DTL

DWD_SPLMNTR_SRVC_USG_MAP Mapping

Populate the table DWD_SPLMNTR_SRVC_USG. For more information, see SUPPLEMENTARY SERVICE USAGE DRVD.

Table 7-41 DWD_SPLMNTR_SRVC_USG__MAP ETL Mapping

Source Table Name
DWB_INVC
DWB_INVC_ITEM
DWB_NTWK_EVT
DWR_BSNS_MO
DWR_SPLMNTR_SRVC

DWD_SUBSDY_AMT__MAP Mapping

Populate the table DWD_SUBSDY_AMT. For more information, see SUBSIDY AMOUNT DRVD.

Table 7-42 DWD_SUBSDY_AMT__MAP ETL Mapping

Source Table Name
DWR_BSNS_MO
DWR_CNRT
DWR_CUST
DWR_PROD
DWR_PROD_MKT_PLN_ASGN

DWD_VAS_SBRP_QCK_SUMM__MAP Mapping

Populate target table DWD_VAS_SBRP_QCK_SUMM. For more information, see VAS SUBSCRIPTION QUICK SUMMARY DRVD.

Table 7-43 DWD_VAS_SBRP_QCK_SUMM__MAP ETL Mapping

Source Table Name
DWR_BSNS_MO
DWR_CUST
DWR_PROD
DWR_SBRP

Intra-ETL PL/SQL Mapping Source and Target Tables

Shows the PL/SQL mapping to populate derived tables.

DWD_DATA_USG_DAY Mapping

Populate target table DWD_DATA_USG_DAY. For more information, see DATA USAGE DAY DRVD.

Table 7-44 DWD_DATA_USG_DAY ETL Mapping

Source Table Name
DWB_CNTNT_DLVRY_EVT
DWB_NTWK_EVT
DWB_WRLS_CNTNT_DNLDG_EVT
DWC_INTRA_ETL_ACTIVITY
DWR_ACCS_MTHD
DWR_CNTNT
DWR_CUST
DWR_DAY
DWR_TIME_SLT

DWD_VAS_USG_DAY Mapping

Populate target table DWD_VAS_USG_DAY. For more information, see VAS USAGE DAY DRVD.

Table 7-45 DWD_VAS_USG_DAY ETL Mapping

Source Table Name
DWB_ISP_USG_EVT
DWB_NTWK_EVT
DWB_UMS_EVT
DWB_WRLS_CALL_EVT
DWC_INTRA_ETL_ACTIVITY
DWL_PK_OFPK_TIME
DWR_CUST
DWR_DAY
DWR_MAILBOX
DWR_TIME_SLT
DWR_VAL_ADD_SRVC

DWD_VOI_CALL_DAY Mapping

Populate target table DWD_VOI_CALL_DAY. For more information, see VOICE CALL DAY DRVD.

Table 7-46 DWD_VOI_CALL_DAY ETL Mapping

Source Table Name
DWB_CRNCY_EXCHNG_RATE
DWB_MDTD_CALL_EVT
DWB_WRLS_CALL_EVT
DWC_INTRA_ETL_ACTIVITY
DWL_CALL_TYP
DWL_PK_OFPK_TIME
DWR_ADDR_LOC
DWR_CUST
DWR_DAY
DWR_ORG_BSNS_UNIT
DWR_TIME_SLT

Intra-ETL Process Flows

The INTRA_ETL_FLW is the complete Intra-ETL process designed using Oracle Warehouse Builder, and is composed of individual sub-process flows to populate derived aggregate tables, and relational materialized views where the data originates from base, reference, and lookup tables. This process flow respects the dependency of each individual program.

Figure 7-1 shows the main process flow INTRA_ETL_FLW.

Figure 7-1 Intra-ETL Main Process Flow

Description of "Figure 7-1 Intra-ETL Main Process Flow"

The process flow INTRA_ETL_FLW is initialized from START_PROCESS, and this checks if any previous process flows are running. If any process is running then START_PROCESS jumps to END_ERROR or START_PROCESS generate the process number from the sequence. This process number is sent as input to the Derived Flow.

In the DRVD_FLW when the START process is initiated this generates the process number and is sent as input to the Derived mapping. Once the number is generated it updates the status at backend (Control Tables). If derived mapping is successful then the derived mapping checks the status in control tables.

Details of the DRVD_FLW Intra-ETL Flow

The DRVD_FLW sub-process flow contains all the Oracle Warehouse Builder mappings for populating derived tables, based on the content of the base, reference, and lookup tables. This sub-process flow has a dependency on the AGGR_FLW. If the DRVD_FLW is successful then it navigates to AGGR_FLW otherwise the process ends.

Figure 7-2 shows the DRVD_FLW sub-process flow for populating the derived tables.

Figure 7-2 Intra-ETL Derived Flow Sub-process (DRVD_FLW)

Description of "Figure 7-2 Intra-ETL Derived Flow Sub-process (DRVD_FLW) "

After the DRVD_FLW starts successfully, it moves to the fork. The sub-process FORK performs the derived mappings (these run in parallel). Once the activity is started then Start_Activity inserts one record in the control table and the state is set to 'Running'. The End_Activity updates the status in control tables (the state mapping is COMPLETED-SUCCESS or COMPLETED-ERROR) in the control tables. The AND activity specifies whether all the parallel mappings have been completed or not and then switches to the next activity, for example END_SUCCESS. This DRVD_FLW depends on the AGGR_FLW sub-process flow.

Details of the AGGR_FLW Intra-ETL Flow

The AGGR_FLW sub-process flow contains PL/SQL code using Partitions Change Tracking Strategy for refreshing all the aggregate tables which are Materialized Views in Oracle Communications Data Model.

Figure 7-3 shows the AGGR_FLW sub-process flow for refreshing all the aggregate tables.

Figure 7-3 Intra-ETL Aggregate Flow Sub-process (AGGR_FLW)

Description of "Figure 7-3 Intra-ETL Aggregate Flow Sub-process (AGGR_FLW) "

After the AGGR_FLW is initiated and started successfully it is moved to the Fork. The FORK process makes the aggregates to run in parallel. The AND activity specifies that all the parallel aggregates have been completed or not and then switches over to the next activity, (for example, END_SUCCESS).

Details of the OLAP_FLW Intra-ETL Flow

The OLAP_FLW sub-process flow triggers the OLAP package which can load data from Oracle Communications Data Model aggregate tables to Oracle Communications Data Model Analytical Workspace and calculate the forecast data. It reads OLAP ETL parameters from DWC_OLAP_ETL_PARAMETER table.

Figure 7-4 shows the OLAP_FLW sub-process flow that triggers the OLAP packages.

Figure 7-4 Intra-ETL OLAP Flow Sub-process (OLAP_FLW)

Description of "Figure 7-4 Intra-ETL OLAP Flow Sub-process (OLAP_FLW) "

Details of the MINING_FLW Intra-ETL Flow

The MINING_FLW sub process flow triggers the data mining model.

Figure 7-5 shows the MINING_FLW sub-process flow.

Figure 7-5 Intra-ETL Mining Flow Sub-process (MINING_FLW)

Description of "Figure 7-5 Intra-ETL Mining Flow Sub-process (MINING_FLW) "

Executing the Intra-ETL

One component of Oracle Communications Data Model is the INTRA_ETL_FLW process flow which is a complete Intra-ETL process composed of sub process flows to populate the derived and aggregate tables and relational materialized views with the data from the base, reference, and lookup tables. This process flow respects the dependency of each individual program. The INTRA_ETL_FLW process flow executes the programs in the proper order.

You can execute the Intra ETL mappings and packages by executing the INTRA_ETL_FLW from Oracle Warehouse Builder.

The INTRA_ETL_FLW is the complete Intra ETL process designed using Oracle Warehouse Builder, and is composed of individual sub-process flows:

DRVD_FLW - This sub-process flow contains all the OWB mappings for populating derived tables based on the content of the base, reference, and lookup tables.
AGGR_FLW - This sub-process flow contains PL/SQL code using Partitions Change Tracking Strategy for refreshing all the aggregate tables which are Materialized Views in our OCDM
OLAP_FLW - The OLAP sub-process flow triggers the OLAP package which can load data from OCDM aggregate tables to OCDM Analytical Workspace and calculate the forecast data. It reads OLAP ETL parameters from DWC_OLAP_ETL_PARAMETER table
MINING_FLW - This sub process flow triggers the data mining model.

Note:

The INTRA_ETL_FLW process flow provided with Oracle Communications Data Model assume that there is no data in the derived tables and aggregate tables and views. Typically, you perform this type of load only when you first create your data warehouse. Later, you need to add additional data to the tables and refresh your views.

The AGGR_FLW sub process flow is executed only after the successful execution of all the mappings in the DRVD_FLW. If there is an error in any individual mapping in the derived process flow (DRVD_FLW), the execution of the Aggregate Process flow (AGGR_FLW) is skipped and only after a successful refreshing of all materialized views in AGGR_FLW process flow MINING_FLW can be executed.

Using the Intra-ETL involves the following tasks:

Executing the Intra-ETL for Oracle Communications Data Model
Monitoring the Execution of the Intra-ETL Process
Recovering an Intra_ETL Process

Monitoring the Execution of the Intra-ETL Process

Two control tables, DWC_INTRA_ETL_PROCESS and DWC_INTRA_ETL_ACTIVITY, monitor the execution of the Intra-ETL process. For more information on these tables, see Table A-3 and Table A-4.

Each normal run, as opposed to an error-recovery run, of a separate Intra-ETL execution performs the following steps:

Inserts a record into table DWC_INTRA_ETL_PROCESS with a monotonically increasing system generated unique process key, SYSDATE as process start time, RUNNING as the process status, input date range in the fields FROM_DATE_ETL and TO_DATE_ETL.
Invokes each of the individual Intra-ETL programs in the appropriate order of dependency. Before the invocation of each program, the procedure inserts a record into the Intra-ETL Activity detail table DWC_INTRA_ETL_ACTIVITY with a system generated unique activity key, the process key value corresponding to the Intra-ETL process, individual program name as the Activity Name, a suitable activity description, SYSDATE as activity start time, RUNNING as the activity status.
Updates the corresponding record in the DWC_INTRA_ETL_ACTIVITY table for the activity end time and activity status after the completion of each individual ETL program (either successfully or with errors. For successful completion of the activity, the procedure updates the status as COMPLETED-SUCCESS. If an error occurs, the procedure updates the activity status as 'COMPLETED-ERROR', and also updates the corresponding error detail in the ERROR_DTL column.
Updates the record corresponding to the process in the DWC_INTRA_ETL_ PROCESS table for the process end time and status, after the completion of all individual intra-ETL programs. If all the individual programs succeed, the procedure updates the status to 'COMPLETED-SUCCESS', otherwise it updates the status to 'COMPLETED-ERROR'. You can monitor the execution state of the Intra-ETL, including current process progress, time taken by individual programs, or the complete process, by viewing the contents of the DWC_INTRA_ETL_PROCESS and DWC_INTRA_ETL_ACTIVITY tables corresponding to the maximum process key. Monitoring can be done both during and after the execution of the Intra-ETL procedure.