Oracle Utilities Extractors and Schema for Oracle Utilities Network Management System

Data Mapping Guide

Release 2.7.0

E83234-01

March 2017(Updated May 2019)

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

E83234-01

Copyright © 2000, 2019, 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, then 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 about 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 unless otherwise set forth in an applicable agreement between you and Oracle. 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, except as set forth in an applicable agreement between you and Oracle.

Preface

Welcome to the Oracle Utilities Extractors and Schema for Oracle Utilities Network Management System Data Mapping Guide.

This guide provides 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 Network Management System.

Documentation Accessibility

For information about configuring and using accessibility features for Oracle Utilities Analytics, see the documentation at http://docs.oracle.com/cd/E23943_01/bi.1111/e10544/appaccess.htm#BIEUG2756.

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/us/corporate/accessibility/index.html .

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For more information, visit: http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Related Documents

For more information, see the following Oracle resources.

Release Notes

•

Oracle Utilities Analytics Release Notes

Administration and Installation Guide

•

Oracle Utilities Analytics Getting Started Guide

•

Oracle Utilities Analytics Quick Installation Guide

•

Oracle Utilities Analytics Installation Guide

•

Oracle Utilities Analytics Administration Guide

•

Oracle Utilities Analytics Developer’s Guide

•

Oracle Utilities Analytics Licensing Information User Manual

Reference Guides

•

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

•

Oracle Utilities Extractors and Schema for Oracle Utilities Meter Data Management Data Mapping Guide

•

Oracle Utilities Extractors and Schema for Oracle Utilities Mobile Workforce Management Data Mapping Guide

•

Oracle Utilities Extractors and Schema for Oracle Utilities Operational Device Management Data Mapping Guide

•

Oracle Utilities Extractors and Schema for Oracle Utilities Work and Asset Management Data Mapping Guide

•

Oracle Utilities Analytics Dashboards for Customer Analytics, Revenue Analytics, and Credit & Collections Analytics Metric Reference Guide

•

Oracle Utilities Analytics Dashboards for Meter Data Analytics Metric Reference Guide

•

Oracle Utilities Analytics Dashboards for Mobile Workforce Analytics Metric Reference Guide

•

Oracle Utilities Analytics Dashboards for Operational Device Analytics Metric Reference Guide

•

Oracle Utilities Analytics Dashboards for Exception Analytics Metric Reference Guide

•

Oracle Utilities Analytics Dashboards for Work and Asset Analytics Metric Reference Guide

•

Oracle Utilities Analytics Dashboards for Distribution Analytics, Outage Analytics Metric Reference 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 data mapping information from the Oracle Utilities Network Management System source system to the Oracle Utilities Extractors and Schema target product, along with the rules of data transformation.

Terminologies

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

<Table Name>

Indicates the name of fact or dimension in a star schema in the data warehouse.

Properties

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

Property

Value

Target Table

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

Table Type

Fact or Dimension

SCD Type

Type 1 - Existing records are updated directly.

Type 2 - Existing records are deactivated and changes are captured by inserting new effective records. The existing records are deactivated by assigning an effective end date of the current date. The new records will have effective start date of the current date and effective end date of a significantly future date.

Fact Type

Whether this is a snapshot or transactional fact table.

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

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

Source System Driver Table

Name of the table in source database on which the data is primarily based.

Oracle Data Integrator Package

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

ETL View

Resides in the replication layer and fetches the base values from the replication tables as a starting point for further transformations to be done by the Oracle Data Integrator interfaces.

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 by separate ETL jobs.

Fields

Lists the individual properties of each field in a fact or dimension. It includes the following columns:

Property

Value

Target Field

Name of the column in a fact or dimension present in the data warehouse. The extracted/transformed data is loaded here.

Description

Description of the target field

Source Field

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

Transformation Logic

Specifies the details of how the data from the source field is transformed before being populated into the target field.

Field Categories

Lists the categories under which fields in a particular fact or dimension are grouped. The categories are as below:

Category

Details

Surrogate Key

The primary key on the fact/dimension table maintained within the data warehouse.

Natural Keys

The set of columns from the source system define the granularity of the fact or dimension table. The natural key columns from the source along with certain other data load attributes can be used to uniquely identify a record in the table.

The additional columns needed for the identification depend on the type of the table as listed below:

•

SCD Type 1 Dimension - Data Source Indicator

•

SCD Type 2 Dimension - Effective Start Date, Effective End Date, Data Source Indicator

•

Accumulation Fact - Data Source Indicator

•

Snapshot Fact - Snapshot Date, Data Source Indicator

Attributes

Hold descriptive data from the source system and are typically available in dimension tables.

Measures

Hold numerical values based data that is calculated from the source system data and are typically available on fact tables.

Degenerate Dimensions

Hold descriptive data from the source system and are typically available in fact tables. These cannot be included into any of the available dimensions.

Foreign Keys

Foreign key references on the fact tables to the dimension tables.

User Defined Attributes

Additional fields available to customers for extending the star schemas. These fields will not be populated out of the box. Customers need to customize the ETL and populate these fields.

Data Load Attributes

Hold the date/time information, job number details, etc, related to the ETL processes, that are needed for audit purposes.

Data Maps for Oracle Utilities Network Management System

This chapter describes data maps for the following Oracle Utilities Extractors and Schema for Oracle Utilities Network Management System tables:

•

Dimensions

•

Shared Dimensions

•

User Defined Dimensions

•

Facts

•

Views

Dimensions

This section lists all the dimension tables that belong to the Oracle Utilities Network Management System star schemas only and are populated only from the Oracle Utilities Network Management System source product.

Account Type

Captures account type details from the source system.

Properties

Property

Value

Target Table

CD_ACCT_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CU_ACCOUNT_TYPE

ODI Package

B1_PKG_CD_ACCT_TYPE

ETL View

B1_D_ACCT_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ACCT_TYPE_KEY

Account Type Dimension

Populated with the B1_ACCT_TYPE_SEQ sequence.

Natural Keys

SRC_ACCT_TYPE_CD

Account Type Code

CU_ACCOUNT_TYPE. ACCTYP_ACCOUNT_TYPE

Attributes

There are no attributes in this dimension.

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

UPDATE_DTTM

Update Date and Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Alarm Log

Captures attributes related to DDS alarm logs from the source system.

Properties

Property

Value

Target Table

CD_ALARM_LOG

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

DDS_ALARM_LOG

ODI Package

B1_PKG_CD_ALARM_LOG

ETL View

B1_D_ALARM_LOG_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ALARM_LOG_KEY

Alarm Log Dimension Surrogate Key

Populated with the sequence from B1_ALARM_LOG_SEQ.

Natural Keys

SRC_ALARM_CLS

Alarm Class

DDS_ALARM_LOG.ALARM_CLS

SRC_ALARM_IDX

Alarm Index

DDS_ALARM_LOG.ALARM_IDX

Attributes

ALARM_TYPE

Alarm Type

DDS_ALARM_LOG.ALARM_TYPE

DESCR

Description

DDS_ALARM_LOG.DESCRIPTION

PRIORITY

Priority

DDS_ALARM_LOG.ALARM_PRIORITY

SOURCE

Source

DDS_ALARM_LOG.SOURCE

PHASE

Phase

DDS_ALARM_LOG.PHASE

CURR_STATE

Current State

DDS_ALARM_LOG.STATE

NOMINAL_STATE

Nominal State

DDS_ALARM_LOG.NOMINAL

DEVICE

Device

DDS_ALARM_LOG.DEVICE

DEVICE_ATTRIBUTE

Device Attribute

DDS_ALARM_LOG.ATTRIBUTE

DEVICE_ATTRIBUTE_UNIT

Device Attribute Unit

DDS_ALARM_LOG.ATTRIBUTE_UNIT

DEVICE_ATTRIBUTE_DESCR

Device Attribute Description

DDS_ALARM_LOG.ATTRIBUTE_DESC

DEVICE_QUALITY

Device Quality

DDS_ALARM_LOG.QUALITY

TERM_STATION

Terminal Station

DDS_ALARM_LOG.TERM_STATION

ACTIVE_IND

Active Indicator

DDS_ALARM_LOG.ACTIVE

ALARM_IND

Alarm Indicator

DDS_ALARM_LOG.IS_ALARM

RTI_ALIAS

RTI Name

DDS_ALARM_LOG.RTI_ALIAS

EXTERNAL_ID

External ID

DDS_ALARM_LOG.EXTERNAL_ID

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Call Info

Extracts the call details (along with the caller information) created in the source system.

Properties

Property

Value

Target Table

CD_CALL_INFO

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

INCIDENTS

ODI Package

B1_PKG_CD_CALL_INFO

ETL View

B1_D_CALL_INFO_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CALL_INFO_KEY

Call Info Dimension Surrogate Key

Populated with the sequence from SPL_CALL_INFO_SEQ.

Natural Keys

SRC_INCIDENT_ID

Call Number

INCIDENTS.NUMB

Attributes

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

ADDRESS

Address

INCIDENTS.ADDRESS

ALTERNATE_PHONE

Alternate Phone

INCIDENTS. ALTERNATE_PHONE

APPT_RANGE

Appointment Range

INCIDENTS. APPT_RANGE

APPT_TYPE

Appointment Type

INCIDENTS. APPT_TYPE

BITMASK

Bit Mask

INCIDENTS. BITMASK

CANCEL_IND

Call Cancelled Indicator

INCIDENTS. CALL_CANCEL

CUSTOMER_TYPE

Customer Type

INCIDENTS. CUSTOMER_TYPE

CALLBACK_REQUEST

Callback Request

INCIDENTS. CALLBACK_REQUEST

CALLBACK_STATUS

Callback Status

INCIDENTS. CALLBACK_STATUS

CALLBACK_LATE_IND

Callback Late Indicator

INCIDENTS. CALLBACK_LATE

CALLBACK_CANC_REASON

Callback Cancel Reason

INCIDENTS. CB_CANCEL_REASON

CALLBACK_TRANSF_IND

Callback Transfer Indicator

INCIDENTS. CB_TRANSFERRED

CALLBACK_USERNAME

Callback Agent Name

INCIDENTS. CB_USERNAME

CLUE_IND

Clue

INCIDENTS. CLUE

EXTERNAL_ID

External Id

INCIDENTS. EXTERNAL_ID

LIFE_SUPPORT_IND

Life Support Indicator

INCIDENTS. LIFE_SUPPORT

MEET_CODE

Meet Code

INCIDENTS. MEET_CODE

MESSAGE_IND

Message Indicator

INCIDENTS. MESSAGES

ORDER_NBR

Order Number

INCIDENTS. ORDER_NUMBER

AFFECTED_PHASES

Affected Phases

INCIDENTS. PHASES

PRIORITY

Priority

INCIDENTS. PRIORITY

TOTAL_PRIORITY

Total Priority

INCIDENTS. TOTALPRIORITY

TROUBLE_LOC

Trouble Location

INCIDENTS. TROUBLE_LOC

TROUBLE_QUEUE

Trouble Queue

INCIDENTS. TROUBLE_QUEUE

USER_NAME

User Name

INCIDENTS. USER_NAME

ACTIVE_IND

Call Active Indicator

INCIDENTS.ACTIVE

COMPLETE_IND

Complete Indicator (Y/N)

INCIDENTS.COMPLETE

DESCRIPTION

Trouble Code Short Description

INCIDENTS.SHORT_DESC

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

TE_STATUSES.JRN_UPDATE_DTTM

TE_STATUS_GROUPS.JRN_UPDATE_DTTM

Populated with the updated timestamp value.

DELETE_IND

INCIDENTS.JRN_FLAG

Populated with "1" for deleted records, and "0" for new/modified records.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

City

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

ODI Package

B1_PKG_CD_CITY

ETL View

B1_D_CITY_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CITY_KEY

City Dimension Surrogate Key

Populated with the sequence from SPL_CITY_SEQ.NEXTVAL.

Natural Keys

There are no natural keys in this dimension.

Attributes

SRC_STATE

State

CU_SERVICE_LOCATIONS.SERV_STATE

SRC_CITY

City

CU_SERVICE_LOCATIONS.SERV_CITY

SRC_COUNTRY

Country

CU_SERVICE_LOCATIONS.SERV_COUNTRY

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

Data Load Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

CU_SERVICE_LOCATIONS.LAST_UPDATE_TIME

Populated with the updated timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Control Zone

Captures the network model structure from the source system via its control zone records and also stores the hierarchy structure between them.

Properties

Property

Value

Target Table

CD_CTRL_ZONE

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CONTROL_ZONE_STRUCTURES

CONTROL_ZONES

ODI Package

B1_PKG_CD_CTRL_ZONE

ETL View

B1_D_CTRL_ZONE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CTRL_ZONE_KEY

Control Zone Dimension Surrogate Key

Populated with the sequence from SPL_CTRL_ZONE_SEQ.NEXT VAL.

Natural Keys

SRC_NCG_ID

CONTROL_ZONES.NCG_ID

CONTROL_ZONE_STRUCTURES.CHILD_NCG_ID

Attributes

HIERARCHY_TYPE

Hierarchy Type

"CIR"

CTRL_ZONE_NAME

Control Zone Name

CONTROL_ZONES.NAME

PARENT_NCG_LVL1_ID

Parent NCG Level 1 ID

CONTROL_ZONES. NCG_ID

Primary hierarchy root NCG

PARENT_NCG_LVL1_NAME

Parent NCG Level 1 Name

CONTROL_ZONES.NAME

Name of primary hierarchy root NCG

PARENT_NCG_LVL2_ID

Parent NCG Level 2 ID

CONTROL_ZONES. NCG_ID

Control zone one-level below the root NCG

PARENT_NCG_LVL2_NAME

Parent NCG Level 2 Name

CONTROL_ZONES.NAME

Name of the control zone one-level below root NCG

PARENT_NCG_LVL3_ID

Parent NCG Level 3 ID

CONTROL_ZONES. NCG_ID

Control zone two-levels below the root NCG

PARENT_NCG_LVL3_NAME

Parent NCG Level 3 Name

CONTROL_ZONES.NAME

Name of the control zone two-levels below root NCG

PARENT_NCG_LVL4_ID

Parent NCG Level 4 ID

CONTROL_ZONES. NCG_ID

Control zone three-levels below the root NCG

PARENT_NCG_LVL4_NAME

Parent NCG Level 4 Name

CONTROL_ZONES.NAME

Name of the control zone three-levels below root NCG

PARENT_NCG_LVL5_ID

Parent NCG Level 5 ID

CONTROL_ZONES. NCG_ID

Control zone four-levels below the root NCG

PARENT_NCG_LVL5_NAME

Parent NCG Level 5 Name

CONTROL_ZONES.NAME

Name of the control zone four-levels below root NCG

PARENT_NCG_LVL6_ID

Parent NCG Level 6 ID

CONTROL_ZONES. NCG_ID

Control zone five-levels below the root NCG

PARENT_NCG_LVL6_NAME

Parent NCG Level 6 Name

CONTROL_ZONES.NAME

Name of the control zone five-levels below root NCG

User Defined Attributes

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 Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Control Zone Secondary

Captures details of the second control zone hierarchy if it is setup in the source system.

Properties

Property

Value

Target Table

CD_CTRL_ZONE_SEC

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CONTROL_ZONE_STRUCTURES

CONTROL_ZONES

ODI Package

B1_PKG_CD_CTRL_ZONE_SEC

ETL View

B1_ D_CTRL_ZONE_SEC_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CTRL_ZONE_SEC_KEY

Control Zone (Secondary) Dimension Surrogate Key

Populated with the sequence from B1_CTRL_ZONE_SEC_SEQ.

Natural Keys

SRC_NCG_ID

Source NCG ID

CONTROL_ZONES.NCG_ID

All leaf level NCG (i.e. NCG's are not parent) and second parent hierarchy NCG's will be populated into this dimension.

If an NCG has more than one parent NCG, then maximum parent NCG from CONTROL_ZONE_STRUCTURES table will be treated as second hierarchy.

Attributes

CTRL_ZONE_NAME

Control Zone Name

CONTROL_ZONES.NAME

PARENT_NCG_LVL1_ID

Parent NCG ID Level 1

CONTROL_ZONES. NCG_ID

Second hierarchy root NCG

PARENT_NCG_LVL1_NAME

Parent NCG Name Level 1

CONTROL_ZONES.NAME

Name of secondary hierarchy root NCG

PARENT_NCG_LVL2_ID

Parent NCG ID Level 2

CONTROL_ZONES. NCG_ID

Control zone one-level below the root NCG

PARENT_NCG_LVL2_NAME

Parent NCG Name Level 2

CONTROL_ZONES.NAME

Name of the control zone one-level below root NCG

PARENT_NCG_LVL3_ID

Parent NCG Level 3 ID

CONTROL_ZONES. NCG_ID

Control zone two-levels below the root NCG

PARENT_NCG_LVL3_NAME

Parent NCG Level 3 Name

CONTROL_ZONES.NAME

Name of the control zone two-levels below root NCG

PARENT_NCG_LVL4_ID

Parent NCG Level 4 ID

CONTROL_ZONES. NCG_ID

Control zone three-levels below the root NCG

PARENT_NCG_LVL4_NAME

Parent NCG Level 4 Name

CONTROL_ZONES.NAME

Name of the control zone three-levels below root NCG

PARENT_NCG_LVL5_ID

Parent NCG Level 5 ID

CONTROL_ZONES. NCG_ID

Control zone four-levels below the root NCG

PARENT_NCG_LVL5_NAME

Parent NCG Level 5 Name

CONTROL_ZONES.NAME

Name of the control zone four-levels below root NCG

PARENT_NCG_LVL6_ID

Parent NCG Level 6 ID

CONTROL_ZONES. NCG_ID

Control zone five-levels below the root NCG

PARENT_NCG_LVL6_NAME

Parent NCG Level 6 Name

CONTROL_ZONES.NAME

Name of the control zone five-levels below root NCG

User Defined Attributes

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 Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Crew

Extracts crew records from both Mobile Workforce Management and 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

ODI Package

B1_PKG_CD_CREW

ETL View

B1_D_CREW_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CREW_KEY

Crew Dimension Surrogate Key

Populated with the sequence from SPL_CREW_SEQ.

Natural Keys

SRC_CREW_ID

Crew ID

CREWS.CREW_ID

Attributes

ACTIVE

Active

CREW_CD

Crew Code

CREWS.CREW_KEY

CREW_DESCR

Crew Description

CREW_TYPES.CREW_NAME

CREW_CENTER

Crew Center

CREW_CONTACT

Crew Contact

CREWS.CONTACT

CREW_SIZE

Crew Size

CREWS.CREW_SIZE

CREW_TYPE_CD

Crew Type Code

CREWS.CREW_TYPE

CREW_TYPE_DESCR

Crew Type Description

CREW_TYPES.CREW_TYPE

MOBILE_NUM

Mobile Number

CREWS.MOBILE_NUM

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

PAGER_NUM

Page Number

CREWS.PAGER_NUM

VEHICLE_ID

Vehicle ID

VEHICLE_NBR

Vehicle Number

VEHICLE_TYPE

Vehicle Type

User Defined Attributes

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 Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Crew Type

Captures crew type details from the source system.

Properties

Property

Value

Target Table

CD_CREW_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CREWS

ODI Package

B1_PKG_CD_CREW_TYPE

ETL View

B1_ D_CREW_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CREW_TYPE_KEY

Crew Type Dimension Surrogate Key

Populated with the sequence from B1_CREW_TYPE_SEQ.

Natural Keys

SRC_CREW_TYPE_ID

Crew Type ID

CREW_TYPES.TYPE_NUM

Attributes

CREW_TYPE

Crew Type

CREW_TYPES.CREW_TYPE

MDT_IND

MDT Type Indicator

CREW_TYPES.MDT_TYPE

SKILL

Skills

CREW_TYPES.SKILLS

User Defined Attributes

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 Attributes

UPDATE_DTTM

Update Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Customers Out

Captures customer size ranges. This is a bucket dimension.

Properties

Property

Value

Target Table

CD_CUST_OUT

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

B1_RANGE_LOOKUP

ODI Package

B1_PKG_CD_CUST_OUT

ETL View

B1_D_CUST_OUT_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CUST_OUT_KEY

Customers Out Dimension Surrogate Key

Populated with the sequence from B1_CUST_OUT_SEQ.

Natural Keys

RANGE_START

Customers Out Start Range

B1_RANGE_LOOKUP.START_RANGE

RANGE_END

Customers Out End Range

B1_RANGE_LOOKUP.END_RANGE

Attributes

CUST_OUT_DESCR

Description

B1_RANGE_LOOKUP.DESCR

User Defined Attributes

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 Attributes

UPDATE_DTTM

Update Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Damage Assessment

Captures various attributes of a damage report as available from the source system.

Properties

Property

Value

Target Table

CD_DAMAGE_ASMT

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

DAMAGE_REPORT

ODI Package

B1_PKG_CD_DAMAGE_ASMT

ETL View

B1_ D_DAMAGE_ASMT_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DAMAGE_ASMT_KEY

Damage Assessment Dimension Surrogate Key

Populated from the B1_DAMAGE_ASMT_SEQ sequence.

Natural Keys

SRC_DAMAGE_ASMT_ID

Damage Assessment ID

DAMAGE_REPORT.REPORT_ID

Attributes

SECTION

Affected Section Type

DAMAGE_REPORT.SECTION

PHASE

Affected Phase

DAMAGE_REPORT.PHASE

ASSESOR

Assessor

DAMAGE_REPORT.CREW_ID

MOBILE_NBR

Mobile Number

DAMAGE_REPORT.RADIO_NUM

ADDRESS

Address

DAMAGE_REPORT.ADDRESS

CITY

City

DAMAGE_REPORT.CITY

LATITUDE

Latitude

DAMAGE_REPORT.LATITUDE

LONGITUDE

Longitude

DAMAGE_REPORT.LONGITUDE

ZONE

Zone

DAMAGE_REPORT.ZONE

GRID

Grid

DAMAGE_REPORT.GRID

HAZARDOUS_IND

Hazardous Indicator

DAMAGE_REPORT.HAZARDOUS

LOAD

Load

DAMAGE_REPORT.LOAD

LOCATION

Location

DAMAGE_REPORT.LOCATION

ROAD_BLOCK_IND

Road Blocked Indicator

DAMAGE_REPORT.ROAD_BLOCKED

STREET_LIGHT_IND

Street Light Indicator

DAMAGE_REPORT.STREET_LIGHT

COMMENT1

Comment1

DAMAGE_REPORT.COMMENT1

COMMENT2

Comment2

DAMAGE_REPORT.COMMENT2

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF1

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF2

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF3

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF4

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF5

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF6

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF7

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF8

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF9

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF10

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF11

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF12

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF13

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF14

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

UDF15

UDF16_CD

User Defined Field 16 Code

UDF16_DESCR

User Defined Field 16 Description

UDF16

UDF17_CD

User Defined Field 17 Code

UDF17_DESCR

User Defined Field 17 Description

UDF17

UDF18_CD

User Defined Field 18 Code

UDF18_DESCR

User Defined Field 18 Description

UDF18

UDF19_CD

User Defined Field 19 Code

UDF19_DESCR

User Defined Field 19 Description

UDF19

UDF20_CD

User Defined Field 20 Code

UDF20_DESCR

User Defined Field 20 Description

UDF20

UDF21_CD

User Defined Field 21 Code

UDF21_DESCR

User Defined Field 21 Description

MISC_TEXT1

UDF22_CD

User Defined Field 22 Code

UDF22_DESCR

User Defined Field 22 Description

MISC_TEXT2

UDF23_CD

User Defined Field 23 Code

UDF23_DESCR

User Defined Field 23 Description

MISC_TEXT3

UDF24_CD

User Defined Field 24 Code

UDF24_DESCR

User Defined Field 24 Description

MISC_TEXT4

UDF25_CD

User Defined Field 25 Code

UDF25_DESCR

User Defined Field 25 Description

MISC_TEXT5

UDF26_CD

User Defined Field 26 Code

UDF26_DESCR

User Defined Field 26 Description

MISC_OPTION1

UDF27_CD

User Defined Field 27 Code

UDF27_DESCR

User Defined Field 27 Description

MISC_OPTION2

UDF28_CD

User Defined Field 28 Code

UDF28_DESCR

User Defined Field 28 Description

MISC_OPTION3

UDF29_CD

User Defined Field 29 Code

UDF29_DESCR

User Defined Field 29 Description

MISC_OPTION4

UDF30_CD

User Defined Field 30 Code

UDF30_DESCR

User Defined Field 30 Description

MISC_OPTION5

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.

VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Damage Assessment Status

Captures possible statuses that a damage report can be in.

Properties

Property

Value

Target Table

CD_DAMAGE_ASMT_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

TE_VALID_STATES

ODI Package

B1_PKG_CD_DAMAGE_ASMT_STATUS

ETL View

B1_ D_DAMAGE_ASMT_STATUS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DAMAGE_ASMT_STATUS_KEY

Damage Assessment Status Dimension Surrogate Key

Populated from the B1_DAMAGE_ASMT_STATUS_SEQ sequence.

Natural Keys

SRC_STATE_KEY

State Key

TE_VALID_STATES.STATE_KEY

Attributes

STATE_NAME

State Name

TE_VALID_STATES.STATE_NAME

STATE_DESCR

State Description

TE_VALID_STATES.DESCRIPTION

ALARM_IND

Alarm

TE_VALID_STATES.ALARM

User Defined Attributes

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 Attributes

UPDATE_DTTM

Update Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Damage Part

Captures the damage parts that can be listed in a damage report.

Properties

Property

Value

Target Table

CD_DAMAGE_PART

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

DAMAGE_PARTS

ODI Package

B1_PKG_CD_DAMAGE_PART

ETL View

B1_ D_DAMAGE_PART_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DAMAGE_PART_KEY

Damage Part Dimension Surrogate Key

Populated from the B1_DAMAGE_PART_SEQ sequence.

Natural Keys

SRC_PART_ID

Damage Part ID

DAMAGE_PARTS.PART_ID

Attributes

PART_CODE

Damage Part Code

DAMAGE_PARTS.CODE

PART_NAME

Damage Part Name

DAMAGE_PARTS.PART_NAME

PART_TYPE

Damage Part Type

DAMAGE_PARTS.TYPE

PART_PARENT

Damage Part Parent

DAMAGE_PARTS.PARENT

User Defined Attributes

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 Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Damage Type

Captures the damage types that a damage report can be associated with.

Properties

Property

Value

Target Table

CD_DAMAGE_TYPE

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

DAMAGE_TYPES

ODI Package

B1_PKG_CD_DAMAGE_TYPE

ETL View

B1_ D_DAMAGE_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DAMAGE_TYPE_KEY

Damage Type Dimension Surrogate Key

Populated with the sequence from B1_DAMAGE_TYPE_SEQ.

Natural Keys

SRC_DAMAGE_TYPE_ID

Damage Type ID

DAMAGE_TYPES.TYPE_ID

Attributes

DAMAGE_TYPE_NAME

Damage Type Name

DAMAGE_TYPES.TYPE_NAME

User Defined Attributes

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 Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Device Dimension

Stores 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

ODI Package

B1_PKG_CD_DEVICE

ETL View

B1_D_DEVICE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DEVICE_KEY

Device Dimension Surrogate Key

Populated with the sequence from SPL_DEVICE_SEQ.NEXT VAL.

Natural Keys

SRC_DEVICE_CLS

Device Class

NETWORK_COMPONENTS.H_CLS

SRC_DEVICE_IDX

Device Index

NETWORK_COMPONENTS.H_IDX

Attributes

DEVICE_NAME

Device Name

NETWORK_COMPONENTS.NAME

DEVICE_TYPE_CD

Device Type Code

CLASSES.C_TYPE

DEVICE_TYPE_DESCR

Device Type Description

CLASSES.C_TYPE

DEVICE_CLASS_CD

Device Class Code

CLASSES.C_NAME

DEVICE_CLASS_DESCR

Device Class Description

CLASSES.C_DESC

ACTIVE_IND

Active Indicator

NETWORK_COMPONENTS.ACTIVE

VOLTAGE

Voltage

NETWORK_COMPONENTS.VOLTAGE

User Defined Attributes

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 Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.

VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Estimated Restoration Duration

Contains the bucket ranges used for grouping the estimated restoration duration.

Properties

Property

Value

Target Table

CD_EST_RST_DUR

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

B1_RANGE_LOOKUP

ODI Package

B1_PKG_CD_EST_RST_DUR

ETL View

B1_D_EST_RST_DUR_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

EST_RST_DUR_KEY

Estimated Restoration Duration Dimension Surrogate Key

Populated with the sequence from B1_EST_RST_DUR_SEQ.NEXT_VAL.

Natural Keys

RANGE_START

Estimated Restoration Duration Start Range

B1_RANGE_LOOKUP.START_RANGE

RANGE_END

Estimated Restoration Duration End Range

B1_RANGE_LOOKUP.END_RANGE

Attributes

EST_RST_DUR_DESCR

Estimated Restoration Duration Description

B1_RANGE_LOOKUP.DESCR

User Defined Attributes

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 Attributes

UPDATE_DTTM

Update Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Event

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

ODI Package

B1_PKG_CD_EVENT

ETL View

B1_D_EVENT_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

EVENT_KEY

Event Dimension Surrogate Key

Populated with the sequence from SPL_EVENT_SEQ.NEXT_VAL.

Natural Keys

SRC_NBR

Number (Natural Key)

JOBS.NUMB

Attributes

EVENT_NBR

Event Number

JOBS.EVENT_IDX

DELETE_IND

Delete Indicator

Used for marking the event as deleted in the source.

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

JOBS.ALARM_STATETE_VALID_STATES.DESCRIPTION

NVL(TE_VALID_STATES.DESCRIPTION, 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

JOBS.ADDR_STREET || ',' || JOBS.ADDR_CITY

REMEDY_CD

Remedy

PICKLIST_INFO_UPD_TR.REMEDY_OM

TROUBLE_CD_LIST

Trouble Code

JOBS.TROUBLE_CODE

SCHEDULED_DEV_OM

Scheduled Device Code

PICKLIST_INFO_UPD_TR.SCHEDULED_DEV_OM

HUMAN_ELEM_OM

Human Element Code

PICKLIST_INFO_UPD_TR.HUMAN_ELEM_OM

OTHER_CAUSE_OM

Other Code

PICKLIST_INFO_UPD_TR.OTHER_CAUSE_OM

FOREIGN_INTERF_OM

Foreign Interference Code

PICKLIST_INFO_UPD_TR.FOREIGN_INTERF_OM

VEGETATION_OM

Vegetation Code

PICKLIST_INFO_UPD_TR.VEGETATION_OM

DEF_EQUIP_OM

Defense Equipment Code

PICKLIST_INFO_UPD_TR.DEF_EQUIP_OM

ADV_ENVIRON_OM

Adverse Environment Code

PICKLIST_INFO_UPD_TR.ADV_ENVIRON_OM

ADV_WEATHER_OM

Adverse Weather Code

PICKLIST_INFO_UPD_TR.ADV_WEATHER_OM

REMEDY_OM

Remedy Code

PICKLIST_INFO_UPD_TR.REMEDY_OM

KEY_EVENT_IDX

Key Event Index

RELATED_OUTAGES.KEY_EVENT_IDX

H_CLS

Device Class

JOBS.H_CLS

H_IDX

Device Index

JOBS.H_IDX

ACTIVE_IND

Active Indicator

JOBS.ACTIVE

CITY

City

JOBS.ADDR_CITY

CALL_TYPE

Call Type

JOBS.CALL_TYPE

DISPLAY_NAME

Display Name

JOBS.DISPLAY_NAME

ESTIMATION_SRC_CD

Estimation Source

JOBS.EST_SOURCE

EVENT_CLS

Event Class

JOBS.EVENT_CLS

EVENT_IDX

Event Index

JOBS.EVENT_IDX

EXT_ID

External ID

JOBS.EXTERNAL_ID

REFRL_GRP

Referral Group

JOBS.REFERRAL_GROUP

RULE_SET

Rule Set

JOBS.RULE_SET

TRBLE_QUEUE

Trouble Queue

JOBS.TROUBLE_QUEUE

COMPLETED_BY

Completed By

JOBS.WHO_COMPLETED

ABN_IND

ABN Flag

PICKLIST_INFO_UPD_TR .ABN_FLAG

ACTION_TXT

Action Text

PICKLIST_INFO_UPD_TR.ACTION_TEXT

CLEAR_IND

Clear Flag

PICKLIST_INFO_UPD_TR.CLEAR_FLAG

CTRL_ZONE_TXT

Control Zone Text

PICKLIST_INFO_UPD_TR.CONTROL_ZONE_TEXT

DEVICE_TXT

Device Text

PICKLIST_INFO_UPD_TR.DEVICE_TEXT

EXCP_IND

Exception Flag

PICKLIST_INFO_UPD_TR.EXCEPTION_FLAG

FAILURE_OM

Failure Cause

PICKLIST_INFO_UPD_TR.FAILURE_OM

FEEDER_TXT

Feeder Text

PICKLIST_INFO_UPD_TR.FEEDER_TEXT

INTERRUPT_DEV_OM

Interruption Dev OM

PICKLIST_INFO_UPD_TR.INTERRUPT_DEV_OM

SYSTEM_OM

System OM

PICKLIST_INFO_UPD_TR.SYSTEM_OM

TYPE_OM

Type OM

PICKLIST_INFO_UPD_TR.TYPE_OM

CAUSE_OM

Cause OM

PICKLIST_INFO_UPD_TR.CAUSE_OM

ESTIMATED_OUT_OM

Estimated Out OM

PICKLIST_INFO_UPD_TR.EST_OUT_OM

PRIMARY_CAUSE_OM

Primary Cause Category

PICKLIST_INFO_UPD_TR .PRIMARY_CAUSE_OM

STATUS_CD

Status

JOBS.STATUS

EVENT_STATE_NAME

Event State Name

TE_VALID_STATES.STATE_NAME

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

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

UDF38_DESCR

User Defined Field 38 Description

UDF39_CD

User Defined Field 39 Code

UDF39_DESCR

User Defined Field 39 Description

UDF40_CD

User Defined Field 40 Code

UDF40_DESCR

User Defined Field 40 Description

SORT_COL_1

Sort Column 1

JOBS.SORT_COL_1

SORT_COL_2

Sort Column 2

JOBS.SORT_COL_2

SORT_COL_3

Sort Column 3

JOBS.SORT_COL_3

SORT_COL_4

Sort Column 4

JOBS.SORT_COL_4

SORT_COL_5

Sort Column 5

JOBS.SORT_COL_5

SORT_COL_6

Sort Column 6

JOBS.SORT_COL_6

SORT_COL_7

Sort Column 7

JOBS.SORT_COL_7

SORT_COL_8

Sort Column 8

JOBS.SORT_COL_8

SORT_COL_9

Sort Column 9

JOBS.SORT_COL_9

Data Load Attributes

UPDATE_DTTM

Update Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Event Status

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

ODI Package

B1_PKG_CD_EVENT_STATUS

ETL View

B1_D_EVENT_STATUS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

EVENT_STATUS_KEY

Event Status Dimension Surrogate Key

Populated with the sequence from SPL_EVENT_STATUS_SEQ.NEXT_VAL

Natural Keys

SRC_STATUS

Status (Natural Key)

TE_STATUSES.TRANS_STATUS

Attributes

EVENT_STATUS_CD

Event Status Code

TE_STATUSES.DESCRIPTION

Populated with "-" if null.

EVENT_STATUS_DESCR

Event Status Description

TE_STATUSES.DESCRIPTION

Populated with "-" if null.

GROUP_CD

Group Code

TE_STATUS_GROUPS. GROUP_NAME

GROUP_DESCR

Group Description

TE_STATUS_GROUPS. DESCRIPTION

User Defined Attributes

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 Attributes

UPDATE_DTTM

Update Date/Time

TE_STATUSES.JRN_UPDATE_DTTM

TE_STATUS_GROUPS.JRN_UPDATE_DTTM

Populated with the updated timestamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Exclusion Detail

Captures exclusion period details of a storm from the source system.

Properties

Property

Value

Target Table

CD_EXCL_DETAIL

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

STORMMAN_STORMS

ODI Package

B1_PKG_CD_EXCL_DETAIL

ETL View

B1_D_ EXCL_DETAIL_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

EXCL_DETAIL_KEY

Excludable Storm Dimension Surrogate Key

Populated from the B1_EXCL_DETAIL_SEQ sequence.

Natural Keys

SRC_NCG_ID

Strom Level NCG

STORMMAN_STORMS.ZONE_IDX

SRC_STORM_NAME

Storm Name

STORMMAN_STORMS.STORM_NAME

Attributes

SRC_STORM_NAME_SFX

Storm Name Suffix

STORMMAN_STORMS.STORM_DATE

EXCL_PERIOD_COMMENTS

Exclusion Period Comments

STORMMAN_STORMS.EX_STORM_COMMENT

User Defined Attributes

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 Attributes

UPDATE_DTTM

Update Date/Time

Populated with the updated timestamp value.

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Feeder

Extracts 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

ODI Package

B1_PKG_CD_FEEDER

ETL View

B1_D_FEEDER_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

FEEDER_KEY

Feeder Duration Dimension Surrogate Key

Populated with the sequence from SPL_FEEDER_SEQ.NEXT_VAL.

Natural Keys

SRC_FEEDER_IDX

Feeder Index

FEEDERS.H_IDX

SRC_FEEDER_CLS

Feeder Class

FEEDERS.H_CLS

Attributes

FEEDER_NAME

Feeder Name

FEEDERS.FEEDER_NAME

FEEDERS.H_CLS

FEEDERS.H_IDX

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

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

FEEDERS.DEATH

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Major Event Snapshot Type

Holds three static values to be used for classifying the type of snapshot data being captured in the City and Control Zone Outage facts.

Note : This dimension holds static data inserted by the initial setup procedure.

Properties

Property

Value

Target Table

CD_MAJ_ EVT _SNAP_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

n/a

ODI Package

n/a

ETL View

n/a

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SNAP_TYPE_CD

Snapshot Type Dimension Surrogate Key

Populated with one of the static values below:

•

BLUE_SKY - If the Begin DTTM falls outside the exclusion period of the STORM (storm does not have any impact).

•

MAJ_EVT - If the Begin DTTM falls within the exclusion period (storm has an impact).

•

ALL - MAJOR EVENT + BLUE SKY

Natural Keys

There are no natural keys in this dimension.

Attributes

SNAP_TYPE_DESCR

Snapshot Type Description

Populated with one of the static values below:

•

BLUE_SKY - If the Begin DTTM falls outside the exclusion period of the STORM (storm does not have any impact).

•

MAJ_EVT - If the Begin DTTM falls within the exclusion period (storm has an impact).

•

ALL - MAJOR EVENT + BLUE SKY

User Defined Attributes

There are no user defined attributes in this dimension.

Data Load Attributes

There are no data load attributes in this dimension.

Meter

Extracts meter ID and meter type details from 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

ODI Package

B1_PKG_CD_METER

ETL View

B1_D_METER_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

METER_KEY

Meter Dimension Surrogate Key

Populated with the sequence from SPL_METER_SEQ.NEXT_VAL.

Natural Keys

SRC_METER_ID

Meter ID

CU_METERS.METER_ID

Attributes

METER_INFO

Meter Number

CU_METERS.METER_NO

METER_TYPE_CD

Meter Type Code

CU_METERS. METER_TYPE

AMR_ENABLED_IND

AMR Enabled Indicator

CU_METERS. METER_AMR_ENABLED

MANUFACTURER_NAME

Manufacturer Name

CU_METERS. METER_MANUFACTURER

METER_NBR

Meter Number

CU_METERS. METER_NO

NBR_PHASES

Number of Phases

CU_METERS. METER_PHASES

RATE_CD

Rate Code

CU_METERS. METER_RATE_CODE

SERIAL_NBR

Serial Number

CU_METERS. METER_SERIAL_NUMBER

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Network Premise

Captures additional information of service locations such as the criticality types based on the priority indicators defined on service locations from the source system.

Properties

Property

Value

Target Table

CD_NETWORK_PREM

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CU_SERVICE_LOCATIONS

ODI Package

B1_PKG_CD_NETWRORK_PREM

ETL View

B1_D_NETWRORK_PREM_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

NETWORK_PREM_KEY

Network Premise Dimension Surrogate Key

Populated from the B1_NETWORK_PREM_SEQ sequence.

Natural Keys

SRC_NETWORK_PREM_ID

Source Network Premise ID

CU_SERVICE_LOCATIONS.SERV_LOC_ID

Attributes

PRIORITY_A_IND

A Priority

CU_SERVICE_LOCATIONS.SERV_A_PRIORITY

PRIORITY_B_IND

B Priority

CU_SERVICE_LOCATIONS.SERV_B_PRIORITY

PRIORITY_C_IND

Critical Priority

CU_SERVICE_LOCATIONS.SERV_C_PRIORITY

PRIORITY_D_IND

Medical Priority

CU_SERVICE_LOCATIONS.SERV_D_PRIORITY

PRIORITY_E_IND

Entertainment Priority

CU_SERVICE_LOCATIONS.SERV_E_PRIORITY

PRIORITY_F_IND

Fire Priority

CU_SERVICE_LOCATIONS.SERV_F_PRIORITY

PRIORITY_G_IND

Government Priority

CU_SERVICE_LOCATIONS.SERV_G_PRIORITY

PRIORITY_H_IND

Hospital Priority

CU_SERVICE_LOCATIONS.SERV_H_PRIORITY

PRIORITY_I_IND

I Priority

CU_SERVICE_LOCATIONS.SERV_I_PRIORITY

PRIORITY_J_IND

J Priority

CU_SERVICE_LOCATIONS.SERV_J_PRIORITY

PRIORITY_K_IND

Key Priority

CU_SERVICE_LOCATIONS.SERV_K_PRIORITY

PRIORITY_L_IND

Life Support Priority

CU_SERVICE_LOCATIONS.SERV_L_PRIORITY

PRIORITY_M_IND

Manufacturing Priority

CU_SERVICE_LOCATIONS.SERV_M_PRIORITY

PRIORITY_N_IND

Nursing Home Priority

CU_SERVICE_LOCATIONS.SERV_N_PRIORITY

PRIORITY_O_IND

O Priority

CU_SERVICE_LOCATIONS.SERV_O_PRIORITY

PRIORITY_P_IND

Police Priority

CU_SERVICE_LOCATIONS.SERV_P_PRIORITY

PRIORITY_Q_IND

Q Priority

CU_SERVICE_LOCATIONS.SERV_Q_PRIORITY

PRIORITY_R_IND

R Priority

CU_SERVICE_LOCATIONS.SERV_R_PRIORITY

PRIORITY_S_IND

Sensitive Priority

CU_SERVICE_LOCATIONS.SERV_S_PRIORITY

PRIORITY_T_IND

Transportation Priority

CU_SERVICE_LOCATIONS.SERV_T_PRIORITY

PRIORITY_U_IND

U Priority

CU_SERVICE_LOCATIONS.SERV_U_PRIORITY

PRIORITY_V_IND

V Priority

CU_SERVICE_LOCATIONS.SERV_V_PRIORITY

PRIORITY_W_IND

W Priority

CU_SERVICE_LOCATIONS.SERV_W_PRIORITY

PRIORITY_X_IND

X Priority

CU_SERVICE_LOCATIONS.SERV_X_PRIORITY

PRIORITY_Y_IND

Y Priority

CU_SERVICE_LOCATIONS.SERV_Y_PRIORITY

PRIORITY_Z_IND

911 Priority

CU_SERVICE_LOCATIONS.SERV_Z_PRIORITY

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Outage Duration Deviation

The Outage Duration Deviation dimension contains bucket ranges used for grouping the deviation between estimated and actual outage duration.

Properties

Property

Value

Target Table

CD_DURATION_DEVIATION

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

B1_RANGE_LOOKUP

ODI Package

B1_PKG_CD_DURATION_DEVIATION

ETL View

B1_D_DURATION_DEVIATION_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

DURATION_DEVIATION_KEY

Estimated VS Actual Duration Dimension Surrogate Key

Populated with the sequence from B1_DURATION_DEVIATION_SEQ.NEXT_VAL.

Natural Keys

RANGE_START

Duration Deviation Start Range

B1_RANGE_LOOKUP.START_RANGE

RANGE_END

Outage Duration Deviation End Range

B1_RANGE_LOOKUP.END_RANGE

Attributes

DURATION_DEVIATION_DESCR

Outage Duration Deviation Description

B1_RANGE_LOOKUP.DESCR

User Defined Attributes

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 Attributes

UPDATE_DTTM

Update Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Phase

Extracts phase data from the Network Management System source 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

ODI Package

B1_PKG_CD_PHASE

ETL View

B1_D_PHASE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

PHASE_KEY

Phase Dimension Surrogate Key

Populated with the sequence from SPL_PHASE_SEQ.NEXT_VAL.

Natural Keys

SRC_PHASE_ID

Phase ID

PHASE_BITMAP_LOOKUP.PHASE_BITS

Attributes

PHASE_CD

PHASE_BITMAP_LOOKUP.PHASE_STRING

PHASE_DESCR

Phase

PHASE_BITMAP_LOOKUP.PHASE_STRING

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Storm

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

ODI Package

B1_PKG_CD_STORM

ETL View

B1_D_STORM_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

STORM_KEY

Storm Dimension Surrogate Key

Populated with the sequence from SPL_STORM_SEQ.

Natural Keys

SRC_STORM_NAME

Storm Name

STORMMAN_STORMS.STORM_NAME

SRC_STORM_NAME_SFX

Storm Name Suffix

STORMMAN_STORMS.STORM_DATE

Attributes

STORM_TYPE_CD

Storm Type Code

STORMMAN_STORMS.STORM_TYPE

STORM_TYPE_DESCR

Storm Type Description

STORMMAN_STORM_TYPES.STORM_TYPE_NAME

STORM_LEVEL_CD

Storm Level Code

STORMMAN_STORMS.STORM_LEVEL

STORM_LEVEL_DESCR

Storm Level Description

STORMMAN_STORMS.STORM_LEVEL

STORM_START_DTTM

Date/Time the Storm Started

STORMMAN_STORMS.STORM_START

STORM_END_DTTM

Date/Time the Storm Ended

STORMMAN_STORMS.STORM_END

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Storm Outage Type

Extracts the storm outage type data from 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

ODI Package

B1_PKG_CD_STORM_OUTAGE_TYPE

ETL View

B1_D_STORM_OUTAGE_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Dimension Surrogate Key

Populated with the sequence from SPL_STORM_OUTAGE_TYPE_SEQ.NEXT_VAL.

Natural Keys

SRC_OUTAGE_TYPE

Outage Type

STORMMAN_OUTAGE_TYPES.OUTAGE_TYPE

Attributes

OUTAGE_TYPE_CD

Outage Type Code

STORMMAN_OUTAGE_TYPES.OUTAGE_TYPE

OUTAGE_TYPE_DESCR

Outage Type Description

STORMMAN_OUTAGE_TYPES.OUTAGE_TYPE_NAME

If null, this is populated with "-".

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Supply Node Lookup

Extracts the distinct service point (premise, person, and meter) information, along with the device they are attached to.

Note : This dimension is no longer referenced as part of any fact ETL. It is used only for reports.

Properties

Property

Value

Target Table

CD_SNL

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CES_CUSTOMERS_HISTORY

ODI Package

B1_PKG_CD_SNL

ETL View

B1_D_SNL_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SNL_KEY

Supply Node Lookup Dimension Surrogate Key

Populated with the sequence from SPL_SNL_SEQ.NEXT_VAL.

Natural Keys

SRC_METER_ID

Meter ID (Natural Key)

CES_CUSTOMERS_HISTORY.METER_ID

SRC_PERSON_ID

Person ID (Natural Key)

CES_CUSTOMERS_HISTORY.CUST_ID

SRC_PREMISE_ID

Premise ID (Natural Key)

CES_CUSTOMERS_HISTORY.SERV_LOC_ID

SRC_ACCOUNT_ID

Account ID

CES_CUSTOMERS_HISTORY.ACCOUNT_NUMBER

SRC_ADDR_ID

Address

CES_CUSTOMERS_HISTORY.SERV_LOC_ID

Attributes

DEVICE_ID

Device ID

CES_CUSTOMERS_HISTORY.NEW_DEVICE_ID

FEEDER_ID

Feeder ID

CES_CUSTOMERS_HISTORY.FEEDER_ID

NCG_ID

NCG ID

CES_CUSTOMERS_HISTORY.NEW_NCG

DEVICE_CLS

Device Class

CES_CUSTOMERS_HISTORY.NEW_H_CLS

DEVICE_IDX

Device Index

CES_CUSTOMERS_HISTORY.NEW_H_IDX

User Defined Attributes

There are no user defined attributes in this dimension.

Data Load Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

CES_CUSTOMERS_HISTORY.BIRTH

EFF_END_DTTM

Effective End Date/Time

CES_CUSTOMERS_HISTORY.DEATH

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Switch Plan

Extracts switch plan data from the 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

ODI Package

B1_PKG_CD_SW_PLAN

ETL View

B1_D_SW_PLAN_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SW_PLAN_KEY

Switch Plan Dimension Surrogate Key

Populated with the sequence from SPL_SW_PLAN_SEQ.

Natural Keys

SRC_SW_PLAN_CLS

Switch Plan Class

SWMAN_SHEET.SWITCH_SHEET_CLS

SRC_SW_PLAN_IDX

Switch Plan Index

SWMAN_SHEET.SWITCH_SHEET_IDX

Attributes

CHARGE_NBR1

Charge Number 1

SWMAN_SHEET.CHARGENUMBER1

CHARGE_NBR2

Charge Number 2

SWMAN_SHEET.CHARGENUMBER2

CHARGE_NBR3

Charge Number 3

SWMAN_SHEET.CHARGENUMBER3

DESCR

Description

SWMAN_SHEET.DESCRIPTION

DEVICE_ALIAS

Device Alias

SWMAN_SHEET. DEVICE_ALIAS

INS_USER

Inserted User

SWMAN_SHEET.INSERT_USER

LOCATION

Location

SWMAN_SHEET.LOCATION

OFFICE

Office

SWMAN_SHEET.OFFICE

REQUESTED_CUST_NAME

Requested Customer Name

SWMAN_SHEET.CUSTOMERREQUESTEDNAME

REQUESTED_CUST_PHONE_NBR

Requested Customer Phone Number

SWMAN_SHEET.CUSTOMERREQUESTEDPHONENUMBER

SW_SHEET_TYPE

Switch Sheet Type

SWMAN_SHEET_CLS. SWITCH_SHEET_TYPE

SW_SHEET_TYPE_DESCR

Switch Sheet Type Description

SWMAN_SHEET_CLS.DESCRIPTION

OWNER

Owner

SWMAN_SHEET.OWNER

CREW_WILL_SWITCH_IND

Crew Will Switch Indicator

SWMAN_SHEET. CREWWILLSWITCH

CREW_WILL_RST_IND

Crew Will Restore Indicator

SWMAN_SHEET.CREWWILLRESTORE

GROUNDS_REQUIRED_IND

Grounds Required Indicator

SWMAN_SHEET.GROUNDSREQUIRED

SHEET_ID

Sheet ID

SWMAN_SHEET.SEQ_SHEET_ID

VERIFY_IND

Verify Indicator

SWMAN_SHEET.MB_EDIT

VOLTAGE

Voltage

SWMAN_SHEET.VOLTAGE

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

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

UDF38_DESCR

User Defined Field 38 Description

UDF39_CD

User Defined Field 39 Code

UDF39_DESCR

User Defined Field 39 Description

UDF40_CD

User Defined Field 40 Code

UDF40_DESCR

User Defined Field 40 Description

UDF41_CD

User Defined Field 41 Code

UDF41_DESCR

User Defined Field 41 Description

UDF42_CD

User Defined Field 42 Code

UDF42_DESCR

User Defined Field 42 Description

UDF43_CD

User Defined Field 43 Code

UDF43_DESCR

User Defined Field 43 Description

UDF44_CD

User Defined Field 44 Code

UDF44_DESCR

User Defined Field 44 Description

UDF45_CD

User Defined Field 45 Code

UDF45_DESCR

User Defined Field 45 Description

UDF46_CD

User Defined Field 46 Code

UDF46_DESCR

User Defined Field 46 Description

UDF47_CD

User Defined Field 47 Code

UDF47_DESCR

User Defined Field 47 Description

UDF48_CD

User Defined Field 48 Code

UDF48_DESCR

User Defined Field 48 Description

UDF49_CD

User Defined Field 49 Code

UDF49_DESCR

User Defined Field 49 Description

UDF50_CD

User Defined Field 50 Code

UDF50_DESCR

User Defined Field 50 Description

Data Load Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Switch Plan Jobs Relation

Holds the relation between the switch plan and jobs in the Network Management System. This entity acts as a bridge table that provides the relation between switch plan and jobs, as well as jobs and switch plan.

Note that this dimension is not referenced in any of the facts.

Properties

Property

Value

Target Table

CD_SWP_JOBS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

JOBS

SWITCH_SHEET_OUTAGE_EVENTS

ODI Package

ETL View

B1_ D_SWP_JOBS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SWP_JOBS_KEY

Switch Plan Jobs Dimension Surrogate Key

Populated with the sequence from B1_SWP_JOBS_SEQ.

Natural Keys

SWITCH_SHEET_CLS

Switch Sheet CLS

JOBS.SWSHEET_CLS

SWITCH_SHEET_OUTAGE_EVENTS.SWITCH_SHEET_CLS

SWITCH_SHEET_IDX

Switch Sheet IDX

JOBS.SWSHEET_IDX

SWITCH_SHEET_OUTAGE_EVENTS.SWITCH_SHEET_IDX

Attributes

ASSOC_TYPE

Association Type

ASSOC_NAME

Association Name

User Defined Attributes

There are no user defined attributes in this table.

Data Load Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

TE_VALID_STATES.UPDATE_DTTM

Populated with the updated timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Switch Plan State

Extracts switch plan data from the 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

ODI Package

B1_PKG_CD_SW_PLAN_STATE

ETL View

B1_D_SW_PLAN_STATE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SW_PLAN_STATE_KEY

Switch Plan State Dimension Surrogate Key

Populated with the sequence from SPL_SW_PLAN_STATE_SEQ.NEXT_VAL.

Natural Keys

SRC_STATE_KEY

Switch Plan State Key

TE_VALID_STATES.STATE_KEY

Attributes

STATE_CD

Switch Plan State Code

TE_VALID_STATES.STATE_NAME

STATE_DESCR

Switch Plan State

TE_VALID_STATES.DESCRIPTION

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

UPDATE_DTTM

Update Date/Time

TE_VALID_STATES.UPDATE_DTTM

Populated with the updated timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Shared Dimensions

Shared dimensions are used and populated in the star schemas of other source products as well. The data source indicator (DSI) column is used to identify which records are owned by which source product.

Account

Contains the account data from service locations in the source system.

Properties

Property

Value

Target Table

CD_ACCT

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CU_SERVICE_LOCATIONS

ODI Package

B1_PKG_CD_ACCT

ETL View

B1_D_ACCT_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ACCT_KEY

Account Dimension

Populated with the SPL_ACCT_SEQ.NEXTVAL sequence.

Natural Keys

SRC_ACCT_ID

Account ID

CU_SERVICE_LOCATIONS.SERV_ACCOUNT_NUMBER

Attributes

ACCT_INFO

Account Information

CU_SERVICE_LOCATIONS.SERV_LOC_ID

REVENUE_CLASS_CD

Service Revenue Class Code

CU_SERVICE_LOCATIONS.SERV_REVENUE_CLASS

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

Data Load Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Address

Retrieves data from service locations in the source system.

Properties

Property

Value

Target Table

CD_ADDR

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

CU_SERVICE_LOCATIONS

ODI Package

B1_PKG_CD_ADDR

ETL View

B1_D_ADDR_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ADDR_KEY

Address Dimension Surrogate Key

Populated with the sequence from SPL_ADDR_SEQ.NEXTVAL.

Natural Keys

SRC_ADDR_ID

Premise ID

CU_SERVICE_LOCATIONS.SERV_LOC_ID

Attributes

ADDR_INFO

Address

Concatenated string of SERV_ADDR_1, SERV_CITY, SERV_STATE, and SERV_POSTCODE_1, separated by a comma.

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

APARTMENT_NBR

Apartment Number

CU_SERVICE_LOCATIONS.SERV_APT

LOT_NBR

Lot Number

CU_SERVICE_LOCATIONS.SERV_LOT

BLOCK

Block

CU_SERVICE_LOCATIONS.SERV_STR_BLOCK

BUILDING_ADDR

Building Address

CU_SERVICE_LOCATIONS.SERV_STR_STRUC

STREET_NAME

Street Name

CU_SERVICE_LOCATIONS.SERV_STR_NAME

STREET_PREFIX

Street Prefix

CU_SERVICE_LOCATIONS.SERV_STR_PFIX

STREET_SUFFIX

Street Suffix

CU_SERVICE_LOCATIONS.SERV_STR_SFIX

CDL_DIRECTION

Cardinal Direction

CU_SERVICE_LOCATIONS.SERV_STR_CDL_DIR

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

UDF16_CD

User Defined Field 16 Code

UDF16_DESCR

User Defined Field 16 Description

Data Load Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Person

Extracts person data from the source 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

ODI Package

B1_PKG_CD_PER

ETL View

B1_D_PER_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

PER_KEY

Person Dimension Surrogate Key

Populated with the sequence from SPL_PER_SEQ.NEXT_VAL.

Natural Keys

SRC_PER_ID

Person ID (Natural Key)

CU_CUSTOMERS.CUST_ID

Attributes

BUSINESS_IND

Business Indicator Flag

PER_INFO

Person Info String

CU_CUSTOMERS.CUST_NAME

PER_NAME

Person Name

CU_CUSTOMERS.CUST_NAME

PER_PHONE_NBR

Person Phone Number

CU_CUSTOMERS.CUST_HOME_PHONE

CU_CUSTOMERS.CUST_HOME_AC

Populated with phone number in the (XXX) XXX-XXXX format.

For example: (001) 775-0125)

DAY_PHONE_NBR

Day Phone Number

CU_CUSTOMERS.CUST_DAY_AC

CU_CUSTOMERS.CUST_DAY_PHONE

CU_CUSTOMERS. CUST_DAY_PHONE_EX

Populated with phone number in the (YYY) YYY-YYYYxY format.

For example: (330) 201-4858x1)

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

CU_CUSTOMERS.LAST_UPDATE_TIME

EFF_END_DTTM

Effective End Date/Time

CU_CUSTOMERS.LAST_UPDATE_TIME

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Premise

Extracts premise information from 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

ODI Package

B1_PKG_CD_PREM

ETL View

B1_D_PREM_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

PREM_KEY

Premise Dimension Surrogate Key

Populated with the sequence from SPL_PREM_SEQ.NEX T_VAL.

Natural Keys

SRC_PREM_ID

Premise ID

CU_SERVICE_LOCATIONS.SERV_LOC_ID

Attributes

PREM_INFO

Premise Info String

CU_SERVICE_LOCATIONS.SERV_LOC_ID

SERVICE_PRIORITY_LVL

Service Priority Code

CU_SERVICE_LOCATIONS.SERV_PRIORITY

SERVICE_STATUS_CD

Service Status Code

CU_SERVICE_LOCATIONS. SERV_STATUS

COMMENTS

Comments

CU_SERVICE_LOCATIONS. SERV_COMMENT

MAP_LOC_X

Map Location X

CU_SERVICE_LOCATIONS. SERV_MAP_LOC_X

MAP_LOC_Y

Map Location Y

CU_SERVICE_LOCATIONS. SERV_MAP_LOC_Y

SERVICE_TYPE_CD

Service Type Code

CU_SERVICE_LOCATIONS. SERV_TYPE

LIFE_SUPPORT_CD

Life Support Code

CU_SERVICE_LOCATIONS.SERV_LIFE_SUPPORT

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

User

Extracts the details of all users from the 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

ODI Package

B1_PKG_CD_USER

ETL View

B1_D_USER_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

USER_KEY

User Dimension Surrogate Key

Populated with the sequence from SPL_USER_SEQ.NEXT_VAL.

Natural Keys

There are no natural keys in this dimension.

Attributes

USER_CD

User Code (Natural Key)

CES_USER.USER_NAME

USER_DESCR

User Description

CES_USER.FULL_NAME

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

User Defined Attributes

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 Attributes

DATA_LOAD_DTTM

Data Load Date/Time

Populated with the load timestamp value.

EFF_START_DTTM

Effective Start Date/Time

CES_USER.LAST_UPDATE_TIME

EFF_END_DTTM

Effective End Date/Time

CES_USER.LAST_UPDATE_TIME

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

User Defined Dimensions

A user defined dimension (UDD) is a means of extension for an existing fact. These dimensions have a foreign key reference to a fact; however no out-of-box ETL is delivered. Customers can define an ODI ETL process to populate these dimensions with the source data as per their requirement. Each fact is provided with two such user defined dimensions. For details about extending using UDDs, see the Oracle Utilities Analytics Developer’s Guide.

All user defined dimensions have the same structure. The only technical difference is the column name of the primary key, which follows the pattern of using the fact for which it is linked to.

Below is the structure of the Fields table which is common to all user defined dimensions.

Fields

Target Field

Description

Surrogate Key

<Fact_name_prefix>_UDDn_KEY

For example: The surrogate key for Asset Location UDD is ASSET_LOC_UDD1_KEY

<Fact name> User Defined Dimension n Dimension Surrogate Key

For example: Asset Location User Defined Dimension 1 Dimension Surrogate Key

Natural Keys

UDD1_CD

User Defined Dimension 1 Code

Attributes

UDD1_DESCR

User Defined Dimension 1 Description

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDL1_CD

User Defined Field Long 1 Code

UDL1_DESCR

User Defined Field Long 1 Description

UDL2_CD

User Defined Field Long 2 Code

UDL2_DESCR

User Defined Field Long 2 Description

UDL3_CD

User Defined Field Long 3 Code

UDL3_DESCR

User Defined Field Long 3 Description

UDL4_CD

User Defined Field Long 4 Code

UDL4_DESCR

User Defined Field Long 4 Description

UDL5_CD

User Defined Field Long 5 Code

UDL5_DESCR

User Defined Field Long 5 Description

COMMENT1

Comment 1

COMMENT2

Comment 2

COMMENT3

Comment 3

COMMENT4

Comment 4

COMMENT5

Comment 5

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

DATA_SOURCE_IND

Data Source Indicator

JOB_NBR

Job Number

Abnormal Device Log UDD1

Property

Value

Target Table

CD_ABNORMAL_DEVICE_LOG_UDD1

Table Type

Dimension

SCD Type

Type 2

Abnormal Device Log UDD2

Property

Value

Target Table

CD_ABNORMAL_DEVICE_LOG_UDD2

Table Type

Dimension

SCD Type

Type 2

Call UDD1

Property

Value

Target Table

CD_CALL_UDD1

Table Type

Dimension

SCD Type

Type 2

Call UDD2

Property

Value

Target Table

CD_CALL_UDD2

Table Type

Dimension

SCD Type

Type 2

City Outage UDD1

Property

Value

Target Table

CD_CITY_OUTG_UDD1

Table Type

Dimension

SCD Type

Type 2

City Outage UDD2

Property

Value

Target Table

CD_CITY_OUTG_UDD2

Table Type

Dimension

SCD Type

Type 2

Control Zone Outage UDD1

Property

Value

Target Table

CD_CTRL_ZONE_OUTG_UDD1

Table Type

Dimension

SCD Type

Type 2

Control Zone Outage UDD2

Property

Value

Target Table

CD_CTRL_ZONE_OUTG_UDD2

Table Type

Dimension

SCD Type

Type 2

Crew UDD1

Property

Value

Target Table

CD_CREW_UDD1

Table Type

Dimension

SCD Type

Type 2

Crew UDD2

Property

Value

Target Table

CD_CREW_UDD2

Table Type

Dimension

SCD Type

Type 2

Crew Activity UDD1

Property

Value

Target Table

CD_CREW_ACTIVITY_UDD1

Table Type

Dimension

SCD Type

Type 2

Crew Activity UDD2

Property

Value

Target Table

CD_CREW_ACTIVITY_UDD2

Table Type

Dimension

SCD Type

Type 2

Customer Outage UDD1

Property

Value

Target Table

CD_CUST_OUTG_UDD1

Table Type

Dimension

SCD Type

Type 2

Customer Outage UDD2

Property

Value

Target Table

CD_CUST_OUTG_UDD2

Table Type

Dimension

SCD Type

Type 2

Customer Recent Outage UDD1

Property

Value

Target Table

CD_CUST_OUTG_UDD1

Table Type

Dimension

SCD Type

Type 2

Customer Recent Outage UDD2

Property

Value

Target Table

CD_CUST_OUTG_UDD2

Table Type

Dimension

SCD Type

Type 2

Damage Assessment UDD1

Property

Value

Target Table

CD_DAMAGE_ASMT_UDD1

Table Type

Dimension

SCD Type

Type 2

Damage Assessment UDD2

Property

Value

Target Table

CD_DAMAGE_ASMT_UDD2

Table Type

Dimension

SCD Type

Type 2

Damage Assessment Detail UDD1

Property

Value

Target Table

CD_DAMAGE_ASMT_DETAIL_UDD1

Table Type

Dimension

SCD Type

Type 2

Damage Assessment Detail UDD2

Property

Value

Target Table

CD_DAMAGE_ASMT_DETAIL_UDD2

Table Type

Dimension

SCD Type

Type 2

Job UDD1

Property

Value

Target Table

CD_JOB_UDD1

Table Type

Dimension

SCD Type

Type 2

Job UDD1

Property

Value

Target Table

CD_JOB_UDD2

Table Type

Dimension

SCD Type

Type 2

Outage UDD1

Property

Value

Target Table

CD_OUTG_UDD1

Table Type

Dimension

SCD Type

Type 2

Outage UDD2

Property

Value

Target Table

CD_OUTG_UDD2

Table Type

Dimension

SCD Type

Type 2

Recent Call UDD1

Property

Value

Target Table

CD_CALL_UDD1

Table Type

Dimension

SCD Type

Type 2

Recent Call UDD2

Property

Value

Target Table

CD_CALL_UDD2

Table Type

Dimension

SCD Type

Type 2

Recent Crew UDD1

Property

Value

Target Table

CD_CREW_UDD1

Table Type

Dimension

SCD Type

Type 2

Recent Crew UDD2

Property

Value

Target Table

CD_CREW_UDD2

Table Type

Dimension

SCD Type

Type 2

Recent Job UDD1

Property

Value

Target Table

CD_JOB_UDD1

Table Type

Dimension

SCD Type

Type 2

Recent Job UDD2

Property

Value

Target Table

CD_JOB_UDD2

Table Type

Dimension

SCD Type

Type 2

Storm UDD1

Property

Value

Target Table

CD_STORM_UDD1

Table Type

Dimension

SCD Type

Type 2

Storm UDD2

Property

Value

Target Table

CD_STORM_UDD2

Table Type

Dimension

SCD Type

Type 2

Switch Plan UDD1

Property

Value

Target Table

CD_SW_PLAN_UDD1

Table Type

Dimension

SCD Type

Type 2

Switch Plan UDD2

Property

Value

Target Table

CD_SW_PLAN_UDD2

Table Type

Dimension

SCD Type

Type 2

Switch Plan State UDD1

Property

Value

Target Table

CD_SW_PLAN_STATE_UDD1

Table Type

Dimension

SCD Type

Type 2

Switch Plan State UDD2

Property

Value

Target Table

CD_SW_PLAN_STATE_UDD2

Table Type

Dimension

SCD Type

Type 2

Facts

Abnormal Device Log

Captures details of devices that go into an abnormal state and end up with an alarm log in the source system.

Note : This fact is designed to cater to various KPI requirements about abnormal devices in the Switching dashboard in Oracle Utilities Analytics.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_ABNORMAL_DEVICE_LOG

Table Type

Fact

Fact Type

Accumulation

Driver Table

DDS_ALARM_LOG

ODI Package Name

B1_PKG_CF_ABNORMAL_DEVICE_LOG

ETL View Name

B1_F_ABNORMAL_DEVICE_LOG_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

ABNORMAL_DEVICE_LOG_KEY

Abnormal Device Log Surrogate Key

Populated with the sequence from B1_ABNORMAL_DEVICE_LOG_SEQ.NEXTVAL.

Natural Keys

SRC_ALARM_CLS

Alarm Message Class

DDS_ALARM_LOG.ALARM_CLS

SRC_ALARM_IDX

Alarm Message Index

DDS_ALARM_LOG.ALARM_IDX

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

Degenerate Dimensions

ABNORMAL_DTTM

Abnormal Date/Time

DDS_ALARM_LOG.USER_TIME

ACK_DTTM

Acknowledgment Date/Time

DDS_ALARM_LOG.TIME_ACK

DEACT_DTTM

Deactivation Date/Time

DDS_ALARM_LOG.WHEN_DEACTIVATED

SYSTEM_TIME

System Date/Time

DDS_ALARM_LOG.SYSTEM_TIME

Foreign Keys

ABNORMAL_DATE_KEY

Abnormal Date Key

DDS_ALARM_LOG.USER_TIME

Populated based on the Date part of ABNORMAL_DTTM.

ABNORMAL_TIME_KEY

Abnormal Time Key

DDS_ALARM_LOG.USER_TIME

Populated based on the Time of ABNORMAL_DTTM.

ACK_DATE_KEY

Acknowledgment Date Key

DDS_ALARM_LOG.TIME_ACK

Populated based on the Date part of ACK_DTTM.

ACK_TIME_KEY

Acknowledgment Time Key

DDS_ALARM_LOG.TIME_ACK

Populated based on the Time part of ACK_DTTM.

DEACT _DATE_KEY

Deactivation Date Key

DDS_ALARM_LOG. WHEN_DEACTIVATED

Populated based on the Date part of DEACT_DTTM.

DEACT _TIME_KEY

Deactivation Time Key

DDS_ALARM_LOG. WHEN_DEACTIVATED

Populated based on the Time part of DEACT_DTTM.

ALARM_LOG_KEY

Alarm Log Key

DDS_ALARM_LOG.ALARM_CLS

DDS_ALARM_LOG.ALARM_IDX

Populated based on ALARM_CLS and ALARM_IDX from DDS_ALARM_LOG table. If any (either alarm class or index) of the values is 0, then map to the zeroth key.

DEVICE_KEY

Device Dimension Surrogate Key

DDS_ALARM_LOG.DEVICE_CLS

DDS_ALARM_LOG.DEVICE_IDX

Populated based on DEVICE_CLS and DEVICE_IDX from DDS_ALARM_LOG table.

If any (either device class or index) of the values is 0, then map to the zeroth key.

In case of non-electrical devices, devices will not be available in the Device dimension. Facts referring to these devices will be populated with 0 instead of -99.

CTRL_ZONE_KEY

Control Zone Dimension Surrogate Key

DDS_ALARM_LOG.NCG

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

CTRL_ZONE_SEC_KEY

Control Zone Sec Dimension Surrogate Key

DDS_ALARM_LOG.NCG

Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table.

ABNORMAL_DEVICE_LOG_UDD1_KEY

Abnormal Device Log User Defined Dimension 1 Surrogate Key

ABNORMAL_DEVICE_LOG_UDD2_KEY

Abnormal Device Log User Defined Dimension 2 Surrogate Key

SYSTEM_DATE_KEY

System Date Key

DDS_ALARM_LOG.SYSTEM_TIME

Populated based on the SYSTEM_DTTM part from the CD_DATE table.

SYSTEM_TIME_KEY

System Time Key

DDS_ALARM_LOG.SYSTEM_TIME

Populated based on the SYSTEM_DTTM part from the CD_TIME table.

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Call

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 Name

INCIDENTS

ODI Package Name

B1_PKG_CF_RST_CALL

ETL View Name

B1_F_RECENT_CALL_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

RST_CALL_KEY

Restored Call Fact Surrogate Key

Populated with the sequence from SPL_RST_CALL_SEQ.

Natural Keys

SRC_INCIDENT_ID

Source Incident ID

INCIDENTS.DECODE(NVL(ASSOCIATE_IDX, 0), 0,NU=MB,ASSOCIATE_IDX)

Populated with NUMB if the Associate Index value is 0. Else, Associated Index value is populated.

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

Degenerate Dimensions

PRIORITY_IND

Priority Indicator

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

Populate from the extracted nth character of the COMPLAINT column of INCIDENTS table, where ‘n’ is defined by the configuration parameter B1_PRTY_BIT. If the extracted value is 1 or 2, populate as 1, else populate as 0.

RST_IND

Restored Indicator

JOBS.RESTORE_TIME

If RESTORE_TIME is populated in the JOBS table, then this field is populated as 1; else as 0.

Foreign Keys

EVENT_KEY

Event Key

JOBS.NUMB

Populated from CD_EVENT's surrogate key using NUMB column JOBS table.

ACCT_KEY

Account Key

CES_CUSTOMERS_HISTORY.ACCOUNT_NUMBER

Populated based on the account in the CD_ACCOUNT dimension.

CALL_DATE_KEY

Call Date Key

INCIDENTS.INPUT_TIME

Populated based on the date part in the CD_DATE dimension.

CALL_TIME_KEY

Call Time Key

INCIDENTS.INPUT_TIME

Populated based on the Time part in the CD_TIME dimension.

CTRL_ZONE_KEY

Control Zone Key

INCIDENTS.NCG

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

USER_KEY

User Key

CES_USER.USER_NAME

Populated with user name in the CD_USER table.

CALL_INFO_KEY

Call Info Key

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

If the Associate Index value is 0, this field is populated with NUMB. Else, it is populated with Associated Index value.

PREM_KEY

Premise Key

CES_CUSTOMERS_HISTORY.SERV_LOC_ID

Populated with the service location ID in the CD_PREM table.

ADDR_KEY

Address Key

CES_CUSTOMERS_HISTORY.SERV_LOC_ID

Populated with the service location ID in the CD_PREM table.

DEVICE_KEY

Device Key

INCIDENTS.H_IDXINCIDENTS.H_CLS

Populated based on H_IDX and H_CLS in the CD_DEVICE table.

ACCT_TYPE_KEY

Account Type Key

CES_CUSTOMERS_HISTORY.ACCOUNT_TYPE

Populated with the account type.

METER_KEY

Meter Key

CES_CUSTOMERS_HISTORY.METER_ID

Populated with the meter ID in the CD_METER table.

NETWORK_PREMISE_KEY

Network Premise Key

CES_CUSTOMERS_HISTORY.SERV_LOC_ID

Populated with the service location ID in the CD_NETWORK_PREM table.

CTRL_ZONE_SEC_KEY

Control Zone Secondary Key

INCIDENTS.NCG

Populate from CD_CTRL_ZONE_SEC dimension's surrogate key based on NCG ID and records effective date/time is between effective start and end date/time of dimension.

STORM_KEY

Storm Key

STORMMAN_STORMS.STORM_NAME STORMMAN_STORMS.STORM_DATE

Populated based on the storm name and date.

CALL_UDD1_KEY

User Defined Dimension 1 Key

CALL_UDD2_KEY

User Defined Dimension 2 Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

City Outage

A monthly snapshot fact that stores various measures (such as CMI, SAIDI, SAIFI, number of customers, number of interruptions, etc) at the city and major event type 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 Customer restored facts.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CITY_OUTG

Table Type

Fact

Fact Type

Snapshot

Driver Table

CD_CITY

ODI Package Name

B1_PKG_CF_CITY_OUTG

ETL View Name

n/a

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

CITY_OUTG_KEY

City Outage Snapshot Fact Key

Populated with the sequence from SPL_CITY_OUTG_SEQ.

Natural Keys

SRC_CITY

City

CD_CITY.SRC_CITY

Loaded with city information from the City dimension that is active during the slice period.

SRC_STATE

State

CD_CITY.SRC_STATE

Loaded with state information from the City dimension that is active during the slice period.

SRC_COUNTRY

Country

CD_CITY.SRC_COUNTRY

Loaded with country information from the City dimension that is active during the slice period.

SNAP_TYPE_CD

Snapshot Type Code

B1_DSI_CONFIG_VW.SNAP_TYPE_CD

Populated with snapshot type from DSI config view based on the configuration of the snapshot defined in the target entity table.

MAJ_EVT_SNAP_TYPE_CD

Major Event Snapshot Type Code

CD_MJR_EVT_SNAP_TYPE.SNAP_TYPE_CD

Populated with all combinations of control zone.

Measures

NUM_CUST_SERVED

Number of Customers Served

CES_CUSTOMERS_HISTORY.NUM_CUST_SERVED

Populated with the number of active customers served within the City during the snapshot period.

NUM_SUST_INTRPT

Number of Sustained Interruptions

CF_CUST_RST_OUTG.NUM_SUST_INTRPT

Populated with the count of sustained outages during the snapshot period.An outage is considered sustained if the difference between the beginning and ending time of the outage (in minutes) is greater than the configured momentary threshold in minutes.

NUM_MOM_INTRPT

Numb. Mom. Interruptions

CF_CUST_RST_OUTG.NUM_MOM_INTRPT

Populated with the count of momentary outages during the snapshot period.An outage is considered momentary if the difference between the beginning and ending time of the outage (in minutes) is less than or equal to the configured momentary threshold in minutes.

CMI

CMI

CF_CUST_RST_OUTG.CMI

Populated with the sum of customer minutes interrupted (in minutes) for sustained outages.

NUM_MULT_SUST_INTRPT

Number of Multiple Sustained Interruptions

CF_CUST_RST_OUTG.NUM_MULT_SUST_INTRPT

Populated with the count of sustained outages greater than the configured CEMI count.

NUM_MULT_CUST_INTRPT

Number of Multiple Customer Interruptions

CF_CUST_RST_OUTG.NUM_MULT_CUST_INTRPT

Populated with the count of sustained and momentary outages greater than the configured CEMI count.

SAIDI

SAIDI

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Populated with the customer minutes interrupted by number of customers served.

CAIDI

CAIDI

CF_CUST_RST_OUTG.CAIDI

Populated with the customer minutes interrupted divided by the number of customers interrupted.

SAIFI

SAIFI

CF_CUST_RST_OUTG.SAIFI

Populated with the number of customer interruptions divided by the number of customers served.

MAIFIE

MAIFIE

CF_CUST_RST_OUTG.MAIFIE

Populated with the momentary customer interruptions divided by the number of customers served. Note: This metric is for momentary events - if there were multiple reclose attempts to clear a fault, it only counts as a single event.

NUM_CUST_INTRPT

Customers Interrupted

CF_CUST_RST_OUTG.NUM_CUST_INTRPT

Populated with the number of sustained customer interruptions. Note: If a customer has had multiple sustained interruptions, each one is counted.

NUM_MOM_E_INTRPT

Number of Momentary Events Preceding a Sustained Interruption

CF_CUST_RST_OUTG.NUM_MOM_E_INTRPT

Populated with the number of momentary events during the period, excluding the events that immediately precede a sustained interruption.

NUM_EVENT

Number of Events

CF_CUST_RST_OUTG.NUM_EVENT

Populated with the number of events during the period. It is the sum of number of sustained interruptions and number of momentary interruptions.

NUM_CUST_SUST_INTRPT

Number of Customers who experienced a Sustained Interruption

CF_CUST_RST_OUTG.NUM_CUST_SUST_INTRPT

Populated with the number of customers who experienced a sustained interruption during the period.

FACT_CNT

Count

Populated with the standard value of ‘1’.

Degenerate Dimensions

SNAPSHOT_DT

Snapshot Date

B1_JOB_EXEC.SLICE_END_DTTM

Consider one second less than the slice end date (<date>23:59:59).

Foreign Keys

SNAPSHOT_DATE_KEY

Snapshot Date Surrogate Key

B1_JOB_EXEC.SLICE_END_DTTM

Populated with the last date of current snapshot period (populates only the date part).

CITY_KEY

City Surrogate Key

CD_CITY.CITY_KEY

Populated based on the customers active during the snapshot period.

BEGIN_DATE_KEY

Snapshot Begin Date Surrogate Key

B1_JOB_EXEC.SLICE_START_DTTM

Populated based on the slice start date from the B1_JOB_EXEC table.

END_DATE_KEY

Snapshot End Date Surrogate Key

B1_JOB_EXEC.SLICE_END_DTTM

Populated based on the difference between slice end date and one second from the B1_JOB_EXEC table.

CITY_OUTG_UDD1_KEY

City outage User Defined Dimension 1 Surrogate Key

CITY_OUTG_UDD2_KEY

City outage User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Control Zone Outage

A monthly snapshot fact that stores various measures at the control zone where the customer is operational, and major event type 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 Customer restored facts.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CTRL_ZONE_OUTG

Table Type

Fact

Fact Type

Accumulation

Driver Table

CD_CTRL_ZONE

ODI Package Name

B1_PKG_CF_CTRL_ZONE_OUTG

ETL View Name

n/a

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

CTRL_ZONE_OUTG_KEY

Control Zone Outage Snapshot Fact Key

Populated with the sequence from SPL_CTRL_ZONE_OUTG_SEQ.

Natural Keys

SRC_NCG_ID

NCG ID

CD_CTRL_ZONE.NCG_ID

Loaded with control zone details from Control Zone dimension that are active during the slice period.

Measures

NUM_CUST_SERVED

Number of Customers Served

CES_CUSTOMERS_HISTORY

Populated with the number of active customers served in the control zone during the snapshot period.

1.

Joins with CES_CUSTOMERS_HISTORY on NCG and the BIRTH and DEATH effective for that snapshot period.

2.

Counts the number of records.

NUM_SUST_EVENT

Number of Sustained Events

Populated with the count of events that are momentary within the statistics period.

A sustained event can be identified when the difference between begin and end time of the outage (outage duration in minutes) is greater than the configured number of momentary minutes.

NUM_SUST_INTRPT

Number of Sustained Interruptions

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Populated with the count of sustained outages during the period. An outage is considered sustained if the difference between the beginning and ending time of the outage (in minutes) is greater than the configured number of momentary minutes.

NUM_MOM_EVENT

Number of Momentary Event

Populated with the count of momentary events during the period.

A momentary event can be identified when the difference between begin and end time of the outage (the outage duration in minutes) is less than the configured number of momentary minutes.

NUM_MOM_INTRPT

Numb. Mom. Interruptions

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Populated with the count of momentary outages during the snapshot period. An outage is considered momentary if the difference between the beginning and ending time of the outage (in minutes) is less than or equal to the configured number of momentary minutes.

CMI

CMI

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Populated with the sum of customer minutes interrupted (in minutes) for sustained outages.

NUM_MULT_SUST_INTRPT

Number of Multiple Sustained Interruptions

CF_CUST_RST_OUTG.PER.SRC_PER_ID

CF_CUST_RST_OUTG.PREM.SRC_PREM_ID

CF_CUST_RST_OUTG.METER.SRC_METER_ID

Populated with the count of sustained outages that are greater than the configured CEMI count.

NUM_MULT_CUST_INTRPT

Number of Multiple Customer Interruptions

CF_CUST_RST_OUTG.PER.SRC_PER_ID

CF_CUST_RST_OUTG.PREM.SRC_PREM_ID

CF_CUST_RST_OUTG.METER.SRC_METER_ID

Populated with the count of sustained and momentary outages that are greater than the configured CEMI count.

SAIDI

SAIDI

CF_CUST_RST_OUTG.BEGIN_DTTM

CF_CUST_RST_OUTG.RST_DTTM

Populated with the customer minutes interrupted by number of customers served.

CAIDI

CAIDI

Populated with the number of customer minutes interrupted divided by the number of customers interrupted.

SAIFI

SAIFI

Populated with the number of customers interrupted divided by the number of customers served.

MAIFIE

MAIFIE

Populated with the number of momentary customer interruption events divided by the number of customers served.

NUM_CUST_INTRPT

Customers Interrupted

Populated with the sum of customer interruptions during the period. If a customer experiences multiple interruptions, each should be counted.

NUM_MOM_E_INTRPT

Number of Momentary Events

Populated with the number of momentary events during the period, excluding the events that immediately precede a sustained interruption.

NUM_EVENT

Number of Events

Populated with the number of events during the period.

NUM_CUST_SUST_INTRPT

Number of Customers who experienced a Sustained Interruption

Populated with the count of customers who experienced more than P_CEMI_COUNT sustained interruptions during the period.

FACT_CNT

Count

Populated with the standard value of ‘1’.

Degenerate Dimensions

STATISTICS_DTTM

Statistics Date/Time

Populated with job processing date end time in the DD-MON-YYYY HH24:MI:SS format.

Foreign Keys

CTRL_ZONE_KEY

Control Zone Surrogate Key

CD_CTRL_ZONE.CTRL_ZONE_KEY

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

CTRL_ZONE_SEC_KEY

Control Zone Secondary Surrogate Key

CD_CTRL_ZONE_SEC.CTRL_ZONE_SEC_KEY

Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table.

BEGIN_DATE_KEY

Snapshot Begin Date Surrogate Key

B1_JOB_EXEC.SLICE_START_DTTM

Populated based on the slice start date from the B1_JOB_EXEC table.

END_DATE_KEY

Snapshot End Date Surrogate Key

B1_JOB_EXEC.SLICE_END_DTTM

Populated based on the difference between slice end date and one second from the B1_JOB_EXEC table.

CTRL_ZONE_OUTG_UDD1_KEY

Control Zone outage User Defined Dimension 1 Surrogate Key

CITY_OUTG_UDD2_KEY

City outage User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Crew

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.

This 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 Name

CREW_ASSIGNMENTS

CREW_DISPATCHES

STORMMAN_STORMS

ODI Package Name

B1_PKG_CF_RST_CREW

ETL View Name

B1_F_RST_CREW_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

RST_CREW_KEY

Recent Crew Key

Populated with the sequence from SPL_RECENT_CREW_SEQ.

Natural Keys

SRC_CREW_ID

Source Crew ID

CREW_ASSIGNMENTS.CREW_IDCREW_DISPATCHES.CREW_ID

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

INROUTE_DURATION

Inroute Duration

CREW_DISPATCHES.WHEN_HAPPENED

Populated with the total time spent by the crew in ‘Dispatch’ state for a particular event.

WORK_DURATION

Work Duration

CREW_DISPATCHES.WHEN_HAPPENED

Populated with the total time spent by the crew in ‘Arrive’ state for a particular event.

ASSIGN_DURATION

Assign Duration

CREW_ASSIGNMENT.WHEN_HAPPENED

Populated with the total time spent by the crew in ‘Assign’ state for a particular event.

DISPATCH_DURATION

Dispatch Duration

CREW_DISPATCHES.WHEN_HAPPENEDCREW_ASSIGNMENTS.WHEN_HAPPENED

Populated with the total time spent by the crew while assigned, en route and working on the event. Dispatch Duration = En Route Duration + Work Duration + Assign Duration

Degenerate Dimensions

SRC_EVENT_NBR

Source Event Number

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

If the Associate Index value is 0, populate this field with NUMB. Else, populate Associated Index value.

EXCL_PERIOD_START_DTTM

Exclusion Period End Date/Time

STORMMAN_STORMS. EX_STORM_START

If a storm is present and it has an exclusion period defined within which the begin date/time of the job falls, then populate this field with the exclusion period start date/time.

EXCL_PERIOD_END_DTTM

Exclusion Period Start Date/Time

STORMMAN_STORMS. EX_STORM_END

If a storm is present and it has an exclusion period defined within which the begin date/time of the job falls, then populate this field with the exclusion period end date/time.

Foreign Keys

EVENT_KEY

Event Key

JOBS.NUMB

Populated from CD_EVENT’s surrogate key using NUMB column in the JOBS table.

ASSIGN_DATE_KEY

Last Assign Date Key

CREW_ASSIGNMENTS.WHEN_HAPPENED

Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest assignment date of the crew. In case of no assignments for the crew, populate as 0.

ASSIGN_TIME_KEY

Last Assign Time Key

CREW_ASSIGNMENTS.WHEN_HAPPENED

Populate with the surrogate key of the CD_TIME dimension by looking up with the time part of the latest assignment date of the crew. In case of no assignments for the crew, populate as 0.

CTRL_ZONE_KEY

Control Zone Key

JOBS.NCG

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

ASSIGN_USER_KEY

Last Assign User Key

CREW_ASSIGNMENTS.CES_USER

Populated with the last user who assigned the crew.

CREW_KEY

Crew Key

CREW_ASSIGNMENTS.CREW_IDCREW_DISPATCHES.CREW_ID

Populated with the crew ID in the CD_CREW dimension.

DEVICE_KEY

Device Key

JOBS.H_IDXJOBS.H_CLS

Populated based on H_IDX and H_CLS in the CD_DEVICE table.

UNASSIGN_DATE_KEY

Unassign Date Key

CREW_ASSIGNMENTS.WHEN_HAPPENED

Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest un-assignment date of the crew. In case of no un-assignments for the crew, populate as 0.

UNASSIGN_TIME_KEY

Unassign Time Key

CREW_ASSIGNMENTS.WHEN_HAPPENED

Populate with the surrogate key of the CD_TIME dimension by looking up with the time part of the latest un-assignment date of the crew. In case of no un-assignments for the crew, populate as 0.

UNASSIGN_USER_KEY

Unassign User Key

CREW_ASSIGNMENTS.CES_USER

Look up the CD_USER dimension with the last user who unassigned the crew and populate with surrogate key.

ACCEPT_DATE_KEY

Unassign Date Key

CREW_DISPATCHES.WHEN_HAPPENED

Populated based on the date part of the latest dispatch date of the crew. In case of no dispatches for the crew, populate as 0.

ACCEPT_TIME_KEY

Accept Time Key

CREW_DISPATCHES.WHEN_HAPPENED

Populated based on the time part of the latest dispatch date of the crew and lookup the CD_TIME dimension. In case of no dispatches for the crew, populate as 0.

ACCEPT_USER_KEY

Accept User Key

CREW_DISPATCHES.CES_USER

Populate with the last user who dispatched the crew, by looking up the CD_USER dimension.

ARRIVE_DATE_KEY

Arrive Date Key

CREW_DISPATCHES.WHEN_HAPPENED

Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest arrival date of the crew. In case of no completions for the crew, populate as 0.

ARRIVE_TIME_KEY

Arrive Time Key

CREW_DISPATCHES.WHEN_HAPPENED

Populated based on the time part of the latest arrival date of the crew and lookup the CD_TIME dimension. In case of no completions for the crew, populate as 0.

ARRIVE_USER_KEY

Arrive User Key

CREW_DISPATCHES.CES_USER

Populate with the last user who marked the crew activity as arrived, by looking up the CD_USER dimension.

CMPL_DATE_KEY

Completed Date Key

CREW_DISPATCHES.WHEN_HAPPENED

Extract the date part of the latest completion date of the crew and lookup the CD_DATE dimension. In case of no completions for the crew, populate as 0.

CMPL_TIME_KEY

Completed Time Key

CREW_DISPATCHES.WHEN_HAPPENED

Extract the time part of the latest completion date of the crew and lookup the CD_TIME dimension. In case of no completions for the crew, populate as 0.

CMPL_USER_KEY

Completed User Key

CREW_DISPATCHES.CES_USER

Populate with the last user who marked the crew activity as completed, by looking up the CD_USER dimension.

STORM_KEY

Storm Key

STORMMAN_STORMS.STORM_NAMESTORMMAN_STORMS.STORM_DATE

Populated based on the storm name and start date of the storm.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

Populated based on the storm outage type in the Jobs table.

CTRL_ZONE_SEC_KEY

Control Zone Key

JOBS.NCG

Populated from CD_CTRL_ZONE_SEC dimension's surrogate key based on NCG ID and records effective date/time is between effective start and end date/time of dimension.

EXCL_DETAIL_KEY

Excludable Detail Key

STORMMAN_STORMS.ZONE_IDXSTORMMAN _STORMS. STORM_NAMESTORMMAN _STORMS. STORM_DATE

Populated based on the storm level NCG, storm name, and storm name suffix from the CD_EXCL_DETAIL table.

EXCL_START_DATE_KEY

Exclusion Period Start Date Key

STORMMAN_STORMS. EX_STORM_START

Populated based on the date part in the CD_DATE table.

EXCL _END_DATE_KEY

Exclusion Period End Date Key

STORMMAN_STORMS. EX_STORM_END

Populated based on the date part from the CD_DATE dimension.

EXCL_START_TIME_KEY

Exclusion Period Start Time Key

STORMMAN_STORMS. EX_STORM_START

Populated based on the time part from the CD_TIME dimension.

EXCL _END_TIME_KEY

Exclusion Period End Time Key

STORMMAN_STORMS. EX_STORM_END

Populated based on the time part from the CD_TIME dimension.

CREW_UDD1_KEY

User Defined Dimension 1 Key

CREW_UDD2_KEY

User Defined Dimension 2 Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Crew Activity

Captures the complete history of all the crews that worked on an event, including multiple assignments of the same crew to the same event.

Note : The data for this fact is captured from both the driver tables (CREW_ASSIGNMENTS and CREW_DISPATCHES) in a mutually exclusive manner. All columns of the table are separately populated from each of these driver tables.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CREW_ACTIVITY

Table Type

Fact

Fact Type

Accumulation

Driver Table

CREW_ASSIGNMENTS

CREW_DISPATCHES

STORMMAN_STORMS

ODI Package Name

B1_PKG_CF_CREW_ACTIVITY

ETL View Name

B1_F_CREW_ACTIVITY_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

CREW_ACTIVITY_KEY

Crew Activity Fact Surrogate Key

Populated with the sequence from B1_CREW_ACTIVITY_SEQ.NEXTVAL.

Natural Keys

SRC_IND

Source Indicator

Populated based on the table the data is being loaded from.

•

‘A’ if the source table is CREW_ASSIGNMENTS

•

‘D’ if the source is CREW_DISPATCHES

SEQ_NUM

Sequence Number

CREW_ASSIGNMENTS.SEQ_NUM

CREW_DISPATCHES.SEQ_NUM

ACTIVE_FLG

Active Flag

CREW_ASSIGNMENTS.ACTIVE

CREW_DISPATCHES.ACTIVE

ACTION_CD

Action Code

CREW_ASSIGNMENTS.ACTION

CREW_DISPATCHES.ACTION

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

Degenerate Dimensions

ACTION_DTTM

Action Date/Time

CREW_ASSIGNMENTS.WHEN_HAPPENED

CREW_DISPATCHES. WHEN_HAPPENED

Foreign Keys

ACTION_DATE_KEY

Action Date Key

CREW_ASSIGNMENTS.WHEN_HAPPENED

CREW_DISPATCHES. WHEN_HAPPENED

Populated based on the date part of ACTION_DTTM.

ACTION _TIME_KEY

Action Time Key

CREW_ASSIGNMENTS.WHEN_HAPPENED

CREW_DISPATCHES. WHEN_HAPPENED

Populated based on the time of ACTION_DTTM.

CREW_KEY

Crew Key

CREW_ASSIGNMENTS.CREW_ID

CREW_DISPATCHES.CREW_ID

Populated based on the CREW_ID from the CREW_ASSIGNMENTS/CREW_DISPATCHES.

EVENT_KEY

Event Key

JOBS.NUMB

Populated based on NUMB from the JOBS table.

CTRL_ZONE_ KEY

Control Zone Dimension Surrogate Key

JOBS.NCG

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

CTRL_ZONE_SEC_KEY

Control Zone Sec Dimension Surrogate Key

JOBS.NCG

Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table.

STORM_KEY

Storm Dimension Surrogate Key

STORMMAN_STORMS.STORM_NAME

STORMMAN_STORMS.STORM_DATE

Populated based on the storm name and date.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Dimension Surrogate Key

JOBS.STORMMAN_TYPE

Populated based on jobs STORMMAN_TYP.

DEVICE_KEY

Device Key

JOBS.H_IDX

JOBS.H_CLS

Populated based on H_IDX and H_CLS from JOBS table.If any (either device class or index) value is 0, then map to the zeroth key. In case of non-electrical devices, devices are not available in Device dimension. Facts referring to these devices will be populated with 0 instead of -99.

ASSIGN_USER_KEY

Assigned User Key

CREW_ASSIGNMENTS.CES_USER

Populated based on CES_USER from CREW_ASSIGNMENTS.

CREW_ACTIVITY_UDD1_KEY

Crew Activity User Defined Dimension 1 Surrogate Key

CREW_ACTIVITY_UDD2_KEY

Crew Activity User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Customer Outage

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 Name

SUPPLY_NODE_LOGJOBSSTORMMAN_STORMS

ODI Package Name

B1_PKG_CF_CUST_RST_OUTG

ETL View Name

B1_F_CUST_RECENT_OUTG_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

CUST_RST_OUTG_KEY

Customer Restored Outage Fact Surrogate Key

Populated with the sequence from SPL_CUST_RST_OUTG_SEQ.

Natural Keys

SRC_ID

Source Outage ID

SUPPLY_NODE_LOG.ID

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

OUTG_DURATION

Outage Duration

SUPPLY_NODE_LOG.OUTAGE_TIME

SUPPLY_NODE_LOG.RESTORE_TIME

Populated with the difference between restore time and outage time.

NUM_MOMENTARY

Number of Momentaries Covered

JOBS.MOMENTARIES

Populated with the number of momentaries covered.

CMI

Customer Minutes Interrupted

SUPPLY_NODE_LOG.OUTAGE_TIME

SUPPLY_NODE_LOG.RESTORE_TIME

Populated with difference between restored time and outage time, in minutes.

EST_RST_DUR

Estimated Restoration Duration

JOBS.BEGIN_TIME

JOBS.EST_REST_TIME

Populated with the difference between estimated restore time and begin time, in minutes.

DURATION_DEVIATION

Duration Deviation

JOBS.RESTORE_TIME

JOBS.EST_REST_TIME

Populated with the difference between restore time and estimated restore time, in minutes.

Degenerate Dimensions

BEGIN_DTTM

Begin Date Time

SUPPLY_NODE_LOG.OUTAGE_TIME

RST_DTTM

Restored Date Time

SUPPLY_NODE_LOG.RESTORE_TIME

RST_IND

Restored Indicator

SUPPLY_NODE_LOG.RESTORE_TIME

Populated based on JOBS.RESTORE_TIME.If the restored date/time exists, then this field is populated with 1. Else, with 0.

PLANNED_IND

Planned Indicator

SWMAN_SHEET.SHEET_CATEGORY_NAME

Populated based on SHEET_CATEGORY_NAME.If the sheet category is planned, then this field is populated with 1. Else, 0.

EXCLUDE_IND

Exclude Indicator

JOBS J.STATE_VALUE

PICKLIST_INFO_UPD_TR P.NO_DTR_FLAG

SWMAN_SHEET_CATEGORYSSCA.SHEET_CATEGORY_NAME

Populated based on STATE_VALUE '1024, NO_DTR_FLAG 'Y' and SHEET_CATEGORY_NAME 'Planned.

OMS_EXCLUDE_IND

OMS Exclude Indicator

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

Populated based on NO_DTR_FLAG. If it is set to 'Y', then this field is populated with 1. Else, with 0.

CANCELLED_IND

Cancelled Indicator

JOBS.STATE_VALUE

Populated based on STATE_VALUE.if set to 1024, then the indicator is populated with 1. Else, with 0.

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

Foreign Keys

EVENT_KEY

Event Key

JOBS.NUMB

Populated from CD_EVENT’s surrogate key using NUMB column in the JOBS table.

EVENT_STATUS_KEY

Event Status Key

JOBS.STATUS

Populated based on the jobs status.

ACCT_KEY

Account Key

CD_ACCT.SRC_ACCT_ID

INCIDENTS.ACCOUNT_NUMCES_CUSTOMERS.ACCOUNT_NUMBER

Populated based on the account ID retrieved from the CES_CUSTOMER_HISTORY table.

PREM_KEY

Premise Key

CD_PREM.SRC_PREM_ID

Populated based on the service location retrieved from CES_CUSTOMER_HISTORY.

PER_KEY

Person Key

CD_PER.SRC_PER_ID

Populated based on the CUST_ID retrieved from CES_CUSTOMER_HISTORY.

METER_KEY

Meter Key

CD_METER.SRC_METER_ID

Populated based on METER_ID retrieved from CES_CUSTOMER_HISTORY.

ADDR_KEY

Address Key

CD_ADDR.SRC_ADDR_ID

Populated based on service location retrieved from CES_CUSTOMER_HISTORY.

BEGIN_DATE_KEY

Begin Date Key

SUPPLY_NODE_LOG.OUTAGE_TIME

Populated based on the outage begin date.

BEGIN_TIME_KEY

Begin Time Key

SUPPLY_NODE_LOG.OUTAGE_TIME

Populated based on the outage begin time.

RST_DATE_KEY

Restored Time Key

SUPPLY_NODE_LOG.RESTORE_TIME

Populated based on the restored date.

RST_TIME_KEY

Restored Date Key

SUPPLY_NODE_LOG.RESTORE_TIME

Populated based on the restored time.

EST_RST_TIME_KEY

Estimated Restored Time Key

JOBS.EST_REST_TIME

Populate based on the estimated restored date.

EST_RST_DATE_KEY

Estimated Restored Date Key

JOBS.EST_REST_TIME

Populate based on the estimated restored time.

CTRL_ZONE_KEY

Control Zone Key

SUPPLY_NODE_LOG.NCG

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

CAUSE_CTRL_ZONE_KEY

Cause Control Zone Key

JOBS.NCG

Populated based on jobs NCG.

AFF_DEVICE_KEY

AFF Device Key

SUPPLY_NODES.DEVICE_IDX

SUPPLY_NODES.DEVICE_CLS

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

CAUSE_DEVICE_KEY

Cause Device Key

JOBS.H_IDX

JOBS.H_CLS

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

FEEDER_KEY

Feeder Key

SUPPLY_NODES.FEEDER

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

SW_PLAN_KEY

Switch Plan Key

JOBS.SWSHEET_IDX

JOBS.SWSHEET_CLS

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

STORM_KEY

Storm Key

STORMMAN_STORMS.STORM_NAME

STORMMAN_STORMS.STORM_DATE

Populated with the storm name and date.

STORM_OUTG_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

Populated based on jobs STORMMAN_TYPE.

PARENT_EVENT_KEY

Parent Event Key

PARENT_EVENT.NUMB

Populated based on PARENT_EVENT.NUMB.

OUTG_DUR_KEY

Outage Duration Key

SUPPLY_NODE_LOG.OUTAGE_TIME

SUPPLY_NODE_LOG.RESTORE_TIME

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

Populated with the difference between estimated restoration time and begin time.

DURATION_DEVIATION_KEY

Duration Deviation Key

JOBS.RESTORE_TIME

JOBS.EST_REST_TIME

Populated with the difference between restoration time and estimated restore time.

CREW_KEY

CREW_VW.CREW_ID

Lookup in CD_CREW and populate the surrogate key.

CUST_OUTG_UDD1_KEY

User Defined Dimension 1 Key

CUST_OUTG_UDD2_KEY

User Defined Dimension 2 Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Damage Assessment

Captures information about the damage assessment (such as what parts are damaged).

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_DAMAGE_ASMT

Table Type

Fact

Fact Type

Accumulation

Driver Table

DAMAGE_REPORT

STROMMAN_STORMS

ODI Package Name

B1_PKG_CF_DAMAGE_ASMT

ETL View Name

B1_F_DAMAGE_ASMT_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

DAMAGE_ASMT_F_KEY

Damage Assessment Fact Surrogate Key

Populated with the sequence from B1_DAMAGE_ASMT_F_SEQ.

Natural Keys

SRC_DAMAGE_ASMT_ID

Damage Assessment ID

DAMAGE_REPORT.REPORT_ID

Populated with the primary key of the Damage Assessment Report.

Measures

EST_REPAIR_DUR

Estimated Repair Duration

DAMAGE_REPORT.REPAIR_MINUTES

Populated with the estimated repair duration.

FACT_CNT

Count

Populated with the standard value of ‘1’.

Degenerate Dimensions

REPORT_DTTM

Reported Date/Time

DAMAGE_REPORT.REPORT_TIME

Populated with the reported date/time of the Damage Assessment Report.

Foreign Keys

REPORT_DATE_KEY

Reported Date Key

CD_DATE.DATE_KEY

Populated based on the date part of REPORT_DTTM.

REPORT_TIME_KEY

Reported Time Key

CD_TIME.TIME_KEY

Populated based on the time part of REPORT_DTTM.

DAMAGE_ASMT_KEY

Damage Assessment Dimension Surrogate Key

DAMAGE_REPORT.REPORT_ID

Populated based on the Damage Report ID.

DAMAGE_ASMT_STATUS_KEY

Damage Assessment Status Dimension Surrogate Key

DAMAGE_REPORT.STATE_KEY

Populated based on the Damage Report State Key Value.

DEVICE_KEY

Device Dimension Surrogate Key

JOBS.H_CLS

JOBS.H_IDX

Populated based on Jobs Device Class and Index.

If the Damage Report is mapped to a non-electrical device, this field will be populated with 0.

EVENT_KEY

Event Dimension Surrogate Key

JOBS.NUMB

Populated based on the Jobs Number.

Jobs number is retrieved from event class and index from the incident based on the Damage Report incident number.

EVENT_STATUS_KEY

Event Status Dimension Surrogate Key

JOBS.STATUS

Populated based on the Jobs Status.

CTRL_ZONE_ KEY

Control Zone Dimension Surrogate Key

INCIDENTS.NCG

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

CTRL_ZONE_SEC_KEY

Control Zone Sec Dimension Surrogate Key

INCIDENTS.NCG

Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table.

STORM_KEY

Storm Dimension Surrogate Key

STORMMAN_STORMS. STORM_NAME

STORMMAN_STORMS.STORM_DATE

Populated based on the storm name and date. Storm will affect zones at higher level. Damage Report mapped to NCG falls within the same zone and jobs begin time within the storm period will be populated with storm key.

DAMAGE_ASMT_DETAIL_UDD1_KEY

Damage Assessment Detail User Defined Dimension 1 Surrogate Key

DAMAGE_ASMT_DETAIL_UDD2_KEY

Damage Assessment Detail User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Damage Assessment Detail

Populated with a combination of the various components involved in a damage report, namely outage parts, damage types, and crew types.

Note : For a single damage report, there will be a mutually exclusive fact record for reach of its outage parts, damage types, and crew types. The fact records are loaded into this fact with the combination below:

1.Damage Assessment and Damage Part

2.Damage Assessment and Damage Type

3.Damage Assessment and Crew Type

The record for the damage part only contains that damage part information, and does not include information for damage types and crew types. Similarly, a record for the damage type only contains that damage type information, and not information for damage parts and crew types. A record for the crew type only includes information for that crew type, and not for damage parts or damage types.

Measures are also populated according to the combination mentioned above. For example: Crew count is populated for those whose Crew Type ID is not null. Otherwise, it will be set to 0.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_DAMAGE_ASMT_DETAIL

Table Type

Fact

Fact Type

Accumulation

Driver Table

DAMAGE_REPORT

DAMAGE_OUTAGE_PARTS

DAMAGE_OUTAGE_TYPES

DAMAGE_CREW_TYPES

STORMMAN_STORMS

ODI Package Name

B1_PKG_CF_DAMAGE_ASMT_DETAIL

ETL View Name

B1_F_DAMAGE_ASMT_DETAIL _VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

DAMAGE_ASMT_DETAIL_KEY

Damage Assessment Detail Fact Surrogate Key

Populated with the sequence from B1_DAMAGE_ASMT_DETAIL_SEQ.NEXTVAL.

Natural Keys

SRC_DAMAGE_ASMT_ID

Damage Assessment ID

DAMAGE_REPORT.REPORT_ID

Populated with the primary key of the Damage Report.

SRC_DAMAGE_TYPE_ID

Damage Type ID

DAMAGE_OUTAGE_TYPES.TYPE_ID

Populated with the primary key of the Damage Type, if the fact record is for damage types. Else, it is set to 0.

SRC_DAMAGE_PART_ID

Damage Part ID

DAMAGE_OUTAGE_PARTS.PART_ID

Populated with the primary key of the Damage Part, if the fact record is for damage parts. Else, it is set to 0.

SRC_CREW_TYPE_ID

Crew Type

DAMAGE_CREW_TYPES.CREW_TYPE_ID

Populated with the primary key of the Crew Type, if the fact record is for crew types. Else, it is set to 0.

Measures

ACCESS_CNT

Accessible Count

DAMAGE_OUTAGE_TYPES.NUM_ACC

Populated only if the fact record is for Damage Types.

INACCESS_CNT

Inaccessible Count

DAMAGE_OUTAGE_TYPES.NUM_INACC

Populated only if the fact record is for Damage Types.

PART_QUANTITY

Part Quantity

DAMAGE_OUTAGE_PARTS.QUANTITY

Populated only if the fact record is for Damage Parts.

CREW_NEEDED

Crews Needed

DAMAGE_CREW_TYPES.NUM_CREWS

Populated only if the fact record is for Crew Types.

FACT_CNT

Count

Populated with the standard value of ‘1’.

Degenerate Dimensions

REPORT_DTTM

Reported Date/Time

DAMAGE_REPORT.REPORT_TIME

Foreign Keys

REPORT_DATE_KEY

Reported Date Key

Populated based on the Damage Report date.

REPORT_TIME_KEY

Reported Time Key

Populated based on the Damage Report time.

DAMAGE_ASMT_KEY

Damage Assessment Dimension Surrogate Key

Populated based on the Damage Report ID.

DAMAGE_ASMT_STATUS_KEY

Damage Assessment Status Dimension Surrogate Key

Populated based on the Damage Report State Key value.

DAMAGE_TYPE_KEY

Damage Type Dimension Surrogate Key

Populated based on the Damage Outage Types Type ID.

CREW_TYPE_KEY

Crew Type Dimension Surrogate Key

Populated based on the damage Crew Type's Crew Type ID.

DAMAGE_PART_KEY

Damage Part Dimension Surrogate Key

Populated based on the Damage Outage Parts Part ID.

DEVICE_KEY

Device Dimension Surrogate Key

Populated based on Jobs Device Class and Index. If the damage report is mapped to a non-electrical device, then this field will be populated with 0.

EVENT_KEY

Event Dimension Surrogate Key

Populated based on the Jobs Number.

Jobs number is retrieved from event class and index from incident based on damage report incident number.

EVENT_STATUS_KEY

Event Status Dimension Surrogate Key

Populated based on the Jobs Status.

CTRL_ZONE_ KEY

Control Zone Dimension Surrogate Key

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

CTRL_ZONE_SEC_KEY

Control Zone Sec Dimension Surrogate Key

Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table.

STORM_KEY

Storm Dimension Surrogate Key

Populated based on the storm name and date.

Storm will affect zones at higher level. Damage report mapped to NCG fall within the same zone and jobs begin time within the storm period will be populated with storm key.

DAMAGE_ASMT_DETAIL_UDD1_KEY

Damage Assessment Detail User Defined Dimension 1 Surrogate Key

DAMAGE_ASMT_DETAIL_UDD2_KEY

Damage Assessment Detail User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Feeder Delivered Load

Extracts the hourly feeder delivered load information from Network Management System on a weekly or monthly schedule.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_FEEDER_DLVRD_LOAD

Table Type

Fact

Fact Type

Snapshot

Driver Table

FLM_FDR_LOAD

ODI Package Name

B1_PKG_CF_FEEDER_DLVRD_LOAD

ETL View Name

B1_F_FEEDER_DLVRD_LOAD_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

FEEDER_DLVRD_LOAD_KEY

Feeder Delivered Load Snapshot Fact Surrogate Key

Populated with the sequence from SPL_FEEDER_DLVRD_LOAD_SEQ.

Natural Keys

SRC_FEEDER_CLS

Source Feeder Class

FLM_FDR_LOAD.FDR_CLS

SRC_FEEDER_IDX

Source Feeder Index

FLM_FDR_LOAD.FDR_IDX

SRC_PHASE_ID

Source Phase ID

FLM_FDR_LOAD.ANALYSIS_DATE

Measures

BRKR_AMP_LIMIT

Breaker Amp Limit

PF_SWITCHES.AMP_LIMIT

AMP

Ampere

FLM_FDR_LOAD.AMP_A

KW

Kilo Watt

FLM_FDR_LOAD.KW_A

KVAR

KVAR

FLM_FDR_LOAD.KVAR_A

KVA

Kilo

FLM_FDR_LOAD.KW_A

FLM_FDR_LOAD.KVAR_A

VOLTAGE

Voltage

FLM_FDR_LOAD.KV_A

POWER_FACTOR

Power Factor

FACT_CNT

Count

Populated with the standard value of ‘1’.

Degenerate Dimensions

SRC_DTTM

Source Date

Foreign Keys

FEEDER_KEY

Feeder Dimension Surrogate Key

FLM_FDR_LOAD.FDR_CLS

FLM_FDR_LOAD.FDR_IDX

CTRL_ZONE_KEY

Control Zone Dimension Surrogate Key

NETWORK_COMPONENTS.NCG

BRKR_DEVICE_KEY

FLM_FDR_LOAD.CB_CLS

FLM_FDR_LOAD.CB_IDX

SUBSTN_DEVICE_KEY

FLM_FDR_LOAD.CB_CLS

FLM_FDR_LOAD.CB_IDX

SNAPSHOT_DATE_KEY

Snapshot Date Key

FLM_FDR_LOAD.ANALYSIS_DATE

SNAPSHOT_TIME_KEY

Snapshot Time Key

FLM_FDR_LOAD.ANALYSIS_DATE

PHASE_KEY

Source Phase Key

FEEDER_DLVRD_LOAD_UDD1_KEY

Feeder Delivered Load User Defined Dimension 1 Surrogate Key

FEEDER_DLVRD_LOAD_UDD2_KEY

Feeder Delivered Load User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

UDDGEN4

User Defined Degenerate Dimension 4

UDDGEN5

User Defined Degenerate Dimension 5

UDDGENL1

User Defined Long Degenerate Dimension 1

UDDGENL2

User Defined Long Degenerate Dimension 2

UDDGENL3

User Defined Long Degenerate Dimension 3

UDDGENL4

User Defined Long Degenerate Dimension 4

UDDGENL5

User Defined Long Degenerate Dimension 5

UDDFK1_KEY

User Defined Dimension Foreign Key 1

UDDFK2_KEY

User Defined Dimension Foreign Key 2

UDDFK3_KEY

User Defined Dimension Foreign Key 3

UDDFK4_KEY

User Defined Dimension Foreign Key 4

UDDFK5_KEY

User Defined Dimension Foreign Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Job

Stores job information from Network Management System. It populates key measures, such as outage duration, number of calls, and customers impacted. 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 Name

JOBS

PICKLIST_INFO_UPD_TR

ODI Package Name

B1_PKG_CF_RST_JOB

ETL View Name

B1_F_RECENT_JOB_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

RST_JOB_KEY

Restored Job Fact Surrogate Key

Populated with the sequence from SPL_RST_JOB_SEQ.

Natural Keys

SRC_JOB_NBR

Source Job Number

JOBS.NUMB

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

OUTG_DURATION

Outage Duration

JOBS.RESTORE_TIME JOBS.BEGIN_TIME

Populated as the difference (in minutes) between begin date/time and the actual restoration date/time of the job.

EST_RST_DUR

Estimated Restoration Duration

JOBS.EST_REST_TIME

JOBS.BEGIN_TIME

Populated with the difference (in minutes) between the begin date/time and the estimated restoration date/time of the job.

CUST_OUT

Customers Out

JOBS. NUM_CUST_OUT

CALL_RECVD

Calls Received

INCIDENTS. NUMB

Populated with the number of calls received by counting the number of incidents associated with the job.

DURATION_DEVIATION

Duration Deviation

JOBS. RESTORE_TIMEJOBS. EST_REST_TIME

Populated with the difference (in minutes) between the actual restoration date/time and the estimated restoration date/time of the job.

CRIT_A

Critical Customer A Count

JOBS.CRIT_A

Populated with the total number of customers affected with type A criticality for the outage.

CRIT_B

Critical Customer B Count

JOBS.CRIT_B

Populated with the total number of customers affected with type B criticality for the outage.

CRIT_C

Critical Customer C Count

JOBS.CRIT_C

Populated with the total number of customers affected with type C criticality for the outage.

CRIT_D

Critical Customer D Count

JOBS.CRIT_D

Populated with the total number of customers affected with type D criticality for the outage.

CRIT_E

Critical Customer E Count

JOBS.CRIT_E

Populated with the total number of customers affected with type E criticality for the outage.

CRIT_F

Critical Customer F Count

JOBS.CRIT_F

Populated with the total number of customers affected with type F criticality for the outage.

CRIT_G

Critical Customer G Count

JOBS.CRIT_G

Populated with the total number of customers affected with type G criticality for the outage.

CRIT_H

Critical Customer H Count

JOBS.CRIT_H

Populated with the total number of customers affected with type H criticality for the outage.

CRIT_I

Critical Customer I Count

JOBS.CRIT_I

Populated with the total number of customers affected with type I criticality for the outage.

CRIT_J

Critical Customer J Count

JOBS.CRIT_J

Populated with the total number of customers affected with type J criticality for the outage.

CRIT_K

Critical Customer K Count

JOBS.CRIT_K

Populated with the total number of customers affected with type K criticality for the outage.

CRIT_L

Critical Customer L Count

JOBS.CRIT_L

Populated with the total number of customers affected with type L criticality for the outage.

CRIT_M

Critical Customer M Count

JOBS.CRIT_M

Populated with the total number of customers affected with type M criticality for the outage.

CRIT_N

Critical Customer N Count

JOBS.CRIT_N

Populated with the total number of customers affected with type N criticality for the outage.

CRIT_O

Critical Customer O Count

JOBS.CRIT_O

Populated with the total number of customers affected with type O criticality for the outage.

CRIT_P

Critical Customer P Count

JOBS.CRIT_P

Populated with the total number of customers affected with type P criticality for the outage.

CRIT_Q

Critical Customer Q Count

JOBS.CRIT_Q

Populated with the total number of customers affected with type Q criticality for the outage.

CRIT_R

Critical Customer R Count

JOBS.CRIT_R

Populated with the total number of customers affected with type R criticality for the outage.

CRIT_S

Critical Customer S Count

JOBS.CRIT_S

Populated with the total number of customers affected with type S criticality for the outage.

CRIT_T

Critical Customer T Count

JOBS.CRIT_T

Populated with the total number of customers affected with type T criticality for the outage.

CRIT_U

Critical Customer U Count

JOBS.CRIT_U

Populated with the total number of customers affected with type U criticality for the outage.

CRIT_V

Critical Customer V Count

JOBS.CRIT_V

Populated with the total number of customers affected with type V criticality for the outage.

CRIT_W

Critical Customer W Count

JOBS.CRIT_W

Populated with the total number of customers affected with type W criticality for the outage.

CRIT_X

Critical Customer X Count

JOBS.CRIT_X

Populated with the total number of customers affected with type X criticality for the outage.

CRIT_Y

Critical Customer Y Count

JOBS.CRIT_Y

Populated with the total number of customers affected with type Y criticality for the outage.

CRIT_Z

Critical Customer Z Count

JOBS.CRIT_Z

Populated with the total number of customers affected with type Z criticality for the outage.

Degenerate Dimensions

RST_IND

Restored Indicator

JOBS.RESTORE_TIME

Populated based on JOBS.RESTORE_TIME.If the restored date/time exists, then this field is populated with 1. Else, with 0.

PLANNED_IND

Planned Indicator

SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME

Populated based on SHEET_CATEGORY_NAME.If the sheet category is planned, then this field is populated with 1. Else, with 0.

EXCLUDE_IND

Exclude Indicator

JOBS.STATE_VALUE

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME

Populated based on STATE_VALUE '1024, NO_DTR_FLAG 'Y' and SHEET_CATEGORY_NAME 'Planned'.

OMS_EXCLUDE_IND

OMS Exclude Indicator

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

Populated based on NO_DTR_FLAG.If set to 'Y,' then this field is populated with 1. Else, with 0.

CANCELLED_IND

Cancelled Indicator

JOBS.STATE_VALUE

Populated based on STATE_VALUE.If set to 1024, then the indicator is populated with 1. Else, with 0.

BEGIN_DTTM

Begin Date Time

JOBS.BEGIN_TIME

RST_DTTM

Restored Date Time

JOBS.RESTORE_TIME

COMPLETION_DTTM

Completion Time

JOBS.JOB_COMPLETE_TIME

EXCL _START_DTTM

Exclusion Period Start Date/Time

STORMMAN_STORMS.EX_STORM_START

Populated with begin time only when there's excludable storm based on NCG.

EXCL _END_DTTM

Exclusion Period End Date/Time

STORMMAN_STORMS. EX_STORM_END

Populated with end time only when there's excludable storm based on NCG.

Foreign Keys

EVENT_KEY

Event Key

JOBS.NUMB

Populated based on the event.

EVENT_STATUS_KEY

Event Status Key

JOBS.STATUS

Populated based on the event status.

BEGIN_DATE_KEY

Begin Date Key

JOBS.BEGIN_TIME

Populated based on the data part of the CD_DATE dimension.

BEGIN_TIME_KEY

Begin Time Key

JOBS.BEGIN_TIME

Populated based on the time part of the CD_TIME dimension.

RST_TIME_KEY

Restored Time Key

JOBS.RESTORE_TIME

Populated based on the time part of the CD_TIME dimension.

RST_DATE_KEY

Restored Date Key

JOBS.RESTORE_TIME

Populated based on the date part of the CD_DATE dimension.

EST_RST_TIME_KEY

Estimated Restored Time Key

JOBS.EST_REST_TIME

Populated based on the time part of the CD_TIME dimension.

EST_RST_DATE_KEY

Estimated Restored Date Key

JOBS.EST_REST_TIME

Populated based on the date part of the CD_DATE dimension.

CTRL_ZONE_KEY

Control Zone Key

JOBS.NCG

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

DEVICE_KEY

Device Key

JOBS.H_IDXJOBS.H_CLS

If H_IDX and H_CLS is '0', then populate DEVICE_KEY as '0'.

CMPL_USER_KEY

Completed User Key

WHO_COMPLETED.USER_NAME

Populated based on the users who completed the tasks.

RESP_USER_KEY

Responsible User Key

WHO_RESPONSIBLE.USER_NAME

Populated based on the responsible user key in the CD_USER table.

CREW_KEY

Crew Key

CREW_VW.CREW_ID

Populated based on the crew details in the CD_CREW table.

FEEDER_KEY

Feeder Key

JOBS.FEEDER_CLSJOBS.FEEDER_IDX

Populated with 0 if FEEDER_IDX and FEEDER_CLS are 0.

SW_PLAN_KEY

Switch Plan Key

JOBS.SWSHEET_IDXJOBS.SWSHEET_CLS

Populated with 0 if SWSHEET_IDX and SWSHEET_CLS are 0.

STORM_KEY

Storm Key

STORMMAN_STORMS.STORM_NAMESTORMMAN_STORMS.STORM_DATE

Populated based on the storm key in the CD_STORM table.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

Populated based on the storm outage type from the CD_STORM_OUTAGE_TYPE table.

PARENT_EVENT_KEY

Parent Event Key

PARENT_EVENT.NUMBJOBS.NUMB

Populated with parent event from the CD_EVENT table.

OUTG_DUR_KEY

Outage Duration

JOBS.RESTORE_TIMEJOBS.BEGIN_TIME

Populated with the outage duration (different between start range and end range).

EST_RST_DUR_KEY

Est Restoration Duration

JOBS.EST_REST_TIMEJOBS.BEGIN_TIME

Populated with the estimated restoration duration.

DURATION_DEVIATION_KEY

Duration Deviation

JOBS.RESTORE_TIMEJOBS.EST_REST_TIME

Populated with duration deviation (outage duration is between start range and end range).

CUST_OUT_KEY

Customers Out Key

JOBS. NUM_CUST_OUT

Populated with number of customers experiencing outage.

COMPLETION_DATE_KEY

Completion Date Key

JOBS.JOB_COMPLETE_TIME

Populated with the completion date.

COMPLETION_TIME_KEY

Completion Time Key

JOBS.JOB_COMPLETE_TIME

Populated with the completion time.

CTRL_ZONE_SEC_KEY

Control Zone Sec Key

JOBS.NCG

Populated from CD_CTRL_ZON_SEC dimension surrogate key based on NCG ID and modified date/time is between effective start and end date/times

EXCL_DETAIL_KEY

Excludable Detail Key

STORMMAN_STORMS.ZONE_IDXSTORMMAN_STORMS.STORM_NAMESTORMMAN _STORMS. STORM_DATE

Populated based on the storm level NCG, storm name, and storm name suffix.

EXCL_START_DATE_KEY

Exclusion Period Start Date Key

STORMMAN_STORMS. EX_STORM_START

Populated based on the date part in the CD_DATE table.

EXCL _END_DATE_KEY

Exclusion Period End Date Key

STORMMAN_STORMS. EX_STORM_END

Populated based on the date part in the CD_DATE table.

EXCL_START_TIME_KEY

Exclusion Period Start Time Key

STORMMAN_STORMS. EX_STORM_START

Populated based on the time part in the CD_TIME table.

EXCL _END_TIME_KEY

Exclusion Period End Time Key

STORMMAN_STORMS. EX_STORM_END

Populated based on the time part in the CD_TIME table.

JOB_UDD1_KEY

User Defined Dimension 1 Key

JOB_UDD2_KEY

User Defined Dimension 2 Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 21

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Outage

A snapshot fact (that can be configured hourly, weekly, monthly etc) and 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 ( Storm , Storm , Switch Plan , and Storm ) and the recent facts ( Recent Call , Recent Crew , Recent Job , and Storm ).

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_OUTG

Table Type

Fact

Fact Type

Snapshot

Driver Table

CF_RECENT_CALL

CF_RECENT_CREW

CF_RECENT_JOB JOB

CF_CUST_RECENT_OUTG CUST

Stage Table Name

STG_CF_OUTG

ODI Package Name

B1_PKG_CF_OUTG

ETL View Name

B1_F_CF_OUTG_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

OUTG_KEY

Outage Fact Surrogate Key

Populated with the sequence from SPL_OUTG_SEQ.

Natural Keys

SNAP_TYPE_CD

Snapshot Type

Populated with ‘H’, ‘W’, or ‘M’ depending on whether the snapshot is configured as Hourly, Weekly, or Monthly. The out-of-the-box solution is Hourly.

SRC_NCG_ID

Control Zone ID

CD_CTRL_ZONE.SRC_NCG_ID

Measures

NUM_CREW_ASSIGN

Number of Crew Assignments

CF_RECENT_CREW.FACT_CNT

Populated with the count of crew assignments during the hour.

NUM_CREW_DISP

Number of Crew Dispatches

CF_RECENT_CREW.FACT_CNT

Populated with the count of crew dispatches during the hour.

NUM_CUST_OUTG

Number of Customers Out

CF_CUST_RECENT_OUTG.FACT_CNT

Populated with the count of customers experiencing outage.

NUM_CUST_RST

Number of Customers Restored

CF_CUST_RECENT_OUTG.FACT_CNT

Populated with the count of customers restored during the hour.

NUM_CUST_NEW

Number of New Customers

CF_CUST_RECENT_OUTG.FACT_CNT

Populated with the count of new customers experiencing outages during the hour.

NUM_CUST_MOM

Number of Customer Momentaries

CF_RECENT_JOB.FACT_CNT

Populated with the sum of fact count from the Recent Customer Job fact suffering momentary outages during that hour.

NUM_EVENT

Number of Events

CF_RECENT_JOB.FACT_CNT

Populated with the number of events.

NUM_NEW_EVENT

Number of New Events

CF_RECENT_JOB.FACT_CNT

Populated with the number of new events started during the hour.

NUM_CLOSED_EVENT

Number of Closed Events

CF_RECENT_JOB.FACT_CNT

Populated with the number of events closed during the hour.

NUM_CANCELLED_EVENT

Number of Cancelled Events

CF_RECENT_JOB.CANCELLED_IND

Populated with the number of events cancelled during the hour.

NUM_CALL

Number of Calls

CF_RECENT_CALL.FACT_CNT

Populated with the number of calls received during the hour.

NUM_CUST_EST_RST

Number of Customers Estimated to be Restored

CF_CUST_RECENT_OUTG. FACT_CNT

Populated with the number of customers estimated to be restored during the hour.

FACT_CNT

Count

Populated with the standard value of ‘1’.

Degenerate Dimensions

SNAPSHOT_DTTM

Snapshot Date/Time

B1_JOB_CONFIG.SLICE_END_DTTM

Foreign Keys

CTRL_ZONE_KEY

Control Zone Key

CD_CTRL_ZONE.CTRL_ZONE_KEY

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

CTRL_ZONE_SEC_KEY

Control Zone Sec Key

JOBS.NCG

Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table.

SNAPSHOT_DATE_KEY

Snapshot Date Type

Populated based on the date part of the snapshot type key.

SNAPSHOT_TIME_KEY

Snapshot Time Type

Populated based on the time part of the snapshot type key.

OUTG_UDD1_KEY

Outage User Defined Dimension 1 Surrogate Key

OUTG_UDD2_KEY

Outage User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

UDDGEN1

User Defined Degenerate Dimension 1

UDDGEN2

User Defined Degenerate Dimension 2

UDDGEN3

User Defined Degenerate Dimension 3

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Recent Call

Stores the details of the incidents, including associated job details, which are created in the Network Management System. The fact stores details about all un-restored jobs, as well as jobs restored within the last 'X' number of days. The value of 'X' days has to be configured using the Oracle Utilities Administration Tool.

See the Configuring Oracle Utilities Network Management System (NMS) section 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 there is a purging mechanism based on a user configured number of days that is set in Oracle Utilities Administration Tool.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_RECENT_CALL

Table Type

Fact

Fact Type

Accumulation

Driver Table

INCIDENTS

ODI Package Name

B1_PKG_CF_RECENT_CALL

ETL View Name

B1_F_RECENT_CALL_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

RECENT_CALL_KEY

Recent Call Fact Surrogate Key

Populated with the sequence from SPL_RECENT_CALL_SEQ.

Natural Keys

SRC_INCIDENT_ID

Source Incident ID

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

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

Degenerate Dimensions

PRIORITY_IND

Priority Indicator

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

Use parameter B1_PRTY_BIT instead of 7.

RST_IND

Restored Indicator

JOBS.RESTORE_TIME

Populated with 1 if restoration date/time is populated. Else, it is populated with 0.

Foreign Keys

EVENT_KEY

Event Key

JOBS.NUMB

Populated with the event key.

ACCT_KEY

Account Key

INCIDENTS.ACCOUNT_NUM

Populated with the account key.

CALL_DATE_KEY

Call Date Key

INCIDENTS.INPUT_TIME

Populated with the date part.

CALL_TIME_KEY

Call Time Key

INCIDENTS.INPUT_TIME

Populated with the time part.

CTRL_ZONE_KEY

Control Zone Key

INCIDENTS.NCG

Populated based on the incidents NCG.

USER_KEY

User Key

CES_USER.USER_NAME

Populated based on the user key value.

CALL_INFO_KEY

Call Info Key

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

Populated based on the call information.

PREM_KEY

Premise Key

CES_CUSTOMERS.SERV_LOC_ID

Populated with the mailing premise ID.

ADDR_KEY

Address Key

CES_CUSTOMERS.SERV_LOC_ID

Populated with the mailing address.

DEVICE_KEY

Device Key

INCIDENTS.H_IDX

INCIDENTS.H_CLS

Populated with the device key value.

ACCT_TYPE_KEY

Account Type Key

CES_CUSTOMERS_HISTORY.ACCOUNT_TYPE

Populated with the account type value.

METER_KEY

Meter Key

CES_CUSTOMERS_HISTORY.METER_ID

Populated with the meter key value.

NETWORK_PREMISE_KEY

Network Premise Key

CES_CUSTOMERS_HISTORY.SERV_LOC_ID

Populated with the network premise key value.

CTRL_ZONE_SEC_KEY

Control Zone Secondary Key

INCIDENTS.NCG

Populated based on the incidents NCG.

STORM_KEY

Storm Key

STORMAN_STORMS.STORM_NAME

STORMAN_STORMS.STORM_DATE

Populated based on the storm key.

CALL_UDD1_KEY

Call User Defined Dimension 1 Key

CALL_UDD2_KEY

Call User Defined Dimension 2 Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Recent Crew

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 is carried out by the corresponding crew. This fact stores the summarized information of all such crew activities for every job created in the source system. The fact stores details about all un- restored jobs as well as jobs restored within the last 'X' number of days. The value of 'X' days has to be configured using the Oracle Utilities Administration Tool.

See Configuring Oracle Utilities Network Management System (NMS) 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 there is a purging mechanism based on a user configured number of days that is set in Oracle Utilities Administration Tool.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_RECENT_CREW

Table Type

Fact

Fact Type

Accumulation

Driver Table

CREW_ASSIGNMENTS

CREW_DISPATCHES

STORMMAN_STORMS

ODI Package Name

B1_PKG_CF_RECENT_CREW

ETL View Name

n/a

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

RECENT_CREW_KEY

Recent Crew Fact Surrogate Key

Populated with the sequence from SPL_RECENT_CALL_SEQ.

Natural Keys

SRC_EVENT_NBR

Source Event Number

JOBS.NUMB

SRC_CREW_ID

Source Crew ID

CREW_EVENT_HISTORY.CREW_ID

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

INROUTE_DURATION

En Route Duration

CREW_DISPATCHES.WHEN_HAPPENED

Populated with the total time spent by the crew in ‘Dispatch’ state for a particular event.

WORK_DURATION

Work Duration

CREW_DISPATCHES.WHEN_HAPPENED

Populated with the total time spent by the crew in ‘Arrive’ state for a particular event.

ASSIGN_DURATION

Assign Duration

CREW_ASSIGNMENTS.WHEN_HAPPENED

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

Populated with the total time spent by the crew while assigned, en route and working on the event. Dispatch Duration = En Route Duration + Work Duration + Assign Duration

Degenerate Dimensions

EXCL_PERIOD_START_DTTM

Exclusion Period End Date/Time

STORMMAN_STORMS. EX_STORM_START

If a storm is present and it has an exclusion period defined within which the begin date/time of the job falls, then populate this field with the exclusion period start date/time.

EXCL_PERIOD_END_DTTM

Exclusion Period Start Date/Time

STORMMAN_STORMS. EX_STORM_END

If a storm is present and it has an exclusion period defined within which the begin date/time of the job falls, then populate this field with the exclusion period end date/time.

Foreign Keys

EVENT_KEY

Event Key

JOBS.NUMB

Populated from CD_EVENT’s surrogate key using NUMB column in the JOBS table.

ASSIGN_DATE_KEY

Last Assign Date Key

CREW_ASSIGNMENTS ASSIGN.WHEN_HAPPENED

Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest assignment date of the crew. In case of no assignments for the crew, populate as 0.

ASSIGN_TIME_KEY

Last Assign Time Key

CREW_ASSIGNMENTS ASSIGN.WHEN_HAPPENED

Populate with the surrogate key of the CD_TIME dimension by looking up with the time part of the latest assignment date of the crew. In case of no assignments for the crew, populate as 0.

CTRL_ZONE_KEY

Control Zone Key

JOBS.NCG

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

ASSIGN_USER_KEY

Assign User Key

CREW_ASSIGNMENTS ASSIGN.CES_USER

Look up the CD_USER dimension with the last user who assigned the crew.

CREW_KEY

Crew Key

CREW_EVENT_HISTORY.CREW_ID

Lookup in CD_CREW dimension to populate surrogate key.

DEVICE_KEY

Device Key

JOBS.H_IDX

JOBS.H_CLS

Populated based on H_IDX and H_CLS in the CD_DEVICE table. .

UNASSIGN_USER_KEY

Unassign User Key

CREW_ASSIGNMENTS UNASSIGN.CES_USER

Look up the CD_USER dimension with the last user who unassigned the crew and populate with surrogate key.

UNASSIGN_DATE_KEY

Unassign Date Key

CREW_ASSIGNMENTS UNASSIGN.WHEN_HAPPENED

Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest un-assignment date of the crew. In case of no un-assignments for the crew, populate as 0.

UNASSIGN_TIME_KEY

Unassign Time Key

CREW_ASSIGNMENTS UNASSIGN.WHEN_HAPPENED

Populate with the surrogate key of the CD_TIME dimension by looking up with the time part of the latest un-assignment date of the crew. In case of no un-assignments for the crew, populate as 0.

ACCEPT_USER_KEY

Accept User Key

CREW_DISPATCHES DISPATCH.CES_USER

Populate with the last user who dispatched the crew, by looking up the CD_USER dimension.

ACCEPT_DATE_KEY

Accept Date Key

CREW_DISPATCHES DISPATCH.WHEN_HAPPENED

Populated based on the date part of the latest dispatch date of the crew. In case of no dispatches for the crew, populate as 0.

ACCEPT_TIME_KEY

Accept Time Key

CREW_DISPATCHES DISPATCH.WHEN_HAPPENED

Populated based on the time part of the latest dispatch date of the crew and lookup the CD_TIME dimension. In case of no dispatches for the crew, populate as 0.

ARRIVE_TIME_KEY

Arrive Time Key

CREW_DISPATCHES ARRIVE.WHEN_HAPPENED

Populated based on the time part of the latest arrival date of the crew and lookup the CD_TIME dimension. In case of no completions for the crew, populate as 0.

ARRIVE_DATE_KEY

Arrive Date Key

CREW_DISPATCHES ARRIVE.WHEN_HAPPENED

Populate with the surrogate key of the CD_DATE dimension by looking up with the date part of the latest arrival date of the crew. In case of no completions for the crew, populate as 0.

ARRIVE_USER_KEY

Arrive User Key

CREW_DISPATCHES ARRIVE.CES_USER

Populate with the last user who marked the crew activity as arrived, by looking up the CD_USER dimension.

CMPL_TIME_KEY

Complete Time Key

CREW_DISPATCHES COMPLETE.WHEN_HAPPENED

Populated with the time part of the latest completion date of the crew. In case of no completions for the crew, populate as 0.

CMPL_DATE_KEY

Completed Date Key

CREW_DISPATCHES COMPLETE.WHEN_HAPPENED

Populated with the date part of the latest completion date of the crew. In case of no completions for the crew, populate as 0.

CMPL_USER_KEY

Completed User Key

CREW_DISPATCHES COMPLETE.CES_USER

Populate with the last user who marked the crew activity as completed, by looking up the CD_USER dimension.

STORM_KEY

Storm Key

STORMMAN_STORMS.STORM_NAME

STORMMAN_STORMS.STORM_DATE

Populated based on the storm name and start date of the storm.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

Populated based on the storm outage type in the Jobs table.

CTRL_ZONE_SEC_KEY

Control Zone Key

JOBS.NCG

Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table.

EXCL_DETAIL_KEY

Excludable Detail Key

STORMAN_STORMS.ZONE_IDX

STORMMAN _STORMS. STORM_NAME

STORMMAN _STORMS. STORM_DATE

Populated based on the storm level NCG, storm name, and storm name suffix from the CD_EXCL_DETAIL table.

EXCL_START_DATE_KEY

Exclusion Period Start Date Key

STORMMAN_STORMS. EX_STORM_START

Populated based on the start date part of the exclusion period.

EXCL _END_DATE_KEY

Exclusion Period End Date Key

STORMMAN_STORMS. EX_STORM_END

Populated with the end date part of the exclusion period.

EXCL_START_TIME_KEY

Exclusion Period Start Time Key

STORMMAN_STORMS. EX_STORM_START

Populated with the start time part of the exclusion period.

EXCL _END_TIME_KEY

Exclusion Period End Time Key

STORMMAN_STORMS. EX_STORM_END

Populated with the end time part of the exclusion period.

CREW_UDD1_KEY

Crew User Defined Dimension 1 Key

CREW_UDD2_KEY

Crew User Defined Dimension 2 Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Recent Customer Outage

This fact stores the details of customers experiencing service interruptions based on the supply node log information from the source system. The actual list of customers experiencing the outage is retrieved from CES Customer History. 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 as well as jobs restored within the last 'X' number of days. The value of 'X' days has to be configured using the Oracle Utilities Administration Tool.

See Configuring Oracle Utilities Network Management System (NMS) 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 a user configured 'X' number of days that is set in Oracle Utilities Administration Tool.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CUST_RECENT_OUTG

Table Type

Fact

Fact Type

Accumulation

Driver Table Name

JOBS J

STORMMAN_STORMS

ODI Package Name

B1_PKG_CF_CUST_RECENT_OUTG

ETL View Name

B1_F_CUST_RECENT_OUTG_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

CUST_RECENT_OUTG_KEY

Recent Customer Outage Fact Surrogate Key

Populated with the sequence from SPL_CUST_RECENT_OUTG_SEQ.

Natural Keys

SRC_ID

Source Outage ID

SUPPLY_NODE_LOG.ID

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

OUTG_DURATION

Outage Duration

Populated with the difference between restored time and outage time, in minutes.

NUM_MOMENTARY

Number of Momentaries Covered

JOBS.NUM_MOMENTARIES

CMI

CMI

Populated with the difference between restored time and outage time, in minutes.

EST_RST_DUR

Estimated Restoration Duration

Populated with the difference between jobs estimated restored time and begin time, in minutes.

DURATION_DEVIATION

Duration Deviation

Populated with the difference between jobs restored time and estimated restored time, in minutes.

Degenerate Dimensions

BEGIN_DTTM

Begin Date Time

SUPPLY_NODE_LOG.OUTAGE_TIME

RST_DTTM

Restored Date Time

SUPPLY_NODE_LOG.RESTORE_TIME

EST_RST_DTTM

Estimated Restoration Date/Time

JOBS.EST_REST_TIME

EXCL_START_DTTM

Exclusion Period Start Date/Time

STORMMAN_STORMS. EX_STORM_START

Populated based on NCG only when there is an excludable storm.

EXCL_END_DTTM

Exclusion Period End Date/Time

STORMMAN_STORMS. EX_STORM_END

Populated based on NCG only when there is an excludable storm.

RST_IND

Restored Indicator

Populated with 1 for all restored outages (supply node log restored time is not null). Else, populated with 0.

PLANNED_IND

Planned Indicator

Populated with 1 for all planned outages (SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME is not null). Else, it is populated with 0.

EXCLUDE_IND

Exclude Indicator

JOBS J.STATE_VALUE

PICKLIST_INFO_UPD_TR P.NO_DTR_FLAG

SWMAN_SHEET_CATEGORYSSCA.SHEET_CATEGORY_NAME

OMS_EXCLUDE_IND

OMS Exclude Indicator

Populated with 1 when no_dtr_flag value is 'Y' in PICKLIST_INFO_UPD_TR table. Else, 0.

CANCELLED_IND

Cancelled Indicator

Populated with 1 based on the jobs state value. Else, it is populated with 0.

SRC_PREMISE_ID

Outage Premise ID

CES_CUSTOMER_HISTORY.SERV_LOC_ID

SRC_PERSON_ID

Outage Person ID

CES_CUSTOMER_HISTORY.CUST_ID

SRC_METER_ID

Outage Meter ID

CES_CUSTOMER_HISTORY.METER_ID

DEVICE_ID

Outage Device ID

CES_CUSTOMER_HISTORY.DEVICE_ID

MOM_OUTG_IND

Momentary Outage Indicator

Populated with 1 if outage duration is less than the momentary outage duration configured parameter value. Else, it is populated with 0.

Foreign Keys

EVENT_KEY

Event Key

Populated with the event key.

EVENT_STATUS_KEY

Event Status Key

Populated with the event status.

ACCT_KEY

Account Key

Populated with the account ID.

PREM_KEY

Premise Key

Populated with the service location.

PER_KEY

Person Key

Populated with the customer ID.

METER_KEY

Meter Key

Populated with the meter ID.

ADDR_KEY

Address Key

Populated based on the service location.

BEGIN_DATE_KEY

Begin Date Key

Populated based on the outage begin date.

BEGIN_TIME_KEY

Begin Time Key

Populated based on the outage begin time.

RST_DATE_KEY

Restored Time Key

Populated based on the restored date.

RST_TIME_KEY

Restored Date Key

Populated based on the restored time.

EST_RST_TIME_KEY

Estimated Restored Time Key

Populated based on the estimated restored time.

EST_RST_DATE_KEY

Estimated Restored Date Key

Populated based on the estimated restored date.

CTRL_ZONE_KEY

Control Zone Key

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

CAUSE_CTRL_ZONE_KEY

Cause Control Zone Key

Populated based on the jobs NCG.

CTRL_ZONE_SEC_KEY

Control Zone Sec Dimension Surrogate Key

Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table.

CAUSE_CTRL_ZONE_SEC_KEY

Control Zone Sec Dimension Surrogate Key

Populated based on the jobs NCG.

AFF_DEVICE_KEY

Device Dimension Surrogate Key

Populated based on the supply node device class and index.If the damage report is mapped to a non-electrical device, then this field is populated with 0.

CAUSE_DEVICE_KEY

Device Dimension Surrogate Key

Populated based on the jobs device class and index.

If the damage report is mapped to a non-electrical device, then this field is populated with 0.

FEEDER_KEY

Feeder Key

Populated based on FEEDER_IDX and FEEDER_CLS.

SW_PLAN_KEY

Switch Plan Key

Populated based on jobs SWSHEET_IDX and SWSHEET_CLS.

STORM_KEY

Storm Key

Populated based on the storm name and date.Storm affects the zones at higher level. Outages fall within the same zone and jobs begin time within the storm period is populated with the storm key.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

Populated based on the jobs’ STORMMAN_TYPE.

PARENT_EVENT_KEY

Event Dimension Surrogate Key

Populated based on the parent event number.

OUTG_DUR_KEY

Outage Duration Key

Populated with the outage duration based on the RANGE_START and RANGE_END period.

EST_RST_DUR_KEY

Est Restoration Duration Surrogate key

Populated with the estimated restoration duration based on the RANGE_START and RANGE_END.

DURATION_DEVIATION_KEY

Duration Deviation Surrogate Key

Populated with duration deviation based on the RANGE_START and RANGE_END.

NETWORK_PREM_KEY

Network Premise Dimension Surrogate Key

Populated with the service location ID based on the customer history.

CREW_KEY

Crew Dimension Surrogate Key

Populated based on the crew dispatched crew ID.

ACCT_TYPE_KEY

Account Type Dimension Surrogate Key

Populated with account type based on the customer history.

EXCL_DETAIL_KEY

Excludable Detail Dimension Surrogate Key

Populated based on ZONE_IDX, NAME,DATE retrieved from STORMMAN_STORMS.

EXCL_START_DATE_KEY

Exclusion Period Start Date Key

Populated based on the exclusion detail start date.

EXCL_END_DATE_KEY

Exclusion Period End Date Key

Populated based on the exclusion detail end time.

EXCL_START_TIME_KEY

Exclusion Period Start Time Key

Populated based on the exclusion detail start time.

EXCL_END_TIME_KEY

Exclusion Period End Time Key

Populated based on the exclusion detail end time.

CUST_OUTG_UDD1_KEY

User Defined Dimension 1 Key

CUST_OUTG_UDD2_KEY

User Defined Dimension 2 Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Recent Job

Stores job information from the source system. This fact stores KPIs, such as number of events, outage duration, restoration duration, resolved event count, etc. It also stores details about all un-restored jobs, as well as jobs restored within the last 'X' number of days. The value of 'X' days has to be configured using the Oracle Utilities Administration Tool.

See Configuring Oracle Utilities Network Management System (NMS) 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 there is a purging mechanism based on a user configured 'X' number of days set in the Oracle Utilities Administration Tool.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_RECENT_JOB

Table Type

Fact

Fact Type

Accumulation

Driver Table Name

JOBS

PICKLIST_INFO_UPD_TR

ODI Package Name

B1_PKG_CF_RECENT_JOB

ETL View Name

B1_F_RECENT_JOB_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

RECENT_JOB_KEY

Recent Job Fact Surrogate Key

Populated with the sequence from SPL_RECENT_JOB_SEQ.

Natural Keys

SRC_JOB_NBR

Source Job Number

JOBS.NUMB

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

OUTG_DURATION

Outage Duration

JOBS.RESTORE_TIME JOBS.BEGIN_TIME

Populated with the difference (in minutes) between the begin date/time and the actual restoration date/time of the job.

EST_RST_DUR

Outage Duration

JOBS.EST_REST_TIMEJOBS.BEGIN_TIME

Populated with the difference (in minutes) between the begin date/time and the estimated restoration date/time of the job.

CUST_OUT

Customers Out

JOBS. NUM_CUST_OUT

Populated with the customer count experiencing outage.

CALL_RECVD

Calls Received

INCIDENTS. NUMB

Populated with the number of calls received by counting the number of incidents associated with the job.

DURATION_DEVIATION

Duration Deviation

JOBS. RESTORE_TIMEJOBS. EST_REST_TIME

Populated with the difference (in minutes) between the actual restoration date/time and the estimated restoration date/time of the job.

CRIT_A

Critical Customer A Count

JOBS.CRIT_A

Populated with the total number of customers affected with type A criticality for the outage.

CRIT_B

Critical Customer B Count

JOBS.CRIT_B

Populated with the total number of customers affected with type B criticality for the outage.

CRIT_C

Critical Customer C Count

JOBS.CRIT_C

Populated with the total number of customers affected with type C criticality for the outage.

CRIT_D

Critical Customer D Count

JOBS.CRIT_D

Populated with the total number of customers affected with type D criticality for the outage.

CRIT_E

Critical Customer E Count

JOBS.CRIT_E

Populated with the total number of customers affected with type E criticality for the outage.

CRIT_F

Critical Customer F Count

JOBS.CRIT_F

Populated with the total number of customers affected with type F criticality for the outage.

CRIT_G

Critical Customer G Count

JOBS.CRIT_G

Populated with the total number of customers affected with type G criticality for the outage.

CRIT_H

Critical Customer H Count

JOBS.CRIT_H

Populated with the total number of customers affected with type H criticality for the outage.

CRIT_I

Critical Customer I Count

JOBS.CRIT_I

Populated with the total number of customers affected with type I criticality for the outage.

CRIT_J

Critical Customer J Count

JOBS.CRIT_J

Populated with the total number of customers affected with type J criticality for the outage.

CRIT_K

Critical Customer K Count

JOBS.CRIT_K

Populated with the total number of customers affected with type K criticality for the outage.

CRIT_L

Critical Customer L Count

JOBS.CRIT_L

Populated with the total number of customers affected with type L criticality for the outage.

CRIT_M

Critical Customer M Count

JOBS.CRIT_M

Populated with the total number of customers affected with type M criticality for the outage.

CRIT_N

Critical Customer N Count

JOBS.CRIT_N

Populated with the total number of customers affected with type N criticality for the outage.

CRIT_O

Critical Customer O Count

JOBS.CRIT_O

Populated with the total number of customers affected with type O criticality for the outage.

CRIT_P

Critical Customer P Count

JOBS.CRIT_P

Populated with the total number of customers affected with type P criticality for the outage.

CRIT_Q

Critical Customer Q Count

JOBS.CRIT_Q

Populated with the total number of customers affected with type Q criticality for the outage.

CRIT_R

Critical Customer R Count

JOBS.CRIT_R

Populated with the total number of customers affected with type R criticality for the outage.

CRIT_S

Critical Customer S Count

JOBS.CRIT_S

Populated with the total number of customers affected with type S criticality for the outage.

CRIT_T

Critical Customer T Count

JOBS.CRIT_T

Populated with the total number of customers affected with type T criticality for the outage.

CRIT_U

Critical Customer U Count

JOBS.CRIT_U

Populated with the total number of customers affected with type U criticality for the outage.

CRIT_V

Critical Customer V Count

JOBS.CRIT_V

Populated with the total number of customers affected with type V criticality for the outage.

CRIT_W

Critical Customer W Count

JOBS.CRIT_W

Populated with the total number of customers affected with type W criticality for the outage.

CRIT_X

Critical Customer X Count

JOBS.CRIT_X

Populated with the total number of customers affected with type X criticality for the outage.

CRIT_Y

Critical Customer Y Count

JOBS.CRIT_Y

Populated with the total number of customers affected with type Y criticality for the outage.

CRIT_Z

Critical Customer Z Count

JOBS.CRIT_Z

Populated with the total number of customers affected with type Z criticality for the outage.

Degenerate Dimensions

RST_IND

Restored Indicator

JOBS.RESTORE_TIME

Populated with 1 for all restored outages (supply node log restored time is not null). Else, it is populated with 0.

PLANNED_IND

Planned Indicator

SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME

Populated with 1 for all planned outages. Else, it is populated with 0.

EXCLUDE_IND

Exclude Indicator

JOBS.STATE_VALUE

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

SWMAN_SHEET_CATEGORY.SHEET_CATEGORY_NAME

Populated with 1 for all excluded outages. Else, it is populated with 0.

OMS_EXCLUDE_IND

OMS Exclude Indicator

PICKLIST_INFO_UPD_TR.NO_DTR_FLAG

Populated based on the NO_DTR_FLAG. If this is 'Y', the field is populated with '1'. Else, '0'.

CANCELLED_IND

Cancelled Indicator

JOBS.STATE_VALUE

Populated based on the STATE_VALUE. If this is 1024, then the indicator is populated with '1'. Else, with '0.

BEGIN_DTTM

Begin Date Time

JOBS.BEGIN_TIME

RST_DTTM

Restored Date Time

JOBS.RESTORE_TIME

COMPLETION_DTTM

Completion Time

JOBS.JOB_COMPLETE_TIME

EXCL _START_DTTM

Exclusion Period Start Date/Time

STORMMAN_STORMS. EX_STORM_START

Populated with begin time only when there's excludable storm based on NCG.

EXCL _END_DTTM

Exclusion Period End Date/Time

STORMMAN_STORMS. EX_STORM_END

Populated with end time only when there's excludable storm based on NCG.

Foreign Keys

EVENT_KEY

Event Key

JOBS.NUMB

Populated based on the event.

EVENT_STATUS_KEY

Event Status Key

JOBS.STATUS

Populated based on the event status.

BEGIN_DATE_KEY

Begin Date Key

JOBS.BEGIN_TIME

Populated based on the data part of the CD_DATE dimension.

BEGIN_TIME_KEY

Begin Time Key

JOBS.BEGIN_TIME

Populated based on the time part of the CD_TIME dimension.

RST_TIME_KEY

Restored Time Key

JOBS.RESTORE_TIME

Populated based on the time part of the CD_TIME dimension.

RST_DATE_KEY

Restored Date Key

JOBS.RESTORE_TIME

Populated based on the date part of the CD_DATE dimension.

EST_RST_TIME_KEY

Estimated Restored Time Key

JOBS.EST_REST_TIME

Populated based on the time part of the CD_TIME dimension.

EST_RST_DATE_KEY

Estimated Restored Date Key

JOBS.EST_REST_TIME

Populated based on the date part of the CD_DATE dimension.

CTRL_ZONE_KEY

Control Zone Key

JOBS.NCG

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

DEVICE_KEY

Device Key

JOBS.H_IDXJOBS.H_CLS

If H_IDX and H_CLS is '0', then populate DEVICE_KEY as '0'.

CMPL_USER_KEY

Completed User Key

WHO_COMPLETED.USER_NAME

Populated based on the users who completed the tasks.

RESP_USER_KEY

Responsible User Key

WHO_RESPONSIBLE.USER_NAME

Populated based on the responsible user key in the CD_USER table.

CREW_KEY

Crew Key

CREW_VW.CREW_ID

Populated based on the crew details in the CD_CREW table.

FEEDER_KEY

Feeder Key

JOBS.FEEDER_CLSJOBS.FEEDER_IDX

Populated with 0 if FEEDER_IDX and FEEDER_CLS are 0.

SW_PLAN_KEY

Switch Plan Key

JOBS.SWSHEET_IDXJOBS.SWSHEET_CLS

Populated with 0 if SWSHEET_IDX and SWSHEET_CLS are 0.

STORM_KEY

Storm Key

STORMMAN_STORMS.STORM_NAMESTORMMAN_STORMS.STORM_DATE

Populated based on the storm key in the CD_STORM table.

STORM_OUTAGE_TYPE_KEY

Storm Outage Type Key

JOBS.STORMMAN_TYPE

Populated based on the storm outage type from the CD_STORM_OUTAGE_TYPE table.

PARENT_EVENT_KEY

Parent Event Key

PARENT_EVENT.NUMBJOBS.NUMB

Populated with parent event from the CD_EVENT table.

OUTG_DUR_KEY

Outage Duration

JOBS.RESTORE_TIMEJOBS.BEGIN_TIME

Populated with the outage duration (different between start range and end range).

EST_RST_DUR_KEY

Estimated Restoration Duration

JOBS.EST_REST_TIMEJOBS.BEGIN_TIME

Populated with the estimated restoration duration.

DURATION_DEVIATION_KEY

Duration Deviation

JOBS.RESTORE_TIMEJOBS.EST_REST_TIME

Populated with duration deviation (outage duration is between start range and end range).

CUST_OUT_KEY

Customers Out Key

JOBS. NUM_CUST_OUT

Populated with number of customers experiencing outage.

COMPLETION_DATE_KEY

Completion Date Key

JOBS.JOB_COMPLETE_TIME

Populated with the completion date.

COMPLETION_TIME_KEY

Completion Time Key

JOBS.JOB_COMPLETE_TIME

Populated with the completion time.

CTRL_ZONE_SEC_KEY

Control Zone Sec Key

JOBS.NCG

Populated based on the secondary control zone hierarchy of NCG from DDS_ALARM_LOG table.

EXCL_DETAIL_KEY

Excludable Detail Key

STORMMAN_STORMS.ZONE_IDXSTORMMAN_STORMS.STORM_NAMESTORMMAN _STORMS. STORM_DATE

Populated based on the storm level NCG, storm name, and storm name suffix.

EXCL_START_DATE_KEY

Exclusion Period Start Date Key

STORMMAN_STORMS.EX_STORM_START

Populated based on the date part in the CD_DATE table.

EXCL _END_DATE_KEY

Exclusion Period End Date Key

STORMMAN_STORMS.EX_STORM_END

Populated based on the date part in the CD_DATE table.

EXCL_START_TIME_KEY

Exclusion Period Start Time Key

STORMMAN_STORMS. EX_STORM_START

Populated based on the time part in the CD_TIME table.

EXCL _END_TIME_KEY

Exclusion Period End Time Key

STORMMAN_STORMS. EX_STORM_END

Populated based on the time part in the CD_TIME table.

JOB_UDD1_KEY

User Defined Dimension 1 Key

JOB_UDD2_KEY

User Defined Dimension 2 Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Storm

Captures various metrics (such as CI, CMI, MCI, events, and damage assessments) for every hour a storm was active. This fact caters to the various KPI requirements in the Storm Analysis dashboard in the Oracle Utilities Analytics application.

Note : Though this is an accumulation fact, all measures have to computed on an hourly basis till the end of the storm. This helps to update the computed measures for any changes in the source system. Measures are calculated for every storm at the beginning hour (based on the storm’s start date).

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_STORM

Table Type

Fact

Fact Type

Accumulation

Driver Table Name

STORMMAN_STORMS

ODI Package Name

B1_PKG_CF_STORM

ETL View Name

B1_F_STORM_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

STORM_F_KEY

Storm Fact Surrogate Key

Populated with the sequence from B1_STORM_SEQ.

Natural Keys

SRC_STORM_NAME

Storm Name

CD_STORM.SRC_STORM_NAME

SRC_STORM_NAME_SFX

Storm Name Suffix

CD_STORM.SRC_STORM_NAME_SFX

SRC_NCG_ID

Source NCG ID

STORMMAN_STORMS.ZONE_IDX

MAJ_EVT_SNAP_TYPE_CD

Major Event Snapshot Type

CD_MJR_EVT_SNAP_TYPE.SNAP_TYPE_CD

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

CI

Customers Interrupted

CF_CUST_RST_OUTG.SUM(FACT_CNT)

Populated with the count of customers interrupted.

CMI

Customer Minutes Interrupted

CF_CUST_RST_OUTG.BEGIN_DTTMCF_CUST_RST_OUTG.RST_DTTM

Populated with the customer minutes interrupted.

MCI

Momentary Customers Interrupted

CF_CUST_RST_OUTG.SUM(FACT_CNT)

Populated with the count of momentary customer interruptions.

SAIDI

SAIDI

CF_CUST_RST_OUTG.BEGIN_DTTMCF_CUST_RST_OUTG.RST_DTTM

Populated with customer minutes interrupted by number of customers served.

SAIFI

SAIFI

Populated with the sum of customer interruptions divided by number of customer served.

MAIFI

MAIFI

Populated with the sum of momentary customer interruptions divided by number of customer served.

CAIDI

CAIDI

Populated with the number of customer minutes interrupted divided by the number of customer interruptions.

NUM_NEW_CUST_OUT

New Customers Out

Populated with count of customers that went out only during that hour (begin date/time fall in that hour).

NUM_ CUST_RST

Customers Restored

Populated with count of customers restored during that hour (the RST_DTTM fall in that hour).

NUM_CUST_REMAIN_OUT

Customers Remaining Out

Populated with the remaining customers out.

Total Number of Customer Out - Customers Restored in that hour.

NUM_NEW_EVENT

New Events

Populated with the count of jobs created during that hour.

NUM_CLOSED_EVENT

Closed Events

Populated with the count of jobs restored during that hour.

NUM_REMAIN_EVENT

Remaining Events

Populated with number of remaining events.Total Events - Closed Events

Degenerate Dimensions

STATISTICS_DTTM

Statistics Date/Time

LEAST OF STORM_START AND EX_STORM_START TILL

GREATEST OF STORM_END AND EX_STORM_END

Populate for every hour starting from least of (STORM_START and EX_STORM_START) till greatest of (STORM_END and EX_STORM_END) in the STORMMAN_STORMS table.In case of an active storm, load till SYSDATE and populate the hour end time.Example: For 9th hour, the statistics DTTM is populated as: 09:59:59.

EXCL _START_DTTM

Exclusion Period Start Date/Time

STORMMAN_STORMS. EX_STORM_START

EXCL _END_DTTM

Exclusion Period End Date/Time

STORMMAN_STORMS. EX_STORM_END

Foreign Keys

CTRL_ZONE_KEY

Control Zone Key

JOBS.NCG

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

STORM_KEY

Storm Key

STORMMAN_STORMS.STORM_NAMESTORMMAN_STORMS.STORM_DATE

Populated with the storm name and date of start.

CTRL_ZONE_SEC_KEY

Control Zone Sec Key

JOBS.NCG

Populated from CD_CTRL_ZONE_SEC dimension's surrogate key based on NCG ID and records effective date/time is between effective start and end date/time of dimension.

EXCL_DETAIL_KEY

Excludable Detail Key

STORMMAN_STORMS.ZONE_IDX STORMMAN_STORMS.STORM_NAMESTORMMAN _STORMS. STORM_DATE

Populated based on the storm level NCG, storm name, and storm name suffix from the CD_EXCL_DETAIL table.

EXCL_START_DATE_KEY

Exclusion Period Start Date Key

STORMMAN_STORMS. EX_STORM_START

Populated based on the exclusion detail start date.

EXCL _END_DATE_KEY

Exclusion Period End Date Key

STORMMAN_STORMS. EX_STORM_END

Populated based on the exclusion detail end time.

EXCL_START_TIME_KEY

Exclusion Period Start Time Key

STORMMAN_STORMS. EX_STORM_START

Populated based on the exclusion detail start time.

EXCL _END_TIME_KEY

Exclusion Period End Time Key

STORMMAN_STORMS. EX_STORM_END

Populated based on the exclusion detail end time.

STORM_UDD1_KEY

Storm User Defined Dimension 1 Surrogate Key

STORM_UDD2_KEY

Storm User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Switch Plan

Supports KPIs, such as number of steps, number of aborted/failed steps, and number of safety documents.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_SW_PLAN

Table Type

Fact

Fact Type

Accumulation

Driver Table Name

SWMAN_SHEET S

ODI Package Name

B1_PKG_CF_SW_PLAN

ETL View Name

B1_F_SW_PLAN_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

SW_PLAN_F_KEY

Switch Plan Fact Surrogate Key

Populated with the sequence from SPL_SW_PLAN_SEQ.

Natural Keys

SRC_SW_PLAN_CLS

Switch Plan Class

SWMAN_SHEET.SWITCH_SHEET_CLS

SRC_SW_PLAN_IDX

Switch Plan Index

SWMAN_SHEET.SWITCH_SHEET_IDX

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

DURATION

Duration

Populated with the difference (in minutes) between Switch Plan Begin Date and End Date.

(Switch Plan End - Switch Plan Begin) * 1440

NBR_OF_STEPS

Number of Steps

Populated with the number of steps present in SWMAN_STEP (SWMAN_STEP.COUNT(*)) for a switch plan class and index.

NBR_OF_FAILED_STEPS

Number of Failed Steps

Populated with the number of failed switching plan steps.

NBR_OF_ABORTED_STEPS

Number of Aborted Steps

Populated with the number of aborted switching plan steps.

NBR_OF_SAFETY_DOCS

Number of Safety Documents

SWMAN_SAFETY_DOCS.ID

Degenerate Dimensions

BEGIN_DTTM

Begin Date/Time

Switch Plan Begin.WHEN_ISSUED

END_DTTM

End Date/Time

Switch Plan End.WHEN_EXECUTED

FINISH_DTTM

Finish Date/Time

SWMAN_SHEET.FINISH_DATE

Foreign Keys

BEGIN_DATE_KEY

Begin Date Key

Populated based on the date part of switch plan begin WHEN_ISSUED.

BEGIN_TIME_KEY

Begin Time Key

Populated based on the time part of switch plan begin WHEN_ISSUED.

END_DATE_KEY

End Date Key

Populated based on the date part of Switch Plan End.WHEN_EXECUTED.

END_TIME_KEY

End Time Key

Populated based on the time part of Switch Plan End.WHEN_EXECUTED.

FINISH_DATE_KEY

Finish Date Key

Populated based on the date part of SWMAN_SHEET.FINISH_DATE.

FINISH_TIME_KEY

Finish Time Key

Populated based on the time part of SWMAN_SHEET.FINISH_DATE.

SW_PLAN_KEY

Switch Plan Key

Populated based on: SWMAN_SHEET.SWITCH_SHEET_CLS

SWMAN_SHEET.SWITCH_SHEET_IDX

SW_PLAN_STATE_KEY

Switch Plan State Key

Populated based on SWMAN_SHEET.STATE_KEY.

USER_KEY1

User1

Populated with ‘0’.

USER_KEY2

User2

Populated with ‘0’.

USER_KEY3

User3

Populated with ‘0’.

USER_KEY4

User4

Populated with ‘0’.

USER_KEY5

User5

Populated with ‘0’.

USER_KEY6

User6

Populated with ‘0’.

USER_KEY7

User7

Populated with ‘0’.

USER_KEY8

User8

Populated with ‘0’.

CTRL_ZONE_ KEY

Control Zone Dimension Surrogate Key

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

CTRL_ZONE_SEC_KEY

Control Zone Sec Dimension Surrogate Key

Populate from CD_CTRL_ZONE_SEC dimension's surrogate key based on NCG ID and records effective date/time is between effective start and end date/time of dimension.

SW_PLAN_UDD1_KEY

Switch Plan User Defined Dimension 1 Surrogate Key

SW_PLAN_UDD2_KEY

Switch Plan User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Switch Plan State

Captures details about the crews working on jobs with measures, such as in-route duration, work duration, and dispatch duration.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_SW_PLAN_STATE

Table Type

Fact

Fact Type

Accumulation

Driver Table Name

SWMAN_AUDIT_LOG SAL1

ODI Package Name

B1_PKG_CF_SW_PLAN_STATE

ETL View Name

B1_F_SW_PLAN_STATE_VW

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

SW_PLAN_STATE_F_KEY

Switch Plan State Fact Surrogate Key

Populated with the sequence from SPL_SW_PLAN_STATE_SEQ.

Natural Keys

SRC_LOG_ENTRY

Switch Plan Log Entry ID

SWMAN_AUDIT_LOG.SEQ_LOG_ID

Measures

FACT_CNT

Count

Populated with the standard value of ‘1’.

STATE_DURATION

Duration of the Switch Plan State

Degenerate Dimensions

BEGIN_DTTM

Begin Date/Time

SWMAN_AUDIT_LOG.INSERT_TIME

END_DTTM

End Date/Time

SWMAN_AUDIT_LOG.INSERT_TIME

Populated with the difference between SWMAN_AUDIT_LOG.INSERT_TIME when state change happens * 1440.

Foreign Keys

BEGIN_DATE_KEY

Begin Date Key

Populated based on the switch plan begin date.

BEGIN_TIME_KEY

Begin Time Key

Populated based on the switch plan begin time.

END_DATE_KEY

End Date Key

Populated based on the switch plan end date.

END_TIME_KEY

End Time Key

Populated based on the switch plan end time.

SW_PLAN_KEY

Switch Plan Key

Populated based on jobs SWSHEET_IDX and SWSHEET_CLS.

SW_PLAN_STATE_KEY

Switch Plan State Key

Populated based on the switch plan state key.

USER_KEY

User Key

Populated based on the CES user name.

CTRL_ZONE_ KEY

Control Zone Dimension Surrogate Key

Populated based on the primary control zone hierarchy of NCG from the DDS_ALARM_LOG table.

CTRL_ZONE_SEC_KEY

Control Zone Sec Dimension Surrogate Key

Populated from CD_CTRL_ZONE_SEC dimension's surrogate key based on NCG ID and records effective date/time is between effective start and end date/time of dimension.

SW_PLAN_STATE_UDD1_KEY

Switch Plan State User Defined Dimension 1 Surrogate Key

SW_PLAN_STATE_UDD2_KEY

Switch Plan State User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

UDM11

User Defined Measure 11

UDM12

User Defined Measure 12

UDM13

User Defined Measure 13

UDM14

User Defined Measure 14

UDM15

User Defined Measure 15

UDM16

User Defined Measure 16

UDM17

User Defined Measure 17

UDM18

User Defined Measure 18

UDM19

User Defined Measure 19

UDM20

User Defined Measure 20

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 Surrogate Key 1

UDDFK2_KEY

User Defined Dimension Surrogate Key 2

UDDFK3_KEY

User Defined Dimension Surrogate Key 3

UDDFK4_KEY

User Defined Dimension Surrogate Key 4

UDDFK5_KEY

User Defined Dimension Surrogate Key 5

Data Load Attributes

DATA_SOURCE_IND

Data Source Indicator

CES_PARAMETER.VALUE

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 source system.

JOB_NBR

Job Number

Populated with the ODI job execution session number.

Views

This section provides information about the views created in start schema for the Oracle Utilities Network Management System source tables.

Job Notes

The fields in the B1_JOB_NOTES_VW view are as follows:

Field

Type

Description

DATA_SOURCE_IND

NUMBER

Data Source Indicator

EVENT_IDX

NUMBER

Event IDX

ID

NUMBER

ID

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

LOG_TIME

DATE

Log Time

NOTE

VARCHAR2(4000)

Note

USER_ID

VARCHAR2(32)

User ID

Estimated Restoration Time Change Log

The fields in the B1_ERT_CHANGE_LOG_VW view are as follows:

Field

Type

Description

DATA_SOURCE_IND

NUMBER

Data Source Indicator

ERT

DATE

ERT

EVENT_IDX

NUMBER

Event IDX

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

NUMB

NUMBER

Call Number

PREV_ERT

DATE

Previous ERT

PREV_SOURCE

VARCHAR2(1)

Previous Source

SOURCE

VARCHAR2(1)

Source

TIME_OF_EVENT

DATE

Time of Event

USERNAME

VARCHAR2(32)

User Name

Crew Vehicles

The fields in the B1_CREW_VEHICLES_VW view are as follows:

Field

Type

Description

ACTIVE

VARCHAR2(1)

Active Status

CREW_CENTER

NUMBER

Crew Center

DATA_SOURCE_IND

NUMBER

Data Source Indicator

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

VEHICLE_ID

NUMBER

Vehicle ID

VEHICLE_NUMB

VARCHAR2(10)

Vehicle Number

VEHICLE_TYPE

NUMBER

Vehicle Type

Crew Vehicle Mapping

The fields in the B1_CREW_VEHICLE_MAPPING_VW view are as follows:

Field

Type

Description

CREW_ID

NUMBER

Crew ID

DATA_SOURCE_IND

NUMBER

Data Source Indicator

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_EFF_END_DTTM

DATE

JRN Effective End DTTM

JRN_EFF_START_DTTM

DATE

JRN Effective Start DTTM

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

VEHICLE_ID

NUMBER

Vehicle ID

Crew Vehicle Types

The fields in the B1_CREW_VEHICLE_TYPES_VW view are as follows:

Field

Type

Description

DATA_SOURCE_IND

NUMBER

Data Source Indicator

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

TYPE_NUM

NUMBER

Type Number

VEHICLE_TYPE

VARCHAR2(31)

Vehicle Type

Switchman Audit Log

The fields in the B1_SWMAN_AUDIT_LOG_VW view are as follows:

Field

Type

Description

AUDIT_LOG_ENTRY_TYPE

VARCHAR2(100)

Audit Log Entry Type

DATA_SOURCE_IND

NUMBER

Data Source Indicator

DEV_CLS

NUMBER

Dev CLS

DEV_IDX

NUMBER

Dev IDX

DEVICE_ALIAS

VARCHAR2(100)

Device Alias

INSERT_TIME

DATE

Insert Time

INSERT_USER

VARCHAR2(32)

Insert User

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

PHASES

NUMBER

Phases

LOG_COMMENT

VARCHAR2(1024)

Log Comment

LOG_ORDER

NUMBER

Log Order

REVISION

NUMBER

Revision

SAFETY_DOC_ID

NUMBER

Safety Doc ID

SEQ_SHEET_ID

NUMBER

Sequence Sheet ID

SEQ_LOG_ID

NUMBER

Sequence Log ID

STATE

NUMBER

State

STEP_IDX

NUMBER

Step IDX

USER_LOG

CHAR(1)

User Log

Switchman Implementation Supply Nodes

The fields in the B1_SWMAN_IMP_SUPPLY_NODES_VW view are as follows:

Field

Type

Description

BEGIN_STEP_IDX

NUMBER

Begin Step IDX

DATA_SOURCE_IND

NUMBER

Data Source Indicator

END_STEP_IDX

NUMBER

End Step IDX

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

SEQ_ISN_ID

NUMBER

Seq ISN ID

SEQ_SHEET_ID

NUMBER

Sequence Sheet ID

SUPPLY_IDX

NUMBER

Supply IDX

Switchman Safety Documents

The fields in the B1_SWMAN_SAFETY_DOCS_VW view are as follows:

Field

Type

Description

CREW_KEY

NUMBER

Crew Key

DATA_SOURCE_IND

NUMBER

Data Source Indicator

DCZNUM

NUMBER

DCZVALIDATED

VARCHAR2(4000)

DELETED

VARCHAR2(1)

Deleted

DESCRIPTION

VARCHAR2(4000)

Description

DOC_CLS

NUMBER

Document CLS

DOC_IDX

NUMBER

Document Index

DOC_TYPE

VARCHAR2(32)

Document Type

EXTERNALSAFETYDOCID

VARCHAR2(4000)

External Safety Document ID

FEEDER_NAME

VARCHAR2(4000)

Feeder Name

ID

NUMBER

ID

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

LAST_UPDATE

NUMBER

Last Update

NON_MODELED_DEVICES

VARCHAR2(4000)

Non Modeled Devices

STATE_KEY

NUMBER

State Key

STUDY_STATE_KEY

NUMBER

Study State Key

SW_PLAN_CLS

NUMBER

Switch Plan CLS

SW_PLAN_IDX

NUMBER

Switch Plan IDX

Switchman Steps

The fields in the B1_SWMAN_STEP_VW view are as follows:

Field

Type

Description

ACT_KEY

NUMBER

Act Key

ACTIVE

VARCHAR2(1)

Active

AVAILABLE_PHASES

NUMBER

Available Phases

COMMENTS

VARCHAR2(255)

Comments

COND_CLS

NUMBER

Cond CLS

COND_IDX

NUMBER

Cond IDX

CONDITION_CONDITION_CLASS

VARCHAR2(4000)

Condition Class

CONDITION_CONDITION_TYPE

VARCHAR2(4000)

Condition Type

CONDITION_DATE

DATE

Condition Date

CONDITION_DETAILS

VARCHAR2(4000)

Condition Details

CONDITION_PHASE_ANNOTATION

VARCHAR2(4000)

Condition Phase Annotation

CONDITION_LOCATION

VARCHAR2(4000)

Condition Location

CONDITION_STATUS

VARCHAR2(4000)

Condition Status

CONDITION_SWITCH_CLS

VARCHAR2(4000)

Condition Switch CLS

CONDITION_SWITCH_IDX

VARCHAR2(4000)

Condition Switch Index

CONDITION_TEXT

VARCHAR2(4000)

Condition Text

CONDITION_USER

VARCHAR2(4000)

Condition User

CONTROL_TIME

DATE

Control Time

DATA_SOURCE_IND

NUMBER

Data Source Indicator

DETAILS

VARCHAR2(4000)

Details

DEV_CLS

NUMBER

Dev CLS

DEV_IDX

NUMBER

Dev IDX

DEVICE_ALIAS

VARCHAR2(100)

Device Alias

EDITED_OPERATION

VARCHAR2(4000)

Edited Operation

EXECUTE_ORDER

NUMBER

Execute Order

EXECUTED_BY

VARCHAR2(255)

Executed By

EXECUTED_TIME

DATE

Executed Time

FINISH_DATE

DATE

Finished Date

GND_NODE_CLS

NUMBER

GND Node CLS

GND_NODE_IDX

NUMBER

GND Node IDX

HIDE_STEP

VARCHAR2(1)

Hide Step

INCIDENT_DATA

VARCHAR2(4000)

Incident Data

INSERTSTATUS

VARCHAR2(4000)

Insert Status

INSERT_TIME

DATE

Insert Time

INSERT_USER

VARCHAR2(32)

Insert User

INSTRUCT_CONDITION_IDX

NUMBER

Instruct Condition IDX

INSTRUCTED_CREW_ID

NUMBER

Instructed Crew ID

INSTRUCTED_TIME

DATE

Instructed Time

INSTRUCTED_USER

VARCHAR2(32)

Instructed User

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

LAST_RESULT_OF_OPERATION

VARCHAR2(4000)

Last Result of Operation

LOCATION

VARCHAR2(4000)

Location

MB_EDIT

VARCHAR2(1)

MB Edit

NCG

NUMBER

NCG

OPERATION_OUTCOME

VARCHAR2(4000)

Operation Outcome

PARENT_STEP_ID

NUMBER

Parent Step ID

PHASES

NUMBER

Phases

PLANNED_OFFSET

NUMBER

Planned Offset

PREV_STEP_IDX

NUMBER

Previous Step Index

RESULT_FEEDERS

VARCHAR2(4000)

Result Feeders

RESULT_OF_OPERATION

VARCHAR2(4000)

Result of Operation

REVISION

NUMBER

Revision

REVISION_DELETED

NUMBER

Revision Deleted

SAFETY_COND_ADDED

NUMBER

Safety Condition Added

SAFETY_COND_REMOVED

NUMBER

Safety Condition Removed

SAFETY_DEV_STATUS

VARCHAR2(20)

Safety Dev Status

SAFETY_DOC_ID

NUMBER

Safety Doc ID

SAFETY_INFO_TEXT

VARCHAR2(1024)

Safety Information Text

SEC_DEV_CLS

NUMBER

Sec Dev CLS

SEC_DEV_IDX

NUMBER

Sec Dev Index

SEQ_BLOCK_ID

NUMBER

Sequence Block ID

SEQ_SHEET_ID

NUMBER

Sheet SEQ ID

SET_VALUE

NUMBER

Set Value

SRS_BITMASK

NUMBER

SRS Bitmask

START_DATE

DATE

Start Date

STATE_KEY

NUMBER

State Key

STATE_TRIGGER

VARCHAR2(4000)

State Trigger

STEP_CLS

NUMBER

Step CLS

STEP_DESCRIPTION

VARCHAR2(255)

Step Description

STEP_GROUP_NUMBER

NUMBER

Step Group Number

STEP_IDX

NUMBER

Step Index

STEP_ORDER

NUMBER

Step Order

STUDY_SAFETY_DEV_STATUS

VARCHAR2(20)

Study Safety Dev Status

STUDY_STATE_KEY

NUMBER

Study State Key

TYPE_NUM

NUMBER

Type Number

UNDO_OPERATION_OUTCOME

VARCHAR2(4000)

Undo Operation Outcome

UPDATE_TIME

DATE

Update Time

UPDATE_USER

VARCHAR2(32)

Update User

VEHICLE_TYPE

VARCHAR2(31)

Vehicle Type

VERSION

TIMESTAMP(6)

Version

Switchman Sheet

The fields in the B1_SWMAN_SHEET_VW view are as follows:

Field

Type

Description

ALTERNATEFINISHDATE

DATE

Alternate Finish Date

ALTERNATESTARTDATE

DATE

Alternate Start Date

CHARGENUMBER1

VARCHAR2(4000)

Charge Number 1

CHARGENUMBER2

VARCHAR2(4000)

Charge Number 2

CHARGENUMBER3

VARCHAR2(4000)

Charge Number 3

CHECKED_IN

VARCHAR2(1)

Checked in

COMPLETED_DATE

DATE

Completed Date

CREWWILLRESTORE

VARCHAR2(4000)

Crew Will Restore

CREWWILLSWITCH

VARCHAR2(4000)

Crew Will Switch

CUSTOMERREQUESTEDEMAILADDRESS

VARCHAR2(4000)

Customer Requested Email ID

CUSTOMERREQUESTEDPHONENUMBER

VARCHAR2(4000)

Customer Requested Phone Number

CUSTOMERREQUESTEDNAME

VARCHAR2(4000)

Customer Requested Name

DATA_SOURCE_IND

NUMBER

Data Source Indicator

DEFAULT_OFFSET

NUMBER

Default Offset

DESCRIPTION

VARCHAR2(4000)

Description

DELETED

VARCHAR2(1)

Deleted

DEVICE_ALIAS

VARCHAR2(64)

Device Alias

DEVICE_CLS

NUMBER

Device CLS

DEVICE_IDX

NUMBER

Device IDX

EDIT_MODE

NUMBER

Edit Mode

EXTERNALSHEETID

VARCHAR2(4000)

External Sheet ID

FEEDER_NAME

VARCHAR2(4000)

Feeder Name

FINISH_DATE

DATE

Finish Date

GROUNDSREQUIRED

VARCHAR2(4000)

Grounds Required

INSERT_TIME

DATE

Insert Time

INSERT_USER

VARCHAR2(100)

Insert User

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

LAST_SELECTED_STEP_IDX

NUMBER

Last Selected Step IDX

LOCATION

VARCHAR2(4000)

Location

LOCKED_TIME

DATE

Locked Time

LOCKED_USER

VARCHAR2(255)

Locked User

MB_EDIT

VARCHAR2(1)

MB Edit

NCG

NUMBER

NCG

NUMBER_OF_STEPS

NUMBER

Number of Steps

OFFICE

VARCHAR2(4000)

Office

OWNER

VARCHAR2(32)

Owner

REQUESTEREMAILADDRESS

VARCHAR2(4000)

Requester’s Email Address

REQUESTERPHONENUMBER

VARCHAR2(4000)

Requester’s Phone Number

REVISION

NUMBER

Revision

REWORK_DESCRIPTION

VARCHAR2(55)

Rework Description

REWORK_TIME

DATE

Rework Time

SAS_DOC_TYPE

VARCHAR2(32)

SAS Doc Type

SEQ_SHEET_ID

NUMBER

SEQ Sheet ID

START_DATE

DATE

Start Date

STATE_KEY

NUMBER

State Key

SUBSTATION

VARCHAR2(4000)

Substation

SWITCH_SHEET_CLS

NUMBER

Switch Sheet CLS

SWITCH_SHEET_IDX

NUMBER

Switch Sheet Index

UPDATE_TIME

DATE

Update TIME

UPDATE_USER

VARCHAR2(100)

Update User

VERSION

NUMBER

Version

VOLTAGE

VARCHAR2(4000)

Voltage

Switchman Sheet CLS

The fields in the B1_SWMAN_SHEET_CLS_VW view are as follows:

Field

Type

Description

DATA_SOURCE_IND

NUMBER

Data Source Indicator

DESCRIPTION

VARCHAR2(1024)

Description

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

SHEET_CATEGORY

NUMBER

Sheet Category

SHEET_UI_PANEL

VARCHAR2(255)

Sheet UI Panel

SWITCH_CLS_IDX_POOL

NUMBER

Switch CLS IDX Pool

SWITCH_SHEET_CLS

NUMBER

Switch Sheet CLS

SWITCH_SHEET_

DISPLAY_ORDER

NUMBER

Switch Sheet Display Order

SWITCH_SHEET_TYPE

VARCHAR2(100)

Switch Sheet Type

Switchman Sheet Documents

The fields in the B1_SWMAN_SHEET_DOCUMENTS_VW view are as follows:

Field

Type

Description

DATA

BLOB

Data

DATA_SOURCE_IND

NUMBER

Data Source Indicator

DESCRIPTION

VARCHAR2(4000)

Description

DOCUMENT_ID

NUMBER

Document ID

FILE_MODIFIED_DATE

TIMESTAMP(6)

File Modified Date

FILE_NAME

VARCHAR2(64)

File Name

JRN_CLEANSED_FLAG

CHAR(1)

JRN Cleansed Flag

JRN_FLAG

CHAR(1)

JRN Flag

JRN_SCN

NUMBER

JRN SCN

JRN_SLICING_TS

DATE

JRN Slicing TS

JRN_UPDATE_DTTM

DATE

JRN Update DTTM

LENGTH

NUMBER

Length

SEQ_SHEET_ID

NUMBER

SEQ Sheet ID