Skip Headers
Oracle® Communications Data Model Reference
11g Release 2 (11.2)

Part Number E15886-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

7 Oracle Communications Data Model Intra-ETL

This chapter includes the following sections:

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 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_DTL

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

 

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

 

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_STSC_DAY

Hardcoded

39

DWR_PROD_CAPBLTY

PROD_CAPBLTY_CD

NBRLNS

DWD_SHRD_PKG_USG_STSC_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 MONTH 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 MONTH 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_STSC_DAY__MAP Mapping

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

Table 7-38 DWD_SHRD_PKG_USG_STSC_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 MONTH 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 follows
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 follows
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 follows
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 follows
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 follows
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:

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:

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:

  1. 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.

  2. 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.

  3. 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.

  4. 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.