Oracle Utilities Extractors and Schema for Oracle Utilities Network Management System

Data Mapping Guide

Release 2.5.2.0.1

E49012-07

June 2016

Oracle Utilities Extractors and Schema for Oracle Utilities Network Management System Data Mapping Guide

E49012-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 Oracle Utilities Network Management System source system to the Oracle Utilities Extractors and Schema target product.

Audience

The guide is intended for all implementers of Oracle Utilities Extractors and Schema for Oracle Utilities Extractors and Schema for Oracle Utilities Network Management System.

Related Documents

For more information, see the following documents:

•

Oracle Utilities Analytics Getting Started Guide

•

Oracle Utilities Analytics Release Notes

•

Oracle Utilities Analytics Quick Install Guide

•

Oracle Utilities Analytics Installation Guide

•

Oracle Utilities Analytics Administration Guide

•

Oracle Utilities Analytics Developer’s Guide

•

See Also :

•

Oracle Utilities Network Management System 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 Extractors and Schema for Oracle Utilities Network Management System source system to the Oracle Utilities Extractors and Schema target product. The guide describes the data mapping between the source system and the target, and the rules of data transformation for Oracle Utilities Extractors and Schema for Oracle Utilities Extractors and Schema for Oracle Utilities Network Management System.

Terminologies

The following terminologies are used for the data maps included in this 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 (BI data warehouse) into which data is loaded

Table Type

Fact or dimension

SCD Type

The nature of the dimension on how it handles changes made in the source system.

Type 1 - Existing records are updated directly.

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 end date as a high date.

Fact Type

Whether this is a snapshot or accumulation fact table

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

ODI 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 the 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 Network Management System

This section contains data maps for the following Oracle Utilities Extractors and Schema for Oracle Utilities Network Management System data:

•

Dimension Tables

•

Fact Tables

•

User Defined Dimension Tables

Dimension Tables

Account Dimension

The Account dimension extracts account data from the Oracle Utilities Network Management system with all related attributes.

Properties

Property

Value

Target Table

CD_ACCT

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CU_SERVICE_LOCATIONS

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

SRC_ACCT_ID

Account ID

CU_SERVICE_LOCATIONS.SERV_ACCOUNT_NUMBER

ACCT_INFO

Account Information

CU_SERVICE_LOCATIONS.SERV_LOC_ID

UDF1_CD

Revenue Class Code

CU_SERVICE_LOCATIONS.SERV_REVENUE_CLASS

UDF1_DESCR

Revenue Class Description

CU_SERVICE_LOCATIONS.SERV_REVENUE_CLASS

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. The 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 extracts address information from the CU_SERVICE_LOCATIONS table in Oracle Utilities Network Management System.

Properties

Property

Value

Target Table

CD_ADDR

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CU_SERVICE_LOCATIONS

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

SRC_ADDR_ID

Premise ID

CU_SERVICE_LOCATIONS.SERV_LOC_ID

ADDR_INFO

Address

CU_SERVICE_LOCATIONS.SERV_ADDR_1 || ', ' || SERV_CITY|| ', ' || SERV_STATE || ', ' || SERV_POSTCODE_1

ADDR_LINE1

Address Line 1

CU_SERVICE_LOCATIONS.SERV_ADDR_1

ADDR_LINE2

Address Line 2

CU_SERVICE_LOCATIONS.SERV_ADDR_2

ADDR_LINE3

Address Line 3

CU_SERVICE_LOCATIONS.SERV_ADDR_3

ADDR_LINE4

Address Line 4

CU_SERVICE_LOCATIONS.SERV_ADDR_4

CITY

City

CU_SERVICE_LOCATIONS.SERV_CITY

COUNTY

County

POSTAL

Postal Code

CU_SERVICE_LOCATIONS.SERV_POSTCODE_1

STATE_CD

State Code

CU_SERVICE_LOCATIONS.SERV_STATE

STATE_DESCR

State

CU_SERVICE_LOCATIONS.SERV_STATE

COUNTRY_CD

Country Code

COUNTRY_DESCR

Country

GEO_CODE

Geographical Code

CROSS_STREET

Cross Street

SUBURB

Suburb

UDF1_CD

City Code

CU_SERVICE_LOCATIONS.SERV_CITY

UDF1_DESCR

City

CU_SERVICE_LOCATIONS.SERV_CITY

UDF2_CD

County Code

UDF2_DESCR

County

UDF3_CD

Postal Code

CU_SERVICE_LOCATIONS.SERV_POSTCODE_1

UDF3_DESCR

Postal Code

CU_SERVICE_LOCATIONS.SERV_POSTCODE_1

UDF4_CD

State Code

CU_SERVICE_LOCATIONS.SERV_STATE

UDF4_DESCR

State

CU_SERVICE_LOCATIONS.SERV_STATE

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

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

Call Info Dimension

The Call Info dimension extracts the call details (along with the caller information) created in the Oracle Utilities Network Management System Web Call Entry application.

Properties

Property

Value

Target Table

CD_CALL_INFO

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

INCIDENTS

Stage Table

STG_CD_CALL_INFO

ODI Package

B1_PKG_CD_CALL_INFO

ETL View

B1_D_CALL_INFO_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CALL_INFO_KEY

Call Info Dimension Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_CALL_INFO_SEQ.

CALLER_NAME

Caller Name

INCIDENTS.CUSTOMER_NAME

PHONE_NBR

Phone Number

INCIDENTS.CUSTOMER_PHONE

COMPLAINT

Complaint Description

INCIDENTS.COMPLAINT

COMMENTS

Call Comments

INCIDENTS.OP_COMMENT

SRC_INCIDENT_ID

Call Number

INCIDENTS.NUMB

UDF1_CD

User Defined Field 1 Code

INCIDENTS.ACTIVE

UDF1_DESCR

User Defined Field 1 Description

INCIDENTS.ACTIVE

UDF2_CD

User Defined Field 2 Code

INCIDENTS.COMPLETE

UDF2_DESCR

User Defined Field 2 Description

INCIDENTS.COMPLETE

UDF3_CD

User Defined Field 3 Code

INCIDENTS.COMPLAINT

UDF3_DESCR

User Defined Field 3 Description

INCIDENTS.SHORT_DESC

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

INCIDENTS.LAST_UPDATE_TIME

Transformation Logic :

This field is populated with the updated timestamp value.

DELETE_IND

INCIDENTS.JRN_FLAG

Transformation Logic :

This field is populated with "1" for deleted records, and "0" for new/modified records.

JOB_NBR

Job Number

Transformation Logic:

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

City Dimension

The City dimension extracts distinct city information from the CU_SERVICE_LOCATIONS table. The columns that identify the distinct city records are SRC_STATE, SRC_COUNTRY, SRC_CITY, and DATA_SOURCE_IND.

Properties

Property

Value

Target Table

CD_CITY

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CU_SERVICE_LOCATIONS

Stage Table

STG_CD_CITY

ODI Package

B1_PKG_CD_CITY

ETL View

B1_D_CITY_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CITY_KEY

City Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_CITY_SEQ.NEXTVAL.

SRC_STATE

State

CU_SERVICE_LOCATIONS.SERV_STATE

SRC_CITY

City

CU_SERVICE_LOCATIONS.SERV_CITY

SRC_COUNTRY

Country

CU_SERVICE_LOCATIONS.SERV_COUNTRY

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

UPDATE_DTTM

Update Date/Time

CU_SERVICE_LOCATIONS.LAST_UPDATE_TIME

Transformation Logic :

This field is populated with the updated timestamp value.

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DATA_SOURCE_IND

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. The 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.

JOB_NBR

Job Number

Transformation Logic:

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

Control Zone Dimension

The Control Zone dimension extracts the control zone records and their hierarchical relationships from the Network Management System source database tables CONTROL_ZONES and CONTROL_ZONE_STRUCTURES.

Properties

Property

Value

Target Table

CD_CTRL_ZONE

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CONTROL_ZONE_STRUCTURES

CONTROL_ZONES

CONTROL_ZONE_STRUCTURES A

CONTROL_ZONES B

CONTROL_ZONES C

Stage Table

STG_CD_CTRL_ZONE

ODI Package

B1_PKG_CD_CTRL_ZONE

ETL View

B1_D_CTRL_ZONE_VW

Fields (

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CTRL_ZONE_KEY

Control Zone Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_CTRL_ZONE_SEQ.NEXT VAL.

SRC_NCG_ID

CONTROL_ZONES.NCG_ID

CONTROL_ZONE_STRUCTURES.CHILD_NCG_ID

HIERARCHY_TYPE

CTRL_ZONE_NAME

CONTROL_ZONES.NAME

UDF1_CD

User Defined Field 1 Code

CONTROL_ZONES.NCG_ID

CONTROL_ZONE_STRUCTURES.PARENT_NCG_ID

UDF1_DESCR

User Defined Field 1 Description

CONTROL_ZONES.NAME

UDF2_CD

User Defined Field 2 Code

CONTROL_ZONES.NCG_ID

UDF2_DESCR

User Defined Field 2 Description

CONTROL_ZONES.NAME

UDF3_CD

User Defined Field 3 Code

CONTROL_ZONES.NCG_ID

UDF3_DESCR

User Defined Field 3 Description

CONTROL_ZONES.NAME

UDF4_CD

User Defined Field 4 Code

CONTROL_ZONES.NCG_ID

UDF4_DESCR

User Defined Field 4 Description

CONTROL_ZONES.NAME

UDF5_CD

User Defined Field 5 Code

CONTROL_ZONES.NCG_ID

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

EFF_START_DTTM

Effective Start Date/

Time

EFF_END_DTTM

Effective End Date/

Time

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DATA_SOURCE_IND

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. The 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.

JOB_NBR

Job Number

Transformation Logic:

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

Crew Dimension

The Crew dimension extracts crew records from the Network Management System source database tables CREWS and CREW_TYPES.

Properties

Property

Value

Target Table

CD_CREW

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CREWS

Stage Table

STG_CD_CREW

ODI Package

B1_PKG_CD_CREW

ETL View

B1_D_CREW_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CREW_KEY

Crew Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_CREW_SEQ.NEXT VAL.

CREW_CD

Crew Code

CREWS.CREW_KEY

CREW_DESCR

Crew Description

CREW_TYPES.CREW_NAME

SRC_CREW_ID

Crew ID

CREWS.CREW_ID

CREW_TYPE_CD

Crew Type Code

CREWS.CREW_TYPE

CREW_TYPE_DESCR

Crew Type Description

CREW_TYPES.CREW_TYPE

ORG_UNIT1_CD

Organization Unit 1 Code

ORG_UNIT1_DESCR

Organization Unit 1 Description

ORG_UNIT2_CD

Organization Unit 2 Code

ORG_UNIT2_DESCR

Organization Unit 2 Description

ORG_UNIT3_CD

Organization Unit 3 Code

ORG_UNIT3_DESCR

Organization Unit 3 Description

UDF1_CD

User Defined Field 1 Code

CREWS.CREW_TYPE

UDF1_DESCR

User Defined Field 1 Description

CREW_TYPES.CREW_TYPE

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

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

CREWS.DEATH

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DATA_SOURCE_IND

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. The 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.

JOB_NBR

Job Number

Transformation Logic:

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

Device Dimension

The Device dimension extracts device records from the Network Management System source database table NETWORK_COMPONENTS.

Properties

Property

Value

Target Table

CD_DEVICE

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

NETWORK_COMPONENTS

Stage Table

STG_CD_DEVICE

ODI Package

B1_PKG_CD_DEVICE

ETL View

B1_D_DEVICE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

DEVICE_KEY

Device Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_DEVICE_SEQ.NEXT VAL.

SRC_DEVICE_CLS

Device Class

NETWORK_COMPONENTS.H_CLS

SRC_DEVICE_IDX

Device Index

NETWORK_COMPONENTS.H_IDX

DEVICE_NAME

Device Name

NETWORK_COMPONENTS.NAME

DEVICE_TYPE_CD

Device Type Code

CLASSES.C_TYPE

Transformation Logic:

If null, this field is populated with "-".

DEVICE_TYPE_DESCR

Device Type Description

CLASSES.C_TYPE

Transformation Logic:

If null, this field is populated with "-".

DEVICE_CLASS_CD

Device Class Code

CLASSES.C_NAME

Transformation Logic:

If null, this field is populated with "-".

DEVICE_CLASS_DESCR

Device Class Description

CLASSES.C_DESC

Transformation Logic:

If null, this field is populated with "-".

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

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

NETWORK_COMPONENTS.DEATH

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

JOB_NBR

Job Number

Transformation Logic:

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

Duration Deviation Dimension

The Duration Deviation dimension extracts the estimated versus actual duration bucket information.

Properties

Property

Value

Target Table

CD_DURATION_DEVIATION

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

B1_RANGE_LOOKUP

Stage Table

STG_CD_DURATION_DEVIATION

ODI Package

B1_PKG_CD_DURATION_DEVIATION

ETL View

B1_D_DURATION_DEVIATION_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

DURATION_DEVIATION_KEY

Estimated VS Actual Duration Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_DURATION_DEVIATION_SEQ.NEXT_VAL.

DURATION_DEVIATION_DESCR

Estimated VS Actual Duration Description

B1_RANGE_LOOKUP.DESCR

RANGE_START

Start Range

B1_RANGE_LOOKUP.START_RANGE

RANGE_END

End Range

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

UPDATE_DTTM

Update 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. The 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.

JOB_NBR

Job Number

Transformation Logic:

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

Estimated Restoration Duration Dimension

The Estimated Restoration Duration dimension extracts the estimated restoration duration age bucket information.

Properties

Property

Value

Target Table

CD_EST_RST_DUR

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

B1_RANGE_LOOKUP

Stage Table

STG_CD_EST_RST_DUR

ODI Package

B1_PKG_CD_EST_RST_DUR

ETL View

B1_D_EST_RST_DUR_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

EST_RST_DUR_KEY

Estimated Restoration Duration Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_EST_RST_DUR_SEQ.NEXT_VAL.

EST_RST_DUR_DESCR

Estimated Restoration Duration Description

B1_RANGE_LOOKUP.DESCR

RANGE_START

Start Range

B1_RANGE_LOOKUP.START_RANGE

RANGE_END

End Range

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

UPDATE_DTTM

Update 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. The 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.

JOB_NBR

Job Number

Transformation Logic:

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

Event Dimension

The Event dimension extracts event information from the Network Management System applications using the source database table JOBS.

Properties

Property

Value

Target Table

CD_EVENT

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

JOBS

Stage Table

STG_CD_EVENT

ODI Package

B1_PKG_CD_EVENT

ETL View

B1_D_EVENT_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

EVENT_KEY

Event Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_EVENT_SEQ.NEXT_VAL.

SRC_NBR

Number (Natural Key)

JOBS.NUMB

EVENT_NBR

Event Number

JOBS.EVENT_IDX

DELETE_IND

Delete Indicator

EXCLUDE_REASON

Exclude Reason

PICKLIST_INFO_UPD_TR.DTR_TEXT

OPERATOR_COMMENT

Operator Comment

JOBS.OPERATOR_COMMENT

EVENT_STATE_CD

Event Status Code

JOBS.VALID_STATE_KEY

EVENT_STATE_DESCR

Event Status Description

If TE_VALID_STATES.DESCRIPTION is null, then JOBS.ALARM_STATE

X_COORDINATE

X Coordinate

JOBS.X_COORD

Y_COORDINATE

Y Coordinate

JOBS.Y_COORD

FIRST_CALL_ADDR

First Call Address

JOBS.ADDR_STREET

JOBS.ADDR_CITY

REMEDY_CD

Remedy

PICKLIST_INFO_UPD_TR.REMEDY_OM

TROUBLE_CD_LIST

Trouble Code

JOBS.TROUBLE_CODE

UDF1_CD

User Defined Field 1 Code

PICKLIST_INFO_UPD_TR.SCHEDULED_DEV_OM

UDF1_DESCR

Scheduled

PICKLIST_INFO_UPD_TR.SCHEDULED_DEV_OM

UDF2_CD

User Defined Field 2 Code

PICKLIST_INFO_UPD_TR.HUMAN_ELEM_OM

UDF2_DESCR

Utility Error

PICKLIST_INFO_UPD_TR.HUMAN_ELEM_OM

UDF3_CD

User Defined Field 3 Code

PICKLIST_INFO_UPD_TR.OTHER_CAUSE_OM

UDF3_DESCR

Other

PICKLIST_INFO_UPD_TR.OTHER_CAUSE_OM

UDF4_CD

User Defined Field 4 Code

PICKLIST_INFO_UPD_TR.FOREIGN_INTERF_OM

UDF4_DESCR

Foreign Interference

PICKLIST_INFO_UPD_TR.FOREIGN_INTERF_OM

UDF5_CD

User Defined Field 5 Code

PICKLIST_INFO_UPD_TR.VEGETATION_OM

UDF5_DESCR

Vegetation

PICKLIST_INFO_UPD_TR.VEGETATION_OM

UDF6_CD

User Defined Field 6 Code

PICKLIST_INFO_UPD_TR.DEF_EQUIP_OM

UDF6_DESCR

Defective Equipment

PICKLIST_INFO_UPD_TR.DEF_EQUIP_OM

UDF7_CD

User Defined Field 7 Code

PICKLIST_INFO_UPD_TR.ADV_ENVIRON_OM

UDF7_DESCR

Environment

PICKLIST_INFO_UPD_TR.ADV_ENVIRON_OM

UDF8_CD

User Defined Field 8 Code

PICKLIST_INFO_UPD_TR.ADV_WEATHER_OM

UDF8_DESCR

Weather

PICKLIST_INFO_UPD_TR.ADV_WEATHER_OM

UDF9_CD

User Defined Field 9 Code

PICKLIST_INFO_UPD_TR.SCHEDULED_DEV_OM

PICKLIST_INFO_UPD_TR.HUMAN_ELEM_OM

PICKLIST_INFO_UPD_TR.OTHER_CAUSE_OM

PICKLIST_INFO_UPD_TR.FOREIGN_INTERF_OM

PICKLIST_INFO_UPD_TR.VEGETATION_OM

PICKLIST_INFO_UPD_TR.DEF_EQUIP_OM

PICKLIST_INFO_UPD_TR.ADV_ENVIRON_OM

PICKLIST_INFO_UPD_TR.ADV_WEATHER_OM

UDF9_DESCR

Outage Cause

PICKLIST_INFO_UPD_TR.SCHEDULED_DEV_OM

PICKLIST_INFO_UPD_TR.HUMAN_ELEM_OM

PICKLIST_INFO_UPD_TR.OTHER_CAUSE_OM

PICKLIST_INFO_UPD_TR.FOREIGN_INTERF_OM

PICKLIST_INFO_UPD_TR.VEGETATION_OM

PICKLIST_INFO_UPD_TR.DEF_EQUIP_OM

PICKLIST_INFO_UPD_TR.ADV_ENVIRON_OM

PICKLIST_INFO_UPD_TR.ADV_WEATHER_OM

UDF10_CD

User Defined Field 10 Code

PICKLIST_INFO_UPD_TR.SCHEDULED_DEV_OM

PICKLIST_INFO_UPD_TR.HUMAN_ELEM_OM

PICKLIST_INFO_UPD_TR.OTHER_CAUSE_OM

PICKLIST_INFO_UPD_TR.FOREIGN_INTERF_OM

PICKLIST_INFO_UPD_TR.VEGETATION_OM

PICKLIST_INFO_UPD_TR.DEF_EQUIP_OM

PICKLIST_INFO_UPD_TR.ADV_ENVIRON_OM

PICKLIST_INFO_UPD_TR.ADV_WEATHER_OM

UDF10_DESCR

Detailed Cause

PICKLIST_INFO_UPD_TR.SCHEDULED_DEV_OM

PICKLIST_INFO_UPD_TR.HUMAN_ELEM_OM

PICKLIST_INFO_UPD_TR.OTHER_CAUSE_OM

PICKLIST_INFO_UPD_TR.FOREIGN_INTERF_OM

PICKLIST_INFO_UPD_TR.VEGETATION_OM

PICKLIST_INFO_UPD_TR.DEF_EQUIP_OM

PICKLIST_INFO_UPD_TR.ADV_ENVIRON_OM

PICKLIST_INFO_UPD_TR.ADV_WEATHER_OM

UDF11_CD

User Defined Field 11 Code

PICKLIST_INFO_UPD_TR.REMEDY_OM

UDF11_DESCR

Priority

PICKLIST_INFO_UPD_TR.REMEDY_OM

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

Standard Remarks

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

RELATED_OUTAGES.KEY_EVENT_IDX

UDF15_DESCR

User Defined Field 15 Description

RELATED_OUTAGES.KEY_EVENT_IDX

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

UDF20_CD

User Defined Field 20 Code

UDF20_DESCR

User Defined Field 20 Description

UDF21_CD

User Defined Field 21 Code

UDF21_DESCR

User Defined Field 21 Description

UDF22_CD

User Defined Field 22 Code

UDF22_DESCR

User Defined Field 22 Description

UDF23_CD

User Defined Field 23 Code

UDF23_DESCR

User Defined Field 23 Description

UDF24_CD

User Defined Field 24 Code

UDF24_DESCR

User Defined Field 24 Description

UDF25_CD

User Defined Field 25 Code

UDF25_DESCR

User Defined Field 25 Description

UDF26_CD

User Defined Field 26 Code

UDF26_DESCR

User Defined Field 26 Description

UDF27_CD

User Defined Field 27 Code

UDF27_DESCR

User Defined Field 27 Description

UDF28_CD

User Defined Field 28 Code

UDF28_DESCR

User Defined Field 28 Description

UDF29_CD

User Defined Field 29 Code

UDF29_DESCR

User Defined Field 29 Description

UDF30_CD

User Defined Field 30 Code

UDF30_DESCR

User Defined Field 30 Description

UDF31_CD

User Defined Field 31 Code

UDF31_DESCR

User Defined Field 31 Description

UDF32_CD

User Defined Field 32 Code

UDF32_DESCR

User Defined Field 32 Description

UDF33_CD

User Defined Field 33 Code

UDF33_DESCR

User Defined Field 33 Description

UDF34_CD

User Defined Field 34 Code

UDF34_DESCR

User Defined Field 34 Description

UDF35_CD

User Defined Field 35 Code

UDF35_DESCR

User Defined Field 35 Description

UDF36_CD

User Defined Field 36 Code

UDF36_DESCR

User Defined Field 36 Description

UDF37_CD

User Defined Field 37 Code

UDF37_DESCR

User Defined Field 37 Description

UDF38_CD

User Defined Field 38 Code

JOBS.H_CLS

UDF38_DESCR

User Defined Field 38 Description

JOBS.H_CLS

UDF39_CD

User Defined Field 39 Code

JOBS.H_IDX

UDF39_DESCR

User Defined Field 39 Description

JOBS.H_IDX

UDF40_CD

User Defined Field 40 Code

UDF40_DESCR

User Defined Field 40 Description

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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

JOB_NBR

Job Number

Transformation Logic:

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

Event Status Dimension

The Event Status dimension extracts various statuses of events in the TE_VALID_STATES table from the Network Management System database.

Properties

Property

Value

Target Table

CD_EVENT_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

TE_STATUSES A

Stage Table

STG_CD_EVENT_STATUS

ODI Package

B1_PKG_CD_EVENT_STATUS

ETL View

B1_D_EVENT_STATUS_VW

Fields (

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

EVENT_STATUS_KEY

Event Status Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_EVENT_STATUS_SEQ.NEXT_VAL.

SRC_STATUS

Status (Natural Key)

TE_STATUSES.TRANS_STATUS

EVENT_STATUS_CD

Event Status Code

TE_STATUSES.DESCRIPTION

Transformation Logic :

If null, this field is populated with "-".

EVENT_STATUS_DESCR

Event Status Description

TE_STATUSES.DESCRIPTION

Transformation Logic :

If null, this field is populated with "-".

UDF1_CD

User Defined Field 1 Code

TE_STATUS_GROUPS.DESCRIPTION

UDF1_DESCR

User Defined Field 1 Description

TE_STATUS_GROUPS.DESCRIPTION

UDF2_CD

User Defined Field 2 Code

TE_STATUS_GROUPS.GROUP_NAME

UDF2_DESCR

User Defined Field 2 Description

TE_STATUS_GROUPS.GROUP_NAME

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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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

TE_STATUSES.JRN_UPDATE_DTTM

TE_STATUS_GROUPS.JRN_UPDATE_DTTM

Transformation Logic:

This field is populated with the updated timestamp value.

JOB_NBR

Job Number

Transformation Logic:

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

Feeder Dimension

The Feeder dimension extracts the feeder and corresponding substation information from the FEEDERS table.

Properties

Property

Value

Target Table

CD_FEEDER

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

FEEDERS

Stage Table

STG_CD_FEEDER

ODI Package

B1_PKG_CD_FEEDER

ETL View

B1_D_FEEDER_VW

Fields (

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

FEEDER_KEY

Feeder Duration Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_FEEDER_SEQ.NEXT_VAL.

SRC_FEEDER_CLS

Feeder Class

FEEDERS.H_CLS

SRC_FEEDER_IDX

Feeder Index

FEEDERS.H_IDX

FEEDER_NAME

Feeder Name

FEEDERS.FEEDER_NAME

FEEDERS.H_CLS

FEEDERS.H_IDX

Transformation Logic :

Use the feeder name if available. Else, use the combination of class and index.

SUBSTN_CD

Substation Code

FEEDERS.SUBSTATION_NAME

SUBSTN_DESCR

Substation Name

FEEDERS.SUBSTATION_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 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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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

FEEDERS.DEATH

JOB_NBR

Job Number

Transformation Logic:

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

Meter Dimension

The Meter dimension extracts meter ID and meter type details from the CU_METERS table in the Network Management System source database.

Properties

Property

Value

Target Table

CD_METER

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CU_METERS

Stage Table

STG_CD_METER

ODI Package

B1_PKG_CD_METER

ETL View

B1_D_METER_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

METER_KEY

Meter Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_METER_SEQ.NEXT_VAL.

SRC_METER_ID

Meter ID

CU_METERS.METER_ID

METER_INFO

Meter Number

CU_METERS.METER_NO

UDF1_CD

User Defined Field 1 Code

CU_METERS.METER_TYPE

UDF1_DESCR

User Defined Field 1 Description

CU_METERS.METER_TYPE

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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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

CU_METERS.LAST_UPDATE_TIME

EFF_END_DTTM

Effective End Date/Time

CU_METERS.LAST_UPDATE_TIME

JOB_NBR

Job Number

Transformation Logic:

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

Outage Duration Dimension

The Outage Duration dimension extracts the outage duration age bucket information.

Properties

Property

Value

Target Table

CD_OUTG_DUR

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

B1_RANGE_LOOKUP

Stage Table

STG_CD_OUTG_DUR

ODI Package

B1_PKG_CD_OUTG_DUR

ETL View

B1_D_OUTG_DUR_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

OUTG_DUR_KEY

Outage Duration Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from B1_OUTG_DUR_SEQ.NEXT_VAL.

OUTG_DUR_DESCR

Outage Duration Description

B1_RANGE_LOOKUP.DESCR

RANGE_START

Start Range

B1_RANGE_LOOKUP.START_RANGE

RANGE_END

End Range

B1_RANGE_LOOKUP.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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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. The 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 timestamp value.

JOB_NBR

Job Number

Transformation Logic:

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

Person Dimension

The Person dimension extracts person data from the Oracle Utilities Network Management System with all related attributes.

Properties

Property

Value

Target Table

CD_PER

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CU_CUSTOMERS

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

BUSINESS_IND

Business Indicator Flag

PER_INFO

Person Info String

CU_CUSTOMERS.CUST_NAME

PER_KEY

Person Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_PER_SEQ.NEXT_VAL.

PER_NAME

Person Name

CU_CUSTOMERS.CUST_NAME

PER_PHONE_NBR

Person Phone Number

CU_CUSTOMERS.CUST_HOME_PHONE

CU_CUSTOMERS.CUST_HOME_AC

Transformation Logic:

This field is populated with the phone number in the (001) 775-0125 format.

SRC_PER_ID

Person ID (Natural Key)

CU_CUSTOMERS.CUST_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

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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. The 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_END_DTTM

Effective End Date/Time

CU_CUSTOMERS.LAST_UPDATE_TIME

EFF_START_DTTM

Effective Start Date/Time

CU_CUSTOMERS.LAST_UPDATE_TIME

JOB_NBR

Job Number

Transformation Logic:

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

Phase Dimension

The Phase dimension extracts phase data from the Network Management System with all related attributes.

Properties

Property

Value

Target Table

CD_PHASE

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

PHASE_BITMAP_LOOKUP

Stage Table

STG_CD_PHASE

ODI Package

B1_PKG_CD_PHASE

ETL View

B1_D_PHASE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

PHASE_KEY

Phase Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_PHASE_SEQ.NEXT_VAL.

SRC_PHASE_ID

Phase ID

PHASE_BITMAP_LOOKUP.PHASE_BITS

PHASE_CD

PHASE_BITMAP_LOOKUP.PHASE_STRING

PHASE_DESCR

Phase

PHASE_BITMAP_LOOKUP.PHASE_STRING

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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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. The 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_END_DTTM

Effective End Date/Time

CU_CUSTOMERS.LAST_UPDATE_TIME

EFF_START_DTTM

Effective Start Date/Time

CU_CUSTOMERS.LAST_UPDATE_TIME

JOB_NBR

Job Number

Transformation Logic:

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

Premise Dimension

The Premise dimension extracts premise information from the CU_SERVICE_LOCATIONS table in the Network Management System source database.

Properties

Property

Value

Target Table

CD_PREM

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CU_SERVICE_LOCATIONS

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

SRC_PREM_ID

Premise ID (Natural Key)

CU_SERVICE_LOCATIONS.SERV_LOC_ID

PREM_INFO

Premise Info String

CU_SERVICE_LOCATIONS.SERV_LOC_ID

UDF1_CD

Jurisdiction Code

UDF1_DESCR

Jurisdiction

UDF2_CD

Premise Type Code

CU_SERVICE_LOCATIONS.SERV_TYPE

UDF2_DESCR

Premise Type

CU_SERVICE_LOCATIONS.SERV_TYPE

UDF3_CD

Life Support Flag Code

CU_SERVICE_LOCATIONS.SERV_LIFE_SUPPORT

UDF3_DESCR

Life Support Flag

CU_SERVICE_LOCATIONS.SERV_LIFE_SUPPORT

UDF4_CD

Trend Area Code

UDF4_DESCR

Trend Area

UDF5_CD

In City Limit Code

UDF5_DESCR

In City Limit

UDF6_CD

User Defined Field 6 Code

CU_SERVICE_LOCATIONS.SERV_C_PRIORITY

UDF6_DESCR

User Defined Field 6 Description

CU_SERVICE_LOCATIONS.SERV_C_PRIORITY

UDF7_CD

User Defined Field 7 Code

CU_SERVICE_LOCATIONS.SERV_D_PRIORITY

UDF7_DESCR

User Defined Field 8 Description

CU_SERVICE_LOCATIONS.SERV_D_PRIORITY

UDF8_CD

User Defined Field 8 Code

CU_SERVICE_LOCATIONS.SERV_K_PRIORITY

UDF8_DESCR

User Defined Field 8 Description

CU_SERVICE_LOCATIONS.SERV_K_PRIORITY

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 13Code

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 15Code

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 17Code

UDF17_DESCR

User Defined Field 17 Description

UDF18_CD

User Defined Field 18 Code

UDF18_DESCR

User Defined Field 18 Description

DATA_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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. The 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_END_DTTM

Effective End Date/Time

CU_CUSTOMERS.LAST_UPDATE_TIME

EFF_START_DTTM

Effective Start Date/Time

CU_CUSTOMERS.LAST_UPDATE_TIME

JOB_NBR

Job Number

Transformation Logic:

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

Storm Dimension

The Storm dimension extracts storm specific data from the Network Management System with all related attributes.

Properties

Property

Value

Target Table

CD_STORM

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

STORMMAN_STORMS SS

Stage Table

STG_CD_STORM

ODI Package

B1_PKG_CD_STORM

ETL View

B1_D_STORM_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

STORM_KEY

Storm Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_STORM_SEQ.NEXT_VAL.

SRC_STORM_NAME

Storm Name

SS.STORM_NAME

SRC_STORM_NAME_SFX

Storm Name Suffix

SS.STORM_DATE

STORM_TYPE_CD

Storm Type Code

SS.STORM_TYPE

STORM_TYPE_DESCR

Storm Type

SS.STORM_TYPE_NAME

STORM_LEVEL_CD

SS.STORM_LEVEL

STORM_LEVEL_DESCR

Storm Level

SS.STORM_LEVEL

STORM_START_DTTM

Date/Time the Storm Started

SS.STORM_START

STORM_END_DTTM

Date/Time the Storm Ended

SS.STORM_END

UDF1_CD

User Defined Field 1 Code

SS.STORM_START

UDF1_DESCR

User Defined Field 1 Description

SS.STORM_START

UDF2_CD

User Defined Field 2 Code

SS.STORM_END

UDF2_DESCR

User Defined Field 2 Description

SS.STORM_END

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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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. The 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 timestamp value.

JOB_NBR

Job Number

Transformation Logic:

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

Storm Outage Type Dimension

The Storm Outage Type dimension extracts the storm outage type data from Oracle Utilities Network Management System with all related attributes.

Properties

Property

Value

Target Table

CD_STORM_OUTAGE_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

STORMMAN_OUTAGE_TYPES

Stage Table

STG_CD_STORM_OUTAGE_TYPE

ODI Package

B1_PKG_CD_STORM_OUTAGE_TYPE

ETL View

B1_D_STORM_OUTAGE_TYPE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_STORM_OUTAGE_TYPE_SEQ.NEXT_VAL.

SRC_OUTAGE_TYPE

Outage Type Code

STORMMAN_OUTAGE_TYPES.OUTAGE_TYPE

OUTAGE_TYPE_CD

STORMMAN_OUTAGE_TYPES.OUTAGE_TYPE

OUTAGE_TYPE_DESCR

Outage Type Description

STORMMAN_OUTAGE_TYPES.OUTAGE_TYPE_NAME

Transformation Logic:

If null, this populated with "-".

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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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. The 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 timestamp value.

JOB_NBR

Job Number

Transformation Logic:

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

Supply Node Lookup Dimension

The Supply Node Lookup dimension extracts the distinct service point (premise, person, and meter) information along with the device they are attached to.

Note : This dimension is not joined to any fact, but is used to lookup data before the fact load.

Properties

Property

Value

Target Table

CD_SNL

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

SUPPLY_NODES

CU_SERVICE_POINTS

CU_SERVICE_LOCATIONS

Stage Table

STG_CD_SNL

ODI Package

B1_PKG_CD_SNL

ETL View

B1_D_SNL_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

SNL_KEY

Supply Node Lookup Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_SNL_SEQ.NEXT_VAL.

SRC_METER_ID

Meter ID

CU_SERVICE_POINTS.METER_ID

SRC_PERSON_ID

Person ID

CU_SERVICE_POINTS.CUST_ID

SRC_PREMISE_ID

Premise ID

CU_SERVICE_LOCATIONS.SERV_LOC_ID

SRC_ACCOUNT_ID

Account ID

CU_SERVICE_LOCATIONS.SERV_ACCOUNT_NUMBER

SRC_ADDR_ID

Address

CU_SERVICE_POINTS.SERV_LOC_ID

DEVICE_ID

Device ID

CUSTOMER_EDIT_VIEW.DEVICE_ID

SUPPLY_NODES.DEVICE_ID

FEEDER_ID

Feeder ID

CUSTOMER_EDIT_VIEW.FEEDER

SUPPLY_NODES.FEEDER

NCG_ID

NCG ID

SUPPLY_NODES.NCG

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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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

SUPPLY_NODES.LEAST(SUPPLY_NODES.DEATH,CU_SERVICE_LOCATIONS.JRN_EFF_END_DTTM,CU_SERVICE_POINTS.JRN_EFF_END_DTTM)

JOB_NBR

Job Number

Transformation Logic:

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

Switch Plan Dimension

The Switch Plan dimension extracts switch plan data from the Oracle Utilities Network Management System with all related attributes.

Properties

Property

Value

Target Table

CD_SW_PLAN

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

SWMAN_SHEET

Stage Table

STG_CD_SW_PLAN

ODI Package

B1_PKG_CD_SW_PLAN

ETL View

B1_D_SW_PLAN_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

SW_PLAN_KEY

Switch Plan Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_SW_PLAN_SEQ.NEXT_VAL.

SRC_SW_PLAN_CLS

Switch Plan Class

SWMAN_SHEET.SWITCH_SHEET_CLS

SRC_SW_PLAN_IDX

Switch Plan Index

SWMAN_SHEET.SWITCH_SHEET_IDX

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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

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

SWMAN_SHEET.UPDATE_TIME

Transformation Logic:

This field is populated with the updated timestamp value.

JOB_NBR

Job Number

Transformation Logic:

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

Switch Plan State Dimension

The Switch Plan State dimension extracts switch plan data from the Oracle Utilities Network Management System with all related attributes.

Properties

Property

Value

Target Table

CD_SW_PLAN_STATE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

TE_VALID_STATES

Stage Table

STG_CD_SW_PLAN_STATE

ODI Package

B1_PKG_CD_SW_PLAN_STATE

ETL View

B1_D_SW_PLAN_STATE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

SW_PLAN_STATE_KEY

Switch Plan State Dimension Surrogate Key

Transformation Logic :

This field is populated with the sequence from SPL_SW_PLAN_STATE_SEQ.NEXT_VAL.

SRC_STATE_KEY

TE_VALID_STATES.STATE_KEY

STATE_CD

Switch Plan State Code

TE_VALID_STATES.STATE_NAME

STATE_DESCR

Switch Plan State

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

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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

B1_JOB_CONFIG.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. The 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

TE_VALID_STATES.UPDATE_DTTM

Transformation Logic:

This field is populated with the updated timestamp value.

JOB_NBR

Job Number

Transformation Logic:

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

User Dimension

The User dimension extracts the details of all users from the Oracle Utilities Network Management System source database.

Properties

Property

Value

Target Table

CD_USER

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CES_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.NEXT_VAL.

USER_CD

User Code (Natural Key)

CES_USER.USER_NAME

USER_DESCR

User Description

CES_USER.FULL_NAME

Transformation Logic :

If null, this field is populated with "-".

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_LOAD_DTTM

Data Load Date/Time

Transformation Logic:

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

B1_JOB_CONFIG.DSI

Transformation Logic:

This field is populated with the DSI value on the source product instance configuration. The 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

CES_USER.LAST_UPDATE_TIME

EFF_END_DTTM

Effective End Date/Time

CES_USER.LAST_UPDATE_TIME

JOB_NBR

Job Number

Transformation Logic:

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

Fact Tables

City Outage Fact

The City Outage fact stores KPIs such as CMI, SAIDI, SAIFI, number of customers, number of interruptions, etc.

This fact is unusual in the sense that its data is not populated directly from the source tables in the replication schema. It is a calculated fact whose data is derived from the data in the Restored Customer Outage Fact and its related dimensions.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CITY_OUTG

Table Type

Fact

Fact Type

Snapshot

Driver Table

CD_CITY

Stage Table Name

STG_CF_CITY_OUTG

ODI Package Name

B1_PKG_CF_CITY_OUTG

ETL View Name

B1_F_CITY_OUTG_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CITY_OUTG_KEY

City Outage Snapshot Fact Key

Transformation Logic:

This field is populated with the sequence from SPL_CITY_OUTG_SEQ.

TMED_IND

Timed Indicator

Transformation Logic:

This field is populated with ‘0’.

SNAP_TYPE_CD

Snapshot Type Code

B1_TARGET_ENTITY.SCHED_TYPE_FLG

Transformation Logic:

This field is populated the snapshot type.

SNAPSHOT_DATE_KEY

Snapshot Date

B1_JOB_CONFIG.SLICE_END_DTTM

Transformation Logic:

Based on the snapshot date, this field is populated with surrogate key from CD_DATE.

BEGIN_DATE_KEY

Begin Date

B1_JOB_CONFIG.SLICE_START_DTTM

Transformation Logic:

Based on the begin date, this field is populated with surrogate key from CD_DATE.

END_DATE_KEY

End Date

B1_JOB_CONFIG.SLICE_END_DTTM

Transformation Logic:

Based on the end date, this field is populated with surrogate key from CD_DATE.

NUM_CUST_SERVED

Number of Customers Served

CD_SNL.EFF_START_DTTM

CD_SNL.EFF_END_DTTM

Transformation Logic:

This field is populated with the average number of customers in the city during the period.

NUM_SUST_INTRPT

Number Sustained Interruptions

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Transformation Logic:

This field is populated with the count of outages by city where outage duration is greater than P_MOM_DURATION_MINS.

NUM_MOM_INTRPT

Number of Momentary Interruptions

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Transformation Logic:

This field is populated with the count of outages by city where outage duration is less than P_MOM_DURATION_MINS.

CMI

Total Customer Minutes

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Transformation Logic:

This field is populated with the customer minutes interrupted.

NUM_MULT_SUST_INTRPT

Numb. Mult. Sust. Interruption

CF_CUST_RST_OUTG.PER.SRC_PER_ID

CF_CUST_RST_OUTG.PREM.SRC_PREM_ID

CF_CUST_RST_OUTG.METER.SRC_METER_ID

Transformation Logic:

This field is populated with the customer count who experienced more than P_CEMI_COUNT sustained interruptions during the period.

NUM_MULT_CUST_INTRPT

Numb. Mult. Cust. Interruption

CF_CUST_RST_OUTG.PER.SRC_PER_ID

CF_CUST_RST_OUTG.PREM.SRC_PREM_ID

CF_CUST_RST_OUTG.METER.SRC_METER_ID

Transformation Logic:

This field is populated with the customer count who experienced more than P_CEMI_COUNT interruptions (both sustained and momentary) during the period.

SAIDI

SAIDI

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Transformation Logic:

This field is populated with customer minutes interrupted by number of customers served.

CAIDI

CAIDI

Transformation Logic:

This field is populated with customer minutes interrupted by number of outages.

SAIFI

SAIFI

Transformation Logic:

This field is populated with number of outages by number of customers served.

CEMI

CEMI

Transformation Logic:

This field is populated with the ratio of individual customers experiencing more than P_CEMI_COUNT sustained interruptions to the total number of customers served.

CEMSMI

CEMSMI

Transformation Logic:

This field is populated with the ratio of individual customers experiencing more than P-CEMI_COUNT of both sustained and momentary interruption events to the total customers served.

CAIFI

CAIFI

Transformation Logic:

This field is populated with the average frequency of sustained interruptions for customers experiencing sustained interruptions during the specified period for the specified area.

MAIFI

MAIFI

Transformation Logic:

This field is populated with the number of momentary interruptions by number of customers served.

MAIFIE

MAIFIE

Transformation Logic:

This field indicates how often an average customer experienced a momentary interruption event during the specified period.

Note : It does not include momentary interruptions immediately preceding a lockout.

ASAI

ASAI

Transformation Logic:

This field is populated with the average service availability index value.

ACI

ACI

MSAIFI

MSAIFI

Transformation Logic:

This field indicates how often an average customer experienced a momentary and sustained interruption during the specified period.

CITY_KEY

City Dimension Surrogate Key

CD_CITY.CITY_KEY

NUM_CUST_INTRPT

Number Customers Interrupted

Transformation Logic:

This field is populated with the number of customers who experienced an interruption during the period.

NUM_MOM_E_INTRPT

Number of Mom. E. Intrpt

Transformation Logic:

This field is populated with the number of momentary events during the period, excluding the events that immediately precede a sustained interruption.

NUM_EVENT

Number of Events

Transformation Logic:

This field is populated with the number of events during the period.

CITY_OUTG_UDD1_KEY

City Outage User Defined Dimension 1

CITY_OUTG_UDD2_KEY

City Outage User Defined Dimension 2

FACT_CNT

Number of Calls

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

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

NUM_CUST_SUST_INTRPT

Number of Customers with Sustained Interruptions

Transformation Logic:

This field is populated with the number of customers who experienced a sustained interruption during the period.

SNAPSHOT_DT

Snapshot Date

B1_JOB_CONFIG.SLICE_END_DTTM

SRC_CITY

City

CD_CITY.SRC_CITY

SRC_STATE

State

CD_CITY.SRC_STATE

SRC_COUNTRY

Country

CD_CITY.SRC_COUNTRY

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.

Control Zone Outage Fact

The Control Zone Outage fact stores the KPI related information (such as CMI, SAIDI, SAIFI, number of customers, number of interruptions, etc) in the source system.

This fact is unusual in the sense that its data is not populated directly from the source tables in the replication schema. It is a calculated fact whose data is derived from the data in the Restored Customer Outage Fact and its related dimensions.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CTRL_ZONE_OUTG

Table Type

Fact

Fact Type

Snapshot

Driver Table

CD_CTRL_ZONE

Stage Table Name

STG_CF_CTRL_ZONE_OUTG

ODI Package Name

B1_PKG_CF_CTRL_ZONE_OUTG

ETL View Name

B1_F_CTRL_ZONE_OUTG_VW

Materialized View Name

B1_CTRL_ZONE_OUTG_MON_MV1

B1_CTRL_ZONE_OUTG_MON_MV2

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CTRL_ZONE_OUTG_KEY

Control Zone Outage Snapshot Fact Key

Transformation Logic:

This field is populated with the sequence from SPL_CTRL_ZONE_OUTG_SEQ.

TMED_IND

Timed Indicator

SNAP_TYPE_CD

Snapshot Type Code

B1_TARGET_ENTITY.SCHED_TYPE_FLG

SNAPSHOT_DATE_KEY

Snapshot Date

B1_JOB_CONFIG.SLICE_END_DTTM

Transformation Logic:

Based on the snapshot date, this field is populated with surrogate key from CD_DATE.

BEGIN_DATE_KEY

Begin Date

B1_JOB_CONFIG.SLICE_START_DTTM

Transformation Logic:

Based on the begin date, this field is populated with surrogate key from CD_DATE.

END_DATE_KEY

End Date

B1_JOB_CONFIG.SLICE_END_DTTM

Transformation Logic:

Based on the end date, this field is populated with surrogate key from CD_DATE.

NUM_CUST_SERVED

Number of Customers Served

CD_SNL.EFF_START_DTTM

CD_SNL.EFF_END_DTTM

Transformation Logic:

This field is populated with the average number of customers in the city during the period.

NUM_SUST_INTRPT

Number Sustained Interruptions

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Transformation Logic:

This field is populated with the count of outages by city where outage duration is greater than p_mom_duration mins.

NUM_MOM_INTRPT

Number of Momentary Interruptions

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Transformation Logic:

This field is populated with the count of outages by city where outage duration is less than p_mom_duration mins.

CMI

Total Customer Minutes

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Transformation Logic:

This field is populated with the customer minutes interrupted.

NUM_MULT_SUST_INTRPT

Numb. Mult. Sust. Interruption

CF_CUST_RST_OUTG.PER.SRC_PER_ID

CF_CUST_RST_OUTG.PREM.SRC_PREM_ID

CF_CUST_RST_OUTG.METER.SRC_METER_ID

Transformation Logic:

This field is populated with the customer count who experienced more than P_CEMI_COUNT sustained interruptions during the period.

NUM_MULT_CUST_INTRPT

Numb. Mult. Cust. Interruption

CF_CUST_RST_OUTG.PER.SRC_PER_ID

CF_CUST_RST_OUTG.PREM.SRC_PREM_ID

CF_CUST_RST_OUTG.METER.SRC_METER_ID

Transformation Logic:

This field is populated with the customer count who experienced more than P_CEMI_COUNT interruptions (both sustained and momentary) during the period.

SAIDI

SAIDI

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Transformation Logic:

This field is populated with customer minutes interrupted by number of customers served.

CAIDI

CAIDI

Transformation Logic:

This field is populated with customer minutes interrupted by number of outages.

SAIFI

SAIFI

Transformation Logic:

This field is populated with number of outages by number of customers served.

CEMI

CEMI

Transformation Logic:

This field is populated with the ratio of individual customers experiencing more than P_CEMI_COUNT sustained interruptions to the total number of customers served.

CEMSMI

CEMSMI

Transformation Logic:

This field is populated with the ratio of individual customers experiencing more than P-CEMI_COUNT of both sustained and momentary interruption events to the total customers served.

CAIFI

CAIFI

Transformation Logic:

This field is populated with the average frequency of sustained interruptions for customers experiencing sustained interruptions during the specified period for the specified area.

MAIFI

MAIFI

Transformation Logic:

This field is populated with the number of momentary interruptions by number of customers served.

MAIFIE

MAIFIE

Transformation Logic:

This field indicates how often an average customer experienced a momentary interruption event during the specified period.

Note : It does not include momentary interruptions immediately preceding a lockout.

ASAI

ASAI

Transformation Logic:

This field is populated with the average service availability index value.

ACI

ACI

MSAIFI

MSAIFI

Transformation Logic:

This field indicates how often an average customer experienced a momentary and sustained interruption during the specified period.

CTRL_ZONE_KEY

Control Zone Dimension Surrogate Key

CD_CTRL_ZONE.CTRL_ZONE_KEY

NUM_CUST_INTRPT

Number Customers Interrupted

Transformation Logic:

This field is populated with the number of customers who experienced an interruption during the period.

NUM_MOM_E_INTRPT

Number of Mom. E. Intrpt

Transformation Logic:

This field is populated with the number of momentary events during the period, excluding the events that immediately precede a sustained interruption.

NUM_EVENT

Number of Events

Transformation Logic:

This field is populated with the number of events during the period.

CTRL_ZONE_OUTG_UDD1_KEY

Control Zone Outage User Defined Dimension 1 Key

CTRL_ZONE_OUTG_UDD2_KEY

Control Zone Outage User Defined Dimension 2 Key

FACT_CNT

Number of Calls

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

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

NUM_CUST_SUST_INTRPT

Numb. Cust. Sust. Interruption

Transformation Logic:

This field is populated with the number of customers who experienced a sustained interruption during the period.

SNAPSHOT_DT

Snapshot Date

B1_JOB_CONFIG.SLICE_END_DTTM

SRC_NCG_ID

CD_CTRL_ZONE.CD_CTRL_ZONE

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.

Feeder Delivered Load Snapshot Fact

The Feeder Delivered Load Snapshot fact stores the feeder delivered load information from Oracle Utilities Network Management System.

The following measures are populated by the extract:

•

BRKR_AMP_LIMIT. Breaker - Amp Limit

•

AMP. Amp - Phase A, B and C

•

KW. KW - Phase A, B and C

•

KVAR. KVAR - Phase A, B and C

•

KVA. KVA - Phase A, B and C

•

VOLTAGE. Voltage - Phase A, B and C

•

POWER_FACTOR. Power Factor - Phase A, B and C

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_FEEDER_DLVRD_LOAD

Table Type

Fact

Fact Type

Snapshot

Driver Table

FLM_FDR_LOAD

Stage Table Name

STG_CF_FEEDER_DLVRD_LOAD

ODI Package Name

B1_PKG_CF_FEEDER_DLVRD_LOAD

ETL View Name

B1_F_FEEDER_DLVRD_LOAD_VW

Materialized View Name

B1_FEEDER_DLVRD_MON_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

FEEDER_DLVRD_LOAD_KEY

Feeder Delivered Load Fact Surrogate Key

Transformation Logic:

This field is populated with the sequence from B1_PAY_PLAN_ACCUM_SEQ.

SRC_FEEDER_CLS

Feeder Class

FLM_FDR_LOAD.FDR_CLS

SRC_FEEDER_IDX

Feeder Index

FLM_FDR_LOAD.FDR_IDX

SRC_DTTM

Snapshot Date/Time

FLM_FDR_LOAD.ANALYSIS_DATE

FEEDER_KEY

Feeder Key

FLM_FDR_LOAD.FDR_CLS

FLM_FDR_LOAD.FDR_IDX

Note: Lookup in CD_FEEDER dimension table and populate the surrogate key.

CTRL_ZONE_KEY

Control Zone Key

NETWORK_COMPONENTS.NCG

Note: Lookup in CD_CTRL_ZONE dimension table and populate the surrogate key.

BRKR_DEVICE_KEY

Breaker Device Key

FLM_FDR_LOAD.CB_CLS

FLM_FDR_LOAD.CB_IDX

Note: Lookup in CD_DEVICE dimension table and populate the surrogate key.

SUBSTN_DEVICE_KEY

Substation Device Dimension

FLM_FDR_LOAD.CB_CLS

FLM_FDR_LOAD.CB_IDX

Note: Lookup in CD_DEVICE dimension table and populate the surrogate key.

SNAPSHOT_DATE_KEY

Snapshot Date Type

FLM_FDR_LOAD.ANALYSIS_DATE

Transformation Logic:

Extract the date part and then lookup in CD_DATE dimension table.

SNAPSHOT_TIME_KEY

Snapshot Time Type

FLM_FDR_LOAD.ANALYSIS_DATE

Transformation Logic:

Extract the date part and then lookup in CD_TIME dimension table.

PHASE_KEY

Note: Lookup in CD_PHASE dimension table and populate the surrogate key.

FEEDER_DLVRD_LOAD_UDD1_KEY

FEEDER_DLVRD_LOAD_UDD2_KEY

BRKR_AMP_LIMIT

Breaker Amp Limit

PF_SWITCHES.AMP_LIMIT

AMP

Amp - Phase A, B and C

FLM_FDR_LOAD.AMP_A

FLM_FDR_LOAD.AMP_B

FLM_FDR_LOAD.AMP_C

KW

kW - Phase A, B and C

FLM_FDR_LOAD.KW_A

FLM_FDR_LOAD.KW_B

FLM_FDR_LOAD.KW_C

KVAR

kVAr - Phase A, B and C

FLM_FDR_LOAD.KVAR_A

FLM_FDR_LOAD.KVAR_B

FLM_FDR_LOAD.KVAR_C

KVA

kVA - Phase A, B and C

FLM_FDR_LOAD.KW_A

FLM_FDR_LOAD.KVAR_A

FLM_FDR_LOAD.KW_B

FLM_FDR_LOAD.KVAR_B

FLM_FDR_LOAD.KW_C

FLM_FDR_LOAD.KVAR_C

VOLTAGE

Voltage - Phase A, B and C

FLM_FDR_LOAD.KV_A

FLM_FDR_LOAD.KV_B

FLM_FDR_LOAD.

KV_C

POWER_FACTOR

Power Factor - Phase A, B and C

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

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

SRC_PHASE_ID

Phase ID

DATA_SOURCE_IND

Data Source Indicator

B1_PROD_INSTANCE.DSI

BI_APPLICATION_PARAMS.AP_MIN_VALUE

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.

Outage Fact

The Outage fact is an hourly snapshot fact that stores various measures at the control zone level.

This fact is unusual in the sense that its data is not populated directly from the source tables in the replication schema. It is a calculated fact whose data is derived from the data in the restored facts ( Restored Call Fact , Restored Crew Fact , Restored Job Fact , and Restored Customer Outage Fact ) and the recent facts ( Recent Call Fact , Recent Crew Fact , Recent Job Fact , and Recent Customer Outage Fact ).

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_OUTG

Table Type

Fact

Fact Type

Snapshot

Driver Table

Stage Table Name

STG_CF_OUTG

ODI Package Name

B1_PKG_CF_OUTG

ETL View Name

B1_F_CF_OUTG_VW

Materialized View Name

B1_OUTG_MON_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

OUTG_KEY

Outage Fact Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_OUTG_SEQ.

CTRL_ZONE_KEY

Control Zone Key

CD_CTRL_ZONE.CTRL_ZONE_KEY

SNAP_TYPE_CD

Snapshot Type

Transformation Logic:

This field is populated with the standard value of "H" (hourly).

SNAPSHOT_DATE_KEY

Snapshot Date Type

Note: Extract the date part and lookup in CD_DATE dimension table to populate the surrogate key.

SNAPSHOT_TIME_KEY

Snapshot Time Type

Note: Extract the date part and lookup in CD_TIME dimension table to populate the surrogate key.

NUM_CREW_ASSIGN

Number of Crew Assignments

CF_RECENT_CREW.FACT_CNT

NUM_CREW_DISP

Number of Crew Dispatches

CF_RECENT_CREW.FACT_CNT

NUM_CUST_OUTG

Number of Customers Out

CF_CUST_RECENT_OUTG.FACT_CNT

NUM_CUST_RST

Number of Customers Restore

CF_CUST_RECENT_OUTG.FACT_CNT

NUM_CUST_NEW

Number of New Customers

CF_CUST_RECENT_OUTG.FACT_CNT

NUM_CUST_MOM

Number of Customer Momentaries

CF_RECENT_JOB.FACT_CNT

NUM_EVENT

Number of Events

CF_RECENT_JOB.FACT_CNT

NUM_NEW_EVENT

Number of New Events

CF_RECENT_JOB.FACT_CNT

NUM_CLOSED_EVENT

Number of Closed Events

CF_RECENT_JOB.FACT_CNT

NUM_CANCELLED_EVENT

Number of Cancelled Events

CF_RECENT_JOB.CANCELLED_IND

NUM_CALL

Number of Calls

CF_RECENT_CALL.FACT_CNT

Transformation Logic:

This field is populated with the number of calls received during the hour.

OUTG_UDD1_KEY

OUTG_UDD2_KEY

FACT_CNT

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

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

SNAPSHOT_DTTM

Snapshot Date/Time

B1_JOB_CONFIG.SLICE_END_DTTM

SRC_NCG_ID

CD_CTRL_ZONE.SRC_NCG_ID

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 Call Fact

The Recent Call fact stores incidents’ details created in Oracle Utilities Network Management System, also capturing the associated job details. The fact stores details about all un-restored jobs and jobs restored within the last 'X' number of days. The value 'X' days has to be configured using the Oracle Utilities Administration Tool.

See the Configuring Oracle Utilities Network Management System (NMS) section in the Configuring Oracle Utilities Analytics chapter in the Oracle Utilities Analytics Administration Guide for more details.

The ETL job for this fact is configured to load data in near real-time (NRT). The primary purpose to configure it as NRT is to support tracking current (active) and relatively recent restored outages. The NRT data store can also be used to help gauge the ability of the existing resources to deal with a current storm to help determine if external/foreign (crew) resources are or are not required.

The "recent fact" data store is not intended to be a long-term repository and hence the purging mechanism based on the user configured 'X' days.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_RECENT_CALL

Table Type

Fact

Fact Type

Accumulation

Driver Table

INCIDENTS

Stage Table Name

STG_CF_RECENT_CALL

ODI Package Name

B1_PKG_CF_RECENT_CALL

ETL View Name

B1_F_RECENT_CALL_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

RECENT_CALL_KEY

Recent Call Key

Transformation Logic:

This field is populated with the sequence from SPL_RECENT_CALL_SEQ.

SRC_INCIDENT_ID

Source Incident ID

INCIDENTS.DECODE(NVL(ASSOCIATE_IDX, 0), 0, NUMB, ASSOCIATE_IDX)

EVENT_KEY

Event Key

JOBS.NUMB

Note : Lookup in the CD_EVENT dimension table and replace with surrogate key.

ACCT_KEY

Account Key

INCIDENTS.ACCOUNT_NUM

Note : Lookup in the CD_ACCOUNT dimension table and replace with surrogate key.

PRIORITY_IND

Priority Indicator

INCIDENTS.DECODE(SUBSTR( INCIDENTS.COMPLAINT, 7, 1 ), '1', '1', '2', '1', '0')

Note : Use parameter B1_PRTY_BIT instead of 7.

CALL_DATE_KEY

Call Date Key

INCIDENTS.INPUT_TIME

Note : Extract the date part and then lookup in CD_DATE dimension table.

CALL_TIME_KEY

Call Time Key

INCIDENTS.INPUT_TIME

Note : Extract the date part and then lookup in CD_DATE dimension table.

CALL_UDD1_KEY

Call User Defined Dimension 1 Key

CALL_UDD2_KEY

Call User Defined Dimension 2 Key

CTRL_ZONE_KEY

Control Zone Key

INCIDENTS.NCG

Note : Replace with surrogate key.

USER_KEY

User Key

CES_USER.USER_NAME

Note : Look up in the CD_USER table and populate with surrogate key.

CALL_INFO_KEY

Call Info Key

INCIDENTS.DECODE( NVL( ASSOCIATE_IDX, 0 ), 0, NUMB, ASSOCIATE_IDX )

Note : Replace with surrogate key.

PREM_KEY

Premise Key

CES_CUSTOMERS.SERV_LOC_ID

Note : Look up in the CD_PREM table and populate with surrogate key.

ADDR_KEY

Address Key

CES_CUSTOMERS.SERV_LOC_ID

Note : Look up in the CD_ADDR table and populate with surrogate key.

DEVICE_KEY

Device Key

INCIDENTS.H_IDX

INCIDENTS.H_CLS

Note : Look up in the CD_DEVICE table and populate with surrogate key.

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

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

UDDGEN1

User Defined Degenerate Dimension 1

JOBS.RESTORE_TIME

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

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of 1.

DATA_SOURCE_IND

Data Source Indicator

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 Crew Fact

For every job created in the source system, several activities (such as assignment, un-assignment, dispatch, arrival, and completion) are performed to complete the job. Each of these activities are carried out by the corresponding crew. The Recent Crew fact stores all such crew activity information for every job created in the source system. The fact stores details about all un-restored jobs and jobs restored within the last 'X' number of days. The value 'X' days has to be configured using the Oracle Utilities Administration Tool.

See Configuring Oracle Utilities Network Management System (NMS) in the Configuring Oracle Utilities Analytics chapter in the Oracle Utilities Analytics Administration Guide for more details.

The ETL job for this fact is configured to load data in near real-time (NRT). The primary purpose to configure it as NRT is to support tracking current (active) and relatively recent restored outages. The NRT data store can also be used to help gauge the ability of the existing resources to deal with a current storm to help determine if external/foreign (crew) resources are or are not required.

The "recent fact" data store is not intended to be a long-term repository and hence the purging mechanism based on the user configured 'X' days.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_RECENT_CREW

Table Type

Fact

Fact Type

Accumulation

Driver Table

INCIDENTS

Stage Table Name

STG_CF_RECENT_CREW

ODI Package Name

B1_PKG_CF_RECENT_CREW

ETL View Name

B1_F_RECENT_CREW_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

RECENT_CREW_KEY

Recent Crew Key

Transformation Logic:

This field is populated with the sequence from SPL_RECENT_CALL_SEQ.

EVENT_KEY

Event Key

JOBS.NUMB

Note : Replace with surrogate key.

SRC_EVENT_NBR

Source Event Number

JOBS.NUMB

INROUTE_DURATION

Inroute Duration

CREW_DISPATCHES.WHEN_HAPPENED

Transformation Logic:

This field is populated with the total time spent by the crew in ‘Dispatch’ state for a particular event.

ASSIGN_DATE_KEY

Last Assign Date Key

CREW_ASSIGNMENTS ASSIGN.WHEN_HAPPENED

Note : Replace with surrogate key.

ASSIGN_TIME_KEY

Last Assign Time Key

CREW_ASSIGNMENTS ASSIGN.WHEN_HAPPENED

Note : Replace with surrogate key.

CREW_UDD1_KEY

Crew User Defined Dimension 1 Key

CREW_UDD2_KEY

Crew User Defined Dimension 2 Key

CTRL_ZONE_KEY

Control Zone Key

JOBS.NCG

Note : Lookup in CD_CTRL_ZONE dimension table to populate surrogate key.

ASSIGN_USER_KEY

Assign User Key

CREW_ASSIGNMENTS ASSIGN.CES_USER

Note : Lookup in CD_USER dimension table to populate surrogate key.

CREW_KEY

Crew Key

CREW_EVENT_HISTORY.CREW_ID

Note : Lookup in CD_CREW dimension table to populate surrogate key.

WORK_DURATION

Work Duration

CREW_DISPATCHES.WHEN_HAPPENED

Transformation Logic:

This field is populated with the total time spent by the crew in ‘Arrive’ state for a particular event.

ASSIGN_DURATION

Assign Duration

CREW_ASSIGNMENTS.WHEN_HAPPENED

Transformation Logic:

This field is populated with the total time spent by the crew in ‘Assign’ state for a particular event.

DISPATCH_DURATION

Dispatch Duration

CREW_DISPATCHES.WHEN_HAPPENED

CREW_ASSIGNMENTS.WHEN_HAPPENED

DEVICE_KEY

Device Key

JOBS.H_IDX

JOBS.H_CLS

Note : Look up H_IDX and H_CLS in CD_DEVICE dimension table and populate the surrogate key.

UNASSIGN_USER_KEY

Unassign User Key

CREW_ASSIGNMENTS UNASSIGN.CES_USER

UNASSIGN_DATE_KEY

Unassign Date Key

CREW_ASSIGNMENTS UNASSIGN.WHEN_HAPPENED

Note : Extract the date part from WHEN_HAPPENED and then lookup in CD_DATE dimension table.

UNASSIGN_TIME_KEY

Unassign Time Key

CREW_ASSIGNMENTS UNASSIGN.WHEN_HAPPENED

Note : Extract the time part from WHEN_HAPPENED and then lookup in CD_DATE dimension table.

ACCEPT_USER_KEY

Accept User Key

CREW_DISPATCHES DISPATCH.CES_USER

Transformation Logic:

This field is populated with the last user who dispatched the crew. MAX(DISPATCH.WHEN_HAPPENED).

ACCEPT_DATE_KEY

Accept Date Key

CREW_DISPATCHES DISPATCH.WHEN_HAPPENED

Note : Extract the date part from WHEN_HAPPENED and then lookup in CD_DATE dimension table.

ACCEPT_TIME_KEY

Accept Time Key

CREW_DISPATCHES DISPATCH.WHEN_HAPPENED

Note : Extract the time part from WHEN_HAPPENED and then lookup in CD_TIME dimension table.

ARRIVE_TIME_KEY

Arrive Time Key

CREW_DISPATCHES ARRIVE.WHEN_HAPPENED

Note : Extract the time part from WHEN_HAPPENED and then lookup in CD_TIME dimension table.

CMPL_TIME_KEY

Complete Time Key

CREW_DISPATCHES COMPLETE.WHEN_HAPPENED

Note : Extract the time part from WHEN_HAPPENED and then lookup in CD_TIME dimension table.

ARRIVE_DATE_KEY

Arrive Date Key

CREW_DISPATCHES ARRIVE.WHEN_HAPPENED

Note : Extract the date part from WHEN_HAPPENED and then lookup in CD_DATE dimension table.

CMPL_DATE_KEY

Completed Date Key

CREW_DISPATCHES COMPLETE.WHEN_HAPPENED

Note : Extract the date part from WHEN_HAPPENED and then lookup in CD_DATE dimension table.

CMPL_USER_KEY

Completed User Key

CREW_DISPATCHES COMPLETE.CES_USER

Transformation Logic:

This field is populated with the last user name from CREW_EVENT_HISTORY if the job is complete. Else, it is null.

ARRIVE_USER_KEY

Arrive User Key

CREW_DISPATCHES ARRIVE.CES_USER

STORM_KEY

Storm Key

STORMAN_STORMS.STORM_NAME

STORMAN_STORMS.STORM_DATE

Note : Lookup in CD_STORM dimension table to populate the surrogate key.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

Note: Look up in CD_STORM_OUTAGE_TYPE dimension table and populate the surrogate key.

SRC_CREW_ID

Source Crew ID

CREW_EVENT_HISTORY.CREW_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

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

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

UDDGEN1

User Defined Degenerate Dimension 1

JOBS.RESTORE_TIME

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

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of 1.

DATA_SOURCE_IND

Data Source Indicator

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 Job Fact

The Recent Job fact stores job information from the source system. It stores KPIs such as number of events, outage duration, restoration duration, resolved event count, etc.

The fact stores details about all un-restored jobs and jobs restored within the last 'X' number of days. The value 'X' days has to be configured using the Oracle Utilities Administration Tool.

See Configuring Oracle Utilities Network Management System (NMS) in the Configuring Oracle Utilities Analytics chapter in the Oracle Utilities Analytics Administration Guide for more details.

The ETL job for this fact is configured to load data in near real-time (NRT). The primary purpose to configure it as NRT is to support tracking current (active) and relatively recent restored outages. The NRT data store can also be used to help gauge the ability of the existing resources to deal with a current storm to help determine if external/foreign (crew) resources are or are not required.

The "recent fact" data store is not intended to be a long-term repository and hence the purging mechanism based on the user configured 'X' days.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_RECENT_JOB

Table Type

Fact

Fact Type

Accumulation

Driver Table

JOBS

PICKLIST_INFO_UPD_TR

Stage Table Name

STG_CF_RECENT_JOB

ODI Package Name

B1_PKG_CF_RECENT_JOB

ETL View Name

B1_F_RECENT_JOB_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

RECENT_JOB_KEY

Recent Job Key

Transformation Logic:

This field is populated with the sequence from SPL_RECENT_JOB_SEQ.

SRC_JOB_NBR

Source Job Number

JOBS.NUMB

EVENT_KEY

Event Key

JOBS.NUMB

Note : Lookup in CD_EVENT and populate the surrogate key.

EVENT_STATUS_KEY

Event Status Key

JOBS.STATUS

Note : Lookup in CD_EVENT_STATUS and populate the surrogate key.

BEGIN_DATE_KEY

Begin Date Key

JOBS.BEGIN_TIME

Note: Extract the date part and then lookup in the CD_DATE dimension table to populate the surrogate key.

BEGIN_TIME_KEY

Begin Time Key

JOBS.BEGIN_TIME

Note: Extract the time part and then lookup in the CD_TIME dimension table to populate the surrogate key.

BEGIN_DTTM

Begin Date Time

JOBS.BEGIN_TIME

RST_TIME_KEY

Restored Time Key

JOBS.RESTORE_TIME

Note: Extract the time part and then lookup in the CD_TIME dimension table to populate the surrogate key.

RST_DATE_KEY

Restored Date Key

JOBS.RESTORE_TIME

Note: Extract the date part and then lookup in the CD_DATE dimension table to populate the surrogate key.

RST_DTTM

Restored Date Time

JOBS.RESTORE_TIME

RST_IND

Restored Indicator

JOBS.RESTORE_TIME

EST_RST_TIME_KEY

Estimated Restored Time Key

JOBS.EST_REST_TIME

Note: Extract the time part and then lookup in the CD_TIME dimension table to populate the surrogate key.

EST_RST_DATE_KEY

Estimated Restored Date Key

JOBS.EST_REST_TIME

Note: Extract the date part and then lookup in the CD_DATE dimension table to populate the surrogate key.

PLANNED_IND

Planned Indicator

SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME

EXCLUDE_IND

Exclude Indicator

JOBS.STATE_VALUE

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME

OMS_EXCLUDE_IND

OMS Exclude Indicator

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

CANCELLED_IND

Cancelled Indicator

JOBS.STATE_VALUE

OUTG_DURATION

Outage Duration

JOBS.RESTORE_TIME

JOBS.BEGIN_TIME

Transformation Logic:

This field is populated as the difference (in minutes) between begin date/time and the actual restoration date/time of the job.

CTRL_ZONE_KEY

Control Zone Key

JOBS.NCG

DEVICE_KEY

Device Key

JOBS.H_IDX

JOBS.H_CLS

Transformation Logic:

If H_IDX and H_CLS is ‘0’ then populate DEVICE_KEY as ‘0’.

CMPL_USER_KEY

Completed User Key

WHO_COMPLETED.USER_NAME

Note: Lookup in the CD_USER dimension table and populate the surrogate key.

RESP_USER_KEY

Responsible User Key

WHO_RESPONSIBLE.USER_NAME

Note: Lookup in the CD_USER dimension table and populate the surrogate key.

CREW_KEY

Crew Key

CREW_VW.CREW_ID

Note: Lookup in the CD_CREW dimension table and populate the surrogate key.

JOB_UDD1_KEY

User Defined Dimension 1 Key

Note: Lookup in CD_JOB_UDD1 and populate the surrogate key.

JOB_UDD2_KEY

User Defined Dimension 2 Key

Note: Lookup in CD_JOB_UDD1 and populate the surrogate key.

FEEDER_KEY

Feeder Key

JOBS.FEEDER_CLS

JOBS.FEEDER_IDX

Transformation Logic:

If FEEDER_IDX and FEEDER_CLS are ‘0’ then populate FEEDER_KEY as ‘0’.

SW_PLAN_KEY

Switch Plan Key

JOBS.SWSHEET_IDX

JOBS.SWSHEET_CLS

Transformation Logic:

If SWSHEET_IDX and SWSHEET_CLS are ‘0’ then populate SW_PLAN_KEY as ‘0’.

STORM_KEY

Storm Key

STORMAN_STORMS.STORM_NAME

STORMAN_STORMS.STORM_DATE

Note : Lookup in CD_STORM dimension table to populate surrogate key.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

Note : Lookup in CD_STORM_OUTAGE_TYPE dimension table to populate surrogate key.

PARENT_EVENT_KEY

Parent Event Key

PARENT_EVENT.NUMB

JOBS.NUMB

Note : Lookup in CD_EVENT dimension table to populate surrogate key.

OUTG_DUR_KEY

Outage Duration

JOBS.RESTORE_TIME

JOBS.BEGIN_TIME

Note : Populate the OUTG_DURTN_KEY from CD_OUTG_DURTN where OUTG_DURATION is between RANGE_START and RANGE_END.

EST_RST_DUR_KEY

Est Restoration Duration

JOBS.EST_REST_TIME

JOBS.BEGIN_TIME

Note : Populate the EST_RST_DURTN_KEY from CD_EST_RST_DURTN where OUTG_DURATION is between RANGE_START and RANGE_END.

DURATION_DEVIATION_KEY

Duration Deviation

JOBS.RESTORE_TIME

JOBS.EST_REST_TIME

Note : Populate the ESTMTD_VS_ACTL_DURTN_KEY from CD_ESTMTD_VS_ACTL_DURTN where OUTG_DURATION is between RANGE_START and RANGE_END.

EST_RST_DUR

Outage Duration

JOBS.EST_REST_TIME

JOBS.BEGIN_TIME

Transformation Logic:

This field is populated as the difference (in minutes) between the begin date/time and the estimated restoration date/time of the job.

UDM1

Customers Impacted

JOBS.NUM_CUST_OUT

Transformation Logic:

This field is populated with the number of customers impacted.

UDM2

Calls Received

INCIDENTS.NUMB

Transformation Logic:

This field is populated with the number of calls received by counting the number of incidents associated with the job.

UDM3

Estimated Duration Deviation

JOBS.RESTORE_TIME

JOBS.EST_REST_TIME

Transformation Logic:

This field is populated with the difference (in minutes) between the actual restoration date/time and the estimated restoration date/time of the job.

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

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

UDDGEN1

User Defined Degenerate Dimension 1

JOBS.RESTORE_TIME

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

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of 1.

DATA_SOURCE_IND

Data Source Indicator

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 Customer Outage Fact

The Recent Customer Outage fact stores the details of customers experiencing service interruptions based on the supply node log information from the source system. The supply node log data will be mapped to the Supply Node Lookup Dimension to determine the actual list of customers experiencing the outage. Some of the measures captured in this fact are outage duration, CMI, number of momentary outages etc.

The fact stores details about all un-restored jobs and jobs restored within the last 'X' number of days. The value 'X' days has to be configured using the Oracle Utilities Administration Tool.

See Configuring Oracle Utilities Network Management System (NMS) in the Configuring Oracle Utilities Analytics chapter in the Oracle Utilities Analytics Administration Guide for more details.

The ETL job for this fact is configured to load data in near real-time (NRT). The primary purpose to configure it as NRT is to support tracking current (active) and relatively recent restored outages. The NRT data store can also be used to help gauge the ability of the existing resources to deal with a current storm to help determine if external/foreign (crew) resources are or are not required.

The "recent fact" data store is not intended to be a long-term repository and hence the purging mechanism based on the user configured 'X' days.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CUST_RECENT_OUTG

Table Type

Fact

Fact Type

Accumulation

Driver Table

SUPPLY_NODE_LOG SNL

JOBS J

Stage Table Name

STG_CF_CUST_RECENT_OUTG

ODI Package Name

B1_PKG_CF_CUST_RECENT_OUTG

ETL View Name

B1_F_CUST_RECENT_OUTG_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CUST_RECENT_OUTG_KEY

Recent Outage Key

Transformation Logic:

This field is populated with the sequence from SPL_CUST_RECENT_OUTG_SEQ.

SRC_ID

Source Outage ID

SUPPLY_NODE_LOG.ID

EVENT_KEY

Event Key

JOBS.NUMB

Note : Lookup in the CD_EVENT dimension table to populate the surrogate key.

EVENT_STATUS_KEY

Event Status Key

JOBS.STATUS

Note : Lookup in the CD_EVENT_STATUS dimension table to populate the surrogate key.

ACCT_KEY

Account Key

CD_ACCT.SRC_ACCT_ID

INCIDENTS.ACCOUNT_NUM

CES_CUSTOMERS.ACCOUNT_NUMBER

PREM_KEY

Premise Key

CD_PREM.SRC_PREM_ID

PER_KEY

Person Key

CD_PER.SRC_PER_ID

METER_KEY

Meter Key

CD_METER.SRC_METER_ID

ADDR_KEY

Address Key

CD_ADDR.SRC_ADDR_ID

BEGIN_DATE_KEY

Begin Date Key

SUPPLY_NODE_LOG.OUTAGE_TIME

Note: Extract the date part and then lookup in the CD_DATE dimension table.

BEGIN_TIME_KEY

Begin Time Key

SUPPLY_NODE_LOG.OUTAGE_TIME

Note: Extract the time part and then lookup in the CD_TIME dimension table.

BEGIN_DTTM

Begin Date Time

SUPPLY_NODE_LOG.OUTAGE_TIME

RST_DATE_KEY

Restored Time Key

SUPPLY_NODE_LOG.RESTORE_TIME

Note: Extract the date part and then lookup in the CD_DATE dimension table.

RST_TIME_KEY

Restored Date Key

SUPPLY_NODE_LOG.RESTORE_TIME

Note: Extract the time part and then lookup in the CD_TIME dimension table.

RST_DTTM

Restored Date Time

SUPPLY_NODE_LOG.RESTORE_TIME

RST_IND

Restored Indicator

SUPPLY_NODE_LOG.RESTORE_TIME

EST_RST_TIME_KEY

Estimated Restored Time Key

JOBS.EST_REST_TIME

Note: Extract the time part and then lookup in the CD_TIME dimension table.

EST_RST_DATE_KEY

Estimated Restored Date Key

JOBS.EST_REST_TIME

Note: Extract the date part and then lookup in the CD_DATE dimension table.

CTRL_ZONE_KEY

Control Zone Key

SUPPLY_NODE_LOG.NCG

Note: Lookup in CD_CTRL_ZONE dimension table and populate the surrogate key.

CAUSE_CTRL_ZONE_KEY

Cause Control Zone Key

JOBS.NCG

Note: Lookup in CD_CTRL_ZONE dimension table and populate the surrogate key.

CUST_OUTG_UDD1_KEY

User Defined Dimension 1 Key

CUST_OUTG_UDD2_KEY

User Defined Dimension 2 Key

PLANNED_IND

Planned Indicator

SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME

EXCLUDE_IND

Exclude Indicator

JOBS J.STATE_VALUE

PICKLIST_INFO_UPD_TR P.NO_DTR_FLAG

SWMAN_SHEET_CATEGORY SSCA.SHEET_CATEGORY_NAME

OMS_EXCLUDE_IND

OMS Exclude Indicator

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

CANCELLED_IND

Cancelled Indicator

JOBS.STATE_VALUE

OUTG_DURATION

Outage Duration

SUPPLY_NODE_LOG.OUTAGE_TIME

SUPPLY_NODE_LOG.RESTORE_TIME

NUM_MOMENTARY

number of momentaries covered

JOBS.NUM_MOMENTARIES

AFF_DEVICE_KEY

AFF Device Key

SUPPLY_NODES.DEVICE_IDX

SUPPLY_NODES.

DEVICE_CLS

Transformation Logic:

If H_IDX and H_CLS are ‘0’ then populate DEVICE_KEY as ‘0’.

CAUSE_DEVICE_KEY

Cause Device Key

JOBS.H_IDX

JOBS.H_CLS

Transformation Logic:

If H_IDX and H_CLS are ‘0’ then populate DEVICE_KEY as ‘0’.

FEEDER_KEY

Feeder Key

JOBS.FEEDER_CLS

JOBS.FEEDER_IDX

Transformation Logic:

If FEEDER_IDX and FEEDER_CLS are ‘0’ then populate FEEDER_KEY as ‘0’.

SW_PLAN_KEY

Switch Plan Key

JOBS.SWSHEET_IDX

JOBS.SWSHEET_CLS

Transformation Logic:

If SWSHEET_IDX and SWSHEET_CLS are ‘0’ then populate SW_PLAN_KEY as ‘0’.

STORM_KEY

Storm Key

STORMAN_STORMS.STORM_NAME

STORMAN_STORMS.STORM_DATE

Note: Lookup in CD_STORM dimension to populate the surrogate key.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

Note: Lookup in CD_STORM_OUTAGE_TYPE and populate the surrogate key.

PARENT_EVENT_KEY

Parent Event Key

PARENT_EVENT.NUMB

Note : Lookup in CD_EVENT and populate the surrogate key.

CMI

CMI

SUPPLY_NODE_LOG.OUTAGE_TIME

SUPPLY_NODE_LOG.RESTORE_TIME

OUTG_DUR_KEY

Outage Duration

SUPPLY_NODE_LOG.OUTAGE_TIME,

SUPPLY_NODE_LOG.

RESTORE_TIME

Transformation Logic:

This field is populated with OUTG_DURTN_KEY from CD_OUTG_DURTN where OUTG_DURTN is between RANGE_START and RANGE_END.

EST_RST_DUR_KEY

Est Restoration Duration Surrogate key

JOBS.BEGIN_TIME,

JOBS.EST_REST_TIME

Transformation Logic:

This field is populated with EST_RST_DURTN_KEY from CD_EST_RST_DURTN where OUTG_DURTN is between RANGE_START and RANGE_END.

DURATION_DEVIATION_KEY

Duration Deviation Surrogate Key

JOBS.RESTORE_TIME

JOBS.EST_REST_TIME

Transformation Logic:

This field is populated with ESTMTD_VS_ACTL_DURTN_KEY from CD_ESTMTD_VS_ACTL_DURTN where OUTG_DURTN is between RANGE_START and RANGE_END.

EST_RST_DUR

Est Restoration Duration

JOBS.BEGIN_TIME

JOBS.EST_REST_TIME

DURATION_DEVIATION

Duration Deviation

JOBS.RESTORE_TIME

JOBS.EST_REST_TIME

SRC_PREMISE_ID

CD_PREM.SRC_PREM_ID

SRC_PERSON_ID

CD_PER.SRC_PER_ID

SRC_METER_ID

CD_METER.SRC_METER_ID

DEVICE_ID

CD_DEVICE.DEVICE_ID

CREW_KEY

CREW_VW.CREW_ID

Transformation Logic:

This field is used to lookup in CD_CREW and populate the surrogate key.

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

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

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

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of 1.

DATA_SOURCE_IND

Data Source Indicator

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.

Restored Call Fact

The Restored Call fact stores details about the incidents created in the source system and also captures details about the associated jobs. The data is stored for all restored and un-restored jobs.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_RST_CALL

Table Type

Fact

Fact Type

Accumulation

Driver Table

INCIDENTS

Stage Table Name

STG_CF_RST_CALL

ODI Package Name

B1_PKG_CF_RST_CALL

ETL View Name

B1_F_RECENT_CALL_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

RST_CALL_KEY

Recent Call Key

Transformation Logic:

This field is populated with the sequence from SPL_RST_CALL_SEQ.

SRC_INCIDENT_ID

Source Incident ID

INCIDENTS.DECODE( NVL( ASSOCIATE_IDX, 0 ), 0, NUMB, ASSOCIATE_IDX )

EVENT_KEY

Event Key

JOBS.NUMB

Note : Lookup in the CD_EVENT dimension table and replace with surrogate key.

ACCT_KEY

Account Key

INCIDENTS.ACCOUNT_NUM

Note : Lookup in the CD_ACCOUNT dimension table and replace with surrogate key.

PRIORITY_IND

Priority Indicator

INCIDENTS.DECODE(SUBSTR( COMPLAINT, 7, 1 ), '1', '1', '2', '1', '0'), ' ' )

CALL_DATE_KEY

Call Date Key

INCIDENTS.INPUT_TIME

CALL_TIME_KEY

Call Time Key

INCIDENTS.INPUT_TIME

Transformation Logic:

Extract the date part and then lookup in the CD_DATE dimension table.

CALL_UDD1_KEY

Call User Defined Dimension 1 Key

INCIDENTS.INPUT_TIME

Transformation Logic:

Extract the time part and then lookup in the CD_TIME dimension table.

CALL_UDD2_KEY

Call User Defined Dimension 2 Key

CTRL_ZONE_KEY

Control Zone Key

INCIDENTS.SRC_NCG_ID

Note: Replace with surrogate key.

USER_KEY

User Key

CES_USER.USER_NAME

Note: Look up in the CD_USER dimension table and replace with surrogate key.

CALL_INFO_KEY

Call Info Key

INCIDENTS.DECODE( NVL( ASSOCIATE_IDX, 0 ), 0, NUMB, ASSOCIATE_IDX )

Note: Replace with surrogate key.

PREM_KEY

Premise Key

CES_CUSTOMERS.SERV_LOC_ID

Note: Look up in the CD_PREM dimension table and replace with surrogate key.

ADDR_KEY

Address Key

CES_CUSTOMERS.SERV_LOC_ID

Note: Look up in the CD_ADDR dimension table and replace with surrogate key.

DEVICE_KEY

Device Key

INCIDENTS.H_IDX

INCIDENTS.H_CLS

Note: Look up in the CD_DEVICE dimension table and replace with surrogate key.

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

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

UDDGEN1

User Defined Degenerate Dimension 1

JOBS.RESTORE_TIME

Transformation Logic:

If RESTORE_TIME is ‘null’ then value is ‘0’, else ‘1’. ‘0’ indicates the job is unrestored and ‘1’ indicates it is restored.

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

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of 1.

DATA_SOURCE_IND

Data Source Indicator

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.

Restored Crew Fact

For all jobs created in the source system, several activities (such as assignment, un-assignment, dispatch, arrival and completion) are performed to complete them. Each of these activities are carried out by the corresponding crew.

The Recent Crew fact stores all such crew activity information for every job created in the source system. The data is stored for all restored and un-restored jobs.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_RST_CREW

Table Type

Fact

Fact Type

Accumulation

Driver Table

CREW_EVENT_HISTORY_CEH

Stage Table Name

STG_CF_RST_CREW

ODI Package Name

B1_PKG_CF_RST_CREW

ETL View Name

B1_F_RECENT_CREW_VW

Materialized View Name

B1_RST_CREW_MON_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

RST_CREW_KEY

Restored Crew Key

Transformation Logic:

This field is populated with the sequence from SPL_RST_CREW_SEQ.

EVENT_KEY

Event Key

JOBS.NUMB

Transformation Logic:

This field is populated with the surrogate key.

SRC_EVENT_NBR

Source Event Number

JOBS.NUMB

INROUTE_DURATION

Inroute Duration

CREW_DISPATCHES.WHEN_HAPPENED

ASSIGN_DATE_KEY

Assign Date Key

CREW_ASSIGNMENTS ASSIGN.WHEN_HAPPENED

Transformation Logic:

This field is populated with the last assigned date.

ASSIGN_TIME_KEY

Assign Time Key

CREW_ASSIGNMENTS ASSIGN.WHEN_HAPPENED

Transformation Logic:

This field is populated with the last assigned time.

CREW_UDD1_KEY

Crew User Defined Dimension 1 Key

CREW_UDD2_KEY

Crew User Defined Dimension 2 Key

CTRL_ZONE_KEY

Control Zone Key

JOBS.NCG

Transformation Logic:

This field is populated with the surrogate key.

ASSIGN_USER_KEY

Assign User Key

CREW_ASSIGNMENTS ASSIGN.CES_USER

Transformation Logic:

This field is populated with the surrogate key.

CREW_KEY

Crew Key

CREW_EVENT_HISTORY.CREW_ID

Transformation Logic:

This field is populated with the surrogate key.

WORK_DURATION

Work Duration

CREW_DISPATCHES.WHEN_HAPPENED

ASSIGN_DURATION

Assign Duration

CREW_ASSIGNMENTS.WHEN_HAPPENED

DISPATCH_DURATION

Dispatch Duration

CREW_DISPATCHES.WHEN_HAPPENED

CREW_ASSIGNMENTS.WHEN_HAPPENED

DEVICE_KEY

Device Key

JOBS.H_IDX

JOBS.H_CLS

Transformation Logic:

This field is populated with the surrogate key.

UNASSIGN_USER_KEY

Unassign User Key

CREW_ASSIGNMENTS UNASSIGN.CES_USER

Transformation Logic:

This field is populated with the last assigned user.

UNASSIGN_DATE_KEY

Unassign Date Key

CREW_ASSIGNMENTS UNASSIGN.WHEN_HAPPENED

Transformation Logic:

This field is populated with the last unassigned date.

UNASSIGN_TIME_KEY

Unassign Time Key

CREW_ASSIGNMENTS UNASSIGN.WHEN_HAPPENED

Transformation Logic:

This field is populated with the last unassigned time.

ACCEPT_USER_KEY

Accept User Key

CREW_DISPATCHES DISPATCH.CES_USER

Transformation Logic:

This field is populated with the last dispatched user.

ACCEPT_DATE_KEY

Accept Date Key

CREW_DISPATCHES DISPATCH.WHEN_HAPPENED

Transformation Logic:

This field is populated with the last accepted date.

ACCEPT_TIME_KEY

Accept Time Key

CREW_DISPATCHES DISPATCH.WHEN_HAPPENED

Transformation Logic:

This field is populated with the last accepted time.

ARRIVE_TIME_KEY

Arrive Time Key

CREW_DISPATCHES ARRIVE.WHEN_HAPPENED

Transformation Logic:

This field is populated with the last arrived time.

CMPL_TIME_KEY

Complete Time Key

CREW_DISPATCHES COMPLETE.WHEN_HAPPENED

Transformation Logic:

This field is populated with the last arrived time.

ARRIVE_DATE_KEY

Arrive Date Key

CREW_DISPATCHES COMPLETE.WHEN_HAPPENED

Transformation Logic:

This field is populated with the last arrived date.

CMPL_DATE_KEY

Completed Date Key

CREW_DISPATCHES COMPLETE.WHEN_HAPPENED

Transformation Logic:

This field is populated with the last job completion date.

CMPL_USER_KEY

Completed User Key

CREW_DISPATCHES COMPLETE.CES_USER

Transformation Logic:

This field is populated with the last user name if the job is completed. Else, it is null.

ARRIVE_USER_KEY

Arrive User Key

CREW_DISPATCHES ARRIVE.CES_USER

Transformation Logic:

This field is populated with the last user name.

STORM_KEY

Storm Key

STORMAN_STORMS.STORM_NAME

STORMAN_STORMS.STORM_DATE

Transformation Logic:

This field is populated with the last storm name and date.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

Transformation Logic:

This field is populated with the type of the last storm.

SRC_CREW_ID

Source Crew ID

CREW_EVENT_HISTORY.CREW_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

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

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

UDDGEN1

User Defined Degenerate Dimension 1

JOBS.RESTORE_TIME

Transformation Logic:

If RESTORE_TIME is ‘null’ then value is ‘0’, else ‘1’. ‘0’ indicates the job is unrestored and ‘1’ indicates it is restored.

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

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of 1.

DATA_SOURCE_IND

Data Source Indicator

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.

Restored Job Fact

The Restored Job fact stores job information from the source system. It stores KPIs such as number of events, outage duration, restoration duration, resolved event count, etc. The data is stored for all restored and un-restored jobs.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_RST_JOB

Table Type

Fact

Fact Type

Accumulation

Driver Table

JOBS

PICKLIST_INFO_UPD_TR

Stage Table Name

STG_CF_RST_JOB

ODI Package Name

B1_PKG_CF_RST_JOB

ETL View Name

B1_F_RECENT_JOB_VW

Materialized View Name

B1_RST_JOB_MON_MV1

B1_RST_JOB_MON_MV2

B1_RST_JOB_MON_MV3

B1_RST_JOB_TOPX_MON_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

RST_JOB_KEY

Restored Job Key

Transformation Logic:

This field is populated with the sequence from SPL_RST_JOB_SEQ.

SRC_JOB_NBR

Source Job Number

JOBS.NUMB

EVENT_KEY

Event Key

JOBS.NUMB

EVENT_STATUS_KEY

Event Status Key

JOBS.STATUS

BEGIN_DATE_KEY

Begin Date Key

JOBS.BEGIN_TIME

BEGIN_TIME_KEY

Begin Time Key

JOBS.BEGIN_TIME

BEGIN_DTTM

Begin Date Time

JOBS.BEGIN_TIME

RST_TIME_KEY

Restored Time Key

JOBS.RESTORE_TIME

RST_DATE_KEY

Restored Date Key

JOBS.RESTORE_TIME

RST_DTTM

Restored Date Time

JOBS.RESTORE_TIME

RST_IND

Restored Indicator

JOBS.RESTORE_TIME

Transformation Logic:

If the restoration time is null, this field is populated with ‘0’, else ‘1’.

EST_RST_TIME_KEY

Estimated Restored Time Key

JOBS.EST_REST_TIME

EST_RST_DATE_KEY

Estimated Restored Date Key

JOBS.EST_REST_TIME

PLANNED_IND

Planned Indicator

SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME

EXCLUDE_IND

Exclude Indicator

JOBS.STATE_VALUE

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME

OMS_EXCLUDE_IND

OMS Exclude Indicator

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

CANCELLED_IND

Cancelled Indicator

JOBS.STATE_VALUE

OUTG_DURATION

Outage Duration

JOBS.RESTORE_TIME

JOBS.BEGIN_TIME

Transformation Logic:

This field is populated with the difference (in minutes) between the begin date/time and the actual restoration date/time of the job.

CTRL_ZONE_KEY

Control Zone Key

JOBS.NCG

DEVICE_KEY

Device Key

JOBS.H_IDX

JOBS.H_CLS

Transformation Logic:

If H_IDX and H_CLS are ‘0’, then populate DEVICE_KEY as ‘0’.

CMPL_USER_KEY

Completed User Key

WHO_COMPLETED.USER_NAME

RESP_USER_KEY

Responsible User Key

WHO_RESPONSIBLE.USER_NAME

CREW_KEY

Crew Key

CREW_VW.CREW_ID

JOB_UDD1_KEY

User Defined Dimension 1 Key

JOB_UDD2_KEY

User Defined Dimension 2 Key

FEEDER_KEY

Feeder Key

JOBS.FEEDER_CLS

JOBS.FEEDER_IDX

Transformation Logic :

If FEEDER_IDX and FEEDER_CLS are ‘0’, then this field is populated as ‘0’.

SW_PLAN_KEY

Switch Plan Key

JOBS.SWSHEET_IDX

JOBS.SWSHEET_CLS

Transformation Logic :

If SWSHEET_IDX and SWSHEET_CLS are ‘0’, then this field is populated as ‘0’.

STORM_KEY

Storm Key

STORMAN_STORMS.STORM_NAME

STORMAN_STORMS.STORM_DATE

Transformation Logic :

This field is populated with the storm name and date.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

PARENT_EVENT_KEY

Parent Event Key

PARENT_EVENT.NUMB

JOBS.NUMB

OUTG_DUR_KEY

Outage Duration Key

JOBS.RESTORE_TIME

JOBS.BEGIN_TIME

EST_RST_DUR_KEY

Estimated Restoration Duration Key

JOBS.EST_REST_TIME

JOBS.BEGIN_TIME

DURATION_DEVIATION_KEY

Duration Deviation Key

JOBS.RESTORE_TIME

JOBS.EST_REST_TIME

EST_RST_DUR

Estimated Restoration Duration

JOBS.EST_REST_TIME

JOBS.BEGIN_TIME

Transformation Logic:

This field is populated with the difference (in minutes) between the begin date/time and the estimated restoration date/time of the job.

UDM1

Customers Impacted

JOBS.NUM_CUST_OUT

Transformation Logic:

This field is populated with the number of customers impacted.

UDM2

Calls Received

INCIDENTS.NUMB

Transformation Logic:

This field is populated with the number of calls received by counting the number of incidents associated with the job.

UDM3

Estimated Duration Deviation

JOBS.RESTORE_TIME

JOBS.EST_REST_TIME

Transformation Logic:

This field is populated with the difference (in minutes) between the actual restoration date/time and the estimated restoration date/time of the job.

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

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

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

FACT_CNT

Fact Count

Transformation Logic:

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

DATA_SOURCE_IND

Data Source Indicator

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.

Restored Customer Outage Fact

The Restored Customer Outage fact stores the details of customers experiencing service interruptions based on the supply node log information from the source system. The supply node log data will be mapped to the Supply Node Lookup Dimension to determine the actual list of customers experiencing the outage.

Few measures captured in this fact are outage duration, CMI, number of momentary outages, etc. The data is stored for all restored and un-restored jobs.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CUST_RST_OUTG

Table Type

Fact

Fact Type

Accumulation

Driver Table

SUPPLY_NODE_LOG SNL

Stage Table Name

STG_CF_CUST_RST_OUTG

ODI Package Name

B1_PKG_CF_CUST_RST_OUTG

ETL View Name

B1_F_CUST_RECENT_OUTG_VW

Materialized View Name

B1_CUST_RST_OUTG_MON_MV1

B1_CUST_RST_OUTG_MON_MV2

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

CUST_RST_OUTG_KEY

Recent Outage Key

Transformation Logic:

This field is populated with the sequence from SPL_CUST_RST_OUTG_SEQ.

SRC_ID

Source Outage ID

SUPPLY_NODE_LOG.ID

EVENT_KEY

Event Key

JOBS.NUMB

EVENT_STATUS_KEY

Event Status Key

JOBS.STATUS

ACCT_KEY

Account Key

CD_ACCT.SRC_ACCT_ID

INCIDENTS.ACCOUNT_NUM

CES_CUSTOMERS.ACCOUNT_NUMBER

PREM_KEY

Premise Key

CD_PREM.SRC_PREM_ID

PER_KEY

Person Key

CD_PER.SRC_PER_ID

METER_KEY

Meter Key

CD_METER.SRC_METER_ID

ADDR_KEY

Address Key

CD_ADDR.SRC_ADDR_ID

BEGIN_DATE_KEY

Begin Date Key

SUPPLY_NODE_LOG.OUTAGE_TIME

BEGIN_TIME_KEY

Begin Time Key

SUPPLY_NODE_LOG.OUTAGE_TIME

BEGIN_DTTM

Begin Date Time

SUPPLY_NODE_LOG.OUTAGE_TIME

RST_DATE_KEY

Restored Time Key

SUPPLY_NODE_LOG.RESTORE_TIME

RST_TIME_KEY

Restored Date Key

SUPPLY_NODE_LOG.RESTORE_TIME

RST_DTTM

Restored Date Time

SUPPLY_NODE_LOG.RESTORE_TIME

RST_IND

Restored Indicator

SUPPLY_NODE_LOG.RESTORE_TIME

Transformation Logic:

If restore time is null, this field is populated with ‘0’. Else, ‘1’.

EST_RST_TIME_KEY

Estimated Restored Time Key

JOBS.EST_REST_TIME

EST_RST_DATE_KEY

Estimated Restored Date Key

JOBS.EST_REST_TIME

CTRL_ZONE_KEY

Control Zone Key

SUPPLY_NODE_LOG.NCG

CAUSE_CTRL_ZONE_KEY

Cause Control Zone Key

JOBS.NCG

CUST_OUTG_UDD1_KEY

User Defined Dimension 1 Key

CUST_OUTG_UDD2_KEY

User Defined Dimension 2 Key

PLANNED_IND

Planned Indicator

SWMAN_SHEET.SHEET_CATEGORY_NAME

EXCLUDE_IND

Exclude Indicator

JOBS J.STATE_VALUE

PICKLIST_INFO_UPD_TR P.NO_DTR_FLAG

SWMAN_SHEET_CATEGORY SSCA.SHEET_CATEGORY_NAME

OMS_EXCLUDE_IND

OMS Exclude Indicator

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

CANCELLED_IND

Cancelled Indicator

JOBS.STATE_VALUE

OUTG_DURATION

Outage Duration

SUPPLY_NODE_LOG.OUTAGE_TIME

SUPPLY_NODE_LOG.RESTORE_TIME

Transformation Logic:

This field is populated with the difference between restore time and outage time.

NUM_MOMENTARY

Number of momentaries covered

JOBS.MOMENTARIES

AFF_DEVICE_KEY

AFF Device Key

SUPPLY_NODES.DEVICE_IDX

SUPPLY_NODES.DEVICE_CLS

Transformation Logic:

If H_IDX and H_CLS are ‘0’, this field is populated with ‘0’.

CAUSE_DEVICE_KEY

Cause Device Key

JOBS.H_IDX

JOBS.H_CLS

Transformation Logic:

If H_IDX and H_CLS are ‘0’, this field is populated with ‘0’.

FEEDER_KEY

Feeder Key

SUPPLY_NODES.FEEDER

Transformation Logic:

If FEEDER_IDX and FEEDER_CLS are ‘0’, this field is populated with ‘0’.

SW_PLAN_KEY

Switch Plan Key

JOBS.SWSHEET_IDX

JOBS.SWSHEET_CLS

Transformation Logic:

If SWSHEET_IDX and SWSHEET_CLS are ‘0’, this field is populated with ‘0’.

STORM_KEY

Storm Key

STORMMAN_STORMS.STORM_NAME

STORMMAN_STORMS.STORM_DATE

Transformation Logic:

This field is populated with the storm name and date.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

PARENT_EVENT_KEY

Parent Event Key

PARENT_EVENT.NUMB

CMI

Customer Minutes Interrupted

SUPPLY_NODE_LOG.OUTAGE_TIME

SUPPLY_NODE_LOG.RESTORE_TIME

OUTG_DUR_KEY

Outage Duration Key

SUPPLY_NODE_LOG.OUTAGE_TIME

SUPPLY_NODE_LOG.RESTORE_TIME

Transformation Logic:

This field is populated with the difference between restore time and outage time.

EST_RST_DUR_KEY

Estimated Restore Duration Key

JOBS.BEGIN_TIME

JOBS.EST_REST_TIME

Transformation Logic:

This field is populated with the difference between estimated restoration time and begin time.

DURATION_DEVIATION_KEY

Duration Deviation Key

JOBS.RESTORE_TIME

JOBS.EST_REST_TIME

Transformation Logic:

This field is populated with the difference between restoration time and estimated restore time.

EST_RST_DUR

Estimated Restoration Duration

JOBS.BEGIN_TIME

JOBS.EST_REST_TIME

Transformation Logic:

This field is populated with the difference between estimated restore time and begin time.

DURATION_DEVIATION

Duration Deviation

JOBS.RESTORE_TIME

JOBS.EST_REST_TIME

Transformation Logic:

This field is populated with the difference between restore time and estimated restore time.

SRC_PREMISE_ID

Premise ID

CD_PREM.SRC_PREM_ID

SRC_PERSON_ID

Person ID

CD_PER.SRC_PER_ID

SRC_METER_ID

Meter ID

CD_METER.SRC_METER_ID

DEVICE_ID

Device ID

CD_DEVICE.DEVICE_ID

CREW_KEY

CREW_VW.CREW_ID

Transformation Logic:

This field is used to lookup in CD_CREW and populate the surrogate key.

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

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

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

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of 1.

DATA_SOURCE_IND

Data Source Indicator

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.

Switch Plan Fact

The Switch Plan fact supports KPIs such as number of steps, number of aborted/failed steps, number of safety documents, etc.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_SW_PLAN

Table Type

Fact

Fact Type

Accumulation

Driver Table

SWMAN_SHEET S

Stage Table Name

STG_CF_SW_PLAN

ODI Package Name

B1_PKG_CF_SW_PLAN

ETL View Name

B1_F_SW_PLAN_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

SW_PLAN_F_KEY

Switch Plan Fact Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_SW_PLAN_SEQ.

SRC_SW_PLAN_CLS

Switch Plan Class

SWMAN_SHEET.SWITCH_SHEET_CLS

SRC_SW_PLAN_IDX

Switch Plan Index

SWMAN_SHEET.SWITCH_SHEET_IDX

SW_PLAN_KEY

Switch Plan Key

SWMAN_SHEET.SWITCH_SHEET_CLS

SWMAN_SHEET.SWITCH_SHEET_IDX

CTRL_ZONE_KEY

Control Zone Key

SWMAN_SHEET.NCG

SW_PLAN_STATE_KEY

Switch Plan State Key

SWMAN_SHEET.STATE_KEY

BEGIN_DATE_KEY

Begin Date

Switch Plan Begin.WHEN_ISSUED

BEGIN_TIME_KEY

Begin Time

Switch Plan Begin.WHEN_ISSUED

BEGIN_DTTM

Begin Date/Time

Switch Plan Begin.WHEN_ISSUED

END_DATE_KEY

End Date

Switch Plan End.WHEN_EXECUTED

END_TIME_KEY

End Time

Switch Plan End.WHEN_EXECUTED

END_DTTM

End Date/Time

Switch Plan End.WHEN_EXECUTED

USER_KEY1

User 1

Transformation Logic:

This field is populated with ‘0’.

USER_KEY2

User 2

Transformation Logic:

This field is populated with ‘0’.

USER_KEY3

User 3

Transformation Logic:

This field is populated with ‘0’.

USER_KEY4

User 4

Transformation Logic:

This field is populated with ‘0’.

USER_KEY5

User 5

Transformation Logic:

This field is populated with ‘0’.

USER_KEY6

User 6

Transformation Logic:

This field is populated with ‘0’.

USER_KEY7

User 7

Transformation Logic:

This field is populated with ‘0’.

USER_KEY8

User 8

Transformation Logic:

This field is populated with ‘0’.

SW_PLAN_UDD1_KEY

SW_PLAN_UDD2_KEY

DURATION

Duration

Switch Plan Begin.1440 * ( CT.WHEN_EXECUTED - ID.WHEN_ISSUED )

Switch Plan End.1440 * ( CT.WHEN_EXECUTED - ID.WHEN_ISSUED )

NBR_OF_STEPS

Number of steps

SWMAN_STEP.COUNT(*)

NBR_OF_FAILED_STEPS

Number of failed steps

NBR_OF_ABORTED_STEPS

Number of aborted steps

Transformation Logic:

This field is populated with the difference between restore time and outage time.

NBR_OF_SAFETY_DOCS

Number of safety documents

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

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

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

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

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of 1.

DATA_SOURCE_IND

Data Source Indicator

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.

Switch Plan State Fact

The Switch Plan State fact supports KPIs such as state duration, number of log entries, etc.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_SW_PLAN_STATE

Table Type

Fact

Fact Type

Accumulation

Driver Table

SWMAN_AUDIT_LOG SAL1

Stage Table Name

STG_CF_SW_PLAN_STATE

ODI Package Name

B1_PKG_CF_SW_PLAN_STATE

ETL View Name

B1_F_SW_PLAN_STATE_VW

Materialized View Name

Fields

Target Field

OBIEE Field

Source Field

Transformation /

Calculation Logic

SW_PLAN_STATE_F_KEY

Switch Plan Fact Surrogate Key

Transformation Logic:

This field is populated with the sequence from SPL_SW_PLAN_STATE_SEQ.

SRC_LOG_ENTRY

Switch Plan Log Entry ID

SWMAN_AUDIT_LOG SAL1.SEQ_LOG_ID

SW_PLAN_KEY

Switch Plan Key

SWMAN_SHEET.SWITCH_SHEET_CLS

SWMAN_SHEET.SWITCH_SHEET_IDX

CTRL_ZONE_KEY

Control Zone Key

SWMAN_SHEET.NCG

SW_PLAN_STATE_KEY

Switch Plan State Key

TE_VALID_STATES.STATE_KEY

BEGIN_DATE_KEY

Begin Date

SWMAN_AUDIT_LOG SAL1.INSERT_TIME

BEGIN_TIME_KEY

Begin Time

SWMAN_AUDIT_LOG SAL1.INSERT_TIME

BEGIN_DTTM

Begin Date/Time

SWMAN_AUDIT_LOG SAL1.INSERT_TIME

END_DATE_KEY

End Date

SWMAN_AUDIT_LOG SAL2.INSERT_TIME

END_TIME_KEY

End Time

SWMAN_AUDIT_LOG SAL2.INSERT_TIME

END_DTTM

End Date/Time

SWMAN_AUDIT_LOG SAL2.INSERT_TIME

SW_PLAN_STATE_UDD1_KEY

SW_PLAN_STATE_UDD2_KEY

STATE_DURATION

Duration of the Switch Plan State

SWMAN_AUDIT_LOG SAL1.INSERT_TIME

SWMAN_AUDIT_LOG SAL2.INSERT_TIME

USER_KEY

Number of Switch Plan State Changes

CES_USER.USER_NAME

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

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

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

FACT_CNT

Fact Count

Transformation Logic:

This field is populated with the standard value of 1.

DATA_SOURCE_IND

Data Source Indicator

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.

User Defined Dimension Tables

All of the star schemas in Oracle Utilities Analytics product are supplied with user defined dimension tables that are joined to the respective fact. Customers need not make any changes to the physical table schema for any additional requirement. These dimensions help customers to easily extend an existing star schema without an upgrade impact. Customers can make use of these user defined dimensions to pull in data for an additional dimension for a fact.

For more details, see "Chapter 5: Extending Oracle Utilities Analytics" in the Oracle Utilities Analytics Administration Guide.

The table below lists various facts in the Oracle Utilities Network Management System star schemas and the user defined dimensions available for them.

Fact

User Defined Dimension

CF_CITY_OUTG

CD_CITY_OUTG_UDD1

CD_CITY_OUTG_UDD2

CF_CTRL_ZONE_OUTG

CD_CTRL_ZONE_OUTG_UDD1

CD_CTRL_ZONE_OUTG_UDD2

CF_CUST_RECENT_OUTG

CD_CUST_OUTG_UDD1

CD_CUST_OUTG_UDD2

CF_CUST_RST_OUTG

CD_CUST_OUTG_UDD1

CD_CUST_OUTG_UDD2

CF_FEEDER_DLVRD_LOAD

CD_FEEDER_DLVRD_LOAD_UDD1

CD_FEEDER_DLVRD_LOAD_UDD2

CF_OUTG

CD_OUTG_UDD1

CD_OUTG_UDD2

CF_RECENT_CALL

CD_CALL_UDD1

CD_CALL_UDD2

CF_RECENT_CREW

CD_CREW_UDD1

CD_CREW_UDD2

CF_RECENT_JOB

CD_JOB_UDD1

CD_JOB_UDD2

CF_RST_CALL

CD_CALL_UDD1

CD_CALL_UDD2

CF_RST_CREW

CD_CREW_UDD1

CD_CREW_UDD2

CF_RST_JOB

CD_JOB_UDD1

CD_JOB_UDD2

CF_SW_PLAN

CD_SW_PLAN_UDD1

CD_SW_PLAN_UDD2

CF_SW_PLAN_STATE

CD_SW_PLAN_STATE_UDD1

CD_SW_PLAN_STATE_UDD2