Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing

Data Mapping Guide

Release 2.5.2.0.1

E49009-07

June 2016

Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing Data Mapping Guide

E49009-07

Copyright © 2016, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Preface

This guide provides the data mapping information from the source system of Oracle Utilities Customer Care and Billing to the target product of Oracle Utilities Extractors and Schema.

Audience

The guide is intended for all implementers of Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing.

Related Documents

For more information, see the following documents:

•

Oracle Utilities Analytics Release Notes

•

Oracle Utilities Analytics Getting Started Guide

•

Oracle Utilities Analytics Installation Guide

•

Oracle Utilities Analytics Quick Install Guide

•

Oracle Utilities Analytics Administration Guide

•

Oracle Utilities Analytics Developer’s Guide

See Also :

•

Oracle Utilities Customer Care and Billing Documentation Library

Conventions

The following notational conventions are used in this document:

Notation

Indicates

boldface

Graphical user interface elements associated with an action, terms defined in text, or terms defines in the glossary

italic

Book titles, emphasis, or placeholder variables for which you supply particular values

monospace

Commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter

Overview

This guide provides the data mapping information from the Oracle Utilities Customer Care and Billing source system to the Oracle Utilities Extractors and Schema target product, along with the rules of data transformation.

Terminologies

This section describes the terminologies used for data maps included in the document.

<Table Name>

The Table Name indicates the name of the fact or the dimension in a star schema in the data warehouse.

Properties

The Properties table lists properties of the table independent of each field. The following properties are listed in the table:

Property

Value

Target Table

Name of the table in the target schema (data warehouse) into which data is loaded

Table Type

Fact or Dimension

SCD Type

Type 1 - Existing records are updated directly. The nature of the dimension on how it handles changes made in the source system.

Type 2 - Existing records are inactivated by putting the current date as the effective end date and new records are inserted with an effective start date as today’s date. The new record will have the start date as the current date and a high date.

Fact Type

Whether this is a snapshot or transactional fact table

Snapshot - Captures a snapshot view of the data as available in the source system during that period (monthly or weekly). Each snapshot's data is stored independently.

Accumulation - Data from the source system is accumulated periodically. Changes from source system will be merged with the existing data. Multiple copies of the same data will not be maintained.

Source System Driver Table

Name of the table in source database from which data is extracted

Stage Table

Name of the table in the staging schema (data warehouse) that can be used to query the data records generated by the ETL logic

Oracle Data Integrator Package

Name of the ODI package that needs be executed to extract data from the source application and populate a specific target table in the data warehouse

ETL View

Name of the view in the data warehouse that has the logic for retrieval and transformation of the source data

Materialized View

Names of the materialized views delivered with the product for a specific fact table

These materialized views are designed to support all of the OBIEE answers delivered with the product. Refreshing these materialized views will be taken care as part of the ETL processes.

Fields

The Fields table lists the individual properties of each field in the presentation table or the database table. The following fields are listed in those tables:

Property

Value

Source Field

Name of the field from the source application which is used to load the target field either directly or after transformation.

Target Field

Name of the column in the fact or dimension table present in the data warehouse. This is where the extracted data will be loaded into.

OBIEE Field

Name of the field in the OBIEE Presentation folder. If blank, the field is not available by default in OBIEE.

Data Maps for Oracle Utilities Customer Care and Billing

This section contains data maps for the following Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing:

•

Dimension Tables

•

Fact Tables

Dimension Tables

Account Dimension

The Account dimension stores details about all accounts from the source system with all related attributes.

The following UDF columns are populated by the ETL process supplied with the product.

•

UDF1 - Customer Class

•

UDF2 - Account Management Group

•

UDF3 - CIS Division

•

UDF4 - Bill Cycle

•

UDF5 - Collection Class

Properties

Property

Value

Target Table

CD_ACCT

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_ACCT

Stage Table

STG_CD_ACCT

ODI Package

B1_PKG_CD_ACCT

ETL View

B1_D_ACCT_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

ACCT_KEY

Account Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_ACCT_SEQ.

SRC_ACCT_ID

Account ID

CI_ACCT.ACCT_ID

ACCT_INFO

Account

Transformation Logic:

This field is populated with the primary name of main customer, customer class, and account ID.

UDF1_CD

Customer Class Code

CI_ACCT.CUST_CL_CD

UDF1_DESCR

Customer Class

CI_CUST_CL_L.DESCR

UDF2_CD

Account Management. Group Code

CI_ACCT.ACCT_MGMT_GRP_CD

UDF2_DESCR

Account Management. Group

CI_ACCT_MGMT_GR_L.DESCR

UDF3_CD

Division Code

CI_ACCT.CIS_DIVISION

UDF3_DESCR

Division

CI_CIS_DIVISION_L.DESCR

UDF4_CD

Bill Cycle Code

CI_ACCT.BILL_CYC_CD

UDF4_DESCR

Bill Cycle

CI_BILL_CYC_L.DESCR

UDF5_CD

Collection Class Code

CI_ACCT.COLL_CL_CD

UDF5_DESCR

Collection Class

CI_COLL_CL_L.DESCR

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Address Dimension

The Address dimension stores address related attributes from the premises defined in the source system.

The following UDF columns are populated by the ETL process supplied with Oracle Utilities Extractors and Schema.

•

UDF1 - City

•

UDF2 - County

•

UDF3 - Postal

•

UDF4 - State

•

UDF5 - Country

•

UDF6 - Geo Code

Properties

Property

Value

Target Table

CD_ADDR

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_PREM

Stage Table

STG_CD_ADDR

ODI Package

B1_PKG_CD_ADDR

ETL View

B1_D_ADDR_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

ADDR_KEY

Address Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_ADDR_SEQ.

SRC_ADDR_ID

CC&B Premise ID

CI_PREM.PREM_ID

ADDR_INFO

Address

CI_PREM.ADDRESS1

CI_PREM.CITY

CI_PREM.STATE

CI_PREM.POSTAL

Transformation Logic :

This field is populated with the Address Line1, City, State, and Postal details.

ADDR_LINE1

CC&B Address Line 1

CI_PREM.ADDRESS1

ADDR_LINE2

CC&B Address Line 2

CI_PREM.ADDRESS2

ADDR_LINE3

CC&B Address Line 3

CI_PREM.ADDRESS3

ADDR_LINE4

CC&B Address Line 4

CI_PREM.ADDRESS4

CITY

City

CI_PREM.CITY_UPR

COUNTY

County

CI_PREM.COUNTY

POSTAL

Postal Code

CI_PREM.POSTAL

STATE_CD

State Code

CI_PREM.STATE

STATE_DESCR

State

CI_STATE_L.DESCR

COUNTRY_CD

Country Code

CI_PREM.COUNTRY

COUNTRY_DESCR

Country

CI_COUNTRY_L.DESCR

GEO_CODE

Geographical Code

CI_PREM.GEO_CODE

CROSS_STREET

Cross Street

SUBURB

Suburb

UDF1_CD

City Code

CI_PREM.CITY_UPR

UDF1_DESCR

City

CI_PREM.CITY_UPR

UDF2_CD

County Code

CI_PREM.COUNTY

UDF2_DESCR

County

CI_PREM.COUNTY

UDF3_CD

Postal Code

CI_PREM.POSTAL

UDF3_DESCR

Postal Code

CI_PREM.POSTAL

UDF4_CD

State Code

CI_PREM.STATE

UDF4_DESCR

State

CI_PREM.DESCR

UDF5_CD

Country Code

CI_PREM.COUNTRY

UDF5_DESCR

Country

CI_COUNTRY_L.DESCR

UDF6_CD

Geo Code Value

CI_PREM.GEO_CODE

UDF6_DESCR

Geo Code

CI_PREM.GEO_CODE

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

UDF16_CD

User Defined Field 16 Code

UDF16_DESCR

User Defined Field 16 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Adjustment Type Dimension

The Adjustment Type dimension stores various adjustment types defined in the system.

Properties

Property

Value

Target Table

CD_ADJ_TYPE

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_ADJ_TYPE

Stage Table

STG_CD_ADJ_TYPE

ODI Package

B1_PKG_CD_ADJ_TYPE

ETL View

B1_D_ADJ_TYPE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

ADJ_TYPE_KEY

Adjustment Type Key

Transformation Logic :

This field is populated using the sequence from SPL_ADJ_TYPE_SEQ.

ADJ_TYPE_CD

Adjustment Type Code

CI_ADJ_TYPE.ADJ_TYPE_CD

ADJ_TYPE_DESCR

Adjustment Type Description

CI_ADJ_TYPE_L.DESCR

UDF1_CD

Account Payable Request Type Code

CI_ADJ_TYPE.AP_REQ_TYPE_CD

UDF1_DESCR

Account Payable Request Type

CI_APREQ_TYPE_L.DESCR

UDF2_CD

Distribution Code Value

CI_ADJ_TYPE.DST_ID

UDF2_DESCR

Distribution Code

CI_DST_CODE_L.DESCR

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Bill Cancel Reason Dimension

The Bill Cancel Reason dimension stores various possible reasons for bill cancellation.

Properties

Property

Value

Target Table

CD_BILL_CAN_RSN

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_BILL_CAN_RSN_L

Stage Table

STG_CD_BILL_CAN_RSN

ODI Package

B1_PKG_CD_BILL_CAN_RSN

ETL View

B1_D_BILL_CAN_RSN_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

BILL_CAN_RSN_KEY

Bill Cancel Reason Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from B1_BILL_CAN_RSN_SEQ.

BILL_CAN_RSN_CD

Bill Cancel Reason Code

CI_BILL_CAN_RSN_L.CAN_RSN_CD

BILL_CAN_RSN_DESCR

Description

CI_BILL_CAN_RSN_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Bill Cycle Schedule Dimension

The Bill Cycle Schedule dimension stores the bill cycle and its schedule information.

Properties

Property

Value

Target Table

CD_BILL_CYC_SCH

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_BILL_CYC_SCH

Stage Table

STG_CD_BILL_CYC_SCH

ODI Package

B1_PKG_CD_BILL_CYC_SCH

ETL View

B1_D_BILL_CYC_SCH_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

BILL_CYC_SCH_KEY

Bill Cycle Schedule Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_BILL_CYC_SCH_SEQ.

BILL_CYC_CD

Bill Cycle

CI_BILL_CYC_SCH.BILL_CYC_CD

BILL_CYC_DESCR

Bill Cycle Description

CI_BILL_CYC_L.DESCR

BILL_CYC_WIN_START_DT

Window Start Date

CI_BILL_CYC_SCH.WIN_START_DT

BILL_CYC_WIN_END_DT

Window End Date

CI_BILL_CYC_SCH.WIN_END_DT

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Bill Segment Status Dimension

The Bill Segment Status dimension stores the possible statuses of a bill segment.

Properties

Property

Value

Target Table

CD_BSEG_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_BSEG_STATUS

ODI Package

B1_PKG_CD_BSEG_STATUS

ETL View

B1_D_BSEG_STATUS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

BSEG_STATUS_KEY

Bill Segment Status Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_BSEG_STATUS_SEQ.

BSEG_STATUS_CD

Bill Segment Status Code

CI_LOOKUP_VAL_L.FIELD_VALUE

BSEG_STATUS_DESCR

Bill Segment Status Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Billing Day in Window Dimension

The Billing Day in Window dimension stores the age ranges indicating the day of the bill window when the bill segment was frozen. These age ranges are configured in the source system.

Properties

Property

Value

Target Table

CD_BILL_DAY_IN_WIN

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_BILL_DAY_IN_WIN

ODI Package

B1_PKG_CD_BILL_DAY_IN_WIN

ETL View

B1_D_BILL_DAY_IN_WIN_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

BILL_DAY_IN_WIN_KEY

Billing Day in Window Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_BILL_DAY_IN_WIN_SEQ.

BILL_DAY_IN_WIN_DESCR

Billing Day in Window Description

F1_BKT_CONFIG_VAL_L.DESCR

WIN_STATUS_CD

Window Status Code

F1_BKT_CONFIG.BKT_TYPE_CD

WIN_STATUS_DESCR

Windows Status Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

Note . If the override description is not present, regular description is used.

WIN_CATEGORY_CD

Window Category Code

F1_BKT_CONFIG_VAL.BKT_VAL_TYPE_CD

WIN_CATEGORY_DESCR

Window Category Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

RANGE_START

Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

RANGE_END

End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Note : The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run will not be captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (SA Billing Fact in this case), should be truncated and reloaded.

For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Campaign Dimension

The Campaign dimension stores the campaign programs designed to offer a set of packages to customers.

Properties

Property

Value

Target Table

CD_CAMPAIGN

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_CAMPAIGN

Stage Table

STG_CD_CAMPAIGN

ODI Package

B1_PKG_CD_CAMPAIGN

ETL View

B1_D_CAMPAIGN_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CAMPAIGN_KEY

Campaign Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_CAMPAIGN_SEQ.

CAMPAIGN_CD

Campaign Code

CI_CAMPAIGN.CAMPAIGN_CD

CAMPAIGN_DESCR

Description

CI_CAMPAIGN.DESCR

UDF1_CD

Campaign Status Code

CI_CAMPAIGN.CAMP_STATUS_FLG

UDF1_DESCR

Campaign Status Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Case Condition Dimension

The Case Condition dimension stores various possible states of a case.

Properties

Property

Value

Target Table

CD_CASE_COND

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_CASE_COND

ODI Package

B1_PKG_CD_CASE_COND

ETL View

B1_D_CASE_COND_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CASE_COND_KEY

Case Condition Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_CASECOND_SEQ.

CASE_COND_CD

Case Condition Code

CI_LOOKUP_VAL_L.FIELD_VALUE

CASE_COND_DESCR

Description

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

If an override description is not available, regular description is extracted.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Case Type Status Dimension

The Case Type Status dimension stores various possible case types and their states.

Properties

Property

Value

Target Table

CD_CASETYPE_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_CASE_STATUS

Stage Table

STG_CD_CASETYPE_STATUS

ODI Package

B1_PKG_CD_CASETYPE_STATUS

ETL View

B1_D_CASETYPE_STATUS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CASETY_STAT_KEY

Case Type Status Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_CASETY_STAT_SEQ.

CASE_TYPE_CD

Case Type Code

CI_CASE_TYPE.CASE_TYPE_CD

CASE_TYPE_DESCR

Case Type Description

CI_CASE_TYPE_L.DESCR

CASE_STATUS_CD

Case Status Code

CI_CASE_STATUS.CASE_STATUS_CD

CASE_STATUS_DESCR

Case Status Description

CI_CASE_STATUS_L.STATUS_LBL

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Collectible Process Status Dimension

The Collectible Process Status dimension stores various possible states of the collection process/overdue process defined in a lookup.

Properties

Property

Value

Target Table

CD_COLLPROC_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_COLLPROC_STATUS

ODI Package

B1_PKG_CD_COLLPROC_STATUS

ETL View

B1_D_COLLPROC_STATUS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

COLLPROC_STAT_KEY

Collectible Process Status Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_COLLPROC_STATUS_SEQ.

COLPROC_STAT_CD

Collectible Process Code

CI_LOOKUP_VAL_L.FIELD_VALUE

COLPROC_STAT_DESCR

Collectible Process Description

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

If an override description is not available, regular description is extracted.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Collectible Process Template Dimension

The Collectible Process Template dimension stores both collection process templates and overdue process templates from the source system.

Properties

Property

Value

Target Table

CD_COLLPROC_TMPL

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_COLL_PROC_TM/CI_OD_PROC_TMP

Stage Table

STG_CD_COLLPROC_TMPL

ODI Package

B1_PKG_CD_COLLPROC_TMPL

ETL View

B1_D_COLLPROC_TMPL_VW

Fields

Source 1 - Collection Process Template (CI_COLL_PROC_TM)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

COLL_TMPL_KEY

Collection Process Template Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_COLLTMPL_SEQ.

COLL_TMPL_CD

Collection Process Template Code

CI_COLL_PROC_TM.COLL_PROC_TMPL_CD

Transformation Logic:

The collection process template retrieved is prefixed with ‘C_’.

COLL_TMPL_DESCR

Collection Process Template Description

CI_COLL_PROC_TM_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Source 2 - Overdue Process Template (CI_OD_PROC_TMP)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

COLL_TMPL_KEY

Collection Process Template Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_COLLTMPL_SEQ.

COLL_TMPL_CD

Collection Process Template Code

CI_OD_PROC_TMP.OD_PROC_TMPL_CD

Transformation Logic:

The template code retrieved is prefixed with ‘OD_’.

COLL_TMPL_DESCR

Collection Process Template Description

CI_OD_PROC_TMP_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Collection Event Type Dimension

The Collection Event Type dimension stores various types of collection events, cut events, severance events, and overdue events.

Properties

Property

Value

Target Table

CD_COLLEVT_TYP

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_COLL_EVT_TYP

CI_OD_EVT_TYPE

CI_CUT_EVT_TYPE

CI_SEV_EVT_TYPE

Stage Table

STG_CD_COLLEVT_TYP

ODI Package

B1_PKG_CD_COLLEVT_TYP

ETL View

B1_D_COLLEVT_TYP_VW

B1_D_ODEVT_TYP_VW

B1_D_CUTEVT_TYP_VW

B1_D_SEVEVT_TYP_VW

Fields

Source 1 - Collection Event Type (CI_COLL_EVT_TYP)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CEVT_TYPE_KEY

Collection Event Type Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_COLLEVT_TY_SEQ.

CEVT_TYPE_CD

Collection Event Type Code

CI_COLL_EVT_TYP.COLL_EVT_TYP_CD

Transformation Logic :

This field is populated with the collection event type code prefixed with ‘C_’.

CEVT_TYPE_DESCR

Collection Event Type Description

CI_COLL_EVT_TYP_L.DESCR

CEVT_TY_FLG_CD

Collection Event Type Flag Code

CI_COLL_EVT_TYP.COLL_EVT_TYPE_FLG

CEVT_TY_FLG_DESCR

Collection Event Type Flag Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

CUST_EVT_CD

Customer Event Code

CI_LOOKUP_VAL_L.FIELD_VALUE

CUST_EVT_DESCR

Customer Event Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Source 2 - Overdue Event Type (CI_OD_EVT_TYPE)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CEVT_TYPE_KEY

Collection Event Type Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_COLLEVT_TY_SEQ.

CEVT_TYPE_CD

Collection Event Type Code

CI_OD_EVT_TYPE.OD_EVT_TYPE_CD

Transformation Logic :

The Template Code from Oracle Utilities Customer Care and Billing is prefixed with "OD_".

CEVT_TYPE_DESCR

Collection Event Type Description

CI_OD_EVT_TYPE_L.DESCR

CEVT_TY_FLG_CD

Collection Event Type Flag Code

CEVT_TY_FLG_DESCR

Collection Event Type Flag Description

CUST_EVT_CD

Customer Event Code

CUST_EVT_DESCR

Customer Event Description

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Source 3 - Cut Event Type (CI_CUT_EVT_TYPE)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CEVT_TYPE_KEY

Collection Event Type Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_COLLEVT_TY_SEQ.

CEVT_TYPE_CD

Collection Event Type Code

CI_CUT_EVT_TYPE.CUT_EVT_TYPE_CD

Transformation Logic :

The Template Code from Oracle Utilities Customer Care and Billing is prefixed with "CUT_".

CEVT_TYPE_DESCR

Collection Event Type Description

CI_CUT_EVT_TYPE_L.DESCR

CEVT_TY_FLG_CD

Collection Event Type Flag Code

CEVT_TY_FLG_DESCR

Collection Event Type Flag Description

CUST_EVT_CD

Customer Event Code

CUST_EVT_DESCR

Customer Event Description

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Source 4 - Severance Event Type (CI_SEV_EVT_TYPE)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CEVT_TYPE_KEY

Collection Event Type Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_COLLEVT_TY_SEQ.

CEVT_TYPE_CD

Collection Event Type Code

CI_SEV_EVT_TYPE.SEV_EVT_TYPE_CD

Transformation Logic :

The Template Code from Oracle Utilities Customer Care and Billing is prefixed with "S_".

CEVT_TYPE_DESCR

Collection Event Type Description

CI_SEV_EVT_TYPE_L.DESCR

CEVT_TY_FLG_CD

Collection Event Type Flag Code

CI_SEV_EVT_TYPE.SEV_EVT_TYPE_FLG

CEVT_TY_FLG_DESCR

Collection Event Type Flag Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

CUST_EVT_CD

Customer Event Code

CI_LOOKUP_VAL_L.FIELD_VALUE

CUST_EVT_DESCR

Customer Event Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Customer Contact Type Dimension

The Customer Contact Type dimension stores various types of customer contacts.

Properties

Property

Value

Target Table

CD_CC_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_CC_TYPE

Stage Table

STG_CD_CC_TYPE

ODI Package

B1_PKG_CD_CC_TYPE

ETL View

B1_D_CC_TYPE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CC_TYPE_KEY

Customer Contact Dimension Surrogate key

Transformation Logic :

This field is populated with the sequence from SPL_CC_TYPE_SEQ.

CC_TYPE_CD

Customer Contact Type Code

CI_CC_TYPE.CC_TYPE_CD

CC_TYPE_DESCR

Contact Type

CI_CC_TYPE_L.DESCR

CC_CL_CD

Customer Contact Class Code

CI_CC_CL_L.CC_CL_CD

CC_CL_DESCR

Contact Class

CI_CC_CL_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Date Dimension

The Date dimension holds the date information. It is unique in the sense that it is populated by an ODI package, based on the variables configured in the package.

Properties

Property

Value

Target Table

CD_DATE

Table Type

Dimension

SCD Type

Source System Driver Table

Stage Table

STG_CD_DATE

ODI Package

B1_PKG_CD_DATE

ETL View

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

DATE_KEY

CAL_DT

Calendar Date

DAY_NBR_IN_MONTH

Day in Calendar Month(1-31)

DAY_NBR_IN_WEEK

Day Number in Week (1-7)

DAY_NBR_IN_YEAR

Day Number in Year (1-366)

WORK_DAY_IND

Work Day Indicator

ABS_MONTH_NBR

Absolute Month Number

CAL_MONTH_NBR

Calendar Month Number (1-12)

MONTH_END_DT

Month End Date

ABS_QTR_NBR

Absolute Quarter Number

CAL_QTR_NBR

Calendar Quarter Number (1-4)

QTR_END_DT

Quarter End Date

ABS_WEEK_NBR

Absolute Week Number

CAL_WEEK_NBR

Calendar Week Number (1-53)

WEEK_END_DT

Week End Date

CAL_YEAR

Calendar Year

YEAR_END_DT

Year End Date

UDF1_CD

Day of Week Code

UDF1_DESCR

Day of Week

UDF2_CD

Calendar Quarter Code

UDF2_DESCR

Calendar Quarter

UDF3_CD

Season Code

UDF3_DESCR

Season

UDF4_CD

Workday Code

UDF4_DESCR

Workday

UDF5_CD

Calendar Month Code

UDF5_DESCR

Calendar Month

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

Days of Unbilled Usage Dimension

The Days of Unbilled Usage dimension stores the age ranges indicating the number of days that a service agreement should have been actually billed for, but is not. These age ranges are configured in the source system.

Properties

Property

Value

Target Table

CD_DAYS_UNBILLED_USG

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_DAYS_UNBILLED_USG

ODI Package

B1_PKG_CD_DAYS_UNBILLED_USG

ETL View

B1_D_DAYS_UNBILLED_USG_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

DAYS_UNBILLED_USG_KEY

Days of Unbilled Usage Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_DAYS_UNBILLED_USG_SEQ.

DAYS_UNBILLED_USG_DESCR

Days of Unbilled Usage Description

F1_BKT_CONFIG_VAL_L.DESCR

RANGE_START

Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

RANGE_END

End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Note : The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run will not be captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (Arrears Snapshot Fact in this case), should be truncated and reloaded.

For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Days Since Last Frozen BS Dimension

The Days Since Last Frozen BS dimension stores the age ranges indicating the number of days since a service agreement had a frozen bill segment. These age ranges are configured in the source system.

Properties

Property

Value

Target Table

CD_DAYS_LAST_FRZ_BS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_DAYS_LAST_FRZ_BS

ODI Package

B1_PKG_CD_DAYS_LAST_FRZ_BS

ETL View

B1_D_DAYS_LAST_FRZ_BS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

DAYS_LAST_FRZ_BS_KEY

Days Since Last Frozen Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_DAYS_LAST_FRZ_BS_SEQ.

DAYS_LAST_FRZ_BS_DESCR

Days Since Last Frozen BS Bucket Description

F1_BKT_CONFIG_VAL_L.DESCR

RANGE_START

Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

RANGE_END

End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Note : The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run will not be captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (Arrears Snapshot Fact in this case), should be truncated and reloaded.

For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Days to Window Closure Dimension

The Days to Window Closure dimension stores the age ranges indicating the number of days left before bill window closure. These age ranges are configured in the source system.

Properties

Property

Value

Target Table

CD_DAYS_TO_WIN_CLS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_DAYS_TO_WIN_CLS

ODI Package

B1_PKG_CD_DAYS_TO_WIN_CLS

ETL View

B1_D_DAYS_TO_WIN_CLS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

DAYS_TO_WIN_CLS_KEY

Days to Window Closure Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_DAYS_TO_WIN_CLS_SEQ.

DAYS_TO_WIN_CLS_DESCR

Days to Window Closure Bucket Description

F1_BKT_CONFIG_VAL_L.DESCR

WIN_CLOSURE_STATE_CD

Window Closure State Code

F1_BKT_CONFIG.BKT_TYPE_CD

WIN_CLOSURE_STATE_DESCR

Window Closure State Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

RANGE_START

Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

RANGE_END

End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Note : The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run will not be captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (SA Billing Fact in this case), should be truncated and reloaded.

For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Fiscal Period Dimension

The Fiscal Period dimension stores the accounting periods defined in the source system.

Properties

Property

Value

Target Table

CD_FISCAL_CAL

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_CAL_PERIOD

Stage Table

STG_CD_FISCAL_CAL

ODI Package

B1_PKG_CD_FISCAL_CAL

ETL View

B1_D_FISCAL_CAL_VW

Fields

Target Field

OBIEE Field

Source Field

FISCAL_CAL_KEY

Fiscal Period Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_FISCAL_SEQ.

FISCAL_CAL_CD

Fiscal Calendar Code

CI_CAL_PERIOD.CALENDAR_ID

FISCAL_CAL_DESCR

Fiscal Calendar Description

CI_CAL_GL_L.DESCR

ABS_PERIOD_NBR

Absolute Period Number

Transformation Logic :

This field increments the sequence for each period within the calendar code.

For example: The first period in the calendar code C1 starts with 1.

FISCAL_YEAR

Fiscal Year

CI_CAL_PERIOD.FISCAL_YEAR

PERIOD_NBR

Fiscal Period Code

CI_CAL_PERIOD.ACCOUNTING_PERIOD

PERIOD_DESCR

Fiscal Period Description

CI_CAL_PERIOD_L.PERIOD_DESCR

PERIOD_START_DT

Fiscal Period Start Date

CI_CAL_PERIOD.BEGIN_DT

PERIOD_END_DT

Fiscal Period End Date

CI_CAL_PERIOD.END_DT

UDF1_CD

Fiscal Quarter Number Code

UDF1_DESCR

Fiscal Quarter Number Description

UDF2_CD

Fiscal Quarter Code

UDF2_DESCR

Fiscal Quarter Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Financial Transaction Type Dimension

The Financial Transaction Type dimension stores the financial transaction types defined in a standard lookup in the source system.

Properties

Property

Value

Target Table

CD_FT_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_FT_TYPE

ODI Package

B1_PKG_CD_FT_TYPE

ETL View

B1_D_FT_TYPE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

FT_TYPE_KEY

FT Type Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_FT_TYPE_SEQ.

FT_TYPE_CD

FT Type Code

CI_LOOKUP_VAL_L.FIELD_VALUE

FT_TYPE_DESCR

Description

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

If an override description is not available, regular description is extracted.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

General Ledger Dimension

The General Ledger dimension stores the general ledger account details.

Properties

Property

Value

Target Table

CD_GL_ACCT

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_FT_GL

Stage Table

STG_CD_GL_ACCT

ODI Package

B1_PKG_CD_GL_ACCT

ETL View

B1_D_GL_ACCT_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

GL_ACCT_KEY

GL Account Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_GL_ACCT_SEQ.

SRC_GL_ACCT_ID

General Ledger Account ID (Natural Key)

CI_FT_GL.GL_ACCT

Transformation Logic :

This field fetches the distinct non-blank general ledger accounts and trims any extra spaces.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Installments Count Dimension

The Installments Count dimension stores the age ranges indicating the installment counts for payment arrangements. These age ranges are configured in the source system.

Properties

Property

Value

Target Table

CD_INSTALLMENT_CNT

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_INSTALLMENT_CNT

ODI Package

B1_PKG_CD_INSTALLMENT_CNT

ETL View

B1_D_INSTALLMENT_CNT_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

INSTALLMENT_CNT_KEY

Installments Count Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_INSTALLMENT_CNT_SEQ.

INSTALLMENT_CNT_DESCR

Installments Count Description

F1_BKT_CONFIG_VAL_L.DESCR

F1_BKT_CONFIG.BKT_TYPE_CD

RANGE_START

Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

RANGE_END

End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Note : The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run are not captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (Payment Arrangement Accumulation/ Snapshot Facts in this case), should be truncated and reloaded.

For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Measurement Type Dimension

The Measurement Type dimension stores the type of usage (scalar, interval, both, or N/A) for the bill segment.

Properties

Property

Value

Target Table

CD_MSRMT_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_MSRMT_TYPE

ODI Package

B1_PKG_CD_MSRMT_TYPE

ETL View

B1_MSRMT_TYPE_SEQ

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

MSRMT_TYPE_KEY

Measurement Type Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_MSRMT_TYPE_SEQ.

MSRMT_TYPE_CD

Measurement Type Code

CI_LOOKUP_VAL_L.FIELD_VALUE

MSRMT_TYPE_DESCR

Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

This field is populated with the ODI job execution session number.

Order Cancel Reason Dimension

The Order Cancel Reason dimension stores the possible reasons for an order cancellation.

Properties

Property

Value

Target Table

CD_ORDER_CAN_RSN

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_ENRL_CAN_RSN_L

Stage Table

STG_CD_ORDER_CAN_RSN

ODI Package

B1_PKG_CD_ORDER_CAN_RSN

ETL View

B1_D_ORDER_CAN_RSN_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

ORDER_CAN_RSN_KEY

Order Cancel Reason Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_ORDER_CAN_RSN_SEQ.

ORDER_CAN_RSN_CD

Order Cancel Reason Code

CI_ENRL_CAN_RSN_L.ENRL_CAN_RSN_CD

ORDER_CAN_RSN_DESCR

Description

CI_ENRL_CAN_RSN_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Order Status Dimension

The Order Status dimension stores the possible states of an enrollment order.

Properties

Property

Value

Target Table

CD_ORDER_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_ORDER_STATUS

ODI Package

B1_PKG_CD_ORDER_STATUS

ETL View

B1_D_ORDER_STATUS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

ORDER_STATUS_KEY

Order Status Dimension Surrogate Key

Transformation Logic :

This field populates the sequence from SPL_ORDER_STATUS_SEQ.

ORDER_STATUS_CD

Order Status Code

CI_LOOKUP_VAL_L.FIELD_VALUE

ORDER_STATUS_DESCR

Description

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

If an override description is not available, regular description is extracted.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic :

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic :

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic :

This field is populated with the ODI job execution session number.

Package Dimension

The Package dimension stores information about the goods and services offered to a customer or a prospect.

Properties

Property

Value

Target Table

CD_PKG

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_PKG_L

Stage Table

STG_CD_PKG

ODI Package

B1_PKG_CD_PKG

ETL View

B1_D_PKG_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PKG_KEY

Package Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_PKG_SEQ.

PKG_CD

Package Code

CI_PKG_L.PACKAGE_ID

PKG_DESCR

Description

CI_PKG_L.DESCR

Transformation Logic:

This field’s description is based on CI_PKG_L.PACKAGE_ID.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Pay Method Dimension

The Pay Method dimension stores various methods of payment.

Properties

Property

Value

Target Table

CD_PAY_METHOD

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_PAY_METH

Stage Table

STG_CD_PAY_METHOD

ODI Package

B1_PKG_CD_PAY_METHOD

ETL View

B1_D_PAY_METHOD_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PAY_METHOD_KEY

Pay Method Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from B1_PAY_METH_SEQ.

PAY_METHOD_CD

Pay Method Code

CI_PAY_METH.PAY_METH_CD

PAY_METHOD_DESCR

Description

CI_PAY_METH_L.DESCR

APAY_IND

Auto Pay Indicator

CI_PAY_METH.APAY_SW

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Pay Plan Status Dimension

The Pay Plan Status dimension stores all possible statuses of a pay plan.

Properties

Property

Value

Target Table

CD_PAY_PLAN_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_PAY_PLAN_STATUS

ODI Package

B1_PKG_CD_PAY_PLAN_STATUS

ETL View

B1_D_PAY_PLAN_STATUS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PAY_PLAN_STATUS_KEY

Pay Plan Status Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from B1_PAY_PLAN_STATUS_SEQ.

PAY_PLAN_STATUS_CD

Pay Plan Status Code

CI_LOOKUP_VAL_L.FIELD_VALUE

PAY_PLAN_STATUS_DESCR

Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Pay Plan Type Dimension

The Pay Plan Type dimension stores the information about types of pay plans and their respective debt class.

Properties

Property

Value

Target Table

CD_PAY_PLAN_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_PP_TYPE

Stage Table

STG_CD_PAY_PLAN_TYPE

ODI Package

B1_PKG_CD_PAY_PLAN_TYPE

ETL View

B1_D_PAY_PLAN_TYPE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PAY_PLAN_TYPE_KEY

Play Plan Type Dimension Surrogate Key

Transformation Logic:

This field populates the sequence from B1_PAY_PLAN_TYPE_SEQ.

PAY_PLAN_TYPE_CD

Play Plan Type Code

CI_PP_TYPE.PP_TYPE_CD

PAY_PLAN_TYPE_DESCR

Description

CI_PP_TYPE_L.DESCR

DEBT_CL_CD

Debt class Code

CI_PP_TYPE.DEBT_CL_CD

DEBT_CL_DESCR

Debt Class Description

CI_DEBT_CL_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Payment Arrangement Status Dimension

The Payment Arrangement Status dimension stores various possible states of a payment arrangement.

Properties

Property

Value

Target Table

CD_PA_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_PA_STATUS

ODI Package

B1_PKG_CD_PA_STATUS

ETL View

B1_D_PA_STATUS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PA_STATUS_KEY

Payment Arrangement Status Dimension Surrogate Key

Transformation Logic:

This field is populated by the sequence from B1_PA_STATUS_SEQ.

PA_STATUS_CD

Payment Arrangement Status Code

CI_LOOKUP_VAL_L.FIELD_VALUE

PA_STATUS_DESCR

Payment Arrangement Status Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Payment Cancel Reason Dimension

The Payment Cancel Reason dimension stores various possible reasons for payment cancellation.

Properties

Property

Value

Target Table

CD_PAY_CAN_RSN

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_PAY_CAN_RSN

Stage Table

STG_CD_PAY_CAN_RSN

ODI Package

B1_PKG_CD_PAY_CAN_RSN

ETL View

B1_D_PAY_CAN_RSN_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PAY_CAN_RSN_KEY

Payment Cancel Reason Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_PAY_CAN_RSN_SEQ.

PAY_CAN_RSN_CD

Payment Cancel Reason Code

CI_PAY_CAN_RSN.CAN_RSN_CD

PAY_CAN_RSN_DESCR

Description

CI_PAY_CAN_RSN_L.DESCR

UDF1_CD

NSF Charge Flag Code

CI_PAY_CAN_RSN.NSF_CHARGE_SW

UDF1_DESCR

NSF Charge Flag

CI_MSG_L.MESSAGE_TEXT_OVRD

CI_MSG_L.MESSAGE_TEXT

Transformation Logic:

This field is retrieved based on the NSF charge switch CI_PAY_CAN_RSN.NSF_CHARGE_SW.

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Rate Dimension

The Rate dimension stores the rate component information from the source system.

Properties

Property

Value

Target Table

CD_RATE

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_RS

Stage Table

STG_CD_RATE

ODI Package

B1_PKG_CD_RATE

ETL View

B1_D_RATE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

RATE_KEY

Rate Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_RATE_SEQ.

RATE_SCHED_CD

Rate Code

CI_RS.RS_CD

RATE_SCHED_DESCR

Rate Code Description

CI_RS_L.DESCR

UDF1_CD

Service Type Code

CI_RS.SVC_TYPE_CD

UDF1_DESCR

Service Type Description

CI_SVC_TYP_L.DESCR

UDF2_CD

Frequency Code

CI_RS.FREQ_CD

UDF2_DESCR

Frequency Description

CI_FREQ_L.DESCR

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Recurring Charge Amounts Dimension

The Recurring Charge Amounts dimension stores the age ranges indicating the recurring charge amount ranges of payment arrangement. These age ranges are configured in the source system.

Properties

Property

Value

Target Table

CD_REC_CHARGE_AMOUNT

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_REC_CHARGE_AMOUNT

ODI Package

B1_PKG_CD_REC_CHARGE_AMOUNT

ETL View

B1_D_REC_CHARGE_AMOUNT_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

REC_CHARGE_AMOUNT_KEY

Recurring Charge Amount Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from B1_REC_CHARGE_AMOUNT_SEQ.

REC_CHARGE_AMOUNT_DESCR

Recurring Charge Amount Description

F1_BKT_CONFIG_VAL_L.DESCR

F1_BKT_CONFIG.BKT_TYPE_CD

RANGE_START

Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

RANGE_END

End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Note : The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run will not be captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (Payment Arrangement Accumulation/ Snapshot Facts in this case), should be truncated and reloaded.

For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Service Agreement Status Dimension

The Service Agreement Status dimension stores various possible statuses of the service agreement defined in a lookup.

Properties

Property

Value

Target Table

CD_SA_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_SA_STATUS

ODI Package

B1_PKG_CD_SA_STATUS

ETL View

B1_D_SA_STATUS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

SA_STATUS_KEY

Service Agreement Status Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_SA_STATUS_SEQ.

SA_STATUS_CD

Service Agreement Status Code

CI_LOOKUP_VAL_L.FIELD_VALUE

SA_STATUS_DESCR

Service Agreement Status Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Service Quantity Identifier Dimension

The Service Quantity Identifier Dimension stores the service quantity identifiers defined in the source system.

Properties

Property

Value

Target Table

CD_SQI

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_SQI_L

Stage Table

STG_CD_SQI

ODI Package

B1_PKG_CD_SQI

ETL View

B1_D_SQI_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

SQI_KEY

Service Quantity Identifier Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_SQI_SEQ.

SQI_CD

Service Quantity Identifier Code

CI_SQI_L.SQI_CD

SQI_DESCR

Service Quantity Identifier Description

CI_SQI_L.DESCR

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Tender Source Dimension

The Tender Source dimension stores the details of various tender sources.

Properties

Property

Value

Target Table

CD_TNDR_SRCE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_TNDR_SRCE

Stage Table

STG_CD_TNDR_SRCE

ODI Package

B1_PKG_CD_TNDR_SRCE

ETL View

B1_D_TNDR_SRCE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TNDR_SRCE_KEY

Tender Source Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_TNDR_SRCE_SEQ.

TNDR_SOURCE_CD

Tender Source Code

CI_TNDR_SRCE.TNDR_SOURCE_CD

TNDR_SOURCE_DESCR

Description

CI_TNDR_SRCE_L.DESCR

TNDR_SOURCE_TYPE_CD

Tender Source Type Code

CI_TNDR_SRCE.TNDR_SRCE_TYPE_FLG

TNDR_SOURCE_TYPE_DESCR

Tender Source Type Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Tender Status Dimension

The Tender Status dimension stores the various pay tender statuses.

Properties

Property

Value

Target Table

CD_TNDR_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_TNDR_STATUS

ODI Package

B1_PKG_CD_TNDR_STATUS

ETL View

B1_D_TNDR_STATUS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TNDR_STATUS_KEY

Tender Status Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_TNDR_STATUS_SEQ.

TNDR_STATUS_CD

Tender Status Code

CI_LOOKUP_VAL_L.FIELD_VALUE

TNDR_STATUS_DESCR

Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

If an override description is not available, regular description is extracted.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Tender Type Dimension

The Tender Type dimension stores the various tender types.

Properties

Property

Value

Target Table

CD_TNDR_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_TENDER_TYPE_L

Stage Table

STG_CD_TNDR_TYPE

ODI Package

B1_PKG_CD_TNDR_TYPE

ETL View

B1_D_TNDR_TYPE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TNDR_TYPE_KEY

Tender Type Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_TNDR_TYPE_SEQ.

TNDR_TYPE_CD

Tender Type Code

CI_TENDER_TYPE_L.TENDER_TYPE_CD

TNDR_TYPE_DESCR

Description

CI_TENDER_TYPE_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Time Dimension

The Time dimension holds the time details. It is unique in the sense that it is populated by an ODI package, based on the variables configured in the package.

Properties

Property

Value

Target Table

CD_TIME

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

Stage Table

STG_CD_TIME

ODI Package

B1_PKG_CD_TIME

ETL View

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TIME_KEY

Time Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_TIME_SEQ.

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

SRC_TIME

Time (Natural Key)

AM_IND

AM Indicator

HOUR

Hour

MINUTE

Minute

SECOND

Second

UDF1_CD

Hour Code

UDF1_DESCR

Hour

UDF2_CD

15 Minute Interval Code

UDF2_DESCR

15 Minute Interval

UDF3_CD

5 Minute Interval Code

UDF3_DESCR

5 Minute Interval

UDF4_CD

Time of Day Code

UDF4_DESCR

Time of Day

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Time of Use Dimension

The Time of Use dimension stores the information about various times of use defined in the source system.

Properties

Property

Value

Target Table

CD_TOU

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_TOU

Stage Table

STG_CD_TOU

ODI Package

B1_PKG_CD_TOU

ETL View

B1_D_TOU_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TOU_KEY

Time of Use Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_TOU_SEQ.

TOU_CD

Time of Use Code

CI_TOU_L.TOU_CD

TOU_DESCR

Time of Use Description

CI_TOU_L.DESCR

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Uncollectible Event Type Dimension

The Uncollectible Event Type dimension stores the types of write-off events as defined in the source system.

Properties

Property

Value

Target Table

CD_UCOLEVT_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_WO_EVT_TYP

Stage Table

STG_CD_UCOLEVT_TYPE

ODI Package

B1_PKG_CD_UCOLEVT_TYPE

ETL View

B1_D_UCOLEVT_TYPE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

UCEVT_TYPE_KEY

Uncollection Event Type Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_UCEVT_TYPE_SEQ.

UCEVT_TYPE_CD

Uncollection Event Type Code

CI_WO_EVT_TYP.WO_EVT_TYP_CD

UCEVT_TYPE_DESCR

Uncollection Event Type Description

CI_WO_EVT_TYP_L.DESCR

UCEVT_TY_FLG_CD

Uncollection Event Type Flag Code

CI_WO_EVT_TYP.WO_EVT_TYPE_FLG

UCEVT_TY_FLG_DESCR

Collection Event Type Flag Description

CI_LOOKUP_VAL_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Uncollectible Process Status Dimension

The Uncollectible Process Status dimension stores various possible states of a write-off process in the source system.

Properties

Property

Value

Target Table

CD_UCOLPROC_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_UCOLPROC_STATUS

ODI Package

B1_PKG_CD_UCOLPROC_STATUS

ETL View

B1_D_UCOLPROC_STATUS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

UCPROC_STAT_KEY

Uncollectible Process Status Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_UCOLPROC_STATUS_SEQ.

UCPROC_STAT_CD

Uncollectible Process Code

CI_LOOKUP_VAL_L.FIELD_VALUE

UCPROC_STAT_DESCR

Uncollectible Process Description

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

If an override description is not available, regular description is extracted.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Uncollectible Process Template Dimension

The Uncollectible Process Template dimension stores the write-off process template details from the source system.

Properties

Property

Value

Target Table

CD_UCOLPROC_TMPL

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_WO_PROC_TMPL

Stage Table

STG_CD_UCOLPROC_TMPL

ODI Package

B1_PKG_CD_UCOLPROC_TMPL

ETL View

B1_D_UCOLPROC_TMPL_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

UCPROC_TMPL_KEY

Uncollection Process Template Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_UNCOLLTMPL_SEQ.

UCPROC_TMPL_CD

Uncollection Process Template Code

CI_WO_PROC_TMPL.WO_PROC_TMPL_CD

UCPROC_TMPL_DESCR

Uncollection Process Template Description

CI_WO_PROC_TMPL_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

EFF_END_DTTM

Effective End Date/Time

EFF_START_DTTM

Effective Start Date/Time

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Unit of Measure Dimension

The Unit of Measure dimension stores various units of measure defined in the source system.

Properties

Property

Value

Target Table

CD_UOM

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_UOM

Stage Table

STG_CD_UOM

ODI Package

B1_PKG_CD_UOM

ETL View

B1_D_UOM_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

UOM_KEY

Unit of Measure Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_UOM_SEQ.

UOM_CD

Unit of Measure Code

CI_UOM.UOM_CD

UOM_DESCR

Unit of Measure Description

CI_UOM_L.DESCR

MEAS_PEAK_IND

Measure Peak Indicator

CI_UOM.MSR_PEAK_QTY_SW

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Message Dimension

The Message dimension stores all messages and message category details as defined in the source system.

Properties

Property

Value

Target Table

CD_MSG

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_MSG_L

Stage Table

STG_CD_MSG

ODI Package

B1_PKG_CD_MSG

ETL View

B1_D_MSG_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

MSG_KEY

Message Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from OUBI_MSG_SEQ.

MSG_CD

Message Code (Natural Key)

CI_MSG_L.MESSAGE_CAT_NBR

CI_MSG_L.MESSAGE_NBR

Transformation Logic:

The message category and number are converted to be 5 digit wide by prefixing with zeros when they are short and then concatenated. This ensures the uniqueness of the concatenated value.

MSG_DESCR

Message Description

CI_MSG_L.MESSAGE_TEXT

CI_MSG_L.MESSAGE_TEXT_OVRD

If an override description is not available, regular description will be used.

MSG_TYPE_CD

Message Type Code

CI_MSG_L.MESSAGE_CAT_NBR

MSG_TYPE_DESCR

Message Type Description

CI_MSG_CATEGORY_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDL1_CD

User Defined Language 1 Code

UDL1_DESCR

User Defined Language 1 Description

UDL2_CD

User Defined Language 2 Code

UDL2_DESCR

User Defined Language 2 Description

UDL3_CD

User Defined Language 3 Code

UDL3_DESCR

User Defined Language 3 Description

UDL4_CD

User Defined Language 4 Code

UDL4_DESCR

User Defined Language 4 Description

UDL5_CD

User Defined Language 5 Code

UDL5_DESCR

User Defined Language 5 Description

COMMENT1

Comments 1

COMMENT2

Comments 2

COMMENT3

Comments 3

COMMENT4

Comments 4

COMMENT5

Comments 5

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Note :

•

The fields CROSS_STREET and SUBURB are not populated in the dimension for the Oracle Utilities Customer care and Billing source system. Address is a shared dimension and these fields are populated by other source systems.

•

In the previous releases (v2.4.1 or earlier), UDF fields were populated with the City, County, Postal, State, Country, and Geo Code information. At a later release, named columns have been introduced in the Address dimension for these attributes. But, the base product ETL processes still populate these details in the UDF fields, as well for backward compatibility purposes.

Person Dimension

The Person dimension stores person related attributes from the source system.

Properties

Property

Value

Target Table

CD_PER

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_PER

Stage Table

STG_CD_PER

ODI Package

B1_PKG_CD_PER

ETL View

B1_D_PER_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PER_KEY

Person Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_PER_SEQ.

SRC_PER_ID

Person ID (Natural Key)

CI_PER.PER_ID

PER_INFO

Customer Information

CI_PER_NAME.ENTITY_NAME

Transformation Logic:

This field is populated with the primary name of the customer.

PER_NAME

Customer Name

CI_PER_NAME.ENTITY_NAME

Transformation Logic:

This field is populated with the primary name of the customer.

PER_PHONE_NBR

Customer Phone Number

CI_PER_PHONE.PHONE

CI_PER_PHONE.EXTENSION

Transformation Logic:

This field is populated with the primary phone number of the customer, which is concatenated as Phone 'x' Extension.

BUSINESS_IND

Person/Business Indicator

CI_PER.PER_OR_BUS_FLG

Transformation Logic:

This field is populated with ‘1’ when the person flag is "B". It is populated with ‘0’ when the person flag is "P".

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Premise Dimension

The Premise dimension stores premise related attributes from the premises defined in the source system.

The following UDF columns are populated by the ETL process supplied with the product.

•

UDF1 - Jurisdiction

•

UDF2 - Premise Type

•

UDF3 - Life Support Flag

•

UDF4 - Trend Area

•

UDF5 - In City Limit

Properties

Property

Value

Target Table

CD_PREM

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_PREM

Stage Table

STG_CD_PREM

ODI Package

B1_PKG_CD_PREM

ETL View

B1_D_PREM_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PREM_KEY

Premise Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_PREM_SEQ.

SRC_PREM_ID

Premise ID (Natural Key)

CI_PREM.PREM_ID

PREM_INFO

Premise Information

CI_PREM.ADDRESS1

CI_PREM.CITY

CI_PREM.STATE

CI_PREM.POSTAL

CI_PREM_TYPE_L.DESCR

CI_PREM.PREM_ID

Transformation Logic:

The fields will be concatenated as Address Line 1, City, State, Postal, Premise Type, Premise ID.

UDF1_CD

Jurisdiction Code

CI_PREM.CIS_DIVISION

UDF1_DESCR

Jurisdiction

CI_CIS_DIVISION_L.DESCR

UDF2_CD

Premise Type Code

CI_PREM.PREM_TYPE_CD

UDF2_DESCR

Premise Type

CI_PREM_TYPE_L.DESCR

UDF3_CD

Life Support Flag Code

CI_PREM.LS_SL_FLG

UDF3_DESCR

Life Support Flag

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

Note :

Lookup Name - LS_SL_FLG. If the override description is not available, the regular description is used.

UDF4_CD

Trend Area Code

CI_PREM.TREND_AREA_CD

UDF4_DESCR

Trend Area

CI_TREND_AREA_L.DESCR

UDF5_CD

In City Limit Code

CI_PREM.IN_CITY_LIMIT

UDF5_DESCR

In City Limit

CI_PREM.IN_CITY_LIMIT

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

UDF16_CD

User Defined Field 16 Code

UDF16_DESCR

User Defined Field 16 Description

UDF17_CD

User Defined Field 17 Code

UDF17_DESCR

User Defined Field 17 Description

UDF18_CD

User Defined Field 18 Code

UDF18_DESCR

User Defined Field 18 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Service Agreement Dimension

The Service Agreement dimension stores information about all service agreements defined in the source system.

The following UDF columns are populated by the ETL process supplied with the product.

•

UDF1 - Service Type

•

UDF2 - CIS Division

•

UDF3 - SA Type

•

UDF4 - Revenue Class

•

UDF5 - SIC Code

•

UDF6 - Deposit Class

•

UDF7 - Campaign

•

UDF8 - Debt Class

Properties

Property

Value

Target Table

CD_SA

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_SA

Stage Table

STG_CD_SA

ODI Package

B1_PKG_CD_SA

ETL View

B1_D_SA_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

SA_KEY

SA Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_SA_SEQ.

SRC_SA_ID

SA ID (Natural Key)

CI_SA.SA_ID

SPECIAL_ROLE_CD

Special Role Code

CI_SA_TYPE.SPECIAL_ROLE_FLG

SPECIAL_ROLE_DESCR

Special Role Description

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

Note :

Lookup Name - SPECIAL_ROLE_FLG. If the override description is not available, regular description is used.

UDF1_CD

Service Type (SA) Code

CI_SA_TYPE.SVC_TYPE_CD

UDF1_DESCR

Service Type

CI_SVC_TYPE_L.DESCR

UDF2_CD

CIS Division (SA) Code

CI_SA.CIS_DIVISION

UDF2_DESCR

CIS Division

CI_CIS_DIVISION_L.DESCR

UDF3_CD

SA Type Code

CI_SA.SA_TYPE_CD

UDF3_DESCR

SA Type

CI_SA_TYPE_L.DESCR

UDF4_CD

Revenue Class Code

CI_SA_TYPE.REV_CL_CD

UDF4_DESCR

Revenue Class

CI_REV_CL_L.DESCR

UDF5_CD

SIC Code Value

CI_SA.SIC_CD

UDF5_DESCR

SIC Code

CI_SIC_L.DESCR

UDF6_CD

Deposit Class Code

CI_SA_TYPE.DEP_CL_CD

UDF6_DESCR

Deposit Class

CI_DEP_CL_L.DESCR

UDF7_CD

Campaign Code

CI_ENRL.CAMPAIGN_CD

UDF7_DESCR

Campaign

CI_CAMPAIGN_L.DESCR

UDF8_CD

Debt Class Code

CI_SA_TYPE.DEBT_CL_CD

UDF8_DESCR

Debt Class

CI_DEBT_CL_L.DESCR

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

UDF16_CD

User Defined Field 16 Code

UDF16_DESCR

User Defined Field 16 Description

UDF17_CD

User Defined Field 17 Code

UDF17_DESCR

User Defined Field 17 Description

UDF18_CD

User Defined Field 18 Code

UDF18_DESCR

User Defined Field 18 Description

UDF19_CD

User Defined Field 19 Code

UDF19_DESCR

User Defined Field 19 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

To Do Dimension

The To Do Dimension stores information about all To Do entries created in the source system.

Properties

Property

Value

Target Table

CD_TD

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_TD_ENTRY

Stage Table

STG_CD_TD

ODI Package

B1_PKG_CD_TD

ETL View

B1_D_TD_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TD_KEY

To Do Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from OUBI_TD_SEQ.

SRC_TD_ENTRY_ID

To Do Entry ID (Natural Key)

CI_TD_ENTRY.TD_ENTRY_ID

TD_MESSAGE

To Do Message

CI_MSG_L.MESSAGE_TEXT

CI_MSG_L.MESSAGE_TEXT_OVRD

Transformation Logic:

The parameters provided in the To Do entry will be placed in the available place holders in the message.

Note : If the override message text is not available, the regular message text will be used.

TD_COMMENTS

To Do Comments

CI_TD_ENTRY.COMMENTS

TD_INFO

To Do Info String

CI_TD_TYPE_L.DESCR

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_TD_ENTRY.TD_ENTRY_ID

Transformation Logic .

This field is populated as the concatenation of To Do Type, Status Description, To Do Entry ID.

Note : Lookup Name - ENTRY_STATUS_FLG. If the override description is not available, the regular description will be used.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

To Do Priority Dimension

The To Do Priority dimension stores various priority levels that can be set for a To Do entry in the source system.

Properties

Property

Value

Target Table

CD_TD_PRIORITY

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_TD_PRIORITY

ODI Package

B1_PKG_CD_TD_PRIORITY

ETL View

B1_D_TD_PRIORITY_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TD_PRIORITY_KEY

To Do Priority Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from OUBI_TD_PRIORITY_SEQ.

TD_PRIORITY_CD

To Do Priority Code

CI_LOOKUP_VAL_L.FIELD_VALUE

Note : Lookup Name - TD_PRIORITY_FLG

TD_PRIORITY_DESCR

To Do Priority Description

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

If an override description is not available, regular description will be used.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

To Do Role Dimension

The To Do Role dimension stores various roles defined in the source system, who may view and work on the To Do entries.

Properties

Property

Value

Target Table

CD_TD_ROLE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_ROLE_L

Stage Table

STG_CD_TD_ROLE

ODI Package

B1_PKG_CD_TD_ROLE

ETL View

B1_D_TD_ROLE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TD_ROLE_KEY

To Do Role Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from OUBI_TD_ROLE_SEQ.

TD_ROLE_CD

To Do Role Code (Natural Key)

CI_ROLE_L.ROLE_ID

TD_ROLE_DESCR

To Do Role Description

CI_ROLE_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

To Do Skill Dimension

The To Do Skill dimension stores the characteristic types defined as skills in the AQM feature configuration list in the source system. The characteristic types are extracted along with their characteristic values.

Properties

Property

Value

Target Table

CD_TD_SKILL

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_WFM_OPT/CI_CHAR_VAL_L

Stage Table

STG_CD_TD_SKILL

ODI Package

B1_PKG_CD_TD_SKILL

ETL View

B1_D_TD_SKILL_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TD_SKILL_KEY

To Do Skill Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from OUBI_TD_SKILL_SEQ.

TD_SKILL_LVL_CD

To Do Skill Level Code (Natural Key)

CI_CHAR_VAL_L.CHAR_TYPE_CD

CI_CHAR_VAL_L.CHAR_VAL

Transformation Logic :

This field is populated with Characteristic Type Code <space> Characteristic Value Code.

TD_SKILL_LVL_DESCR

To Do Skill Level Description

CI_CHAR_VAL_L.DESCR

CI_CHAR_TYPE_L.DESCR

Transformation Logic:

This field is populated with the Characteristic Type Description / Characteristic Value Description value.

TD_SKILL_TYPE_CD

To Do Skill Type Code

CI_WFM_OPT.WFM_OPT_VAL

Note : The characteristic types defined as Skill on the Activity Queue Management feature configuration list is extracted.

TD_SKILL_TYPE_DESCR

To Do Skill Type Description

CI_CHAR_TYPE_L.DESCR

Note : The characteristic type descriptions for those characteristic types retrieved as Skills will be populated.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

To Do Status Dimension

The To Do Status dimension extracts the statuses of a To-Do entry. The data is retrieved from the Entry Status Flag (ENTRY_STATUS_FLG) lookup field.

Properties

Property

Value

Target Table

CD_TD_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

Stage Table

STG_CD_TD_STATUS

ODI Package

B1_PKG_CD_TD_STATUS

ETL View

B1_D_TD_STATUS_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TD_STATUS_KEY

To Do Status Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from OUBI_TD_STATUS_SEQ.

TD_STATUS_CD

To Do Status Code

CI_LOOKUP_VAL_L.FIELD_VALUE

Note : Lookup Name - ENTRY_STATUS_FLG

TD_STATUS_DESCR

To Do Status Description

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

If an override description is not available, regular description is used.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load time-stamp value.

UPDATE_DTTM

Update Date/Time

Transformation Logic:

This field is populated with the updated time-stamp value.

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

To Do Type Dimension

The To Do Type dimension stores all To Do types defined in the source system.

Properties

Property

Value

Target Table

CD_TD_TYPE

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CI_TD_TYPE_L

Stage Table

STG_CD_TD_TYPE

ODI Package

B1_PKG_CD_TD_TYPE

ETL View

B1_D_TD_TYPE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TD_TYPE_KEY

To Do Type Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from OUBI_TD_TYPE_SEQ.

TD_TYPE_CD

To Do Type Code (Natural Key)

CI_TD_TYPE_L.TD_TYPE_CD

TD_TYPE_DESCR

To Do Type Description

CI_TD_TYPE_L.DESCR

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

User Dimension

The User dimension stores the user details from the source system.

Properties

Property

Value

Target Table

CD_USER

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

SC_USER

Stage Table

STG_CD_USER

ODI Package

B1_PKG_CD_USER

ETL View

B1_D_USER_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

USER_KEY

User Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_USER_SEQ.

USER_CD

User Code (Natural Key)

SC_USER.USER_ID

USER_DESCR

User Description

SC_USER.LAST_NAME

SC_USER.FIRST_NAME

Transformation Logic:

This field is populated with Last Name, First Name.

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 8 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Fact Tables

Billed Usage Fact

The Billed Usage fact stores the information of the financial transactions and the usage associated with corresponding bills and bill segments.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_BILLED_USAGE

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_FT

Stage Table Name

STG_CF_BILLED_USAGE

ODI Package Name

B1_PKG_CF_BILLED_USAGE

ETL View Name

B1_F_BILLED_USAGE_VW

Materialized View Name

B1_BILLEDUSAGE_MON_MV1

B1_BILLEDUSAGE_MON_TOPX_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

BILLED_USAGE_KEY

Billed Usage Key

Transformation Logic:

This field is populated with the sequence from SPL_BILLED_USAGE_SEQ.

SRC_BILL_ID

Bill ID

CI_FT.PARENT_ID

SRC_BSEG_ID

Bill Segment ID

CI_FT.SIBLING_ID

SRC_FT_ID

Financial Transaction ID

CI_FT.FT_ID

SRC_SQI_CD

Service Quantity Identifier Code

SRC_TOU_CD

Time of Use Code

SRC_UOM_CD

Unit of Measure Code

SRC_RATE_SCHED_CD

Rate Schedule Code

Transformation Logic:

This field is populated with the rate code from bill segment calculation header.

Every primary non-null rate schedule code on the bill segment calculation header leads to a new row in this fact.

BILLED_QTY

Billed Quantity

CI_BSEG_SQ.BILL_SQ

CI_BSEG_READ.FINAL_REG_QTY

Transformation Logic:

If there is a single primary rate on bill segment calculation headers, use Bill SQ value from Bill Segment Service Quantity. Else, for each rate, for each usage period, sum up the Final Registered Quantity from bill segment read for the UOM/TOU/SQI combination.

CALC_AMT

Calculated Amount

CI_BSEG_CALC_LN.CALC_AMT

CI_BSEG_CALC.CALC_AMT

Calculation Logic :

If single primary rate, sum up the calculation amount from bill segment calculation lines. Else, for each rate, calculate the sum of calculation amounts for the UOM/TOU/SQI of the calculation header's usage period.

Note : The amounts are grouped on the UOM/TOU/SQI combination.

CURRENCY_CD

Currency Code

CI_FT.CURRENCY_CD

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of ‘1’.

INIT_QTY

Initial Quantity

CI_BSEG_SQ.INIT_SQ

CI_BSEG_READ.MSR_QTY

Transformation Logic:

If there is a single primary rate single on bill segment calculation headers, use initial SQ value from bill segment service quantity. Else, for each rate, for each usage period, sum up the Measured Quantity from bill segment read for the UOM/TOU/SQI combination.

SEG_DAYS

Bill Segment Days

Calculation Logic :

This field is populated with the difference between the end date and the start date of the respective bill segment.

SA_KEY

Service Agreement Dimension Surrogate Key

CI_FT.SA_ID

Transformation Logic:

This field is populated with the service agreement on the financial transaction.

ACCT_KEY

Account Dimension Surrogate Key

CI_SA.ACCT_ID

Transformation Logic :

This field is populated with the account on the service agreement for which the bill segment was generated.

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

This field is populated with the main customer of the respective account.

ADDR_KEY

Address Dimension Surrogate Key

Transformation Logic :

This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise ID on the account is used.

BILL_DATE_KEY

Date Dimension Surrogate Key

CI_BILL.CRE_DTTM

Transformation Logic :

This field is populated with the creation date of the bill associated with the bill segment.

BSEG_STRT_DATE_KEY

Date Dimension Surrogate Key

CI_BSEG.START_DT

Transformation Logic :

This field is populated with the start date of the respective bill segment.

BSEG_END_DATE_KEY

Date Dimension Surrogate Key

CI_BSEG.END_DT

Transformation Logic :

This field is populated with the end date of the respective bill segment.

FISCAL_CAL_KEY

Fiscal Period Dimension Surrogate Key

CI_GL_DIVISION.CALANDER_CD

Transformation Logic :

This field is populated with the calendar code from GL_DIVISION based on the input accounting date.

PREM_KEY

Premise Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic :

This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise ID on the account is used.

RATE_KEY

Rate Dimension Surrogate Key

CI_BSEG_CALC.RS_CD

Transformation Logic :

This field is populated with rate code from the bill segment calculation header.

Every primary non-null rate schedule code on the bill segment calculation header leads to a new row in this fact.

SQI_KEY

Service Quantity Identifier Dimension Surrogate Key

CI_BSEG_SQ.SQI_CD

Transformation Logic:

This field is populated with the SQI code associated with bill segment service quantity.

TOU_KEY

Time of Use Dimension Surrogate Key

CI_BSEG_SQ.TOU_CD

Transformation Logic:

This field is populated with the TOU code associated with bill segment service quantity.

UOM_KEY

Unit of Measure Dimension Surrogate Key

CI_BSEG_SQ.UOM_CD

Transformation Logic:

This field is populated with the UOM code associated with bill segment service quantity.

BUSG_UDD1_KEY

Bill Segment Usage User Defined Dimension 1 Surrogate Key

BUSG_UDD2_KEY

Bill Segment Usage User Defined Dimension 2 Surrogate Key

UDM1

User Defined Measure 1

CI_FT.FREEZE_DTTM

CI_BSEG.END_DT

Transformation Logic:

This field is populated with the bill lag. Bill lag is calculated as the difference between the bill segment freeze (financial transaction freeze date) and the meter read date (bill segment end date).

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

Case Fact

The Case fact stores all cases defined in the source system, and also their duration.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CASE

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_CASE

Stage Table Name

STG_CF_CASE

ODI Package Name

B1_PKG_CF_CASE

ETL View Name

B1_F_CASE_VW

Materialized View Name

B1_CASE_MON_MV1

B1_CASE_MON_MV2

B1_CASE_TOPX_MON_MV1

B1_CASE_TOPX_MON_MV2

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

ACCT_KEY

Account Dimension Surrogate Key

CI_CASE.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_CASE.PREM_ID

CASETY_STAT_KEY

Case Type Status Dimension Surrogate Key

CI_CASE.CASE_TYPE_CD

CI_CASE.CASE_STATUS_CD

CASE_COND_KEY

Case Condition Dimension Surrogate Key

CI_CASE.CASE_COND_FLG

CASE_LEN

Duration

CI_CASE.CASE_COND_FLG

Transformation Logic:

This field is populated with '0' if the case is open. Else, it is populated, in hours, with the difference between the closed date and the open date.

CASE_UDD1_KEY

Case User Defined Dimension 1 Surrogate Key

CASE_UDD2_KEY

Case User Defined Dimension 2 Surrogate Key

CASE_UDD3_KEY

Case User Defined Dimension 3 Surrogate Key

CASE_UDD4_KEY

Case User Defined Dimension 4 Surrogate Key

CASE_UDD5_KEY

Case User Defined Dimension 5 Surrogate Key

OPEN_DATE_KEY

Date Dimension Surrogate Key

CI_CASE_LOG.LOG_DTTM

Transformation Logic:

This field is populated with the date of the open log for the respective case.

CLOSE_DATE_KEY

Date Dimension Surrogate Key

CI_CASE_LOG.LOG_DTTM

Transformation Logic:

This field is populated only when the case is closed. It is populated with the maximum log date.

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of "1".

PER_KEY

Person Dimension Surrogate Key

CI_CASE.PER_ID

PREM_KEY

Premise Dimension Surrogate Key

CI_CASE.PREM_ID

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

USER_KEY

User Dimension Surrogate Key

CI_CASE.USER_ID

CASE_KEY

Case Fact Key

Transformation Logic:

This field is populated with the sequence from B1_CASE_SEQ.

SRC_CASE_ID

Case ID (Natural Key)

CI_CASE.CASE_ID

CURRENCY_CD

Currency Code

CI_INSTALLATION.CURRENCY_CD

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

OPEN_TIME_KEY

Time Dimension Surrogate Key

CI_CASE_LOG.LOG_DTTM

Transformation Logic:

This field is populated with the time of the open log for the respective case.

CLOSE_TIME_KEY

Time Dimension Surrogate Key

CI_CASE_LOG.LOG_DTTM

Transformation Logic:

This field is populated with 'null' if the case is in open condition. If the case is closed, then it is populated with the maximum log time.

UDDFK1_KEY

User Defined Dimension Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Case Log Fact

The Case Log fact stores the case state transition changes in the source system. This fact captures information to indicate if the case is in initial or final state, the time the case was open, and the time the case spent in the previous state.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CASE_LOG

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_CASE_LOG

Stage Table Name

STG_CF_CASE_LOG

ODI Package Name

B1_PKG_CF_CASE_LOG

ETL View Name

B1_F_CASE_LOG_VW

Materialized View Name

B1_CASELOG_MON_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CASELOG_KEY

Case Log Fact Key

Transformation Logic:

This field is populated with the sequence from B1_CASE_LOG_SEQ.

CASE_LOG_SEQ

Case Log Sequence

CI_CASE_LOG.SEQ_NUM

SRC_CASE_ID

Case ID (Natural Key)

CI_CASE_LOG.CASE_ID

CURRENCY_CD

Currency Code

CI_INSTALLATION.CURRENCY_CD

INITIAL_STATUS_IND

Initial Status Indicator

CI_CASE_LOG.CASE_LOG_TYPE_FLG

Transformation Logic:

This field is populated with '1' when the Log Type is "Created". It is populated with '0' when the Log Type is "Status Transition". Otherwise, it is populated with ‘0’.

FINAL_STATUS_IND

Final Status Indicator

CI_CASE_LOG.CASE_LOG_TYPE_FLG

CI_CASE.CASE_COND_FLG

Transformation Logic:

This field is populated with ‘1’ when the Log Type is "Status Transition" and Case Condition Flag is "Closed".

TIME_CASE_OPEN

Case Open Time

CI_CASE_LOG.LOG_DTTM

Transformation Logic:

This field is populated with ‘0’ if the log type is "Created". If the log type is "Status Transition", it is populated, in hours, with the difference between the current log date and the open log date.

TIME_IN_PREV_ST

Time in Previous Status

CI_CASE_LOG.LOG_DTTM

Transformation Logic:

This field is populated with ‘0’ if the log type is "Created". If the log type is "Status Transition", it is populated, in hours, with the difference between the current log date and the previous "Status Transition" log date.

ACCT_KEY

Account Dimension Surrogate Key

CI_CASE.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_CASE.PREM_ID

CASETY_STAT_KEY

Case Log Type Status Dimension Surrogate Key

CI_CASE_LOG.CASE_TYPE_CD

LOG_DATE_KEY

Date Dimension Surrogate key

CI_CASE_LOG.LOG_DTTM

Transformation Logic :

This field is populated using the date portion from the source.

LOG_TIME_KEY

Time Dimension Surrogate Key

CI_CASE_LOG.LOG_DTTM

Transformation Logic :

This field is populated using the time portion from the source.

OPEN_DATE_KEY

Date Dimension Surrogate key

CI_CASE_LOG.LOG_DTTM

Transformation Logic:

This field is populated using the date portion of the "Created" log entry for the current case.

OPEN_TIME_KEY

Time Dimension Surrogate Key

CI_CASE_LOG.LOG_DTTM

Transformation Logic:

This field is populated using the time portion of the "Created" log entry for the current case.

PER_KEY

Person Dimension Surrogate Key

CI_CASE.PER_ID

PREM_KEY

Premise Dimension Surrogate Key

CI_CASE.PREM_ID

PV_CASETY_STAT_KEY

Case Log Type Status Dimension Surrogate Key

CI_CASE_LOG.CASE_STATUS_CD

Transformation Logic:

This field is populated only when the log type is "Status Transition". It is populated with the case status code from the previous "Status Transition" log.

USER_KEY

User Dimension Surrogate Key

CI_CASE_LOG.USER_ID

CASELOG_UDD1_KEY

Case Log User Defined Dimension 1 Surrogate Key

CASELOG_UDD2_KEY

Case Log User Defined Dimension 2 Surrogate Key

CASELOG_UDD3_KEY

Case Log User Defined Dimension 3 Surrogate Key

CASELOG_UDD4_KEY

Case Log User Defined Dimension 4 Surrogate Key

CASELOG_UDD5_KEY

Case Log User Defined Dimension 5 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of "1".

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDFK1_KEY

User Defined Dimension Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Note : Only the case logs with type as "Created" or "Status Transition" are retrieved.

Collectible Event Fact

The Collectible Event fact stores the collection events, cut event, over due event, and severity event information.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_COLL_EVT

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_COLL_EVT

CI_SEV_EVT

CI_OD_EVT

CI_CUT_EVT

Stage Table Name

STG_CF_COLL_EVT

ODI Package Name

B1_PKG_CF_COLL_EVT

ETL View Name

B1_F_COLL_EVT_VW

Materialized View Name

Fields

Source 1 - Collectible Event (CI_COLL_EVT)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

COLL_EVT_KEY

Collectible Event Fact Surrogate Key

Transformation Logic:

This field is populated with the sequence from B1_COLL_EVT_SEQ.

COLL_PROC_ID

Collection Process ID

CI_COLL_EVT.COLL_PROC_ID

Transformation Logic:

All completed collection events (Status 30) are to be retrieved for this fact.

COLL_EVT_SEQ

Collection Event Sequence

CI_COLL_EVT.EVT_SEQ

COLL_EVT_SRC_IND

Collectible Event Source Indicator

Transformation Logic:

This indicator is set to ‘0’.

CURRENCY_CD

Currency Code

CI_ACCT.CURRENCY_CD

Transformation Logic:

This field is populated with the account’s currency code.

ACCT_KEY

Account Dimension Surrogate Key

CI_COLL_PROC.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic:

This field is populated with the mailing premise ID of the account. If not found, the characteristic premise on the service agreement is used.

CEVT_TYPE_KEY

Collectible Event Type Dimension Key

CI_COLL_EVT.COLL_EVT_TYP_CD

Transformation Logic :

Prefix the Collection Event Type with ‘C_’ and store it in the column.

COLL_TMPL_KEY

Collectible Process Template Dimension Key

CI_COLL_PROC.COLL_PROC_TMPL_CD

Transformation Logic :

Prefix the Collection Process Template Code with ‘C_’.

EVENT_DATE_KEY

Date Dimension Surrogate Key

CI_COLL_EVT.COMPLETION_DT

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic :

This field is populated with the main customer of the account.

PREM_KEY

Premise Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic :

This field is populated with the mailing premise ID of the account. If not found, the characteristic premise on the service agreement is used.

COLLEVT_UDD1_KEY

Collectible Event User Defined Dimension 1 Surrogate Key

COLLEVT_UDD2_KEY

Collectible Event User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic :

This field is populated with the standard value of ‘1’.

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDFK1_KEY

User Defined Dimension Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Source 2 - Severance Event (CI_SEV_EVT)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

COLL_EVT_KEY

Collectible Event Fact Surrogate Key

Transformation Logic:

This field is populated with the sequence from B1_COLL_EVT_SEQ.

COLL_PROC_ID

Collection Process ID

CI_SEV_EVT.SEV_PROC_ID

Transformation Logic:

All completed severance events (Status 30) need to be retrieved for this fact

COLL_EVT_SEQ

Collection Event Sequence

CI_SEV_EVT.EVT_SEQ

COLL_EVT_SRC_IND

Collectible Event Source Indicator

Transformation Logic:

This indicator is set to ‘1’.

CURRENCY_CD

Currency Code

CI_ACCT.CURRENCY_CD

Transformation Logic:

This field is populated with the account’s currency code.

ACCT_KEY

Account Dimension Surrogate Key

CI_SA.ACCT_ID

Transformation Logic:

This field is populated with the account on the service agreement of the severence process.

ADDR_KEY

Address Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic:

This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise ID on the account is used.

CEVT_TYPE_KEY

Collectible Event Type Dimension Key

CI_SEV_EVT.SEV_EVT_TYPE_CD

Transformation Logic :

Prefix the severance event type code with ‘S_’ and use.

COLL_TMPL_KEY

Collectible Process Template Dimension Key

CI_COLL_PROC.COLL_PROC_TMPL_CD

Transformation Logic :

Use the collection template corresponding to severance processes’ collection process ID and prefix it with ‘C_’.

EVENT_DATE_KEY

Date Dimension Surrogate Key

CI_SEV_EVT.COMPLETION_DT

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic :

This field is populated with the main customer of the account.

PREM_KEY

Premise Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic :

This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise ID on the account is used.

COLLEVT_UDD1_KEY

Collectible Event User Defined Dimension 1 Surrogate Key

COLLEVT_UDD2_KEY

Collectible Event User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic :

This field is populated with the standard value of ‘1’.

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDFK1_KEY

User Defined Dimension Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Source 3 - Overdue Event (CI_OD_EVT)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

COLL_EVT_KEY

Collectible Event Fact Surrogate Key

Transformation Logic:

This field is populated with the sequence from B1_COLL_EVT_SEQ.

COLL_PROC_ID

Collection Process ID

CI_OD_EVT.OD_PROC_ID

Transformation Logic:

Completed overdue events (Status 30) are retrieved for this fact.

COLL_EVT_SEQ

Collection Event Sequence

CI_OD_EVT.EVT_SEQ

COLL_EVT_SRC_IND

Collectible Event Source Indicator

Transformation Logic:

This indicator is set to ‘2’.

CURRENCY_CD

Currency Code

CI_ACCT.CURRENCY_CD

Transformation Logic:

This field is populated with the account’s currency code.

ACCT_KEY

Account Dimension Surrogate Key

CI_OD_PROC.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic:

This field is populated with the mailing premise ID of the account. If not found, use the characteristic premise from the service agreement.

CEVT_TYPE_KEY

Collectible Event Type Dimension Key

CI_OD_EVT.OD_EVT_TYPE_CD

Transformation Logic :

Prefix the overdue event type code with ‘OD_’ and use it.

COLL_TMPL_KEY

Collectible Process Template Dimension Key

CI_OD_PROC.OD_PROC_TMP_CD

Transformation Logic :

This field is populated with Collection Process Template Code prefixed with ‘OD_’.

EVENT_DATE_KEY

Date Dimension Surrogate Key

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic :

This field is populated with the main customer of the account.

PREM_KEY

Premise Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic :

This field is populated with the mailing premise ID of the account. If not found, use the characteristic premise from the service agreement.

COLLEVT_UDD1_KEY

Collectible Event User Defined Dimension 1 Surrogate Key

COLLEVT_UDD2_KEY

Collectible Event User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic :

This field is populated with the standard value of ‘1’.

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDFK1_KEY

User Defined Dimension Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Source 4 - Cut Event (CI_CUT_EVT)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

COLL_EVT_KEY

Collectible Event Fact Surrogate Key

Transformation Logic:

This field is populated with the sequence from B1_COLL_EVT_SEQ.

COLL_PROC_ID

Collection Process ID

CI_CUT_EVT.CUT_PROC_ID

Transformation Logic:

All completed cut events are retrieved for this fact.

COLL_EVT_SEQ

Collection Event Sequence

CI_CUT_EVT.EVT_SEQ

COLL_EVT_SRC_IND

Collectible Event Source Indicator

Transformation Logic:

This indicator is set to ‘3’.

CURRENCY_CD

Currency Code

CI_ACCT.CURRENCY_CD

Transformation Logic:

This field is populated with the account’s currency code.

ACCT_KEY

Account Dimension Surrogate Key

CI_SA.ACCT_ID

Transformation Logic:

This field is based on the service agreement ID. It is populated with the respective account ID.

ADDR_KEY

Address Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic:

This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise mailing ID on the account is used.

CEVT_TYPE_KEY

Collectible Event Type Dimension Key

CI_CUT_EVT.CUT_EVT_TYPE_CD

Transformation Logic :

Retrieve the cut event type code and prefix with ‘CUT_’ and use it.

COLL_TMPL_KEY

Collectible Process Template Dimension Key

CI_OD_PROC.OD_PROC_TMP_CD

Transformation Logic :

Use the process template corresponding to cut processes’ overdue process ID and prefix with ‘OD_’.

EVENT_DATE_KEY

Date Dimension Surrogate Key

CI_CUT_EVT.CUT_EVT_STAT_DTTM

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic :

This field is populated with the main customer of the account.

PREM_KEY

Premise Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic :

This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise mailing ID on the account is used.

COLLEVT_UDD1_KEY

Collectible Event User Defined Dimension 1 Surrogate Key

COLLEVT_UDD2_KEY

Collectible Event User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic :

This field is populated with the standard value of ‘1’.

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDFK1_KEY

User Defined Dimension Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Collectible Process Fact

The Collectible Process fact stores the collection process as well as overdue process information from the source system.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_COLL_PROC

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_COLL_PROC

CI_OD_PROC

CI_SEV_PROC

CI_CUT_PROC

Stage Table Name

STG_CF_COLL_PROC

ODI Package Name

B1_PKG_CF_COLL_PROC

ETL View Name

B1_F_COLL_PROC_VW

B1_F_OD_PROC_VW

Materialized View Name

B1_COLLPROC_MON_MV1

B1_COLLPROC_MON_MV2

B1_COLLPROC_MON_TOPX_MV1

B1_COLLPROC_MON_TOPX_MV2

Fields

Source 1 - Collection Process (CI_COLL_PROC)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

COLLPROC_KEY

Collectible Process Fact Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_COLL_PROC_SEQ.

SRC_COLLPROC_ID

Collection Process ID

CI_COLL_PROC.COLL_PROC_ID

ARRS_AT_START

Arrears at Start

CI_FT.TOT_AMT

Transformation Logic:

This field is populated with the balance of all collection service agreements that are linked to the collection process, by using the collection arrears date and creation date of collection process as debit and credit dates.

ARRS_AT_END

Arrears at End

CI_FT.CUR_AMT

Transformation Logic:

This is populated with a zero if collection process is active. Else, is populated with the balance of all active service agreements associated with collection process using Arrears Date and End Date of collection process as debit and credit dates.

ARRS_DIFF

Arrears at End - Arrears at Start

Transformation Logic:

This field is populated with the difference between the arrears at start and arrears at end.

COLLPROC_DURATION

Collection Process Duration

Transformation Logic:

This field is populated with the difference, in hours, between the end date and start date of the collection process.

COLL_PROC_SRC

Collection Process Source

Transformation Logic:

This field is populated with ‘0’.

CURRENCY_ID

Currency Code

CI_COLL_PROC.CURRENCY_CD

ACCT_KEY

Account Dimension Surrogate Key

CI_COLL_PROC.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic:

This field is populated with mailing premise ID of the account. If not found, the characteristic premise ID of the service agreement is used.

COLLPROC_STAT_KEY

Collectible Process Status Dimension Key

CI_COLL_PROC.COLL_STATUS_FLG

Transformation Logic:

1.

Set status as ‘Active’ when collection process is active or if collection process is completed and has a pending severance process.

2.

‘Effective’ when collection process has been canceled or completed, but all associated severance process are canceled.

3.

‘Ineffective’ if collection process is completed and atleast one severance process is completed or no severance processes are found.

COLL_TMPL_KEY

Collectible Process Template Dimension Key

CI_COLL_PROC.COLL_PROC_TMPL_CD

Transformation Logic :

This field is populated with Collection Process Template Code prefixed with ‘C_’.

CEVT_TYPE_KEY

Collectible Event Type Dimension surrogate Key

CI_COLL_EVT.COLL_EVT_TYP_CD

Transformation Logic:

This field is populated only if the collection process is in ‘Effective’ status.

Retrieve the event type of the last completed collection event that was successful in collecting the payment. If multiple events are completed at same time, use the one with highest sequence.

Prefix with ‘C_’ and use it.

START_DATE_KEY

Date Dimension Surrogate Key

CI_COLL_PROC.CRE_DTTM

END_DATE_KEY

Date Dimension Surrogate Key

CI_SEV_EVT.COMPLETION_DT

CI_COLL_EVT.COMPLETION_DT

CI_SEV_PROC.CRE_DTTM

Transformation Logic :

If the collection process status is ‘Active’, no end date would exist. Else, use the maximum severance event completion date. If it is not found, use the collection event completion date. If it is not found, use the creation date of the collection process.

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

Set person ID = account’s main person ID

PREM_KEY

Premise Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic:

This field is populated with mailing premise ID of the account. If it is not found, the characteristic premise ID of the service agreement is used.

COLLPROC_UDD1_KEY

Collectible Process User Defined Dimension 1 Surrogate Key

COLLPROC_UDD2_KEY

Collectible Process User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of ‘1’.

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDFK1_KEY

User Defined Dimension Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Source 2 - Overdue Process (CI_OD_PROC)

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

COLLPROC_KEY

Collectible Process Fact Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_COLL_PROC_SEQ.

SRC_COLLPROC_ID

Collection Process ID

CI_OD_PROC. OD_PROC_ID

ARRS_AT_START

Arrears at Start

CI_FT.CUR_AMT

Transformation Logic:

Retrieve the overdue bill associated with the overdue process using the Overdue Bill Characteristic Type defined on source and fetch the open item bill amounts.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

ARRS_AT_END

Arrears at End

Transformation Logic:

This field is populated with the unpaid bill amount only when the status is not active. Else, it would be zero.

ARRS_DIFF

Arrears at End - Arrears at Start

Transformation Logic:

This field is populated with the difference between the arrears at start and arrears at end.

COLLPROC_DURATION

Collection Process Duration

Transformation Logic:

This field is populated with the difference, in hours, between the end date and start date of the overdue process.

COLL_PROC_SRC

Collection Process Source

Transformation Logic:

This field is populated with ‘2’.

CURRENCY_ID

Currency Code

CI_OD_PROC.CURRENCY_CD

ACCT_KEY

Account Dimension Surrogate Key

CI_OD_PROC.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic:

This field is populated with the mailing premise ID on the account. If it is not found, characteristic premise on the service agreement is used.

COLLPROC_STAT_KEY

Collectible Process Status Dimension Key

Transformation Logic:

1.

Set status as ‘Active’ when the overdue process status is ‘Active’ or the process is complete and at least one cut process is active.

2.

Set status as ‘Effective’ if the overdue process has been canceled or is complete, and all associated cut processes are canceled.

3.

Set status as ‘Ineffective’ if overdue process is complete, and either no cut processes are spawned or at least one of the cut process is complete.

COLL_TMPL_KEY

Collectible Process Template Dimension Key

CI_OD_PROC.OD_PROC_TMP_CD

Transformation Logic :

Prefix the overdue process template with ‘OD_’ and use it.

CEVT_TYPE_KEY

Collectible Event Type Dimension surrogate Key

CI_OD_EVT.OD_EVT_TYP_CD

Transformation Logic:

If the overdue process status is ‘Effective’, use the overdue event type of last completed overdue event. If multiple events have same completion date, pick the one with highest sequence and prefix it with 'OD_'.

START_DATE_KEY

Date Dimension Surrogate Key

CI_OD_PROC.CRE_DTTM

END_DATE_KEY

Date Dimension Surrogate Key

CI_CUT_EVT.CUT_EVT_STAT_DTTM

CI_OD_EVT.OD_EVT_STAT_DTTM

CI_OD_PROC.CRE_DTTM

Transformation Logic :

1.

If overdue process status flag is ‘Active’, the end date is null.

2.

Else, use maximum cut event completion date. If it is not found, use the maximum overdue event collection date.

3.

If it is still not found, use the creation date of overdue process.

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

Set person ID = account’s main person ID

PREM_KEY

Premise Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic:

This field is populated with the mailing premise ID on the account. If not found, the characteristic premise on the service agreement is used.

COLLPROC_UDD1_KEY

Collectible Process User Defined Dimension 1 Surrogate Key

COLLPROC_UDD2_KEY

Collectible Process User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of ‘1’.

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDFK1_KEY

User Defined Dimension Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Customer Contact Fact

The Customer Contact fact stores the details of all customer contacts and their attributes.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CC

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_CC

Stage Table Name

STG_CF_CC

ODI Package Name

B1_PKG_CF_CC

ETL View Name

B1_F_CC_VW

Materialized View Name

B1_CC_HOU_MV1

B1_CC_MON_MV1

B1_CC_TOPX_MON_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CC_KEY

Customer Contact Fact Key

Transformation Logic:

This field is populated with the sequence from B1_CC_SEQ.

SRC_CC_ID

Customer Contact ID (Natural Key)

CI_CC.CC_ID

CURRENCY_CD

Currency Code

CI_ACCT.CURRENCY_CD

CC_DATE_KEY

Customer Contact Date (Date Dimension Surrogate Key)

CI_CC.CC_DTTM

Transformation Logic:

This field extracts only the date from CI_CC.CC_DTTM.

CC_TIME_KEY

Customer Contact Time (Time Dimension Surrogate Key)

CI_CC.CC_DTTM

Transformation Logic:

This field extracts only the time from CI_CC.CC_DTTM.

ACCT_KEY

Account Dimension Surrogate Key

CI_ACCT_PER.ACCT_ID

Transformation Logic:

The account is fetched using the person ID.

1.

If one account is linked to the person, return this account.

2.

If there are more than one accounts linked to the person:

a.

Select the account linked to the non-closed and non-canceled service agreement with the latest start date where the person is linked as main person. If there is more than one account, return the first account.

b.

If there are no non-canceled and non-closed service agreements linked to all of the accounts, return the first account.

ADDR_KEY

Address Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

Transformation Logic:

Retrieve the first characteristic premise of the input account’s non-canceled and non-closed service agreement with the latest start date.

PER_KEY

Person Dimension Surrogate Key

CI_CC.PER_ID

PREM_KEY

Premise Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

Transformation Logic:

Retrieve the first characteristic premise of the input account’s non-canceled and non-closed service agreement with the latest start date.

USER_KEY

User Dimension Surrogate Key

CI_CC.USER_ID

CC_TYPE_KEY

Customer Contact Dimension Surrogate key

CI_CC.CC_TYPE_CD

CC_UDD1_KEY

Customer Contact User Defined Dimension 1 Surrogate Key

CC_UDD2_KEY

Customer Contact User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of 1.

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Financial Fact

The Financial fact stores all frozen financial transactions defined in the source system.

ETL has the capability of excluding up to three adjustment types. These adjustment types need to be configured as part of the BI configuration in the source system.

For more details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_FT

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_FT

Stage Table Name

STG_CF_FT

ODI Package Name

B1_PKG_CF_FT

ETL View Name

B1_F_FT_VW

Materialized View Name

B1_FT_MON_MV1

B1_FT_MON_TOPX_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

ACCT_KEY

Account Dimension Surrogate Key

CI_SA.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Note:

Use the characteristic premise ID from the service agreement. If it is not found, use the mailing premise ID from the account.

ADJ_TYPE_KEY

Adjustment type Dimension Surrogate Key

CI_FT.PARENT_ID

Note:

This field is populated when the financial transaction corresponds to adjustment/adjustment cancellation using the Parent ID column.

ARREARS_DT_KEY

Arrears Date (Date Dimension Surrogate Key)

CI_FT.ARS_DT

CURRENCY_CD

Currency Code

CI_FT.CURRENCY_CD

CURR_AMT

Current Amount

CI_FT.CUR_AMT

FACT_CNT

Count

Transformation Logic:

This field is populated with the standard value of 1.

FISCAL_CAL_KEY

Fiscal Period Dimension Surrogate Key

CI_GL_DIVISION.CALENDER_ID

Transformation Logic:

This field is populated using the calendar from GL division based on the accounting date.

FREEZE_DT_KEY

Freeze Date

CI_FT.FREEZE_DTTM

FT_KEY

Financial Fact Key

FT_TYPE_KEY

Financial Transaction Type Dimension Surrogate Key

CI_FT.FT_TYPE_FLG

FT_UDD1_KEY

Financial User Defined Dimension 1 Surrogate Key

FT_UDD2_KEY

Financial User Defined Dimension 2 Surrogate Key

OTHER_AMT

Other Amount

PAYOFF_AMT

Payoff Amount

CI_FT.TOT_AMT

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

This field is populated with the main customer of the account.

PREM_KEY

Premise Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic:

Use the characteristic premise ID from the service agreement. If it is not found, use the mailing premise ID from the account.

RATE_KEY

Rate Dimension Surrogate Key

CI_BSEG_CALC.RS_CD

Transformation Logic:

This field is populated when a financial transaction is of type bill segment or bill segment cancellation. Rate Schedule Code from the bill segment calculation header is used.

REVENUE_AMT

Revenue Amount

CI_FT_GL.AMOUNT

Transformation Logic:

This field is populated with the absolute value of the total amount of all financial transaction GLs whose distribution code’s characteristic type/ revenue characteristic value match with the characteristic type/value configured as part of the BI configuration in the source system and effective on the specific date.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

SA_KEY

Service Agreement Dimension Surrogate Key

CI_FT.SA_ID

SIBLING_ID

Sibling ID (Natural Key)

CI_FT.SIBLING_ID

SRC_FT_ID

Financial Transaction ID (Natural Key)

CI_FT.FT_ID

TAX_AMT

Tax Amount

CI_FT_GL.AMOUNT

Transformation Logic:

This field is populated with the absolute value of the total amount of all financial transaction GLs whose distribution code’s characteristic type/tax characteristic value match with the characteristic type/value configured as part of the BI configuration in the source system and effective on the specific date.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

USER_KEY

User Dimension Surrogate Key

CI_FT.FREEZE_USER_ID

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Financial General Ledger Fact

The Financial General Ledger fact stores the financial transaction general ledger information, along with its attributes, defined in the source system.

ETL has the capability of excluding up to three adjustment types. These adjustment types need to be configured as part of the BI configuration in the source system.

For more details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_FT_GL

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_FT_PROC

Stage Table Name

STG_CF_FT_GL

ODI Package Name

B1_PKG_CF_FT_GL

ETL View Name

B1_F_FT_GL_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

FT_GL_KEY

Financial General Ledger Fact Key

Transformation Logic:

This field is populated with the sequence from B1_FT_GL_SEQ.

SRC_FT_ID

Financial Transaction ID (Natural Key)

CI_FT_GL.FT_ID

SRC_GL_SEQ_NBR

General Ledger Sequence Number (Natural Key)

CI_FT_GL.GL_SEQ_NBR

SIBLING_ID

Sibling ID

CI_FT.SIBLING_ID

ACCT_KEY

Account Dimension Surrogate Key

CI_SA.ACCT_ID

Transformation Logic:

This field is populated with account information from the CI_SA table.

ADDR_KEY

Address Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic:

This field is populated with the service agreement’s characteristic premise ID. If not found, it is populated with the account’s mailing premise ID.

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

This field is populated with the main customer of the account.

PREM_KEY

Premise Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic:

This field is populated with the service agreement’s characteristic premise ID. If not found, it is populated with the account’s mailing premise ID.

FREEZE_DT_KEY

Freeze Date (Date Dimension Surrogate Key)

CI_FT.FREEZE_DTTM

SA_KEY

Service Agreement Dimension Surrogate Key

CI_FT.SA_ID

USER_KEY

User Dimension Surrogate Key

CI_FT.FREEZE_USER_ID

ADJ_TYPE_KEY

Adjustment type Dimension Surrogate Key

CI_FT.PARENT_ID

Transformation Logic:

This field is populated when a financial transaction corresponds to adjustment/adjustment cancellation using the Parent ID column.

FT_TYPE_KEY

Financial Transaction Type Dimension Surrogate Key

CI_FT.FT_TYPE_FLG

RATE_KEY

Rate Dimension Surrogate Key

CI_BSEG_CALC.RS_CD

Note:

This field is populated when a financial transaction is of type bill segment or bill segment cancellation. The Rate Schedule Code from bill segment calculation header is used.

FISCAL_CAL_KEY

Fiscal Period Dimension Surrogate Key

CI_GL_DIVISION.CALENDER_ID

Transformation Logic:

This field is populated using the calendar from GL division based on the accounting date.

GL_ACCT_KEY

GL Account Dimension Surrogate Key

CI_FT_GL.GL_ACCT

FT_GL_UDD1_KEY

FT GL User Defined Dimensions 1 Surrogate Key

FT_GL_UDD2_KEY

FT GL User Defined Dimensions 1 Surrogate Key

FACT_CNT

Count

Transformation Logic:

This field is populated with the standard value of "1".

CURRENCY_CD

Currency Code

CI_FT.CURRENCY_CD

DEBIT_AMT

Debit Amount

CI_FT_GL.AMOUNT

Note:

(All positive AMOUNT values)

CREDIT_AMT

Credit Amount

CI_FT_GL.AMOUNT

Note:

(All negative AMOUNT values)

GL_AMT

General Ledger Amount

CI_FT_GL.AMOUNT

STATISTIC_AMT

Statistic Amount

CI_FT_GL.STATISTIC_AMT

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGEN6

User Defined Degenerate Dimension 6

UDDGEN7

User Defined Degenerate Dimension 7

UDDGEN8

User Defined Degenerate Dimension 8

UDDGEN9

User Defined Degenerate Dimension 9

UDDGEN10

User Defined Degenerate Dimension 10

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

ARREARS_DT_KEY

Arrears Date (Date Dimension Surrogate Key)

CI_FT.ARS_DT

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Order Fact

The Order fact stores all enrollment orders that are defined in the source system. This fact stores the duration for completed orders.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_ORDER

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_ENRL

Stage Table Name

STG_CF_ORDER

ODI Package Name

B1_PKG_CF_ORDER

ETL View Name

B1_F_ORDER_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

ORDER_KEY

Order Fact Key

Transformation Logic:

This field is populated with the sequence from B1_ORDER_SEQ.

SRC_ORDER_ID

Order ID (Natural Key)

CI_ENRL.ENRL_ID

CURRENCY_CD

Currency Code

CI_ACCT.CURRENCY_CD

B1_PROD_INSTANCE.CURRENCY_CD

Note: If no account is associated with the Order, then the currency code available in the Product Instance metadata configuration will be populated.

DURATION

Duration

CI_ENRL_LOG.ENRL_LOG_DTTM

Transformation Logic:

This field is populated with the difference in hours between the timestamp in the Order Creation log entry and the Order Completion/Cancellation log entry.

Note: This field is populated only if the order is complete or cancelled.

ACCT_KEY

Account Dimension Surrogate Key

CI_ENRL.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ENRL.PREM_ID

CAMPAIGN_KEY

Campaign Dimension Surrogate Key

CI_ENRL.CAMPAIGN_CD

CREATE_DATE_KEY

Date Dimension Surrogate Key

CI_ENRL_LOG.ENRL_LOG_DTTM

Transformation Logic:

This field is populated with the date in the Order Creation log entry.

END_DATE_KEY

Date Dimension Surrogate Key

CI_ENRL_LOG.ENRL_LOG_DTTM

Transformation Logic:

This field is populated with the date in the Order Completion or Cancellation log entry.

ORDER_CAN_RSN_KEY

Order Cancel Reason Dimension Surrogate Key

CI_ENRL.ENRL_CAN_RSN_CD

ORDER_STATUS_KEY

Order Status Dimension Surrogate Key

CI_ENRL.ENRL_STATUS_FLG

PER_KEY

Person Dimension Surrogate Key

CI_ENRL.PER_ID

PKG_KEY

Package Dimension Surrogate Key

CI_ENRL.PACKAGE_ID

PREM_KEY

Premise Dimension Surrogate Key

CI_ENRL.PREM_ID

START_DATE_KEY

Date Dimension Surrogate Key

CI_ENRL.START_DT

FACT_CNT

Fact Count

Transformation Logic:

This field populates the standard value of "1".

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Pay Plan Accumulation Fact

The Pay Plan Accumulation fact stores all pay plans defined in the source system.

This fact is special in the sense that the ETL process setup to load this fact is configured as a daily refresh job. It is primarily to keep the measures updated to reflect on the current status.

The fact captures a variety of information about pay plans, such as the duration from the start, the number of days to go till the last payment, total payment amount, the amount paid so far, the number of future payments, etc.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_PAY_PLAN

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_PP

Stage Table Name

STG_CF_PAY_PLAN

ODI Package Name

B1_PKG_CF_PAY_PLAN

ETL View Name

B1_F_PAY_PLAN_VW

Materialized View Name

B1_PAY_PLAN_MON_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PAY_PLAN_KEY

Pay Plan Accumulation Fact Key

Transformation Logic:

This field is populated with the sequence from B1_PAY_PLAN_ACCUM_SEQ.

SRC_PAY_PLAN_ID

Source Pay Plan ID

CI_PP.PP_ID

CURRENCY_CD

Currency Code

CI_ACCT.CURRENCY_CD

TOT_SCHED_PAY_AMT

Total Scheduled Payment Amount

CI_PP_SCHED_PAY.PP_SCHED_AMT

Transformation Logic:

This field is populated with the sum of all scheduled payments for the respective pay plan ID.

DUR_FROM_START

Duration from Start

CI_PP.START_DT

CI_PP.LAST_STAT_DTTM

Transformation Logic:

•

If pay plan is "Active": This field is populated with the difference (in hours) between the start date and the current date.

•

If pay plan is "Canceled", "Kept", or "Broken": This field is populated with the difference (in days) between the start date and the last status update date.

DAYS_TO_GO

Days to Go

CI_PP_SCHED_PAY.PP_SCHED_DT

Transformation Logic:

This field is populated with the difference (in days) between the last scheduled payment and the current date.

Note : Populated for active pay plans only.

PAID_TO_DATE_AMT

Paid to Date

CI_PP_SCHED_PAY.PP_SCHED_AMT

Transformation Logic:

•

If pay plan is "Active". This field is populated with the sum of all past scheduled payments.

•

If pay plan is "Kept". This field is populated with the sum of all scheduled payments.

•

If pay plan is "Canceled". This field is populated with the sum of all scheduled payments before the end date.

•

If pay plan is "Broken": This field is populated with the sum of all scheduled payments before the scheduled payment closest to the end date.

NBR_TOT_SCHED_PAYMENTS

Total Scheduled Payments

CI_PP_SCHED_PAY.PP_ID,PP_SCHED_DT

Transformation Logic:

This field is populated with the total number of scheduled payments.

NBR_FUTURE_SCHED_PAYMENTS

Future Scheduled Payments

CI_PP_SCHED_PAY.PP_ID,PP_SCHED_DT

Transformation Logic:

This field is populated with the number of payments scheduled in the future.

Note : If the pay plan is "Broken", this field includes the scheduled payment that lapsed and did not clear the payment.

TOTAL_FUTURE_PAY_AMT

Future Payment Amount

CI_PP_SCHED_PAY.PP_SCHED_AMT

Transformation Logic:

This field is populated with the sum of all payments scheduled in the future.

If the pay plan is "Broken/Canceled", this field is populated with zero.

FUTURE_PAY_AMT_BUCKET1

Future Payment Amount Bucket 1

CI_PP_SCHED_PAY.PP_SCHED_AMT

Transformation Logic :

This field is populated with the sum of all future scheduled payments that fall into the age ranges configured for the bucket 1 slot for pay plans in the source system.

For details, see PP Future Payment Age Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

FUTURE_PAY_AMT_BUCKET2

Future Payment Amount Bucket 2

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET3

Future Payment Amount Bucket 3

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET4

Future Payment Amount Bucket 4

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET5

Future Payment Amount Bucket 5

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET6

Future Payment Amount Bucket 6

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET7

Future Payment Amount Bucket 7

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET8

Future Payment Amount Bucket 8

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET9

Future Payment Amount Bucket 9

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET10

Future Payment Amount Bucket 10

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

START_DTTM

Start Date/Time

CI_PP.START_DT

END_DTTM

End Date/Time

CI_PP.LAST_STAT_DTTM

Transformation Logic:

This field is populated only when the pay plan status is "Canceled", "Broken", or "Kept".

START_DATE_KEY

Start Date (Date Dimension Surrogate) Key

CI_PP.START_DT

START_TIME_KEY

Start Time (Time Dimension Surrogate) Key

CI_PP.START_DT

END_DATE_KEY

End Date (Date Dimension Surrogate) Key

CI_PP.LAST_STAT_DTTM

Transformation Logic:

This field is populated only when the pay plan status is "Canceled", "Broken", or "Kept".

END_TIME_KEY

End Time (Time Dimension Surrogate) Key

CI_PP.LAST_STAT_DTTM

Transformation Logic:

This field is populated only when the pay plan status is "Canceled", "Broken", or "Kept".

PAY_PLAN_STATUS_KEY

Pay Plan Status Dimension Surrogate Key

CI_PP.PP_STAT_FLG

PAY_PLAN_TYPE_KEY

Pay Plan Type Dimension Surrogate Key

CI_PP.PP_TYPE_CD

PAY_METHOD_KEY

Pay Method Dimension Surrogate Key

CI_PP.PAY_METH_CD

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

The main customer of the account associated with the pay plan is used to populate this field.

ACCT_KEY

Account Dimension Surrogate Key

CI_PP.ACCT_ID

PAYOR_ACCT_KEY

Payor Account (Account Dimension Surrogate) Key

CI_PP.PAYOR_ACCT_ID

PREM_KEY

Premise Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

PAY_PLAN_UDD1_KEY

Pay Plan User Defined Dimension 1 Surrogate Key

PAY_PLAN_UDD2_KEY

Pay Plan User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of "1".

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Note : The Future Payment Age buckets configured in the source are loaded in the MDADM.B1_RANGE_LOOKUP table in the data warehouse. The ELT job for this is configured to be initial load only. Any incremental changes to these buckets after the initial data load will not be reflected in the warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated and reloaded in the fact table and the range lookup table to reflect the changes.

For details about Range look-up and reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Pay Plan Snapshot Fact

The Pay Plan Snapshot fact provides a snapshot of the pay plans defined in the source system. Once a pay plan reaches its final state (Kept, Canceled, or Broken), it will no longer be included in the snapshots of the further periods.

This fact captures a variety of information about pay plans, such as the duration from the start, the number of days to go till the last payment, total payment amount, the amount paid so far, the number of future payments, etc.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_PAY_PLAN_SNAP

Table Type

Fact

Fact Type

Snapshot

Driver Table

CI_PP

Stage Table Name

STG_CF_PAY_PLAN_SNAP

ODI Package Name

B1_PKG_CF_PAY_PLAN_SNAP

ETL View Name

Materialized View Name

B1_PAY_PLAN_SNAP_MON_MV1

B1_PAY_PLAN_SNP_MON_TOPX_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PAY_PLAN_SNAP_KEY

Pay Plan Snapshot Fact Key

Transformation Logic:

This field is populated with the sequence from B1_PAY_PLAN_SNAP_SEQ.

SRC_PAY_PLAN_ID

Source Pay Plan ID

CI_PP.PP_ID

CURRENCY_CD

Currency Code

CI_ACCT.CURRENCY_CD

TOT_SCHED_PAY_AMT

Total Scheduled Payment Amount

CI_PP_SCHED_PAY.PP_SCHED_AMT

Transformation Logic:

This field is populated with the sum of all scheduled payments for that pay plan ID.

DUR_FROM_START

Duration from Start

CI_PP.START_DT

CI_PP.LAST_STAT_DTTM

Transformation Logic:

•

If pay plan is "Active": This field is populated with the difference (in days) between the start date and the current date.

•

If pay plan is "Canceled", "Kept", or "Broken": This field is populated with the difference (in days) between the start date and the last status update date.

DAYS_TO_GO

Days to Go

CI_PP_SCHED_PAY.PP_SCHED_DT

Transformation Logic:

This field is populated with the difference (in days) between the last scheduled payment and the snapshot end date.

Note : Populated for active pay plans only.

PAID_TO_DATE_AMT

Paid to Date

CI_PP_SCHED_PAY.PP_SCHED_AMT

Transformation Logic:

•

If pay plan is "Active". This field is populated with the sum of all past scheduled payments.

•

If pay plan is "Kept". This field is populated with the sum of all scheduled payments.

•

If pay plan is "Canceled". This field is populated with the sum of all scheduled payments before the end date.

•

If pay plan is "Broken": This field is populated with the sum of all scheduled payments before the scheduled payment closest to the end date.

NBR_TOT_SCHED_PAYMENTS

Total Scheduled Payments

CI_PP_SCHED_PAY.PP_ID,PP_SCHED_DT

Transformation Logic:

This field is populated with the total number of scheduled payments.

NBR_FUTURE_SCHED_PAYMENTS

Future Scheduled Payments

CI_PP_SCHED_PAY.PP_ID,PP_SCHED_DT

Transformation Logic:

This field is populated with the number of payments scheduled in the future.

Note : If the pay plan is "Broken", this field includes the scheduled payment that lapsed and did not clear the payment.

TOTAL_FUTURE_PAY_AMT

Future Payment Amount

CI_PP_SCHED_PAY.PP_SCHED_AMT

Transformation Logic:

This field is populated with the sum of all payments scheduled in the future.

If the pay plan is Broken/Canceled, this field is populated with zero.

FUTURE_PAY_AMT_BUCKET1

Future Payment Amount Bucket 1

CI_PP_SCHED_PAY.PP_SCHED_AMT

Transformation Logic :

This field is populated with the sum of all future scheduled payments that fall into the age ranges configured for the bucket 1 slot for pay plans in the source system.

For details, see PP Future Payment Age Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

FUTURE_PAY_AMT_BUCKET2

Future Payment Amount Bucket 2

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET3

Future Payment Amount Bucket 3

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET4

Future Payment Amount Bucket 4

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET5

Future Payment Amount Bucket 5

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET6

Future Payment Amount Bucket 6

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET7

Future Payment Amount Bucket 7

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET8

Future Payment Amount Bucket 8

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET9

Future Payment Amount Bucket 9

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET10

Future Payment Amount Bucket 10

CI_PP_SCHED_PAY.PP_SCHED_AMT

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

START_DTTM

Start Date/Time

CI_PP.START_DT

END_DTTM

End Date/Time

CI_PP.LAST_STAT_DTTM

Transformation Logic:

This field is populated only when the pay plan status is "Canceled", "Broken", or "Kept".

SNAP_TYPE_CD

Snap Type Code

Transformation Logic:

This field is populated with 'M'(Monthly) or 'W'(Weekly) based on the configuration of the ETL process for this snapshot fact.

SNAPSHOT_DT

Snapshot Date

Transformation Logic:

This field is populated with the last date of the current snapshot period.

SNAPSHOT_DATE_KEY

Snapshot Date (Date Dimension Surrogate) Key

Transformation Logic:

This field is populated with the last date of the current snapshot period.

START_DATE_KEY

Start Date (Date Dimension Surrogate) Key

CI_PP.START_DT

Transformation Logic:

This field is populated with the last date of the current snapshot period.

START_TIME_KEY

Start Time (Time Dimension Surrogate) Key

CI_PP.START_DT

END_DATE_KEY

End Date (Date Dimension Surrogate) Key

CI_PP.LAST_STAT_DTTM

Transformation Logic:

This field is populated only when the pay plan status is "Canceled", "Broken", or "Kept".

END_TIME_KEY

End Time (Time Dimension Surrogate) Key

CI_PP.LAST_STAT_DTTM

Transformation Logic:

This field is populated only when the pay plan status is "Canceled", "Broken", or "Kept".

PAY_PLAN_STATUS_KEY

Pay Plan Status Dimension Surrogate Key

CI_PP.PP_STAT_FLG

PAY_PLAN_TYPE_KEY

Pay Plan Type Dimension Surrogate Key

CI_PP.PP_TYPE_CD

PAY_METHOD_KEY

Pay Method Dimension Surrogate Key

CI_PP.PAY_METH_CD

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

This field is populated with the main customer of the account associated with the pay plan.

ACCT_KEY

Account Dimension Surrogate Key

CI_PP.ACCT_ID

PAYOR_ACCT_KEY

Payor Account (Account Dimension Surrogate) Key

CI_PP.PAYOR_ACCT_ID

PREM_KEY

Premise Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

PAY_PLAN_SNAP_UDD1_KEY

Pay Plan User Defined Dimension 1 Surrogate Key

PAY_PLAN_SNAP_UDD2_KEY

Pay Plan User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of "1".

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Note : The Future Payment Age buckets configured in the source are loaded in the MDADM.B1_RANGE_LOOKUP table in the data warehouse. The ELT job for this is configured to be initial load only. Any incremental changes to these buckets after the initial data load will not be reflected in the warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated and reloaded in the fact table and the range lookup table to reflect the changes.

For details about Range look-up and reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Payment Arrangement Accumulation Fact

The Payment Arrangement Accumulation fact stores all payment arrangements (excluding those in pending start state) in the source system.

The fact is special in the sense that the ETL process setup to load this fact will be configured as a daily refresh job. It is to primarily keep the measures updated to reflect the current status.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_PA

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_SA

Stage Table Name

STG_CF_PA

ODI Package Name

B1_PKG_CF_PA

ETL View Name

B1_F_PA_VW

Materialized View Name

B1_PA_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PA_KEY

Payment Arrangement Accumulation Fact Key

Transformation Logic:

This field is populated with the sequence from B1_PA_ACCUM_SEQ.

SRC_SA_ID

Source Service Agreement ID

CI_SA.SA_ID

CURRENCY_CD

Currency Code

CI_SA.CURRENCY_CD

REC_CHARGE_AMT

Total Scheduled Payment Amount

CI_SA_RCHG_HIST.RCR_CHG_AMT

Transformation Logic:

This field is populated with the recurring charge amount effective as of the sysdate.

TOTAL_PA_AMT

Total Payment Arrangement Amount

CI_ADJ.ADJ_AMT

Transformation Logic:

This field is populated with the adjustment amount for adjustment(s) used to transfer the original service agreement’s balance.

•

If the payment arrangement is "active" or "kept", then all non-canceled xfer adjustments are contributed to the arrangement amount.

•

If the payment arrangement is "canceled", all canceled xfer adjustments are contributed to the arrangement amount.

•

If the payment arrangement is "broken", then all canceled xfer adjustments are contributed to the arrangement amount.

INSTALLMENT_CNT

Total Number of Installments

Calculation Logic:

This field is calculated as the total payment arrangement amount divided by the recurring charge amount.

DUR_FROM_START

Duration from Start

CI_SA.START_DT

CI_SA.END_DT

Transformation Logic:

If the payment arrangement’s service agreement has an end date, this field populates the difference between start date and end date. Else, it populates the difference between start date and current date.

PAID_TO_DATE_AMT

Paid to Date Amount

CI_FT.CUR_AMT

Transformation Logic:

This field is populated with the sum of all payments made against the pay arrangement service agreement.

FUTURE_PAY_AMT

Future Payment Amount

Transformation Logic:

Future payment amount is set to zero when the payment arrangement is in Broken/Canceled state. Else, it is the difference between total pay agreement amount and paid to date columns.

FUTURE_PAY_AMT_BUCKET1

Future Payment Amount Bucket 1

Transformation Logic:

This field is populated with the sum of all future scheduled payments that fall into the age ranges configured for the bucket 1 slot for payment arrangements in the source system.

Future scheduled payments are identified based on the remaining amount, billing frequency, and bill cycle schedule.

For details, see PA Future Payment Age Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

FUTURE_PAY_AMT_BUCKET2

Future Payment Amount Bucket 2

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET3

Future Payment Amount Bucket 3

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET4

Future Payment Amount Bucket 4

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET5

Future Payment Amount Bucket 5

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET6

Future Payment Amount Bucket 6

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET7

Future Payment Amount Bucket 7

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET8

Future Payment Amount Bucket 8

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET9

Future Payment Amount Bucket 9

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET10

Future Payment Amount Bucket 10

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

START_DTTM

Start Date/Time

CI_SA.START_DT

END_DTTM

End Date/Time

CI_SA.END_DT

START_DATE_KEY

Start Date (Date Dimension Surrogate) Key

CI_SA.START_DT

START_TIME_KEY

Start Time (Time Dimension Surrogate) Key

CI_SA.START_DT

END_DATE_KEY

End Date (Date Dimension Surrogate) Key

CI_SA.END_DT

END_TIME_KEY

End Time (Time Dimension Surrogate) Key

CI_SA.END_DT

PA_STATUS_KEY

Payment Arrangement Status Dimension Surrogate Key

CI_SA.SA_STATUS_FLG

Transformation Logic:

1.

Set status as ‘Active’ if the service agreement’s status is ‘Active’, ‘Pending Stop’, ‘Stopped’, or ‘Reactivated’.

2.

‘Broken’ if the service agreement’s status is ‘Closed’ with a broken payment arrangement characteristic.

3.

Kept if the service agreement’s status is ‘Closed’ without the broken payment arrangement characteristic. (Broken characteristic type/value is defined as a parameter on source.For more details on the parameters, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

4.

‘Canceled’ if the service agreement is canceled.

INSTALLMENT_CNT_KEY

Installments Count Dimension Surrogate Key

Transformation Logic:

This field populates the appropriate key using the installment amount and age ranges from the Installment Count dimension.

For details, see PA Number of Installments Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

REC_CHARGE_AMOUNT_KEY

Recurring Charge Bucket Dimension Surrogate Key

CI_SA_RCHG_HIST.RCR_CHG_AMT

Transformation Logic:

This field populates the appropriate key using the recurring charge amount and age ranges from the Recurring Charge Amount Bucket dimension.

The recurring charge amount age ranges are defined on the source.

For details, see PA Recurring Charge Amount Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

This field populates the person ID with the main customer of the account

ACCT_KEY

Account Dimension Surrogate Key

CI_SA.ACCT_ID

SA_KEY

Service Agreement Dimension Surrogate Key

CI_SA.SA_ID

PREM_KEY

Premise Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

PA_UDD1_KEY

Payment Arrangement User Defined Dimension 1 Surrogate Key

PA_UDD2_KEY

Payment Arrangement User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with a standard value of "1".

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Note : The Future Payment Age buckets configured in the source are loaded in the MDADM.B1_RANGE_LOOKUP table in the data warehouse. The ELT job for this is configured to be initial load only. Any incremental changes to these buckets after the initial data load will not be reflected in the warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated and reloaded in the fact table and the range lookup table to reflect the changes.

For details about Range look-up and reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Payment Arrangement Snapshot Fact

The Payment Arrangement Snapshot fact stores snapshots of all payment arrangements that started before the snapshot date and excludes the service agreements of pending start, canceled, and incomplete statuses. The snapshot also excludes closed service agreements where the difference (in days) between the snapshot end date and the service agreement end date is greater than the 'X' number of days to exclude closed service agreements.

The value 'X' will have to be configured as part of the BI configuration in the source system.

For more details on the parameters, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_PA_SNAP

Table Type

Fact

Fact Type

Snapshot

Driver Table

CI_SA

Stage Table Name

STG_CF_PA_SNAP

ODI Package Name

B1_PKG_CF_PA_SNAP

ETL View Name

B1_F_PA_SNAP_VW

Materialized View Name

B1_PA_SNAP_MON_MV1

B1_PA_SNAP_MON_TOPX_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PA_SNAP_KEY

Payment Arrangement Snapshot Fact Key

Transformation Logic:

This field is populated with the sequence from B1_PA_SNAP_SEQ.

SRC_SA_ID

Source Service Agreement ID

CI_SA.SA_ID

CURRENCY_CD

Currency Code

CI_SA.CURRENCY_CD

REC_CHARGE_AMT

Total Scheduled Payment Amount

CI_SA_RCHG_HIST.RCR_CHG_AMT

Transformation Logic:

This field is populated with the recurring charge amount effective as of the snapshot date.

TOTAL_PA_AMT

Total Payment Arrangement Amount

CI_ADJ.ADJ_AMT

Transformation Logic:

This field is populated with the adjustment amount for the adjustment(s) used to transfer the original service agreement’s balance.

•

If the payment arrangement is still "active" or "kept", then all non-canceled xfer adjustments are contributed to the arrangement amount.

•

If the payment arrangement is "broken", then all canceled xfer adjustments are contributed to the arrangement amount.

INSTALLMENT_CNT

Total Number of Installments

Transformation Logic:

This field is populated with the value calculated by dividing the total payment arrangement amount by recurring charge amount.

DUR_FROM_START

Duration from Start

CI_SA.START_DT

CI_SA.END_DT

Transformation Logic:

If the pay arrangement service agreement has an end date, this field is populated with the difference between start date and end date.

PAID_TO_DATE_AMT

Paid to Date Amount

CI_FT.CUR_AMT

Transformation Logic:

This field is populated with the sum of all payments made against the pay arrangement service agreement.

FUTURE_PAY_AMT

Future Payment Amount

Transformation Logic:

The future payment amount is set to zero when the payment arrangement is in Broken/Canceled states. Else, it is the difference between the Total PA Amount and Paid To Date columns.

FUTURE_PAY_AMT_BUCKET1

Future Payment Amount Bucket 1

Transformation Logic:

This field is populated with the sum of all future scheduled payments that fall into the age ranges configured for the bucket 1 slot for payment arrangements in the source system.

Future scheduled payments are identified based on the remaining amount, billing frequency, and bill cycle schedule.

For details, see PA Future Payment Age Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

FUTURE_PAY_AMT_BUCKET2

Future Payment Amount Bucket 2

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET3

Future Payment Amount Bucket 3

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET4

Future Payment Amount Bucket 4

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET5

Future Payment Amount Bucket 5

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET6

Future Payment Amount Bucket 6

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET7

Future Payment Amount Bucket 7

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET8

Future Payment Amount Bucket 8

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET9

Future Payment Amount Bucket 9

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

FUTURE_PAY_AMT_BUCKET10

Future Payment Amount Bucket 10

Note : See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic.

START_DTTM

Start Date/Time

CI_SA.START_DT

END_DTTM

End Date/Time

CI_SA.END_DT

SNAP_TYPE_CD

Snap Type Code

SNAPSHOT_DT

Snapshot Date

SNAPSHOT_DATE_KEY

Snapshot Date (Date Dimension Surrogate) Key

START_DATE_KEY

Start Date (Date Dimension Surrogate) Key

CI_SA.START_DT

START_TIME_KEY

Start Time (Time Dimension Surrogate) Key

CI_SA.START_DT

END_DATE_KEY

End Date (Date Dimension Surrogate) Key

CI_SA.END_DT

END_TIME_KEY

End Time (Time Dimension Surrogate) Key

CI_SA.END_DT

PA_STATUS_KEY

Payment Arrangement Status Dimension Surrogate Key

CI_SA.SA_STATUS_FLG

Transformation Logic:

1.

Set status as ‘Active’ if the service agreement’s status is ‘Active’, ‘Pending Stop’, ‘Stopped’, or ‘Reactivated’.

2.

‘Broken’ if the service agreement’s status is ‘Closed’ with a broken payment arrangement characteristic.

3.

Kept if the service agreement’s status is ‘Closed’ without the broken payment arrangement characteristic. (Broken characteristic type/value is defined as a parameter on source. For more details, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

4.

‘Canceled’ if the service agreement is canceled.

5.

‘Active’ if the service agreement is closed and the service agreement start date is before the snapshot date, but the end date is after the snapshot date.

INSTALLMENT_CNT_KEY

Installments Count Dimension Surrogate Key

Transformation Logic:

Based on the installment amount and the age ranges, the dimension key has to be identified.

The installment count age ranges are defined on the source.

For details, see PA Number of Installments Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

REC_CHARGE_AMOUNT_KEY

Recurring Charge Bucket Dimension Surrogate Key

CI_SA_RCHG_HIST.RCR_CHG_AMT

Transformation Logic :

This field is populated with the recurring charge amount and age ranges from the Recurring Charge Amount bucket dimension.

The recurring charge amount age ranges are defined on the source.

For details, see PA Recurring Charge Amount Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic :

This field is populated with the main customer of the account.

ACCT_KEY

Account Dimension Surrogate Key

CI_SA.ACCT_ID

SA_KEY

Service Agreement Dimension Surrogate Key

CI_SA.SA_ID

PREM_KEY

Premise Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

PA_SNAP_UDD1_KEY

Payment Arrangement User Defined Dimension 1 Surrogate Key

PA_SNAP_UDD2_KEY

Payment Arrangement User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of "1".

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Note : The Future Payment Age buckets configured in the source are loaded in the MDADM.B1_RANGE_LOOKUP table in the data warehouse. The ELT job for this is configured to be initial load only. Any incremental changes to these buckets after the initial data load will not be reflected in the warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated and reloaded in the fact table and the range lookup table to reflect the changes.

For details about Range look-up and reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Payment Tender Fact

The Payment Tender fact stores all pay tenders defined in the source system.

The ETL process for this fact is special in the sense that it considers ‘deletes’ in the source system. If a pay tender is deleted in the source system, it will also be deleted from the fact table. This fact stores the tender amount information.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_PAY_TNDR

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_PAY_TNDR

Stage Table Name

STG_CF_PAY_TNDR

ODI Package Name

B1_PKG_CF_PAY_TNDR

ETL View Name

B1_F_PAY_TNDR_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PAY_TNDR_KEY

Payment Tender Fact Key

Transformation Logic:

This field is populated with the sequence from SPL_PAY_TNDR_SEQ.

SRC_PAY_TENDER_ID

Payment Tender ID (Natural Key)

CI_PAY_TNDR.PAY_TENDER_ID

CURRENCY_CD

Currency Code

CI_PAY_TNDR.CURRENCY_CD

TNDR_CTRL_ID

Tender Control ID

CI_PAY_TNDR.TNDR_CTL_ID

TNDR_AMT

Tender Amount

CI_PAY_TNDR.TENDER_AMT

CANCEL_DATE_KEY

Date Dimension Surrogate Key

CI_FT.FREEZE_DTTM

CI_PAY_EVENT.CRE_DTTM

Transformation Logic:

This field is populated only when the pay tender has been canceled. The freeze date on the pay segment cancellation FT will be used. Otherwise, the pay event's creation date will be used.

PAYEVT_DATE_KEY

Date Dimension Surrogate Key

CI_PAY_EVENT.PAY_DT

TNDR_CTRL_DATE_KEY

Date Dimension Surrogate Key

CI_TNDR_CTL.CRE_DTTM

Transformation Logic:

Only the date portion from the source field will be used.

TNDR_STATUS_KEY

Tender Status Dimension Surrogate Key

CI_PAY_TNDR.TNDR_STATUS_FLG

TNDR_TYPE_KEY

Tender Type Dimension Surrogate Key

CI_PAY_TNDR.TENDER_TYPE_CD

TNDR_SRCE_KEY

Tender Source Dimension Surrogate Key

CI_TNDR_CTL.TNDR_SOURCE_CD

ACCT_KEY

Account Dimension Surrogate Key

CI_PAY_TNDR.PAYOR_ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

Transaction Logic:

This field is populated with the characteristic premise of the payer account’s non-cancelled and non-closed service agreement with latest start date.

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transaction Logic:

The main customer of the payor account will be used to populate this field.

PREM_KEY

Premise Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

Transaction Logic:

This field is populated with the characteristic premise of the payer account’s non-cancelled and non-closed service agreement with latest start date.

PAY_CAN_RSN_KEY

Payment Cancel Reason Dimension Surrogate Key

CI_PAY_TNDR.CAN_RSN_CD

PAY_TNDR_UDD1_KEY

Payment Tender User Defined Dimension 1 Surrogate Key

PAY_TNDR_UDD2_KEY

Payment Tender User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of "1".

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Service Agreement Arrears Snapshot Fact

The Service Agreement Arrears Snapshot fact stores the snapshots of all non-canceled service agreements that started before the snapshot date. The snapshot also excludes the closed service agreements where the difference (in days) between the snapshot end date and the service agreement end date is greater than the 'X' number of days to exclude the closed service agreements. The value 'X' should be configured as part of the BI configuration in the source system.

Note that only UDM1 to UDM10 fields are used to populate the bucketized arrears information. If more than 10 age buckets are configured in Oracle Utilities Customer Care and Billing for arrears, only the first 10 buckets will be considered by the out-of-the-box ETL. If the arrears amount need to be bucketized into more than 10 buckets, customize it to populate the remaining arrears bucket columns.

For details, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_ARREARS

Table Type

Fact

Fact Type

Snapshot

Driver Table

CI_SA

Stage Table Name

STG_CF_ARREARS

ODI Package Name

B1_PKG_CF_ARREARS

ETL View Name

B1_F_ARREARS_VW

Materialized View Name

B1_ARREARS_MON_MV1

B1_ARREARS_MON_TOPX_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

ACCT_KEY

Account Dimension Surrogate Key

CI_SA.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

ARREARS_KEY

Arrears Fact Generated Key

Transformation Logic:

This field is populated with the sequence from B1_ARREARS_SEQ.

CURRENCY_CD

Currency Code

CI_SA.CURRENCY_CD

CURR_BAL_AMT

Current Balance

CI_FT.CUR_AMT

DATE_KEY

Date Dimension Surrogate Key

Transformation Logic:

This field is populated with the snapshot date.

FISCAL_CAL_KEY

Fiscal Period Dimension Surrogate Key

CI_CAL_PERIOD.CALENDER_ID

Transformation Logic:

This field is populated using the calendar based on accounting date.

PAYOFF_BAL_AMT

Payoff Balance Amount

CI_FT.TOT_AMT

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

This field is populated with the main customer of the account.

PREM_KEY

Premise Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic:

This field is populated with the Char Prem ID. If not found, it is populated with the mailing premise ID from account.

SA_KEY

Service Agreement Dimension Surrogate Key

CI_SA.SA_ID

SNAP_TYPE_CD

Snap Type

UDM1

User Defined Measure 1

Transformation Logic:

The arrears date and arrears amounts are identified by fetching the debits and credits. Amounts are summed and put in the UDM columns based on the age ranges. These age ranges are defined on the source.

For details, see SA Arrears Buckets in the Configuring Oracle Utilities Analytics chapter in the Oracle Utilities Analytics Administration Guide.

UDM2

User Defined Measure 2

Note : See the UDM1 field for transformation logic.

UDM3

User Defined Measure 3

Note : See the UDM1 field for transformation logic.

UDM4

User Defined Measure 4

Note : See the UDM1 field for transformation logic.

UDM5

User Defined Measure 5

Note : See the UDM1 field for transformation logic.

UDM6

User Defined Measure 6

Note : See the UDM1 field for transformation logic.

UDM7

User Defined Measure 7

Note : See the UDM1 field for transformation logic.

UDM8

User Defined Measure 8

Note : See the UDM1 field for transformation logic.

UDM9

User Defined Measure 9

Note : See the UDM1 field for transformation logic.

UDM10

User Defined Measure 10

Note : See the UDM1 field for transformation logic.

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

UDM21

User Defined Measure 21

UDM22

User Defined Measure 22

UDM23

User Defined Measure 23

UDM24

User Defined Measure 24

UDM25

User Defined Measure 25

UDM26

User Defined Measure 26

UDM27

User Defined Measure 27

UDM28

User Defined Measure 28

UDM29

User Defined Measure 29

UDM30

User Defined Measure 30

FACT_CNT

Fact Count

Transaction Logic:

This field is populated with a standard value of "1".

RATE_KEY

Rate dimension surrogate key

CI_SA_RS_HIST.RS_CD

Transaction Logic:

This field uses the Rate Code from service agreement rate history based on the effective date.

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

ARREARS_UDD1_KEY

Arrears User Defined Dimension 1 Surrogate Key

ARREARS_UDD2_KEY

Arrears User Defined Dimension 2 Surrogate Key

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

SRC_SA_ID

SA Identifier

CI_SA.SA_ID

SNAPSHOT_DT

Snapshot Date

DAYS_LAST_FRZ_BS

Number of Days Since Last Frozen BS

CI_BSEG.END_DT

Calculation Logic:

This field is populated with the difference (in days) between the freeze date of the last frozen bill segment for the service agreement and the snapshot end date.

Note : If there is no frozen bill segment for the service agreement, use the service agreement start date.

DAYS_LAST_FRZ_BS_KEY

Days Since Last Frozen Dimension Surrogate Key

Transformation Logic:

Based on the measure retrieved above, fetch the corresponding dimension key.

DAYS_UNBILLED_USG

Number of Days of Unbilled Usage

CI_BSEG.END_DT

CI_SA.END_DT

Transformation Logic:

1.

If there is a frozen bill segment:

a.

If the service agreement has ended (Status 60), use the difference between the agreement end date and the end date of bill segment latest before the snapshot date.

b.

Else, use the difference between the snapshot date and the end date of bill segment latest before the snapshot date.

3.

If there is no frozen bill segment:

a.

If the service agreement has ended (Status 60), use the difference between agreement’s end date and start date.

b.

Else, use the difference between the snapshot date and the service agreement’s start date.

DAYS_UNBILLED_USG_KEY

Days of Unbilled Usage Dimension Surrogate Key

Transformation Logic:

Based on the measure retrieved above, fetch the corresponding dimension key.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Note : The Arrears Age buckets configured in the source are loaded in the MDADM.B1_RANGE_LOOKUP table in the data warehouse. The ELT job for this is configured to be initial load only. Any incremental changes to these buckets after the initial data load will not be reflected in the warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated and reloaded in the fact table and the range lookup table to reflect the changes.

For details about Range look-up and reloading the data, see the Data Reload section in Oracle Utilities Analytics Administration Guide.

Service Agreement Billing Fact

The Service Agreement Billing fact stores the details of the service agreements eligible for billing in a bill window based on the bill segment creation. Information related to the latest bill segment for a service agreement, bill cycle, and window start date combination is stored in this fact. Manual bills are excluded from this fact.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_SA_BILLING

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_BSEG

Stage Table Name

STG_CF_SA_BILLING

ODI Package Name

B1_PKG_CF_SA_BILLING

ETL View Name

B1_F_SA_BILLING_VW

Materialized View Name

B1_SA_BILLING_MON_MV1

B1_SA_BILLING_MON_TOPX_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

SA_BILLING_KEY

SA Billing Fact Key

Transformation Logic:

This field is populated with the sequence from B1_SA_BILLING_ACCUM_SEQ.

SRC_SA_ID

Service Agreement ID

CI_BSEG.SA_ID

SRC_BILL_CYC_CD

Bill Cycle Code

CI_BSEG.BILL_CYC_CD

Note: If a bill segment does not have a bill cycle, it is retrieved from the bill.

WIN_START_DTTM

Window Start Date/Time

CI_BSEG.WIN_START_DT

CI_BILL.WIN_START_DT

Note: If a bill segment does not have a window start date populated, it is fetched from the bill.

FROZEN_DUR

Duration to Frozen

CI_BILL.CRE_DTTM

CI_FT.

FREEZE_DTTM

Transformation Logic:

This field is populated with the difference (in hours) between the bill’s creation date and the bill segment freeze date.

REBILL_IND

Re-billed Indicator

CI_BSEG.REBILL_SEG_ID

Transformation Logic:

This field is populated with "1" if the latest bill segment for the service agreement is a re-bill. Else, "0".

ORIG_REV_AMT

Original Revenue Amount

CI_FT_GL.AMOUNT

Transformation Logic:

This field is populated with the revenue amount of the original bill segment if the current bill segment being processed is a re-billed one.

REV_AMT

Revenue Amount

CI_FT_GL.AMOUNT

Transformation Logic:

This field is populated with the absolute value of total amount of all financial transaction GLs whose distribution code’s characteristic type/ revenue characteristic value match with the characteristic type/value configured as part of the BI configuration in the source system and ‘Effective’ on the specific date.

For more details, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

CURRENCY_CD

Currency Code

CI_FT.CURRENCY_CD

FIRST_BS_IND

First Bill Segment of SA Indicator

CI_SA.START_DT

CI_BSEG.START_DT

Calculation Logic:

This field is set to 1 if the service agreement start date matches with the bill segment start date. Else, it is set to zero.

EST_IND

Estimated Indicator

CI_BSEG.EST_SW

Transformation Logic:

This field is populated with "1" if the latest bill segment is an estimate. Else, "0".

HIGH_BILL_CASE_IND

High Bill Complaint Indicator

Transformation Logic:

Set the indicator to 1 if all the below conditions return true:

1.

If there exists a case with Case Characteristic Value same as bill ID of the bill segment being processed. Characteristic type corresponding to the bill would be defined on the source system extract parameters.

2.

The above case should be of Case type corresponding to High Bill Complaint. These Case types are again provided by users on the source system extract parameters.

3.

Case should not be in a status same as the ‘Exclusion’ status mentioned on source system extract parameters.

For more information on these parameters that have to be configured on source, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

FREEZE_DTTM

Freeze Date/Time

CI_FT.FREEZE_DTTM

CRE_DTTM

Creation Date/Time

CI_BILL.CRE_DTTM

BILL_CYC_SCH_KEY

Bill Cycle Schedule Dimension Surrogate Key

CI_BSEG.BILL_CYC_CD + WIN_START_DT

CI_BILL.BILL_CYC_CD + WIN_START_DT

Note: If the bill segment does not have information, it is fetched from the bill.

BSEG_STATUS_KEY

Bill Segment Status Dimension Surrogate Key

CI_BSEG.BSEG_STAT_FLG

Transformation Logic:

1.

Use the actual status if bill segment is not in error or deleted.

2.

If status is ‘Error’, look for the error message. If error displayed is "Awaiting Bill Determinants from MDM", use the ‘Awaiting BD’ status.

3.

When the bill segment is deleted, use the ‘Deleted’ status.

BILL_CAN_RSN_KEY

Bill Cancel Reason Dimension Surrogate Key

CI_BSEG.CAN_RSN_CD

Transformation Logic:

The Bill Cancel Reason value should be populated only if the service agreement’s bill segment is pending cancel or canceled. If the latest bill segment is a Rebill bill segment, the cancel reason should be on the older BS.

MSG_KEY

Message Dimension Surrogate Key

CI_BSEG_EXCP.MESSAGE_CAT_NBR

CI_BSEG_EXCP.MESSAGE_NBR

BILL_DAY_IN_WIN_KEY

Day In Window Dimension Surrogate Key

CI_FT.FREEZE_DTTM

CI_BILL_CYC_SCH.FREEZE_DTTM

Transformation Logic:

This logic is applicable for bill segments with ‘Frozen’ status.

If the freeze date is between the bill cycle schedule start and end dates, set 'In Window', else 'Outside Window'. The billing day in window can be calculated as difference between the freeze date and the window start date. Based on this difference, suitable age range needs to be identified.

These age ranges are configured on source. For details, see Billing Day In Window Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

DAYS_TO_WIN_CLS_KEY

Days to Window Closure Dimension Surrogate Key

CI_BILL_CYC_SCH.WIN_END_DT

Transformation Logic:

This field is populated only if the latest bill segment for the service agreement is in status Error/Deleted/Awaiting BD. This is the difference between the window end date and current date. If the window end date is greater than sysdate, pick the range from open window age configuration. Else, pick the one from closed window age configurations, which are defined on source.

For details, see Days Before Bill Window Closes Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

MSRMT_TYPE_KEY

Measurement Type Dimension Surrogate Key

C1-USAGE.USG_DATA_AREA

Transformation Logic:

To determine the Measurement Type, check Special Role flag on SA Type.

1.

If it’s "Bill Determinants Required", check if there’s a Usage Request for the service agreement’s latest bill segment and determine the measurement type based on the usage request list (each entry in the list has a ‘usage type" associated with it).

2.

If it’s "Interval", set measurement type to "Interval".

3.

If it’s any other special role flag value (billable charge, deposit, PA, etc), set measurement type to "N/A".

4.

If it’s blank, check the valid SP Types linked to the SA Type and see if any have a subtype of "Meter". If yes, set measurement type to "Scalar". Otherwise, set to "N/A".

FREEZE_DATE_KEY

Freeze Date (Date Dimension Surrogate) Key

CI_FT.FREEZE_DTTM

FREEZE_TIME_KEY

Freeze Time (Time Dimension Surrogate) Key

CI_FT.FREEZE_DTTM

CRE_DATE_KEY

Creation Date (Date Dimension Surrogate) Key

CI_BILL.CRE_DTTM

CRE_TIME_KEY

Creation Time (Time Dimension Surrogate) Key

CI_BILL.CRE_DTTM

WIN_START_DATE_KEY

Window Start Date (Date Dimension Surrogate) Key

CI_BSEG.WIN_START_DT

CI_BILL.WIN_START_DT

WIN_START_TIME_KEY

Window Start Time (Time Dimension Surrogate) Key

CI_BSEG.WIN_START_DT

CI_BILL.WIN_START_DT

ACCT_KEY

Account Dimension Surrogate Key

CI_SA.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic:

If the characteristic premise is not found, use the mailing premise from account.

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

This field is populated with the main customer of the account.

PREM_KEY

Premise Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

CI_ACCT.MAILING_PREM_ID

Transformation Logic:

If the characteristic premise is not found, use the mailing premise from account.

SA_KEY

Service Agreement Dimension Surrogate Key

CI_BSEG.SA_ID

RATE1_KEY

Rate Dimension Surrogate Key

CI_SA_RS_HIST.RS_CD

CI_BSEG_CALC.RS_CD

Transformation Logic:

If the bill segment has no calculation header, retrieve the rate effective on the service agreement as of the bill segment start date.

If bill segment has calculation headers, pick the first two distinct primary rate schedules and store these in two columns.

RATE2_KEY

Rate Dimension Surrogate Key

CI_SA_RS_HIST.RS_CD

CI_BSEG_CALC.RS_CD

Note : See RATE1_KEY description for logic details.

SA_BILLING_UDD1_KEY

SA Billing User Defined Dimension 1 Surrogate Key

SA_BILLING_UDD2_KEY

SA Billing User Defined Dimension 2 Surrogate Key

FACT_CNT

Fact Count

Transaction Logic:

This field is populated with a standard value of "1".

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Service Agreement Fact

The Service Agreement fact stores all service agreements defined in the source system.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_SA

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_SA

Stage Table Name

STG_CF_SA

ODI Package Name

B1_PKG_CF_SA

ETL View Name

B1_F_SA_VW

Materialized View Name

B1_SA_TOPX_MON_MV1

B1_SA_TOPX_MON_MV2

B1_SA_MON_MV1

B1_SA_MON_MV2

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

SA_FACT_KEY

Service Agreement Fact Key

Transformation Logic:

This field is populated with the sequence from B1_SA_SEQ.

SRC_SA_ID

Service Agreement ID (Natural Key)

CI_SA.SA_ID

CURRENCY_CD

Currency Code

CI_SA.CURRENCY_CD

SA_DURATION

Service Agreement Duration

CI_SA.START_DT

CI_SA.END_DT

Note: If the end date is not populated, set SA Duration = 0. Else, set SA Duration (computed in hours) = End Date - Start Date.

FACT_CNT

Count

Transformation Logic:

This field is populated with the standard value of "1".

ACCT_KEY

Account Dimension Surrogate Key

CI_SA.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

START_DATE_KEY

Start Date (Date Dimension Surrogate Key)

CI_SA.START_DT

END_DATE_KEY

End Date (Date Dimension Surrogate Key)

CI_SA.END_DT

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

This field is populated with the main person of the account.

PREM_KEY

Premise Dimension Surrogate Key

CI_SA.CHAR_PREM_ID

SA_KEY

Service Agreement Dimension Surrogate Key

CI_SA.SA_ID

SA_STATUS_KEY

Service Agreement Status Dimension Surrogate Key

CI_SA.SA_STATUS_FLG

SA_UDD1_KEY

Service Agreement User Defined Dimension 1 Surrogate Key

SA_UDD2_KEY

Service Agreement User Defined Dimension 2 Surrogate Key

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Uncollectible Event Fact

The Uncollectible Event fact stores all write-off events defined in the source system.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_UCOL_EVT

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_WO_EVT

Stage Table Name

STG_CF_UCOL_EVT

ODI Package Name

B1_PKG_CF_UCOL_EVT

ETL View Name

B1_F_UCOL_EVT_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

ACCT_KEY

Account Dimension Surrogate Key

CI_WO_PROC.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic:

If the mailing premise on the account is not found, then the characteristic premise on the service agreement will be used.

SRC_UCPROC_ID

Uncollectible Process ID (Natural Key)

CI_WO_PROC.WO_PROC_ID

UCOL_EVT_KEY

Uncollectible Event Fact Key

Transformation Logic:

This field is populated with the sequence from B1_UCOL_EVT_SEQ.

CURRENCY_CD

Currency Code

CI_ACCT.CURRENCY_CD

Transformation Logic:

This field is populated with the currency code from account.

EVT_DATE_KEY

Uncollectible Event Date

CI_WO_EVT.COMPLETION_DT

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

This field is populated with the main customer of the account associated with the write-off event.

PREM_KEY

Premise Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic:

If the mailing premise on the account is not found then the characteristic premise on the service agreement will be used.

UCEVT_TYPE_KEY

Uncollectible Event Type Dimension Surrogate Key

CI_WO_EVT.WO_EVT_TYP_CD

UCOL_EVT_SEQ

Uncollectible Event Sequence

CI_WO_EVT.EVT_SEQ

UCPROC_TMPL_KEY

Uncollectible Process Template Dimension Surrogate Key

CI_WO_PROC.WO_PROC_TMPL_CD

FACT_CNT

Count

Transformation Logic:

This field is populated with the standard value of "1".

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UCEVT_UDD1_KEY

Uncollectible Event User Defined Dimension 1 Surrogate Key

UCEVT_UDD2_KEY

Uncollectible Event User Defined Dimension 2 Surrogate Key

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Uncollectible Process Fact

The Uncollectible Process fact stores all write-off processes defined in the source system. This fact captures metrics, such as the duration of the process, the arrears at the start of the process, and at the end of the process.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_UCOL_PROC

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_WO_PROC

Stage Table Name

STG_CF_UCOL_PROC

ODI Package Name

B1_PKG_CF_UCOL_PROC

ETL View Name

B1_F_UCOL_PROC_VW

Materialized View Name

B1_UCOLLPROC_MON_MV1

B1_UCOLLPROC_MON_MV2

B1_UCOLLPROC_MON_TOPX_MV1

B1_UCOLLPROC_MON_TOPX_MV2

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

UCPROC_KEY

Uncollectible Process Fact Key

Transformation Logic:

This field is populated with the sequence from SPL_UCPROC_SEQ.

SRC_UPROC_ID

Uncollectible Process ID (Natural Key)

CI_WO_PROC.WO_PROC_ID

CURRENCY_CD

Currency Code

CI_ACCT.CURRENCY_CD

UCPROC_DURATION

Uncollectible Process Duration

CI_WO_PROC.CRE_DTTM

CI_WO_EVT.COMPLETION_DT

Transformation Logic :

This field is populated as the difference (in hours) between the creation date of the write-off process and the maximum completion date of the associated write-off events.

ARRS_AT_START

Arrears at Start

CI_FT.TOT_AMT

Calculation Logic:

This field is calculated as the sum of total balance of all the service agreements linked to the write-off process as of the creation date.

ARRS_AT_END

Arrears at End

CI_FT.CUR_AMT

Transformation Logic:

This field is populated only if status of the write-off process is not active. It is calculated as the sum of the current balance of all the service agreements linked to the write-off process as of the max completion date of the associated write-off events.

ARRS_DIFF

Arrears at End - Arrears at Start

Calculation Logic :

This field is populated only if status of the write-off process is not active. It is calculated as the difference between the arrears at start and arrears at end.

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of "1".

ACCT_KEY

Account Dimension Surrogate Key

CI_WO_PROC.ACCT_ID

ADDR_KEY

Address Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic:

If the mailing premise on the account is not found, the characteristic premise on the service agreement will be used.

PER_KEY

Person Dimension Surrogate Key

CI_ACCT_PER.PER_ID

Transformation Logic:

This field is populated with the main customer of the account associated with the write-off process.

PREM_KEY

Premise Dimension Surrogate Key

CI_ACCT.MAILING_PREM_ID

CI_SA.CHAR_PREM_ID

Transformation Logic:

If the mailing premise on the account is not found, the characteristic premise on the service agreement will be used.

START_DATE_KEY

Start Date (Date Dimension Surrogate Key)

CI_WO_PROC.CRE_DTTM

END_DATE_KEY

Uncollectible Process End Date

CI_WO_EVT.COMPLETION_DT

CI_WO_PROC.CRE_DTTM

Transformation Logic:

This field is populated only when the write-off process is not active. It is populated with the maximum write-off event completion date. If no event completion dates are found, creation date of the write-off process will be used.

UCPROC_STAT_KEY

Uncollectible Process Status Dimension Surrogate Key

CI_WO_PROC.WO_STATUS_FLG

Transformation Logic:

Note that the write-off process statuses from the source will be transformed while storing in the fact.

•

If the write-off process is ‘Active’, the same status will be retained.

•

If the write-off process is ‘Inactive’ and status reason is ‘Completed’, then the status will be marked as ‘Ineffective’ in the fact.

•

Otherwise the status will be marked as ‘Effective’ in the fact.

UCPROC_TMPL_KEY

Uncollectible Process Template Dimension Surrogate Key

CI_WO_PROC.WO_PROC_TMPL_CD

UCPROC_UDD1_KEY

Uncollectible Process User Defined Dimension 1 Surrogate Key

UCPROC_UDD2_KEY

Uncollectible Process User Defined Dimension 2 Surrogate Key

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

Recent To Do Entry Fact

The Recent To Do Entry fact stores all To-Do entries that have been completed in the last ‘X’ days and also the incomplete To-Do entries.

The value ‘X’ has to be configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_RECENT_TD_ENTRY

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_TD_ENTRY

Stage Table Name

STG_CF_RECENT_TD_ENTRY

ODI Package Name

B1_PKG_CF_RECENT_TD_ENTRY

ETL View Name

B1_F_TD_ENTRY_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TD_ENTRY_KEY

To Do Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from OUBI_TD_ENTRY_SEQ.

SRC_TD_ENTRY_ID

To Do Entry ID (Natural Key)

CI_TD_ENTRY.TD_ENTRY_ID

SA_KEY

Service Agreement Dimension Surrogate Key

CI_TD_ENTRY_CHA.CHAR_VAL_FK1

Transformation Logic:

This field is populated with the SA ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

PER_KEY

Person Dimension Surrogate Key

CI_TD_ENTRY_CHA.CHAR_VAL_FK1

Transformation Logic:

This field is populated with the Person ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

ACCT_KEY

Account Dimension Surrogate Key

CI_TD_ENTRY_CHA.CHAR_VAL_FK1

Transformation Logic:

This field is populated with the Account ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

PREM_KEY

Premise Dimension Surrogate Key

CI_TD_ENTRY_CHA.CHAR_VAL_FK1

Transformation Logic:

This field is populated with the Premise ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

ADDR_KEY

Address Dimension Surrogate Key

CI_TD_ENTRY_CHA.CHAR_VAL_FK1

Transformation Logic:

This field is populated with the Premise ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

USER_KEY

User Dimension Surrogate Key

CI_TD_ENTRY.ASSIGNED_USER_ID

TD_TYPE_KEY

To Do Type Dimension Key

CI_TD_ENTRY.TD_TYPE_CD

TD_ROLE_KEY

To Do Role Dimension Key

CI_TD_ENTRY.ROLE_ID

TD_STATUS_KEY

To Do Status Dimension Key

CI_TD_ENTRY.ENTRY_STATUS_FLG

TD_PRIORITY_KEY

To Do Priority Dimension Key

CI_TD_ENTRY.TD_PRIORITY_FLG

TD_SKILL_KEY

To Do Skill Dimension Surrogate Key

CI_TD_TYPE_CHAR.ADHOC_CHAR_VAL

CI_TD_TYPE_CHAR.CHAR_TYPE_CD

CI_TD_TYPE_CHAR.CHAR_VAL

Transformation Logic:

This field is populated based on the characteristic types configured on the To Do entry’s To Do type. Only those characteristic types configured as skills in the AQM feature configuration will be considered.

If a To Do type has both regular skills and ad-hoc skills, then the ad-hoc skills will be given the priority. The first skill to match the message category/number on the To Do entry will be picked up.

MSG_KEY

Message Dimension Surrogate Key

CI_TD_ENTRY.MESSAGE_CAT_NBR

CI_TD_ENTRY.MESSAGE_NBR

TD_KEY

To Do Dimension Surrogate Key

CI_TD_ENTRY.TD_ENTRY_ID

CREATE_DATE_KEY

Create Date (Date Dimension Surrogate Key)

CI_TD_ENTRY.CRE_DTTM

Transformation Logic:

This field is populated with the date portion.

CREATE_TIME_KEY

Create Time (Time Dimension Surrogate Key)

CI_TD_ENTRY.CRE_DTTM

Transformation Logic:

This field is populated with the time portion.

CREATE_DTTM

Create Date/Time

CI_TD_ENTRY.CRE_DTTM

CMPL_DATE_KEY

Complete Date (Date Dimension Surrogate Key)

CI_TD_ENTRY.COMPLETE_DTTM

Transformation Logic:

This field is populated with the date portion.

CMPL_TIME_KEY

Complete Time (Time Dimension Surrogate Key)

CI_TD_ENTRY.COMPLETE_DTTM

Transformation Logic:

This field is populated with the time portion.

CMPL_DTTM

Complete Date/Time

CI_TD_ENTRY.COMPLETE_DTTM

ASSIGN_DATE_KEY

Assign Date (Date Dimension Surrogate Key)

CI_TD_ENTRY.ASSIGNED_DTTM

Transformation Logic:

This field is populated with the date portion.

ASSIGN_TIME_KEY

Assign Time (Time Dimension Surrogate Key)

CI_TD_ENTRY.ASSIGNED_DTTM

Transformation Logic:

This field is populated with the time portion.

ASSIGN_DTTM

Assign Date/Time

CI_TD_ENTRY.ASSIGNED_DTTM

Transformation Logic:

This field is populated with the last assigned date/time of the respective To-Do entry.

OPEN_IND

Open Indicator

CI_TD_ENTRY.ENTRY_STATUS_FLG

Transformation Logic:

This field is populated with "1" if the To Do entry is still ‘Open’. Else, it is populated with "0".

ASSIGN_IND

Assigned Indicator

CI_TD_ENTRY.ENTRY_STATUS_FLG

Transformation Logic:

This field is populated with "1" if To Do entry is still ‘Assigned’. Else, it is populated with "0".

CMPL_IND

Completed Indicator

CI_TD_ENTRY.ENTRY_STATUS_FLG

Transformation Logic:

This field is populated with "1" if To Do entry is still ‘Completed’. Else, it is populated with "0".

REL_TODOS_CNT

Number of Related To Dos

Transformation Logic:

This field is populated with the count of other incomplete To Do entries that reference the same set of characteristics as the current To Do entry.

HOURS

Hours

CI_TD_ENTRY.CRE_DTTM

CI_TD_ENTRY.COMPLETE_DTTM

Transformation Logic:

This field is populated with the number of hours between the Create Time and Completion Time. It is populated with "0" if the respective To Do is incomplete.

OPEN_HOURS

Open Hours

CI_TD_LOG.LOG_DTTM

Transformation Logic:

This field is populated with the total number of hours the To Do entry was in "Open" state (not assigned).

ASSIGN_HOURS

Assign Hours

CI_TD_LOG.LOG_DTTM

Transformation Logic:

This field is populated with the total number of hours the To Do entry was in "Being Worked On" state. It is populated with "0" if the respective To Do is still being worked on. It is populated with "0" if the respective To Do is still Open.

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGEN6

User Defined Degenerate Dimension 6

UDDGEN7

User Defined Degenerate Dimension 7

UDDGEN8

User Defined Degenerate Dimension 8

UDDGEN9

User Defined Degenerate Dimension 9

UDDGEN10

User Defined Degenerate Dimension 10

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of "1".

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.

To Do Entry Fact

The To Do Entry fact accumulates all To-Do entries created in the source system. This fact captures metrics, such as the number hours it was open, assigned, total hours, and also the number of related To Dos.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_TD_ENTRY

Table Type

Fact

Fact Type

Accumulation

Driver Table

CI_TD_ENTRY

Stage Table Name

STG_CF_TD_ENTRY

ODI Package Name

B1_PKG_CF_TD_ENTRY

ETL View Name

B1_F_TD_ENTRY_VW

Materialized View Name

B1_TD_ENTRY_DOW_MV1

B1_TD_ENTRY_DOW_MV2

B1_TD_ENTRY_MON_TOPX_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

TD_ENTRY_KEY

To Do Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from OUBI_TD_ENTRY_SEQ.

SRC_TD_ENTRY_ID

To Do Entry ID (Natural Key)

CI_TD_ENTRY.TD_ENTRY_ID

SA_KEY

Service Agreement Dimension Surrogate Key

CI_TD_ENTRY_CHA.CHAR_VAL_FK1

Transformation Logic:

This field is populated with the SA ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

PER_KEY

Person Dimension Surrogate Key

CI_TD_ENTRY_CHA.CHAR_VAL_FK1

Transformation Logic:

This field is populated with the Person ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

ACCT_KEY

Account Dimension Surrogate Key

CI_TD_ENTRY_CHA.CHAR_VAL_FK1

Transformation Logic:

This field is populated with the Account ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

PREM_KEY

Premise Dimension Surrogate Key

CI_TD_ENTRY_CHA.CHAR_VAL_FK1

Transformation Logic:

This field is populated with the Premise ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

ADDR_KEY

Address Dimension Surrogate Key

CI_TD_ENTRY_CHA.CHAR_VAL_FK1

Transformation Logic:

This field is populated with the Premise ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system.

For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics chapter) in the Oracle Utilities Analytics Administration Guide.

USER_KEY

User Dimension Surrogate Key

CI_TD_ENTRY.ASSIGNED_USER_ID

TD_TYPE_KEY

To Do Type Dimension Key

CI_TD_ENTRY.TD_TYPE_CD

TD_ROLE_KEY

To Do Role Dimension Key

CI_TD_ENTRY.ROLE_ID

TD_STATUS_KEY

To Do Status Dimension Key

CI_TD_ENTRY.ENTRY_STATUS_FLG

TD_PRIORITY_KEY

To Do Priority Dimension Key

CI_TD_ENTRY.TD_PRIORITY_FLG

TD_SKILL_KEY

To Do Skill Dimension Surrogate Key

CI_TD_TYPE_CHAR.ADHOC_CHAR_VAL

CI_TD_TYPE_CHAR.CHAR_TYPE_CD

CI_TD_TYPE_CHAR.CHAR_VAL

Transformation Logic:

This field is populated based on the characteristic types configured on the To Do entry’s To Do type. Only those characteristic types configured as skills in the AQM feature configuration will be considered.

If a To Do type has both regular skills and ad-hoc skills, then the ad-hoc skills will be given the priority. The first skill to match the message category/number on the To Do entry will be picked up.

MSG_KEY

Message Dimension Surrogate Key

CI_TD_ENTRY.MESSAGE_CAT_NBR

CI_TD_ENTRY.MESSAGE_NBR

TD_KEY

To Do Dimension Surrogate Key

CI_TD_ENTRY.TD_ENTRY_ID

CREATE_DATE_KEY

Create Date (Date Dimension Surrogate Key)

CI_TD_ENTRY.CRE_DTTM

Transformation Logic:

This field is populated with the date portion.

CREATE_TIME_KEY

Create Time (Time Dimension Surrogate Key)

CI_TD_ENTRY.CRE_DTTM

Transformation Logic:

This field is populated with the time portion.

CREATE_DTTM

Create Date/Time

CI_TD_ENTRY.CRE_DTTM

CMPL_DATE_KEY

Complete Date (Date Dimension Surrogate Key)

CI_TD_ENTRY.COMPLETE_DTTM

Transformation Logic:

This field is populated with the date portion.

CMPL_TIME_KEY

Complete Time (Time Dimension Surrogate Key)

CI_TD_ENTRY.COMPLETE_DTTM

Transformation Logic:

This field is populated with the time portion.

CMPL_DTTM

Complete Date/Time

CI_TD_ENTRY.COMPLETE_DTTM

ASSIGN_DATE_KEY

Assign Date (Date Dimension Surrogate Key)

CI_TD_ENTRY.ASSIGNED_DTTM

Transformation Logic:

This field is populated with the date portion.

ASSIGN_TIME_KEY

Assign Time (Time Dimension Surrogate Key)

CI_TD_ENTRY.ASSIGNED_DTTM

Transformation Logic:

This field is populated with the time portion.

ASSIGN_DTTM

Assign Date/Time

CI_TD_ENTRY.ASSIGNED_DTTM

Transformation Logic:

This field is populated with the last assigned date/time of the respective To-Do entry.

OPEN_IND

Open Indicator

CI_TD_ENTRY.ENTRY_STATUS_FLG

Transformation Logic:

This field is populated with "1" if the To Do entry is still ‘Open’. Else, it is populated with "0".

ASSIGN_IND

Assigned Indicator

CI_TD_ENTRY.ENTRY_STATUS_FLG

Transformation Logic:

This field is populated with "1" if To Do entry is still ‘Assigned’. Else, it is populated with "0".

CMPL_IND

Completed Indicator

CI_TD_ENTRY.ENTRY_STATUS_FLG

Transformation Logic:

This field is populated with "1" if To Do entry is still ‘Completed’. Else, it is populated with "0".

REL_TODOS_CNT

Number of Related To Dos

Transformation Logic:

This field is populated with the count of other incomplete To Do entries that reference the same set of characteristics as the current To Do entry.

HOURS

Hours

CI_TD_ENTRY.CRE_DTTM

CI_TD_ENTRY.COMPLETE_DTTM

Transformation Logic:

This field is populated with the number of hours between the Create Time and Completion Time. It is populated with "0" if the respective To Do is incomplete.

OPEN_HOURS

Open Hours

CI_TD_LOG.LOG_DTTM

Transformation Logic:

This field is populated with the total number of hours the To Do entry was in "Open" state (not assigned).

ASSIGN_HOURS

Assign Hours

CI_TD_LOG.LOG_DTTM

Transformation Logic:

This field is populated with the total number of hours the To Do entry was in "Being Worked On" state. It is populated with "0" if the respective To Do is still being worked on. It is populated with "0" if the respective To Do is still Open.

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGEN6

User Defined Degenerate Dimension 6

UDDGEN7

User Defined Degenerate Dimension 7

UDDGEN8

User Defined Degenerate Dimension 8

UDDGEN9

User Defined Degenerate Dimension 9

UDDGEN10

User Defined Degenerate Dimension 10

TD_ENTRY_UDD1_KEY

To Do Entry User Defined Dimension 1 Surrogate Key

TD_ENTRY_UDD2_KEY

To Do Entry User Defined Dimension 2 Surrogate Key

TD_ENTRY_UDD3_KEY

To Do Entry User Defined Dimension 3 Surrogate Key

TD_ENTRY_UDD4_KEY

To Do Entry User Defined Dimension 4 Surrogate Key

TD_ENTRY_UDD5_KEY

To Do Entry User Defined Dimension 5 Surrogate Key

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of "1".

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID).

JOB_NBR

Job Number

Transformation Logic:

This field is populated with the ODI job execution session number.