Skip Headers
Oracle® Communications Data Model Adapters and Analytics User's Guide
Release 11.3.2

E37699-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 Billing Analytics Reference

This chapter provides information on Oracle Communications Data Model Billing Analytics tables, relationships, and measures.

Billing Analytics Physical Objects

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

Table 10-1 Billing Analytics Oracle Communications Data Model Physical Objects

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 (OBIEE View)


Billing Analytics Relationships and Measures

DWB_INVC_ITEM Analytics

Table 10-2 DWB_INVC_ITEM Analytics

Component Description

Base Table

N/A

Derived Table

DWB_INVC_ITEM

Intra-ETL

Not required as 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 Measure

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 Measure

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 )

No Of Sms

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

No Of Non-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 )

No 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 Non-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 )

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 )

No Of MMS

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

No Of Non-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 )

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

No Of Roaming Calls

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

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

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

No 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 Non-roaming Calls

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

No Of Outgoing Calls

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

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