10 Billing Analytics Reference

This chapter provides information on Billing Analytics tables, relationships, and measures.

Billing Analytics Physical Objects

Table 10-1 shows the Oracle Communications Data Model physical objects associated with the Billing Analytics reports.

Table 10-1 Physical Objects for Billing Analytics

Oracle Communications Data Model Objects for Billing Analytics

DWA_CNT_MO

DWA_RVN_MO

DWB_INVC_ITEM

DWD_ACCT_DEBT_MO

DWD_CNT_DAY

DWD_DATA_USG_DAY

DWD_PRPD_ACCT_STTSTC_DAY

DWD_TMF_KPI

DWD_VAS_USG_DAY

DWD_VOI_CALL_DAY

DWL_CALL_TYP

DWL_CUST_TYP

DWL_INVC_ADJ_RSN

DWL_INVC_ADJ_TYP

DWL_INVC_DISC_TYP

DWL_PRTY_TYP

DWL_RMNG_TYP

DWR_ACCT

DWR_CNTNT

DWR_COLLCTN_AGNCY

DWR_CUST

DWR_DAY

DWR_GEO_CITY

DWR_GEO_CNTRY

DWR_GEO_CNTY

DWR_GEO_STATE

DWR_ORG_BSNS_UNIT

DWR_PROD_OFR

DWR_PROD_SPEC

DWV_PRPD_CUST_STAT (Oracle Business Intelligence Enterprise Edition View)


Billing Analytics Relationships and Measures

DWB_INVC_ITEM Analytics

Table 10-2 DWB_INVC_ITEM Analytics

Component Description

Base Table

Not Applicable

Derived Table

DWB_INVC_ITEM

Intra-ETL

Not required because this is a base table


Figure 10-1 DWB_INVC_ITEM Relationships

Description of Figure 10-1 follows
Description of ''Figure 10-1 DWB_INVC_ITEM Relationships''

Table 10-3 DWB_INVC_ITEM Relationships

Relationship

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_SPEC"."PROD_SPEC_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWB_INVC_ITEM"."PROD_SPEC_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_CUST"."CUST_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWB_INVC_ITEM"."CUST_KEY"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."CLNDR_DT" = cast("Billing Analytics".""."OCDM_BILLING"."DWB_INVC_ITEM"."BLLG_DT" as date)

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DT" = cast("Billing Analytics".""."OCDM_BILLING"."DWB_INVC_ITEM"."BLLG_DT" as date)

"Billing Analytics".""."OCDM_BILLING"."DWL_INVC_ADJ_RSN"."INVC_ADJ_RSN_CD" = "Billing Analytics".""."OCDM_BILLING"."DWB_INVC_ITEM"."INVC_ADJ_RSN_CD"

"Billing Analytics".""."OCDM_BILLING"."DWL_INVC_ADJ_TYP"."INVC_ADJ_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWB_INVC_ITEM"."INVC_ADJ_TYP_CD"

"Billing Analytics".""."OCDM_BILLING"."DWR_ORG_BSNS_UNIT"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWB_INVC_ITEM"."ORG_BSNS_UNIT_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_INVC_DISC_TYP"."INVC_DISC_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWB_INVC_ITEM"."INVC_DISC_TYP_CD"


Table 10-4 DWB_INVC_ITEM Facts and Calculated Measures

Measure Description

Adjustment Value

sum(DWB_INVC_ITEM.ADJD_AMT)

Discount Value

sum(DWB_INVC_ITEM.DISC_AMT)

Adjustment LY

ago("Billing Analytics"."Dwb Invc Item"."Adjustment Value", "Billing Analytics"."Calendar Daydim"."Year",1 )

Discount LY

ago("Billing Analytics"."Dwb Invc Item"."Discount Value", "Billing Analytics"."Calendar Daydim"."Year",1 )

% Change of Adjustment

(("Billing Analytics"."Dwb Invc Item"."Adjustment Value" - "Billing Analytics"."Dwb Invc Item"."Adjustment LY" ) / "Billing Analytics"."Dwb Invc Item"."Adjustment LY" ) * 100

% Change of Discount

(("Billing Analytics"."Dwb Invc Item"."Discount Value" - "Billing Analytics"."Dwb Invc Item"."Discount LY" ) / "Billing Analytics"."Dwb Invc Item"."Discount LY" ) * 100


DWD_ACCT_BAL_MO Analytics

Table 10-5 DWD_ACCT_BAL_MO Analytics

Component Description

Base Tables

  • DWB_ACCT_BAL

  • DWB_ACCT_BAL_IMPC

  • DWB_UNIT_ALWNCE

Derived Table

DWD_ACCT_BAL_MO

Intra-ETL

dwd_acct_bal_mo_pkg


Figure 10-2 DWD_ACCT_BAL_MO Relationships

Description of Figure 10-2 follows
Description of ''Figure 10-2 DWD_ACCT_BAL_MO Relationships''

Table 10-6 DWD_ACCT_BAL_MO Relationships

Relationship

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_SPEC"."PROD_SPEC_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_BAL_MO"."PROD_SPEC_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ACCT"."ACCT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_BAL_MO"."ACCT_KEY"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_BAL_MO"."MO_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ACCT_STAT"."ACCT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_BAL_MO"."ACCT_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_ACCT_BAL_TYP"."ACCT_BAL_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_BAL_MO"."ACCT_BAL_TYP_CD"

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_BAL_MO"."MO_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_OFR"."PROD_OFR_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_BAL_MO"."PROD_OFR_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_CUST"."CUST_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_BAL_MO"."CUST_KEY"


Table 10-7 DWD_ACCT_BAL_MO Facts and Calculated Measures

Measure Description

Bonus Value

sum(DWD_ACCT_BAL_MO.PPA_BONUS_ORIG_BAL_AMT)

Carried Forward Value

last(DWD_ACCT_BAL_MO.PPA_BONUS_MAX_CARRIED_FRWD_AMT)

Ppa Value

sum(DWD_ACCT_BAL_MO.PPA_ORIG_BAL_AMT)

Adjustment Value

sum(DWD_ACCT_BAL_MO.ADJD_AMT)

Adjustment LY

Ago(sum(DWD_ACCT_BAL_MO.ADJD_AMT), [Level Year], 1)

% Change of Adjustment

(("Billing Analytics"."Dwd Acct Bal Mo"."Adjustment Value" - "Billing Analytics"."Dwd Acct Bal Mo"."Adjustment LY" ) / "Billing Analytics"."Dwd Acct Bal Mo"."Adjustment LY" ) *100

Inactive Customer

count(distinct case when DWR_ACCT_STAT.STAT_CD <> 'A' and DWD_ACCT_BAL_MO.MAX_BAL_EXP_DT < DATE '2013-06-28' then DWD_ACCT_BAL_MO.ACCT_KEY end )

Active Customer

count(distinct case when DWR_ACCT_STAT.STAT_CD <> 'A' and DWD_ACCT_BAL_MO.MAX_BAL_EXP_DT >= DATE '2013-06-28' then DWD_ACCT_BAL_MO.ACCT_KEY else 0 end )

Zero Balance Customer

count(distinct case when DWD_ACCT_BAL_MO.BAL_AMT = 0 and DWD_ACCT_BAL_MO.MAX_BAL_EXP_DT >= DATE '2013-06-28' then DWD_ACCT_BAL_MO.ACCT_KEY end )

Total Utilization

sum(DWD_ACCT_BAL_MO.UTILZD_AMT)

Bonus Utilization

sum(case when DWD_ACCT_BAL_MO.PPA_BONUS_ORIG_BAL_AMT >= DWD_ACCT_BAL_MO.UTILZD_AMT then ifnull(DWD_ACCT_BAL_MO.PPA_BONUS_ORIG_BAL_AMT , 0) else ifnull(DWD_ACCT_BAL_MO.UTILZD_AMT , 0) end )

Utilized Value

CASE WHEN IFNULL("Billing Analytics"."Dwd Acct Bal Mo"."Total Utilization", 0) - IFNULL("Billing Analytics"."Dwd Acct Bal Mo"."Bonus Utilization", 0 ) > 0 THEN IFNULL("Billing Analytics"."Dwd Acct Bal Mo"."Total Utilization", 0) - IFNULL("Billing Analytics"."Dwd Acct Bal Mo"."Bonus Utilization", 0 ) ELSE 0 END

Total Allowance Value

IFNULL("Billing Analytics"."Dwd Acct Bal Mo"."Bonus Value" ,0) + IFNULL("Billing Analytics"."Dwd Acct Bal Mo"."Carried Forward Value" ,0) + IFNULL("Billing Analytics"."Dwd Acct Bal Mo"."Ppa Value",0) + IFNULL("Billing Analytics"."Dwd Acct Bal Mo"."Utilized Value",0 )

Total Allowance Value LY

AGO("Billing Analytics"."Dwd Acct Bal Mo"."Total Allowance Value", "Billing Analytics"."Calendar Daydim"."Quarter",1 )

Bonus Value LY

AGO("Billing Analytics"."Dwd Acct Bal Mo"."Bonus Value", "Billing Analytics"."Calendar Daydim"."Year" ,1 )


DWD_ACCT_DEPT_MO Analytics

Table 10-8 DWD_ACCT_DEPT_MO Analytics

Component Description

Base Table

  • DWB_INVC INVC

  • DWB_INVC_ADJ INVC_ADJ

  • DWB_INVC_PYMT_ASGN

  • INVC_PYMT_ASGN

  • DWB_ACCT_PYMT PYMT

  • DWR_ACCT ACCT

  • DWR_ADDR_LOC ADDR

  • DWR_CUST CUST

Derived Table

DWD_ACCT_DEPT_MO

Intra-ETL

dwd_acct_debt_mo_pkg


Figure 10-3 DWD_ACCT_DEPT_MO Relationships

Description of Figure 10-3 follows
Description of ''Figure 10-3 DWD_ACCT_DEPT_MO Relationships''

Table 10-9 DWD_ACCT_DEPT_MO Relationships

Relationship

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_DEBT_MO"."MO_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_CUST"."CUST_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_DEBT_MO"."CUST_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_GEO_CNTY"."GEO_CNTY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_DEBT_MO"."GEO_CNTY_KEY"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_DEBT_MO"."MO_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ORG_BSNS_UNIT"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_DEBT_MO"."ORG_BSNS_UNIT_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_CUST_TYP"."CUST_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_DEBT_MO"."CUST_TYP_CD"

"Billing Analytics".""."OCDM_BILLING"."DWR_COLLCTN_AGNCY"."COLLCTN_AGNCY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_DEBT_MO"."COLLCTN_AGNCY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ACCT"."ACCT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_DEBT_MO"."ACCT_KEY"


Table 10-10 DWD_ACCT_DEPT_MO Facts and Calculated Measures

Measure Description

Payment Collected Amount

sum(DWD_ACCT_DEBT_MO.TOT_PYMT_COLCTD_AMT)

Waiving Amount

sum(DWD_ACCT_DEBT_MO.TOT_WVNG_AMT)

Payment Collected Amount Ly

Ago("Billing Analytics"."Dwd Acct Debt Mo"."Payment Collected Amount" , "Billing Analytics"."Calendar Daydim"."Year" , 1)

Waving Amount Ly

Ago("Billing Analytics"."Dwd Acct Debt Mo"."Waiving Amount" , "Billing Analytics"."Calendar Daydim"."Year" , 1)

Write Off Amount

sum(DWD_ACCT_DEBT_MO.TOT_WRT_OFF_AMT)

Write Off Amount Ly

Ago("Billing Analytics"."Dwd Acct Debt Mo"."Write Off Amount" , "Billing Analytics"."Calendar Daydim"."Year" , 1)

Payment Collected Amount % Change

(("Billing Analytics"."Dwd Acct Debt Mo"."Payment Collected Amount" - "Billing Analytics"."Dwd Acct Debt Mo"."Payment Collected Amount Ly" ) / "Billing Analytics"."Dwd Acct Debt Mo"."Payment Collected Amount Ly" ) * 100

Billed Revenue

sum(DWD_ACCT_DEBT_MO.CURR_INVC_AMT)

Billed Revenue Ly

Ago("Billing Analytics"."Dwd Acct Debt Mo"."Billed Revenue" , "Billing Analytics"."Calendar Daydim"."Year" , 1)

Total Written Off Amount

IFNULL("Billing Analytics"."Dwd Acct Debt Mo"."Waiving Amount" , 0) + IFNULL("Billing Analytics"."Dwd Acct Debt Mo"."Write Off Amount" ,0)

Total Penalty Amount

sum(DWD_ACCT_DEBT_MO.TOT_PNLTY_AMT)

Revenue In Collections

ifnull("Billing Analytics"."Dwd Acct Debt Mo"."Payment Collected Amount" ,0) + ifnull("Billing Analytics"."Dwd Acct Debt Mo"."Total Penalty Amount" , 0)

Tot Written Off Amount Ly

Ago(sum(DWD_ACCT_DEBT_MO.TOT_WRT_OFF_AMT), [Level Year], 1)

Revenue In Collections Ly

ago("Billing Analytics"."Dwd Acct Debt Mo"."Revenue In Collections", "Billing Analytics"."Calendar Daydim"."Year", 1 )


DWD_ACCT_PYMT_DAY Analytics

Table 10-11 DWD_ACCT_PYMT_DAY Analytics

Component Description

Base Table

  • DWB_ACCT_PYMT

  • DWB_INVC_PYMT_ASGN

  • DWB_INVC

  • DWV_AGRMNT_ACCT_SBRP_PROD

Derived Table

DWD_ACCT_PYMT_DAY

Intra-ETL

dwd_acct_pymt_day_pkg


Figure 10-4 DWD_ACCT_PYMT_DAY Relationships

Description of Figure 10-4 follows
Description of ''Figure 10-4 DWD_ACCT_PYMT_DAY Relationships''

Table 10-12 DWD_ACCT_PYMT_DAY Relationships

Relationship

"Billing Analytics".""."OCDM_BILLING"."DWR_CUST"."CUST_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_PYMT_DAY"."CUST_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ACCT"."ACCT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_PYMT_DAY"."ACCT_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_PYMT_DAY"."DAY_KEY"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_PYMT_DAY"."DAY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_ACCT_TYP"."ACCT_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_ACCT_PYMT_DAY"."ACCT_TYP_CD"


Table 10-13 DWD_ACCT_PYMT_DAY Facts and Calculated Measures

Measure Description

Total Amount of Recharge

sum(cast(DWD_ACCT_PYMT_DAY.TOT_PYMT_AMT as INTEGER ) )

Total Count of Recharge

sum(DWD_ACCT_PYMT_DAY.PYMT_SUCC_CNT)

Avg 3 Months Count of Recharge

(PeriodRolling( "Billing Analytics"."Dwd Acct Pymt Day"."Total Count Of Recharge" , -2,0))/3

Avg 3 Months Amount of Recharge

(PeriodRolling("Billing Analytics"."Dwd Acct Pymt Day"."Total Amount Of Recharge" , -2,0))/3

Avg Total Recharge Value

"Billing Analytics"."Dwd Acct Pymt Day"."Total Amount Of Recharge" /"Billing Analytics"."Dwd Acct Pymt Day"."Total Count Of Recharge"

Count of Days

sum(case when DWD_ACCT_PYMT_DAY.PYMT_RSN_CD = 'RCHRG' then DWD_ACCT_PYMT_DAY.TOT_PYMT_CNT else 0 end )

Average Days Between Recharges

"Billing Analytics"."Dwd Acct Pymt Day"."Total Count Of Recharge" /"Billing Analytics"."Dwd Acct Pymt Day"."Count Of Days"

Total Count of Recharge LY

Ago("Billing Analytics"."Dwd Acct Pymt Day"."Total Count Of Recharge" , "Billing Analytics"."Calendar Daydim"."Year" , 1)

Total Amount of Recharge LY

Ago("Billing Analytics"."Dwd Acct Pymt Day"."Total Amount Of Recharge" , "Billing Analytics"."Calendar Daydim"."Year" , 1)

Total Count of Recharge % Change LY

(("Billing Analytics"."Dwd Acct Pymt Day"."Total Count Of Recharge" -"Billing Analytics"."Dwd Acct Pymt Day"."Total Count Of Recharge LY" )/"Billing Analytics"."Dwd Acct Pymt Day"."Total Count Of Recharge LY" )*100

Total Amount of Recharge % Change LY

(("Billing Analytics"."Dwd Acct Pymt Day"."Total Amount Of Recharge" -"Billing Analytics"."Dwd Acct Pymt Day"."Total Amount Of Recharge LY" )/"Billing Analytics"."Dwd Acct Pymt Day"."Total Amount Of Recharge LY" )*100

Last Recharge Date

max(DWD_ACCT_PYMT_DAY.CUST_START_DT)

Current Amount of Days Since Last Recharge

TimeStampDiff(SQL_TSI_DAY, "Billing Analytics"."Dwd Acct Pymt Day"."Last Recharge Date" , CURRENT_DATE)

Number Of Days Disturbed Recharge Cycle

"Billing Analytics"."Dwd Acct Pymt Day"."Current Amount Of Days Since Last Recharge" - "Billing Analytics"."Dwd Acct Pymt Day"."Average Days Between Recharges"

% Disturbance

("Billing Analytics"."Dwd Acct Pymt Day"."Number Of Days Disturbed Recharge Cycle" / "Billing Analytics"."Dwd Acct Pymt Day"."Average Days Between Recharges" ) * 100


DWD_CNT_DAY Analytics

Table 10-14 DWD_CNT_DAY Analytics

Component Description

Base Table

  • DWR_ADDR_LOC

  • DWR_CUST

  • DWR_PROD_SBRP

  • V_PROD_OFR_HIE

  • V_PROD_SPEC_HIE

  • DWR_ACCT

  • DWR_AGRMNT AGRMNT

Derived Tables

DWD_CNT_DAY

DWA_CNT_MO

Intra-ETL

dwd_cnt_day_pkg


Figure 10-5 DWD_CNT_DAY Relationships

Description of Figure 10-5 follows
Description of ''Figure 10-5 DWD_CNT_DAY Relationships''

Table 10-15 DWD_CNT_DAY Relationships

Relationship

"Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO"."PROD_OFR_LVL" = 'PROD_OFR_KEY' and TRIM(CAST("Billing Analytics".""."OCDM_BILLING"."DWR_PROD_OFR"."PROD_OFR_KEY" AS CHAR)) = "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO"."PROD_OFR_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO"."MO_KEY"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO"."MO_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_GEO_CNTY"."GEO_CNTY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO"."GEO_CNTY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO"."PROD_SPEC_LVL" = 'PROD_SPEC_KEY' and TRIM(CAST("Billing Analytics".""."OCDM_BILLING"."DWR_PROD_SPEC"."PROD_SPEC_KEY" AS CHAR)) = "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO"."PROD_SPEC_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ORG_BSNS_UNIT"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO"."ORG_BSNS_UNIT_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_CUST_TYP"."CUST_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO"."CUST_TYP_CD"


Table 10-16 DWD_CNT_DAY Facts and Calculated Measures

Measure Description

Total Acquisition Count

sum(DWA_CNT_MO.NEW_ACTV_PROD_SBRP_CNT)

Total Acquisition Count LY

ago("Billing Analytics"."Dwd Count Day"."Total Acquisition Count", "Billing Analytics"."Calendar Daydim"."Year" ,1 )

Involuntary Churn Count

sum(DWA_CNT_MO.INVLTRY_DACTD_PODSBP_TSPD_CNT)

Voluntary Churn Count

sum(DWA_CNT_MO.VLTRY_DACTDPROD_SBRP_TSPD_CNT)

Churn Count

ifnull("Billing Analytics"."Dwd Count Day"."Involuntary Churn Count" , 0) + ifnull("Billing Analytics"."Dwd Count Day"."Voluntary Churn Count", 0 )

Churn Count LY

ago("Billing Analytics"."Dwd Count Day"."Churn Count", "Billing Analytics"."Calendar Daydim"."Year", 1 )

Churn Count % Change LY

(("Billing Analytics"."Dwd Count Day"."Churn Count" - "Billing Analytics"."Dwd Count Day"."Churn Count LY" ) / "Billing Analytics"."Dwd Count Day"."Churn Count LY" ) * 100

Prod Spec Cd

CASE "Billing Analytics"."Dwd Count Day"."Prod Spec Lvl" WHEN 'PROD_SPEC_TYP_CD' THEN '-5000' WHEN 'TPROD_SPEC' THEN '-5000' WHEN 'PROD_SPEC_KEY' THEN "Billing Analytics"."Dwr Prod Spec"."Prod Spec Cd" END

Prod Spec Typ Cd

CASE "Billing Analytics"."Dwd Count Day"."Prod Spec Lvl" WHEN 'PROD_SPEC_TYP_CD' THEN "Billing Analytics"."Dwd Count Day"."Prod Spec Key" WHEN 'TPROD_SPEC' THEN 'N/A' WHEN 'PROD_SPEC_KEY' THEN "Billing Analytics"."Dwr Prod Spec"."Prod Spec Typ Cd" END

Prod Ofr Name

CASE "Billing Analytics"."Dwd Count Day"."Prod Ofr Lvl" WHEN 'PROD_OFR_KEY' THEN "Billing Analytics"."Dwr Prod Ofr"."Prod Ofr Name" ELSE '-5000' END

Acquisition % Change

(("Billing Analytics"."Dwd Count Day"."Total Acquisition Count" - "Billing Analytics"."Dwd Count Day"."Total Acquisition Count LY" ) / "Billing Analytics"."Dwd Count Day"."Total Acquisition Count LY" ) * 100

Pln Typ

CASE "Billing Analytics"."Dwd Count Day"."Prod Ofr Lvl" WHEN 'PLN_TYP' THEN "Billing Analytics"."Dwd Count Day"."Prod Ofr Key" WHEN 'PROD_OFR_KEY' THEN IFNULL("Billing Analytics"."Dwr Prod Ofr"."Pln Typ", 'N/A') else 'N/A' END


DWD_DATA_USG_DAY Analytics

Table 10-17 DWD_DATA_USG_DAY Analytics

Component Description

Base Table

  • DWB_CNTNT_DLVRY_EVT

  • DWB_WRLS_CNTNT_DNLDG_EVT

Derived Table

DWD_DATA_USG_DAY

Intra-ETL

dwd_data_usg_day_pkg


Figure 10-6 DWD_DATA_USG_DAY Relationships

Description of Figure 10-6 follows
Description of ''Figure 10-6 DWD_DATA_USG_DAY Relationships''

Table 10-18 DWD_DATA_USG_DAY Relationships

Relationship

"Billing Analytics".""."OCDM_BILLING"."DWR_PRTY"."PRTY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."RMNG_OPRTR_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_CUST_TYP"."CUST_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."CUST_TYP_CD"

"Billing Analytics".""."OCDM_BILLING"."DWR_GEO_CNTY"."GEO_CNTY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."GEO_CNTY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_RMNG_TYP"."RMNG_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."RMNG_TYP_CD"

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_OFR"."PROD_OFR_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."PROD_OFR_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ORG_BSNS_UNIT"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."ORG_BSNS_UNIT_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_CNTNT"."CNTNT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."CNTNT_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."DAY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_CALL_TYP"."CALL_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."USG_TYP"

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_SPEC"."PROD_SPEC_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."PROD_SPEC_KEY"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_DATA_USG_DAY"."DAY_KEY"


Table 10-19 DWD_DATA_USG_DAY Facts and Calculated Measures

Measure Description

Inbound Transferred Bytes

sum(DWD_DATA_USG_DAY.TOT_BYTES_TRNSFRD_INBND)

Outbound Transferred Bytes

sum(DWD_DATA_USG_DAY.TOT_BYTES_TRNSFRD_OUTBND)

Bytes Used

ifnull("Billing Analytics"."Dwd Data Usg Day"."Inbound Transferred Bytes", 0 ) + ifnull("Billing Analytics"."Dwd Data Usg Day"."Outbound Transferred Bytes", 0 )

Data Revenue

sum(DWD_DATA_USG_DAY.CHRGD_AMT)


DWD_INVC_DAY Analytics

Table 10-20 DWD_INVC_DAY Analytics

Component Description

Base Table

  • DWB_INVC

  • DWB_INVC_ITEM

Derived Table

DWD_INVC_DAY

Intra-ETL

dwd_invc_day_pkg


Figure 10-7 DWD_INVC_DAY Relationships

Description of Figure 10-7 follows
Description of ''Figure 10-7 DWD_INVC_DAY Relationships''

Table 10-21 DWD_INVC_DAY Relationships

Relationships

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_INVC_DAY"."DAY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_CUST_TYP"."CUST_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_INVC_DAY"."CUST_TYP_CD"

"Billing Analytics".""."OCDM_BILLING"."DWR_ORG_BSNS_UNIT"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_INVC_DAY"."ORG_BSNS_UNIT_KEY"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_INVC_DAY"."DAY_KEY"


Table 10-22 DWD_INVC_DAY Facts and Calculated Measures

Measure Calculation

Discount Value

sum(DWD_INVC_DAY.BLNG_DISC_AMT)

Discount LY

ago("Billing Analytics"."Dwd Invc Day"."Discount Value", "Billing Analytics"."Calendar Daydim"."Year", 1 )

% Change of Discount

(("Billing Analytics"."Dwd Invc Day"."Discount Value" - "Billing Analytics"."Dwd Invc Day"."Discount LY" ) / "Billing Analytics"."Dwd Invc Day"."Discount LY" ) * 100


DWD_PRPD_ACCT_STTSTC_DAY Analytics

Table 10-23 DWD_PRPD_ACCT_STTSTC_DAY Analytics

Component Description

Base Table

  • DWB_ACCT_PYMT

  • DWR_ACCT

  • DWR_AGRMNT

Derived Table

DWD_PRPD_ACCT_STTSTC_DAY

Intra-ETL

dwd_prpd_acct_sttstc_day_pkg


Figure 10-8 DWD_PRPD_ACCT_STTSTC_DAY Relationships

Description of Figure 10-8 follows
Description of ''Figure 10-8 DWD_PRPD_ACCT_STTSTC_DAY Relationships''

Table 10-24 DWD_PRPD_ACCT_STTSTC_DAY Relationships

Relationships

"Billing Analytics".""."OCDM_BILLING"."DWR_CUST"."CUST_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_PRPD_ACCT_STTSTC_DAY"."CUST_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_SPEC"."PROD_SPEC_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_PRPD_ACCT_STTSTC_DAY"."PROD_SPEC_KEY"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_PRPD_ACCT_STTSTC_DAY"."DAY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_PRPD_ACCT_STTSTC_DAY"."DAY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ACCT"."ACCT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_PRPD_ACCT_STTSTC_DAY"."ACCT_KEY"


Table 10-25 DWD_PRPD_ACCT_STTSTC_DAY Facts and Calculated Measures

Measure Description

Total Amount of Recharge

sum(DWD_PRPD_ACCT_STTSTC_DAY.TOT_RCHRG_AMT)

Total Count of Recharge

sum(DWD_PRPD_ACCT_STTSTC_DAY.TOT_RCHRG_CNT)

Total Amount of Recharge LY

ago("Billing Analytics"."Dwd Prpd Acct Sttstc Day"."Total Amount Of Recharge" , "Billing Analytics"."Calendar Daydim"."Year",1 )

Total Count of Recharge LY

ago("Billing Analytics"."Dwd Prpd Acct Sttstc Day"."Total Count Of Recharge", "Billing Analytics"."Calendar Daydim"."Year",1 )

Total Amount of Recharge % Change LY

(("Billing Analytics"."Dwd Prpd Acct Sttstc Day"."Total Amount Of Recharge" - "Billing Analytics"."Dwd Prpd Acct Sttstc Day"."Total Amount Of Recharge LY" ) / "Billing Analytics"."Dwd Prpd Acct Sttstc Day"."Total Amount Of Recharge LY" ) * 100

Total Count of Recharge % Change LY

(("Billing Analytics"."Dwd Prpd Acct Sttstc Day"."Total Count Of Recharge" - "Billing Analytics"."Dwd Prpd Acct Sttstc Day"."Total Count Of Recharge LY" )/ "Billing Analytics"."Dwd Prpd Acct Sttstc Day"."Total Count Of Recharge LY" ) * 100


DWD_RVN_DAY Analytics

Table 10-26 DWD_RVN_DAY Analytics

Component Description

Base Table

  • DWB_INVC

  • DWB_INVC_ITEM

  • DWB_ACCT_BAL

  • DWD_VAS_USG_DAY

  • DWD_DATA_USG_DAY

  • DWD_VOI_CALL_DAY

Derived Table

DWD_RVN_DAY

DWA_RVN_MO

Intra-ETL

dwd_rnv_day_pkg


Figure 10-9 DWD_RVN_DAY Relationship

Description of Figure 10-9 follows
Description of ''Figure 10-9 DWD_RVN_DAY Relationship''

Table 10-27 DWD_RVN_DAY Relationships

Relationships

"Billing Analytics".""."OCDM_BILLING"."DWL_CUST_TYP"."CUST_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."CUST_TYP_CD"

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."MO_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."PROD_OFR_LVL" = 'PROD_OFR_KEY' and TRIM(CAST("Billing Analytics".""."OCDM_BILLING"."DWR_PROD_OFR"."PROD_OFR_KEY" AS CHAR)) = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."PROD_OFR_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO_ARPU"."MO_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."MO_KEY" AND "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO_ARPU"."CUST_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."CUST_TYP_CD" AND "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO_ARPU"."GEO_CNTY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."GEO_CNTY_KEY" AND "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO_ARPU"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."ORG_BSNS_UNIT_KEY" AND "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO_ARPU"."PLN_TYP" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."PLN_TYP" AND "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO_ARPU"."SL_CHNL_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."SL_CHNL_KEY" AND "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO_ARPU"."PROD_OFR_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."PROD_OFR_KEY" AND "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO_ARPU"."PROD_OFR_LVL" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."PROD_OFR_LVL" AND "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO_ARPU"."PROD_SPEC_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."PROD_SPEC_KEY" AND "Billing Analytics".""."OCDM_BILLING"."DWA_CNT_MO_ARPU"."PROD_SPEC_LVL" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."PROD_SPEC_LVL"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."MO_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."PROD_SPEC_LVL" = 'PROD_SPEC_KEY' and TRIM(CAST("Billing Analytics".""."OCDM_BILLING"."DWR_PROD_SPEC"."PROD_SPEC_KEY" AS CHAR)) = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."PROD_SPEC_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ORG_BSNS_UNIT"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."ORG_BSNS_UNIT_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_GEO_CNTY"."GEO_CNTY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWA_RVN_MO"."GEO_CNTY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWD_RVN_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWVSBRPCNT"."DAY_KEY"

and "Billing Analytics".""."OCDM_BILLING"."DWD_RVN_DAY"."GEO_CNTY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWVSBRPCNT"."GEO_CNTY_KEY"

and "Billing Analytics".""."OCDM_BILLING"."DWVSBRPCNT"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_RVN_DAY"."ORG_BSNS_UNIT_KEY"

and "Billing Analytics".""."OCDM_BILLING"."DWVSBRPCNT"."CUST_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_RVN_DAY"."CUST_TYP_CD"

and "Billing Analytics".""."OCDM_BILLING"."DWVSBRPCNT"."PLN_TYP" = "Billing Analytics".""."OCDM_BILLING"."DWD_RVN_DAY"."PLN_TYP"

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_SPEC"."PROD_SPEC_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_RVN_DAY"."PROD_SPEC_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ORG_BSNS_UNIT"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_RVN_DAY"."ORG_BSNS_UNIT_KEY"


Table 10-28 DWD_RVN_DAYFacts and Calculated Measures

Measure Description

Usage Revenue

sum(DWA_RVN_MO.USG_RVN_INVCD)

Expired Revenue

sum(DWA_RVN_MO.EXPIRED_RVN)

Prepaid Revenue

sum(case when DWA_RVN_MO.PLN_TYP = 'PREPAID' then ifnull(DWA_RVN_MO.EXPIRED_RVN , 0) + ifnull(DWA_RVN_MO.USG_RVN_INVCD , 0) else 0 end )

Recurring Bill

sum(DWA_RVN_MO.RCRNG_ARREAR_FEES_INVCD)

Recurring Forward Bill

sum(DWA_RVN_MO.RCRNG_FRWD_FEES_INVCD_ERND)

Equipment Sale Revenue Bill

sum(DWA_RVN_MO.EQPMNT_SL_RVN_INVCD)

Other Revenue

sum(DWA_RVN_MO.OTHR_RVN_INVCD)

Postpaid Revenue

sum(case when DWA_RVN_MO.PLN_TYP = 'POSTPAID' then ifnull(DWA_RVN_MO.RCRNG_ARREAR_FEES_INVCD , 0) + ifnull(DWA_RVN_MO.USG_RVN_INVCD , 0) + ifnull(DWA_RVN_MO.RCRNG_FRWD_FEES_INVCD_ERND , 0) + ifnull(DWA_RVN_MO.EQPMNT_SL_RVN_INVCD , 0) else 0 end )

Total Gross Revenue

sum(ifnull(DWA_RVN_MO.EXPIRED_RVN , 0) + ifnull(DWA_RVN_MO.USG_RVN_INVCD , 0) + ifnull(DWA_RVN_MO.RCRNG_ARREAR_FEES_INVCD , 0) + ifnull(DWA_RVN_MO.RCRNG_FRWD_FEES_INVCD_ERND , 0) + ifnull(DWA_RVN_MO.EQPMNT_SL_RVN_INVCD , 0) + ifnull(DWA_RVN_MO.OTHR_RVN_INVCD , 0))

Billed Refund Amount

sum(DWA_RVN_MO.BLLD_RFND_AMT)

Billed Written Off Amount

sum(DWA_RVN_MO.BLLD_WRITE_OFF_AMT)

Total Net Revenue

sum(ifnull(DWA_RVN_MO.EXPIRED_RVN , 0) + ifnull(DWA_RVN_MO.USG_RVN_INVCD , 0) + ifnull(DWA_RVN_MO.RCRNG_ARREAR_FEES_INVCD , 0) + ifnull(DWA_RVN_MO.RCRNG_FRWD_FEES_INVCD_ERND , 0) + ifnull(DWA_RVN_MO.EQPMNT_SL_RVN_INVCD , 0) + ifnull(DWA_RVN_MO.OTHR_RVN_INVCD , 0)) - ifnull(sum(DWA_RVN_MO.BLLD_RFND_AMT) , 0) - ifnull(sum(DWA_RVN_MO.BLLD_WRITE_OFF_AMT) , 0) - ifnull(sum(DWA_RVN_MO.BLLD_TAX_AMT) , 0)

Bill Tax Amount

sum(DWA_RVN_MO.BLLD_TAX_AMT)

Total Gross Revenue LY

AGO("Billing Analytics"."Dwd Rvn Day"."Total Gross Revenue" , "Billing Analytics"."Calendar Daydim"."Year", 1 )

Total Net Revenue LY

AGO("Billing Analytics"."Dwd Rvn Day"."Total Net Revenue", "Billing Analytics"."Calendar Daydim"."Year" , 1 )

Revenue % Change

(("Billing Analytics"."Dwd Rvn Day"."Total Net Revenue" - "Billing Analytics"."Dwd Rvn Day"."Total Net Revenue LY" ) / "Billing Analytics"."Dwd Rvn Day"."Total Net Revenue LY" ) * 100

Prod Ofr Name

CASE WHEN "Billing Analytics"."Dwd Rvn Day"."Prod Ofr Lvl" = 'PROD_OFR_KEY' THEN "Billing Analytics"."Dwr Prod Ofr"."Prod Ofr Name" ELSE '-5000' END

Prod Spec Cd

CASE "Billing Analytics"."Dwd Rvn Day"."Prod Spec Lvl" WHEN 'PROD_SPEC_TYP_CD' THEN '-5000' WHEN 'TPROD_SPEC' THEN '-5000' WHEN 'PROD_SPEC_KEY' THEN "Billing Analytics"."Dwr Prod Spec"."Prod Spec Cd" END

Prod Spec Typ Cd

CASE "Billing Analytics"."Dwd Rvn Day"."Prod Spec Lvl" WHEN 'PROD_SPEC_TYP_CD' THEN "Billing Analytics"."Dwd Rvn Day"."Prod Spec Key" WHEN 'TPROD_SPEC' THEN 'N/A' WHEN 'PROD_SPEC_KEY' THEN "Billing Analytics"."Dwr Prod Spec"."Prod Spec Typ Cd" END

Pln Typ

CASE "Billing Analytics"."Dwd Rvn Day"."Prod Ofr Lvl" WHEN 'PLN_TYP' THEN "Billing Analytics"."Dwd Rvn Day"."Prod Ofr Key" WHEN 'PROD_OFR_KEY' THEN IFNULL("Billing Analytics"."Dwr Prod Ofr"."Pln Typ", 'N/A') ELSE 'N/A' END

ARPU Prepaid

avg(case when DWA_RVN_MO.PLN_TYP = 'PREPAID' then ifnull(DWA_RVN_MO.EXPIRED_RVN , 0) + ifnull(DWA_RVN_MO.USG_RVN_INVCD , 0) else 0 end / nullif( DWA_CNT_MO_ARPU.TOT_ACTV_MN_PROD_SBRP_CNT , 0))

ARPU Postpaid

avg(case when DWA_RVN_MO.PLN_TYP = 'POSTPAID' then ifnull(DWA_RVN_MO.RCRNG_ARREAR_FEES_INVCD , 0) + ifnull(DWA_RVN_MO.USG_RVN_INVCD , 0) + ifnull(DWA_RVN_MO.RCRNG_FRWD_FEES_INVCD_ERND , 0) + ifnull(DWA_RVN_MO.EQPMNT_SL_RVN_INVCD , 0) else 0 end / nullif( DWA_CNT_MO_ARPU.TOT_ACTV_MN_PROD_SBRP_CNT , 0))

Total ARPU

avg((ifnull(DWA_RVN_MO.EXPIRED_RVN , 0) + ifnull(DWA_RVN_MO.USG_RVN_INVCD , 0) + ifnull(DWA_RVN_MO.RCRNG_ARREAR_FEES_INVCD , 0) + ifnull(DWA_RVN_MO.RCRNG_FRWD_FEES_INVCD_ERND , 0) + ifnull(DWA_RVN_MO.EQPMNT_SL_RVN_INVCD , 0)) / nullif( DWA_CNT_MO_ARPU.TOT_ACTV_MN_PROD_SBRP_CNT , 0))

Total ARPU LY

AGO("Billing Analytics"."Dwd Rvn Day"."Total ARPU", "Billing Analytics"."Calendar Daydim"."Year" ,1 )

ARPU % Change

(("Billing Analytics"."Dwd Rvn Day"."Total ARPU" - "Billing Analytics"."Dwd Rvn Day"."Total ARPU LY" ) / "Billing Analytics"."Dwd Rvn Day"."Total ARPU LY" ) * 100


DWD_TMF_KPI Analytics

Table 10-29 DWD_TMF_KPI Analytics

Component Description

Base Table

  • dwd_acct_pymt_day

  • dwr_cust_sgmnt

  • dwb_invc

  • dwb_invc_item

Derived Table

DWD_TMF_KPI

Intra-ETL

pkg_tmf_kpi_load


Figure 10-10 DWD_TMF_KPI Relationships

Description of Figure 10-10 follows
Description of ''Figure 10-10 DWD_TMF_KPI Relationships''

Table 10-30 DWD_TMF_KPI Relationships

Relationship

"Billing Analytics".""."OCDM_BILLING"."DWD_TMF_KPI"."BATCHID" = "Billing Analytics".""."OCDM_BILLING"."DWD_TMF_KPI_A"."BATCHID"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."CLNDR_DT" = CAST("Billing Analytics".""."OCDM_BILLING"."DWD_TMF_KPI_A"."LOAD_DT" AS DATE)

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DT" = CAST("Billing Analytics".""."OCDM_BILLING"."DWD_TMF_KPI_A"."LOAD_DT" AS DATE)


Table 10-31 DWD_TMF_KPI Facts and Calculated Measures

Measure Description

Entry

sum(DWD_TMF_KPI_A.ENTRY)


DWD_VAS_USG_DAY Analytics

Table 10-32 DWD_VAS_USG_DAY Analytics

Component Description

Base Table

  • DWB_ISP_USG_EVT

  • DWB_WRLS_CALL_EVT

  • DWB_UMS_EVT

  • DWB_SMS_EVT

  • DWB_MMS_EVT

  • DWB_UDR_EVT

Derived Table

DWD_VAS_USG_DAY

Intra-ETL

dwd_vas_usg_day_pkg


Figure 10-11 DWD_VAS_USG_DAY Relationships

Description of Figure 10-11 follows
Description of ''Figure 10-11 DWD_VAS_USG_DAY Relationships''

Table 10-33 DWD_VAS_USG_DAY Relationships

Relationship

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_OFR"."PROD_OFR_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VAS_USG_DAY"."PROD_OFR_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ORG_BSNS_UNIT"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VAS_USG_DAY"."ORG_BSNS_UNIT_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_CUST"."CUST_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VAS_USG_DAY"."CUST_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_GEO_CNTY"."GEO_CNTY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VAS_USG_DAY"."GEO_CNTY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_SPEC"."PROD_SPEC_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VAS_USG_DAY"."PROD_SPEC_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VAS_USG_DAY"."DAY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ACCT"."ACCT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VAS_USG_DAY"."ACCT_KEY"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VAS_USG_DAY"."DAY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_CUST_TYP"."CUST_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_VAS_USG_DAY"."CUST_TYP_CD"


Table 10-34 DWD_VAS_USG_DAY Facts and Calculated Measures

Measure Description

Vas Revenue

sum(DWD_VAS_USG_DAY.CHRGD_AMT)

Minutes of Usage

sum(DWD_VAS_USG_DAY.EVT_DRTN)

Sms Revenue

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'SMS' then DWD_VAS_USG_DAY.CHRGD_AMT else 0 end )

Number of Sms

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'SMS' then DWD_VAS_USG_DAY.EVT_CNT else 0 end )

Number of Nonroaming SMS

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'SMS' and DWD_VAS_USG_DAY.RMNG_OPRTR_KEY <> '-5000' then DWD_VAS_USG_DAY.EVT_CNT else 0 end )

Number of Roaming SMS

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'SMS' and DWD_VAS_USG_DAY.RMNG_OPRTR_KEY = '-5000' then DWD_VAS_USG_DAY.EVT_CNT else 0 end )

Total No. of SMS

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'SMS' then DWD_VAS_USG_DAY.EVT_CNT else 0 end )

Total No. of Subscribers

count(distinct case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'SMS' then DWD_VAS_USG_DAY.ACCT_KEY end )

Total KB

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'SMS' then DWD_VAS_USG_DAY.DATA_VOL else 0 end )

Average KB per Subscriber

"Billing Analytics"."Dwd Vas Usg Day"."Total KB" / "Billing Analytics"."Dwd Vas Usg Day"."Total No. Of Subscribers"

Revenue from Roaming SMS

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'SMS' and DWD_VAS_USG_DAY.RMNG_OPRTR_KEY <> '-5000' then DWD_VAS_USG_DAY.CHRGD_AMT else 0 end )

Revenue from Nonroaming SMS

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'SMS' and DWD_VAS_USG_DAY.RMNG_OPRTR_KEY = '-5000' then DWD_VAS_USG_DAY.CHRGD_AMT else 0 end )

Total Duration

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'SMS' then DWD_VAS_USG_DAY.EVT_DRTN else 0 end )

Total No. of SMS LY

AGO("Billing Analytics"."Dwd Vas Usg Day"."Total No. Of SMS", "Billing Analytics"."Calendar Daydim"."Year", 1 )

Total Duration LY

AGO("Billing Analytics"."Dwd Vas Usg Day"."Total Duration" , "Billing Analytics"."Calendar Daydim"."Year", 1 )

Average SMS per Subscriber

"Billing Analytics"."Dwd Vas Usg Day"."Total No. Of SMS" / "Billing Analytics"."Dwd Vas Usg Day"."Total No. Of Subscribers"

Average Duration per Subscriber

"Billing Analytics"."Dwd Vas Usg Day"."Total Duration" / "Billing Analytics"."Dwd Vas Usg Day"."Total No. Of Subscribers"

MMS Revenue

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'MMS' then DWD_VAS_USG_DAY.CHRGD_AMT else 0 end )

Number of MMS

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'MMS' then DWD_VAS_USG_DAY.EVT_CNT else 0 end )

Number of Nonroaming MMS

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'MMS' and DWD_VAS_USG_DAY.RMNG_OPRTR_KEY <> '-5000' then DWD_VAS_USG_DAY.EVT_CNT else 0 end )

Number of Roaming MMS

sum(case when DWD_VAS_USG_DAY.VAS_TYP_CD = 'MMS' and DWD_VAS_USG_DAY.RMNG_OPRTR_KEY = '-5000' then DWD_VAS_USG_DAY.EVT_CNT else 0 end )


DWD_VOI_CALL_DAY Analytics

Table 10-35 DWD_VOI_CALL_DAY Analytics

Component Description

Base Tables

DWB_WRLS_CALL_EVT

Derived Table

DWD_VOI_CALL_DAY

Intra-ETL

dwd_voi_call_day_pkg


Figure 10-12 DWD_VOI_CALL_DAY Relationships

Description of Figure 10-12 follows
Description of ''Figure 10-12 DWD_VOI_CALL_DAY Relationships''

Table 10-36 DWD_VOI_CALL_DAY Relationships

Relationship

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_SPEC"."PROD_SPEC_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VOI_CALL_DAY"."PROD_SPEC_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_GEO_CNTY"."GEO_CNTY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VOI_CALL_DAY"."GEO_CNTY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_DAY"."BSNS_DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VOI_CALL_DAY"."DAY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_PROD_OFR"."PROD_OFR_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VOI_CALL_DAY"."PROD_OFR_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWL_RMNG_TYP"."RMNG_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_VOI_CALL_DAY"."RMNG_TYP_CD"

"Billing Analytics".""."OCDM_BILLING"."DWL_CUST_TYP"."CUST_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_VOI_CALL_DAY"."CUST_TYP_CD"

"Billing Analytics".""."OCDM_BILLING"."DWL_CALL_TYP"."CALL_TYP_CD" = "Billing Analytics".""."OCDM_BILLING"."DWD_VOI_CALL_DAY"."CALL_TYP_CD"

"Billing Analytics".""."OCDM_BILLING"."CALENDAR_DAY"."DAY_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VOI_CALL_DAY"."DAY_KEY"

"Billing Analytics".""."OCDM_BILLING"."DWR_ORG_BSNS_UNIT"."ORG_BSNS_UNIT_KEY" = "Billing Analytics".""."OCDM_BILLING"."DWD_VOI_CALL_DAY"."ORG_BSNS_UNIT_KEY"


Table 10-37 DWD_VOI_CALL_DAY Facts and Calculated Measures

Measure Description

Total Call Duration

sum(DWD_VOI_CALL_DAY.CHRGD_DRTN)

Average Call Duration

avg(DWD_VOI_CALL_DAY.CHRGD_DRTN)

Total Revenue

sum(DWD_VOI_CALL_DAY.CHRGD_AMT)

Average Revenue per Min

sum(DWD_VOI_CALL_DAY.CHRGD_AMT) / nullif( sum(DWD_VOI_CALL_DAY.CHRGD_DRTN) , 0)

International Minutes

sum(case when DWD_VOI_CALL_DAY.CALL_TYP_CD = 'INTL' then DWD_VOI_CALL_DAY.CHRGD_DRTN else 0 end )

Number of Roaming Calls

sum(case when DWD_VOI_CALL_DAY.RMNG_TYP_CD = 'ROAM' then DWD_VOI_CALL_DAY.EVT_CNT else 0 end )

Number of Nonroaming Calls

sum(case when DWD_VOI_CALL_DAY.RMNG_TYP_CD = 'NONROAM' then DWD_VOI_CALL_DAY.EVT_CNT else 0 end )

Total Roaming Voice Minutes

sum(case when DWD_VOI_CALL_DAY.RMNG_TYP_CD = 'ROAM' then DWD_VOI_CALL_DAY.EVT_DRTN else 0 end )

Total Nonroaming Voice Minutes

sum(case when DWD_VOI_CALL_DAY.RMNG_TYP_CD = 'NONROAM' then DWD_VOI_CALL_DAY.EVT_DRTN else 0 end )

Number of Roaming Call 60

sum(case when DWD_VOI_CALL_DAY.RMNG_TYP_CD = 'ROAM' then DWD_VOI_CALL_DAY.CEILED_DRTN_60 else 0 end )

Number of Nonroaming Call 60

sum(case when DWD_VOI_CALL_DAY.RMNG_TYP_CD = 'NONROAM' then DWD_VOI_CALL_DAY.CEILED_DRTN_60 else 0 end )

Revenue from Roaming Calls

sum(case when DWD_VOI_CALL_DAY.RMNG_TYP_CD = 'ROAM' then DWD_VOI_CALL_DAY.CHRGD_AMT else 0 end )

Revenue from Nonroaming Calls

sum(case when DWD_VOI_CALL_DAY.RMNG_TYP_CD = 'NONROAM' then DWD_VOI_CALL_DAY.CHRGD_AMT else 0 end )

Number of Outgoing Calls

sum(case when DWD_VOI_CALL_DAY.EVT_DRCTN_CD = 'OUT' then DWD_VOI_CALL_DAY.EVT_CNT else 0 end )

Number of Incoming Calls

sum(case when DWD_VOI_CALL_DAY.EVT_DRCTN_CD = 'IN' then DWD_VOI_CALL_DAY.EVT_CNT else 0 end )


DWV_PRPD_CUST_STAT View Analytics

Figure 10-13 DWV_PRPD_CUST_STAT Relationships

Description of Figure 10-13 follows
Description of ''Figure 10-13 DWV_PRPD_CUST_STAT Relationships''

Table 10-38 DDV_PRPD_CUST_STAT _DAY Facts and Calculated Measures

Measure Description

Total Amount of Recharge

sum(DWD_PRPD_ACCT_STTSTC_DAY.TOT_RCHRG_AMT)

Total Count of Recharge

sum(DWV_PRPD_CUST_STAT.CUST_TOT_RCRRG_CNT)

Average Days Between Recharges

avg(DWV_PRPD_CUST_STAT.AVG_DRTN_BTWN_RCHRG)

Last Recharge Date

max(DWV_PRPD_CUST_STAT.LAST_RCHRG_DT)

Days Since Last Recharge

TimeStampDiff(SQL_TSI_DAY, "Billing Analytics"."Prepaid Customers Current Status"."Last Recharge Date" , CURRENT_DATE)

Days Disturbed Recharge Cycle

"Billing Analytics"."Prepaid Customers Current Status"."Days Since Last Recharge" - "Billing Analytics"."Prepaid Customers Current Status"."Average Days Between Recharges"

% Disturbance

("Billing Analytics"."Prepaid Customers Current Status"."Days Disturbed Recharge Cycle" / "Billing Analytics"."Prepaid Customers Current Status"."Average Days Between Recharges" ) * 100

Avg Total Recharge Value

avg(DWV_PRPD_CUST_STAT.AVG_RCHRG_AMT)

Last 3 Months Recharge Count

avg(DWV_PRPD_CUST_STAT.TOT_RCHRG_CNT_LAST3MO)

Last 3 Months Recharge Amount

avg(DWV_PRPD_CUST_STAT.TOT_RCHRG_AMT_LAST3_MO)

Last Recharge Amount

sum(DWV_PRPD_CUST_STAT.LAST_RCHRG_AMT)