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 Source and Target Tables
Intra-ETL PL/SQL Mapping Packages for Source and Target Tables
Intra-ETL Process Flows

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:

Changes to intra-ETL cannot be supported. But it is expected that if the business needs require a change in the business logic of the intra-ETLs, some customer adaptations could be necessary even if they are not be 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 Oracle Communications Data Model Implementation and 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 Source and Target Tables

Shows the packages to populate the derived tables. The naming convention by default is the physical name of the target table plus,"_PKG"

DWD_ACCT_DEBT_DAY_PKG Package

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

Table 7-2 DWD_ACCT_DEBT_DAY_PKG Package

Source Table Name
DWB_ACCT_BAL_HIST
DWB_ACCT_BAL_ADJ
DWB_ACCT_PYMT
DWL_DEBT_AGNG_BND
DWR_ACCT
DWR_ADDR_LOC
DWR_CUST
DWR_DAY

DWD_ACCT_PYMT_DAY_PKG Package

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

Table 7-3 DWD_ACCT_PYMT_DAY_PKG Package

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_PKG Package

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_PKG Package

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_PKG Package

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

Table 7-5 DWD_ACCT_RFND_DAY_PKG Package

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_PKG Package

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

Table 7-6 DWD_ACCT_STAT_PKG Package

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_ARPU_BASE_PKG Package

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

Table 7-7 DWD_ARPU_BASE_PKG Package

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_PKG Package

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

Table 7-8 DWD_CALL_CNTR_CALL_DAY_PKG Package

Source Table Name
DWB_EVT_PRTY_INTRACN_CALL
DWR_ACCT
DWR_DAY
DWR_TIME_SLT

DWD_CALL_CNTR_CASE_DAY_PKG Package

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

Table 7-9 DWD_CALL_CNTR_CASE_DAY_PKG Package

Source Table Name
DWB_PRTY_INTRACN_THRD
DWR_CUST
DWR_DAY

DWD_CANBLZTN_DTL_DAY_PKG Package

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

Table 7-10 DWD_CANBLZTN_DTL_DAY_PKG Package

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_PKG Package

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

Table 7-11 DWD_CMISN_DAY_PKG Package

Source Table Name
DWB_CNRT_TERM_VAL
DWB_INVC_ITEM
DWB_SL_CMISN_DTL
DWR_CNRT
DWR_DAY
DWR_SBRP

DWD_CNCT_DSCNCT_DAY_PKG Package

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-12 DWD_CNCT_DSCNCT_DAY_PKG Package

Source Table Name
DWB_EVT
DWB_EVT_ACCS_MTHD_ACTVTY
DWR_ACCS_MTHD
DWR_ADDR_LOC
DWR_DAY
DWR_EVT_LOC

DWD_CNRT_PKG Package

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

Table 7-13 DWD_CNRT_PKG Package

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_PKG Package

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

Table 7-14 DWD_CNRT_CHNG_PKG Package

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_PKG Package

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

Table 7-15 DWD_COST_CUST_PKG Package

Source Table Name
DWB_CUST_COST
DWR_BSNS_MO
DWR_CUST
DWR_SL_CHNL_RPRSTV

DWD_COST_ORG_PKG Package

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

Table 7-16 DWD_COST_ORG_PKG Package

Source Table Name
DWB_BSNS_UNIT_COST
DWR_BSNS_MO
DWR_ORG_BSNS_UNIT

DWD_CRDT_CTGRY_PKG Package

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

Table 7-17 DWD_CRDT_CTGRY_PKG Package

Source Table Name
DWB_ACCT_CRDT_LMT
DWL_AGE_ON_NET_BND
DWR_BSNS_MO
DWR_CNRT
DWR_SBRP

DWD_CUST_ACQSTN_SUMM_DAY_PKG Package

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

Table 7-18 DWD_CUST_ACQSTN_SUMM_DAY_PKG Package

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_EXTRNL_DEBT_COLLCTN_DAY_PKG Package

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

Table 7-19 DWD_EXTRNL_DEBT_COLLCTN_DAY_PKG Package

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_PKG Package

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

Table 7-20 DWD_GIVE_AWAY_ITEM_DAY_PKG Package

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_INTRNL_DEBT_COLLCTN_DAY_PKG Package

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

Table 7-21 DWD_INTRNL_DEBT_COLLCTN_DAY_PKG Package

Source Table Name
DWB_ACCT_BAL_ADJ
DWB_ACCT_BAL_HIST
DWB_ACCT_PYMT
DWB_DEBT_COLLCTN
DWR_ACCT
DWR_DAY

DWD_INVC_PKG Package

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

Table 7-22 DWD_INVC_PKG Package

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_PKG Package

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

Table 7-23 DWD_INVC_ADJ_PKG Package

Source Table Name
DWB_INVC
DWB_INVC_ADJ
DWB_INVC_ITEM
DWR_ACCT
DWR_ADDR_LOC
DWR_BSNS_MO
DWR_CUST
DWR_SBRP

DWD_LYLTY_PROG_DAY_PKG Package

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

Table 7-24 DWD_LYLTY_PROG_DAY_PKG Package

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_OPRTR_PRTNG_PKG Package

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-25 DWD_MKT_OPRTR_PRTNG_PKG Package

Source Table Name
DWB_ACCS_MTHD_PORT_HIST
DWR_BSNS_MO

DWD_PRPD_ACCT_STTSTC_PKG Package

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

Table 7-26 DWD_PRPD_ACCT_STTSTC_PKG Package

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_PKG Package

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

Table 7-27 DWD_PRPD_ALWNCE_DAY_PKG Package

Source Table Name
DWB_MNT_ALLWNC
DWR_DAY
DWR_SL_CHNL_RPRSTV

DWD_PYMT_AGNG_DAY_PKG Package

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

Table 7-28 DWD_PYMT_AGNG_DAY_PKG Package

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_PKG Package

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

Table 7-29 DWD_RDMPTN_DAY_PKG Package

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_SHOP_EFFNCY_DAY_PKG Package

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

Table 7-30 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_SL_DAY_PKG Package

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

Table 7-31 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_PKG Package

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

Table 7-32 DWD_SL_RPRSTV_STTSTC_MO_PKG Package

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-33 DWD_SPLMNTR_SRVC_USG_PKG Package

Source Table Name
DWB_INVC
DWB_INVC_ITEM
DWB_NTWK_EVT
DWR_BSNS_MO
DWR_SPLMNTR_SRVC

DWD_VAS_SBRP_QCK_SUMM_PKG Package

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

Table 7-34 DWD_VAS_SBRP_QCK_SUMM_PKG Package

Source Table Name
DWR_BSNS_MO
DWR_CUST
DWR_PROD
DWR_SBRP

Intra-ETL PL/SQL Mapping Packages for Source and Target Tables

Shows the PL/SQL mapping to populate derived tables.

DWD_DATA_USG_DAY_PKG Package

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

Table 7-35 DWD_DATA_USG_DAY_PKG Package

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_PKG Package

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

Table 7-36 DWD_VAS_USG_DAY_PKG Package

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_PKG Package

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

Table 7-37 DWD_VOI_CALL_DAY_PKG Package

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, DWC_INTRA_ETL_ACTIVITY, 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_MAP Intra-ETL Flow

The OLAP_MAP 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_MAP sub-process flow that triggers the OLAP packages.

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

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

Details of the MINING_FLW Intra-ETL Flow

The MINING_FLW sub process flow triggers the data mining model.

The window of data is decided by following variables:

churn_interval_months
ltv_age_interval_years
dwc_etl_parameter.to_date_etl

The dwc_etl_parameter.to_date_etl limits the end date for data and first two parameters limit the start date of data for the churn models and life time value models respectively.

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)"