Oracle Utilities Extractors and Schema for Oracle Utilities Mobile Workforce Management

Data Mapping Guide

Release 2.7.0

E70889-01

March 2017

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

E70889-01

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

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

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

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

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

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

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

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

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

Preface

This guide provides the data mapping information from the Oracle Utilities Extractors and Schema for Oracle Utilities Mobile Workforce Management source system to the Oracle Utilities Extractors and Schema target product.

Audience

The guide is intended for all implementers of Oracle Utilities Extractors and Schema for Oracle Utilities Extractors and Schema for Oracle Utilities Mobile Workforce Management.

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 documents:

•

Oracle Utilities Analytics Release Notes

•

Oracle Utilities Analytics Getting Started Guide

•

Oracle Utilities Analytics Quick Install Guide

•

Oracle Utilities Analytics Installation Guide

•

Oracle Utilities Analytics Administration Guide

•

Oracle Utilities Analytics Developer’s Guide

See Also :

•

Oracle Mobile Workforce Management 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

Acronyms

The following acronyms are used in this document:

•

MDM - Oracle Utilities Meter Data Management

•

MWM - Oracle Utilities Mobile Workforce Management

•

CCB - Oracle Utilities Customer Care and Billing

•

NMS - Oracle Utilities Network Management System

•

ETL - Extraction, Transformation, and Loading

•

ODI - Oracle Data Integrator

•

OBIEE - Oracle Business Intelligence Enterprise Edition

•

OUAF - Oracle Utilities Application Framework

Overview

This guide provides the data mapping information from the Oracle Utilities Mobile Workforce Management 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>

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

Properties

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

Property

Value

Target Table

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

Table Type

Fact or Dimension

SCD Type

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

Type 2 - When changes occur, a new effective row is inserted. The existing records are deactivated by entering the current date as the effective end date and new records are inserted with an effective start date as today’s date. The new record will have the end date as a high date.

Fact Type

Whether this is a snapshot or accumulation fact table

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

Source System Download Table

Name of the download table populated by the source batch program.

Note that this property is applicable only for the snapshot facts for which special batch programs have been designed in the source system for data extraction.

Stage Table

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

Oracle Data Integrator Package

Name of the ODI package that needs to be executed to transform data from the replication layer 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.

Source Batch Program

The batch program that needs to be executed in the MDM system to extract the data into download tables. This is available only for the MDM snapshot facts.

Fields

The Fields table lists the individual properties of each field in the Presentation table or the Database table. The following fields are listed in the tables:

Property

Value

Target Field

Name of the column in the fact or dimension table 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

This table lists the categories under which the fields in a particular fact or dimension are grouped. The categories presented 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 value based data 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 grouped 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 Mobile Workforce Management

This chapter contains data maps for the following Oracle Utilities Extractors and Schema for Oracle Utilities Mobile Workforce Management tables:

•

Dimensions

•

Shared Dimensions

•

Facts

Dimensions

This section lists all the dimension tables belonging to the Oracle Utilities Mobile Workforce Management star schemas only and are populated only from the Oracle Utilities Mobile Workforce Management source product.

Appointment Time Dimension

The Appointment Time dimension stores the time ranges in minutes indicating whether the crew missed an appointment, and if so, by how much time.

Properties

Property

Value

Target Table

CD_APPT_TM

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_APPT_TM

ODI Package

B1_PKG_CD_APPT_TM

ETL View

B1_D_APPT_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

APPT_TM_KEY

Appointment Time Dimension Surrogate Key

This field is populated from the OUBI_APPT_TM_SEQ sequence.

Natural Keys

START_RANGE

Appointment Time Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

This field is populated with the bucket start range based on the bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for appointment time.

END_RANGE

Appointment Time End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

The field is populated with the bucket end range based on the bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for appointment time.

Attributes

APPT_TM_DESCR

Appointment Time Description

F1_BKT_CONFIG_VAL_L.DESCR

APPT_STATUS_DESCR

Appointment Status Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from Lookup Language table for M1_APPT_STATUS_FLG lookup field.

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

APPT_STATUS_CD

Appointment Status Code

F1_BKT_CONFIG_VAL.BKT_VAL_TYPE_CD

APPT_TM_CD

Appointment Time Code

Note : This field is obsolete and is not populated. The new bucket configurations based on the OUAF bucket configuration tables do not need this.

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 and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Note : This bucket dimension is configured as one time load only. For information about data reload, see Reloading the Bucket Configuration section in the Oracle Utilities Analytics Administration Guide.

Appointment Time of Day Dimension

The Appointment Time of Day dimension stores time ranges classifying an appointment time of day.

Properties

Property

Value

Target Table

CD_APPT_TM_OF_DAY

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_APPT_TM_OF_DAY

ODI Package

B1_PKG_CD_APPT_TM_OF_DAY

ETL View

B1_D_APPT_OF_DAY_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

APPT_TM_OF_DAY_KEY

Appointment Time of Day Dimension Surrogate Key

This field is populated from the OUBI_APPT_TM_OF_DAY_SEQ sequence.

Natural Keys

START_RANGE

Appointment Time of Day Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

The field is populated with the bucket start range based on the bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for appointment time of day.

END_RANGE

Appointment Time of Day End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

The field is populated with the bucket end range based on the bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for appointment time of day.

Attributes

APPT_TM_OF_DAY_DESCR

Appointment Time of Day Description

F1_BKT_CONFIG_VAL_L.DESCR

APPT_TM_OF_DAY_CD

Appointment Time Of Day Code

Note : This field is obsolete and is not populated. The new bucket configurations based on the OUAF bucket configuration tables do not need this.

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 and Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Note : This bucket dimension is configured as one time load only. For information about data reload, see the Reloading the Bucket Configuration section in Oracle Utilities Analytics Administration Guide.

Crew Shift Dimension

The Crew Shift dimension includes the details of all crew shifts defined in the Oracle Utilities Extractors and Schema for Oracle Utilities Mobile Workforce Management system.

Properties

Property

Value

Target Table

CD_CREW_SHIFT

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

M1_CREW_SHFT

F1_BUS_OBJ

Stage Table

STG_CD_CREW_SHIFT

ODI Package

B1_PKG_CD_CREW_SHIFT

ETL View

B1_D_CREW_SHIFT_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CREW_SHIFT_KEY

Crew Shift Surrogate Key

This field is populated from the SPL_CREW_SHIFT_SEQ sequence.

Natural Keys

SRC_CREW_SHIFT_ID

Crew Shift ID

M1_CREW_SHFT.CREW_SHFT_ID

Attributes

CREW_SHIFT_TYPE_DESCR

Crew Shift Type Description

M1_CREW_SHFT_TYPE_L.DESCR

PRIMARY_FN_DESCR

Primary Function Description

M1_SVC_CLS_L.DESCR

CREW_SHIFT_INFO

Crew Shift Description

M1_RESRC_ID.RESRC_ID_VAL

M1_CREW_SHFT_TYPE_L.DESCR

M1_CREW_SHFT.PLAN_START_DTTM

M1_CREW_SHFT.PLAN_END_DTTM

F1_BUS_OBJ_STATUS_L.DESCR

This field is a concatenation of crew name, crew shift type, planned start date, planned end date, and shift status.

CREW_SHIFT_TYPE_CD

Crew Shift Type Code

M1_CREW_SHFT.CREW_SHFT_TYPE_CD

PRIMARY_FN_CD

Primary Function Code

M1_CREW_SHFT_SVC_CLS.SVC_CLS_CD

This field is populated from the Crew Shift Service Class table based on the usage flag (set as 'Primary') associated with the current shift ID.

User Defined Attributes

UDF1_CD

Shift Type

UDF1_DESCR

Shift Type Code

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

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_END_DTTM

Effective End Date/Time

EFF_START_DTTM

Effective Start Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Crew Time Usage Dimension

The Crew Time Usage dimension holds all valid values for crew time usages based on the tasks carried out by crews. The crew time usages are further classified as productive or non productive.

Properties

Property

Value

Target Table

CD_CREW_TM_USG

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_EXT_LOOKUP_VAL

Stage Table

STG_CD_CREW_TM_USG

ODI Package

B1_PKG_CD_CREW_TM_USG

ETL View

B1_D_CREW_TM_USG_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CREW_TM_USG_KEY

Crew Time Usage Dimension Surrogate Key

This field is populated from the OUBI_CREW_TM_USG_SEQ sequence.

Natural Keys

CREW_TM_USG_CD

Crew Time Usage Code

F1_EXT_LOOKUP_VAL.F1_EXT_LOOKUP_VALUE

This field extracts the records defined only for the M1-CrewTimeUsageLookup lookup business object.

Attributes

CREW_TM_USG_DESCR

Crew Time Usage Description

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

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

PRODUCTIVITY_DESCR

Productivity Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with description from the Lookup Language table for the M1_CREW_TM_USG_CLS_FLG lookup field.

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

PRODUCTIVITY_CD

Productivity Code

F1_EXT_LOOKUP_VAL.BO_DATA_AREA

This field is populated with the productivity code from the CLOB column.

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

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

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Early Logoff Time Dimension

The Early Logoff Time dimension stores the time ranges (in minutes) indicating by how much time the crew is logging off the shift earlier than planned.

Properties

Property

Value

Target Table

CD_EARLY_LOGOFF_TM

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_EARLY_LOGOFF_TM

ODI Package

B1_PKG_CD_EARLY_LOGOFF_TM

ETL View

B1_D_EARLY_LOGOFF_TM_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

EARLY_LOGOFF_TM_KEY

Early Logoff Time Dimension Surrogate Key

This field is populated from the OUBI_EARLY_LOGOFF_TM_SEQ sequence.

Natural Keys

START_RANGE

Early Logoff Time Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

The field is populated with the bucket start range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for early logoff time.

END_RANGE

Early Logoff Time End Ran

F1_BKT_CONFIG_VAL.BKT_END_RANGE

The field is populated with the bucket end range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for early log off time.

Attributes

EARLY_LOGOFF_TM_DESCR

Early Logoff Time Description

F1_BKT_CONFIG_VAL_L.DESCR

CONSIDERED_ONTIME_CD

Considered On Time Code

F1_BKT_CONFIG_VAL.BKT_VAL_TYPE_CD

CONSIDERED_ONTIME_DESCR

Considered On Time Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with description from the Lookup Language table for the M1_CONSD_ON_TIME_FLG lookup field.

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

EARLY_LOGOFF_TM_CD

Early Logoff Time Code

Note : This field is obsolete and is not be populated. The new bucket configurations based on the OUAF bucket configuration tables do not need this.

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

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

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Note : This bucket dimension is configured as one time load only. For information about data reload, see Reloading the Bucket Configuration section in the Oracle Utilities Analytics Administration Guide.

Late Logon Time Dimension

The Late Logon Time dimension stores the time ranges (in minutes) indicating by how much time the crew is late to logon for their shift.

Properties

Property

Value

Target Table

CD_LATE_LOGON_TM

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_LATE_LOGON_TM

ODI Package

B1_PKG_CD_LATE_LOGON_TM

ETL View

B1_D_LATE_LOGON_TM_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

LATE_LOGON_TM_KEY

Late Logon Time Dimension Surrogate Key

This field is populated from the OUBI_LATE_LOGON_TM_SEQ sequence.

Natural Keys

START_RANGE

Late Logon Time Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

The field is populated with the bucket start range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for late logon time.

END_RANGE

Late Logon Time End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

The field is populated with the bucket end range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for late logon time.

Attributes

LATE_LOGON_TM_DESCR

Late Logon Time Description

F1_BKT_CONFIG_VAL_L.DESCR

CONSIDERED_ONTIME_CD

Considered On Time Code

F1_BKT_CONFIG_VAL.BKT_VAL_TYPE_CD

CONSIDERED_ONTIME_DESCR

Considered On Time Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with description from the Lookup Language table for the M1_CONSD_ON_TIME_FLG lookup field.

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

LATE_LOGON_TM_CD

Late Logon Time Code

Note : This field is obsolete now and is not populated. The new bucket configurations based on the OUAF bucket configuration tables do not need this.

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

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

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Note : This bucket dimension is configured as one time load only. For information about data reload, see Reloading the Bucket Configuration section in the Oracle Utilities Analytics Administration Guide.

Response Time Deviation Dimension

The Response Time Deviation dimension stores the configurable ranges (in minutes) for the deviations in the response time of crews against the SLAs defined for various activities. It has an indicator to tell if the SLA is met or missed.

Properties

Property

Value

Target Table

CD_RESP_TM_DEV

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_RESP_TM_DEV

ODI Package

B1_PKG_CD_RESP_TM_DEV

ETL View

B1_D_RESP_TM_DEV_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

RESP_TM_DEV_KEY

Response Time Deviation Dimension Surrogate Key

This field is populated from the OUBI_RESP_TM_DEV_SEQ sequence.

Natural Keys

START_RANGE

Response Time Deviation Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

The field is populated with the bucket start range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for response time deviation.

END_RANGE

Response Time Deviation End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

The field is populated with the bucket end range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for response time deviation.

Attributes

RESP_TM_DEV_DESCR

Response Time Deviation Description

F1_BKT_CONFIG_VAL_L.DESCR

SLA_STATUS_DESCR

SLA Status Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with description from the Lookup Language table for the M1_SLA_STATUS_FLG lookup field.

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

SLA_STATUS_CD

SLA Status Code

F1_BKT_CONFIG_VAL.BKT_VAL_TYPE_CD

RESP_TM_DEV_CD

Response Time Deviation Code

Note : This field is obsolete and is not populated. The new bucket configurations based on the OUAF bucket configuration tables do not need this.

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

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

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Note : This bucket dimension is configured as one time load only. For information about data reload, see Reloading the Bucket Configuration section in the Oracle Utilities Analytics Administration Guide.

Shift BO Status Dimension

The Shift BO Status dimension holds valid combination of status and reason for shifts.

Properties

Property

Value

Target Table

CD_SHIFT_BO_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BUS_OBJ

F1_BUS_OBJ_STATUS_RSN

Stage Table

STG_CD_SHIFT_BO_STATUS

ODI Package

B1_PKG_CD_SHIFT_BO_STATUS

ETL View

B1_D_SHIFT_BO_STATUS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SHIFT_BO_STATUS_KEY

Shift BO Status Surrogate Key

This field is populated from the OUBI_SHIFT_BO_STAT_SEQ sequence.

Natural Keys

BUS_OBJ_CD

Business Object

F1_BUS_OBJ.BUS_OBJ_CD

This field extracts all business objects of the Crew Shift maintenance object only.

STATUS_CD

BO Status Code

F1_BUS_OBJ_STATUS.BO_STATUS_CD

REASON_CD

Status Reason Code

F1_BUS_OBJ_STATUS_RSN.BO_STATUS_REASON_CD

Attributes

BUS_OBJ_DESCR

Business Object Description

F1_BUS_OBJ_L.DESCR

STATUS_DESCR

Status Description

F1_BUS_OBJ_STATUS_L.DESCR

STATUS_COND_CD

Status Condition Code

F1_BUS_OBJ_STATUS.BO_STATUS_COND_FLG

STATUS_COND_DESCR

Status Condition Description

CI_LOOKUP_VAL_L.NVL(CI_LOOKUP_VAL_L.DESCR_OVRD,CI_LOOKUP_VAL_L.DESCR)

This field is populated with description from the Lookup Language table for the BO_STATUS_COND_FLG lookup field.

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

REASON_DESCR

Status Reason Description

F1_BUS_OBJ_STATUS_RSN_L.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 7Code

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

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Task BO Status Dimension

The Task BO Status dimension holds all valid combination of status and reasons for schedulable tasks.

Properties

Property

Value

Target Table

CD_TASK_BO_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BUS_OBJ

F1_BUS_OBJ_STATUS_RSN

Stage Table

STG_CD_TASK_BO_STATUS

ODI Package

B1_PKG_CD_TASK_BO_STATUS

ETL View

B1_D_TASK_BO_STATUS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

TASK_BO_STATUS_KEY

Task BO Status Surrogate Key

This field is populated from the OUBI_TASK_BO_STAT_SEQ sequence.

Natural Keys

BUS_OBJ_CD

Business Object

F1_BUS_OBJ.BUS_OBJ_CD

This field extracts all business objects of the Device Event maintenance object only.

STATUS_CD

BO Status Code

F1_BUS_OBJ_STATUS.BO_STATUS_CD

REASON_CD

Status Reason Code

F1_BUS_OBJ_STATUS_RSN.BO_STATUS_REASON_CD

Attributes

BUS_OBJ_DESCR

Business Object Description

F1_BUS_OBJ_L.DESCR

STATUS_DESCR

Status Description

F1_BUS_OBJ_STATUS_L.DESCR

STATUS_COND_CD

Status Condition Code

F1_BUS_OBJ_STATUS.BO_STATUS_COND_FLG

STATUS_COND_DESCR

Status Condition Description

CI_LOOKUP_VAL_L.NVL(CI_LOOKUP_VAL_L.DESCR_OVRD,CI_LOOKUP_VAL_L.DESCR)

This field is populated with description from the Lookup Language table for the BO_STATUS_COND_FLG lookup field.

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

REASON_DESCR

Status Reason Description

F1_BUS_OBJ_STATUS_RSN_L.DESCR

INCOMPLETE_IND

Incomplete Indicator

F1_BUS_OBJ_STATUS_OPT.BO_OPT_VAL

This field is populated based on the state condition defined on the business object status.

If the status is ‘Postponed’, or ‘Completed’ with ‘Incomplete’ status reason, the indicator is populated as ‘1’, else ‘0’.

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

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

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Task Type Dimension

The Task Type dimension holds all task types defined in the Oracle Utilities Extractors and Schema for Oracle Utilities Mobile Workforce Management system along with their class, priority, service type, and SLA details.

Properties

Property

Value

Target Table

CD_TASK_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

M1_TASK_TYPE

Stage Table

STG_CD_TASK_TYPE

ODI Package

B1_PKG_CD_TASK_TYPE

ETL View

B1_D_TASK_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

TASK_TYPE_KEY

Task Type Dimension Surrogate Key

This field is populated from the OUBI_TASK_TYPE_SEQ sequence.

Natural Keys

TASK_TYPE_CD

Activity Type Code

M1_TASK_TYPE.TASK_TYPE_CD

Attributes

TASK_TYPE_DESCR

Task Type Description

M1_TASK_TYPE_L.DESCR

TASK_CLASS_CD

Task Class Code

M1_TASK_TYPE.TASK_CLS_FLG

PRIORITY_PROF_CD

Priority Profile Code

M1_TASK_TYPE.PRI_PROF_CD

SVC_CATEGORY_CD

Service Category Code

M1_TASK_TYPE.BO_DATA_AREA

This field is populated with the service category code from the CLOB column.

TASK_CLASS_DESCR

Task Class Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with description from the Lookup Language table for the TASK_CLS_FLG lookup field.

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

PRIORITY_PROF_DESCR

Priority Profile Description

M1_PRI_PROF_L.DESCR

SVC_CATEGORY_DESCR

Service Category Description

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

This field is populated with description from the Lookup Language table for the M1-ServiceCategoryLookup lookup business object.

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

RESP_TM_SLA

Response Time SLA

M1_TASK_TYPE.BO_DATA_AREA

This field is populated with the response time SLA from the CLOB column.

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

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

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Travel Distance Deviation Dimension

The Travel Distance Deviation dimension stores the percentage ranges for deviations of actual distance relative to estimated distance traveled by the crew.

Properties

Property

Value

Target Table

CD_TRAVEL_DIST_DEV

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_TRAVEL_DIST_DEV

ODI Package

B1_PKG_CD_TRAVEL_DIST_DEV

ETL View

B1_D_TRAVEL_DIST_DEV_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

TRAVEL_DIST_DEV_KEY

Travel Distance Deviation Dimension Surrogate Key

This field is populated from the OUBI_TRAVEL_DIST_DEV_SEQ sequence.

Natural Keys

START_RANGE

Travel Distance Deviation Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

This field is populated with the bucket start range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for travel distance deviation.

END_RANGE

Travel Distance Deviation End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

This field is populated with the bucket end range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for travel distance deviation.

Attributes

TRAVEL_DIST_DEV_DESCR

Travel Distance Deviation Description

F1_BKT_CONFIG_VAL_L.DESCR

TRAVEL_DIST_DEV_CD

Distance Deviation Code

Note : This field is obsolete and is not populated. The new bucket configurations based on the OUAF bucket configuration tables do not need this.

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

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

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Note : This bucket dimension is configured as one time load only. For information about data reload, see Reloading the Bucket Configuration section in the Oracle Utilities Analytics Administration Guide.

Travel Duration Deviation Dimension

The Travel Duration Deviation dimension stores the percentage ranges for deviations of actual travel duration relative to estimated travel duration of crews.

Properties

Property

Value

Target Table

CD_TRAVEL_DUR_DEV

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_TRAVEL_DUR_DEV

ODI Package

B1_PKG_CD_TRAVEL_DUR_DEV

ETL View

B1_D_TRAVEL_DUR_DEV_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

TRAVEL_DUR_DEV_KEY

Travel Duration Deviation Dimension Surrogate Key

This field is populated from the OUBI_TRAVEL_DUR_DEV_SEQ sequence.

Natural Keys

START_RANGE

Travel Duration Deviation Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

This field is populated with the bucket start range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for travel duration deviation.

END_RANGE

Travel Duration Deviation End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

The field is populated with the bucket end range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for travel duration deviation.

Attributes

TRAVEL_DUR_DEV_DESCR

Travel Duration Deviation

F1_BKT_CONFIG_VAL_L.DESCR

TRAVEL_DUR_DEV_CD

Travel Duration Deviation Code

Note : This field is obsolete and will not be populated. The new bucket configurations based on the OUAF bucket configuration tables do not need this.

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

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

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Note : This bucket dimension is configured as one time load only. For information about data reload, see the Reloading the Bucket Configuration section in the Oracle Utilities Analytics Administration Guide.

Work Duration Deviation Dimension

The Work Duration Deviation dimension stores the percentage ranges for deviations in actual work duration relative to estimated work duration of activities.

Properties

Property

Value

Target Table

CD_WORK_DUR_DEV

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

Stage Table

STG_CD_WORK_DUR_DEV

ODI Package

B1_PKG_CD_WORK_DUR_DEV

ETL View

B1_D_WORK_DUR_DEV_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

WORK_DUR_DEV_KEY

Work Duration Deviation Dimension Surrogate Key

This field is populated from the OUBI_WORK_DUR_DEV_SEQ sequence.

Natural Keys

START_RANGE

Work Duration Deviation Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

This field is populated with the bucket start range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for work duration deviation.

END_RANGE

Work Duration Deviation End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

The field is populated with the bucket end range based on bucket configuration code.

The bucket configuration code from the Bucket Configuration table is retrieved based on the bucket configuration BO defined for work duration deviation.

Attributes

WORK_DUR_DEV_DESCR

Work Duration Deviation Description

F1_BKT_CONFIG_VAL_L.DESCR

WORK_DUR_DEV_CD

Work Duration Deviation Code

Note : This field is obsolete and is not populated. The new bucket configurations based on the OUAF bucket configuration tables do not need this.

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

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

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Note : This bucket dimension is configured as one time load only. For information about data reload, see the Reloading the Bucket Configuration section in Oracle Utilities Analytics Administration Guide.

Shared Dimensions

The dimensions that are used in the star schemas of other source products as well are called the shared dimensions. The other products also populate these shared dimensions with their data. The data source indicator (DSI) column plays a vital role in identifying the records belonging to a specific source product.

Account Dimension

Though Account dimension is referenced in the Oracle Utilities Mobile Workforce Management facts, it is populated only by the Oracle Utilities Customer Care and Billing source product. The ETL processes of the Oracle Utilities Mobile Workforce Management facts map this dimension based on the integration between Oracle Utilities Mobile Workforce Management and Oracle Utilities Customer Care and Billing.

Since this dimension is not populated by Oracle Utilities Mobile Workforce Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing for the mapping details.

Address Dimension

The Address dimension includes a record for every address (such as location, task, POU, and resources) defined in the Oracle Utilities Mobile Workforce Management system.

This dimension is also populated by Oracle Utilities Operational Device Management, Oracle Utilities Meter Data Management, Oracle Utilities Customer Care and Billing, Oracle Utilities Network Management System, and Oracle Utilities Work and Asset Management.

Properties

Property

Value

Target Table

CD_ADDR

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

M1_TASK_ADDR

M1_LOCATION

M1_POU

M1_RESRC

Stage Table

STG_CD_ADDR

ODI Package

B1_PKG_CD_ADDR

ETL View

B1_D_ADDR_VW

Fields

Source 1 - Task Address (M1_TASK ADDR)

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ADDR_KEY

Address Dimension Surrogate Key

This field is populated from the SPL_ADDR_SEQ sequence.

Natural Keys

SRC_ADDR_ID

Premise ID

M1_TASK_ADDR.ADDRESS1

M1_TASK_ADDR.ADDRESS2

M1_TASK_ADDR.ADDRESS3

M1_TASK_ADDR.ADDRESS4

M1_TASK_ADDR.CROSS_STREET

M1_TASK_ADDR.SUBURB

M1_TASK_ADDR.CITY

M1_TASK_ADDR.COUNTY

M1_TASK_ADDR.STATE

M1_TASK_ADDR.POSTAL

M1_TASK_ADDR.COUNTRY

This natural key is the concatenation of all address attributes, in uppercase.

Attributes

ADDR_LINE1

Address Line 1

M1_TASK_ADDR.ADDRESS1

ADDR_LINE2

Address Line 2

M1_TASK_ADDR.ADDRESS2

ADDR_LINE3

Address Line 3

M1_TASK_ADDR.ADDRESS3

ADDR_LINE4

Address Line 4

M1_TASK_ADDR.ADDRESS4

ADDR_INFO

Address Information

M1_TASK_ADDR.ADDRESS1

M1_TASK_ADDR.CITY

M1_TASK_ADDR.STATE

M1_TASK_ADDR.POSTAL

This field is populated from the Task Address table based on the concatenation of the address, city, state, and postal code.

CITY

City

M1_TASK_ADDR.UPPER(CITY)

COUNTY

County

M1_TASK_ADDR.COUNTY

POSTAL

Postal Code

M1_TASK_ADDR.POSTAL

STATE_DESCR

State Description

CI_STATE_L.DESCR

COUNTRY_DESCR

Country Description

CI_COUNTRY_L.DESCR

CROSS_STREET

Cross Street

M1_TASK_ADDR.CROSS_STREET

SUBURB

Suburb

M1_TASK_ADDR.SUBURB

STATE_CD

State Code

M1_TASK_ADDR.STATE

COUNTRY_CD

Country Code

M1_TASK_ADDR.COUNTRY

GEO_CODE

Geographical Code

User Defined Attributes

UDF1_CD

City Code

M1_TASK_ADDR.UPPER(CITY)

UDF1_DESCR

City Description

M1_TASK_ADDR.UPPER(CITY)

UDF2_CD

County Code

M1_TASK_ADDR.COUNTY

UDF2_DESCR

County Description

M1_TASK_ADDR.COUNTY

UDF3_CD

Postal Code

M1_TASK_ADDR.POSTAL

UDF3_DESCR

Postal Code

M1_TASK_ADDR.POSTAL

UDF4_CD

State Code

M1_TASK_ADDR.STATE

UDF4_DESCR

State Description

CI_STATE_L.DESCR

UDF5_CD

Country Code

M1_TASK_ADDR.COUNTRY

UDF5_DESCR

Country Description

CI_COUNTRY_L.DESCR

UDF6_CD

Geo Code Value

UDF6_DESCR

Geo Code

UDF7_CD

Premise Char 1 Code

UDF7_DESCR

Premise Char 1

UDF8_CD

Premise Char 2 Code

UDF8_DESCR

Premise Char 2

UDF9_CD

Premise Char 3 Code

UDF9_DESCR

Premise Char 3

UDF10_CD

Premise Char 4 Code

UDF10_DESCR

Premise Char 4 Code

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

EFF_END_DTTM

Effective End Date/Time

EFF_START_DTTM

Effective Start Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Source 2 - Location (M1_LOCATION)

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ADDR_KEY

Address Dimension Surrogate Key

This field is populated from the SPL_ADDR_SEQ sequence.

Natural Keys

SRC_ADDR_ID

Premise ID

M1_LOCATION.ADDRESS1

M1_LOCATION.ADDRESS2

M1_LOCATION.ADDRESS3

M1_LOCATION.ADDRESS4

M1_LOCATION.CROSS_STREET

M1_LOCATION.SUBURB

M1_LOCATION.CITY

M1_LOCATION.COUNTY

M1_LOCATION.STATE

M1_LOCATION.POSTAL

M1_LOCATION.COUNTRY

This natural key is the concatenation of all address attributes, in uppercase.

Attributes

ADDR_LINE1

Address Line 1

M1_LOCATION.ADDRESS1

ADDR_LINE2

Address Line 2

M1_LOCATION.ADDRESS2

ADDR_LINE3

Address Line 3

M1_LOCATION.ADDRESS3

ADDR_LINE4

Address Line 4

M1_LOCATION.ADDRESS4

ADDR_INFO

Address Information

M1_LOCATION.ADDRESS1

M1_LOCATION.CITY

M1_LOCATION.STATE

M1_LOCATION.POSTAL

This field is populated from the Location table based on the concatenation of the address, city, state, and postal code.

CITY

City

M1_LOCATION.UPPER(CITY)

COUNTY

County

M1_LOCATION.COUNTY

POSTAL

Postal Code

M1_LOCATION.POSTAL

STATE_DESCR

State Description

CI_STATE_L.DESCR

COUNTRY_DESCR

Country Description

CI_COUNTRY_L.DESCR

CROSS_STREET

Cross Street

M1_LOCATION.CROSS_STREET

SUBURB

Suburb

M1_LOCATION.SUBURB

STATE_CD

State Code

M1_LOCATION.STATE

COUNTRY_CD

Country Code

M1_LOCATION.COUNTRY

GEO_CODE

Geographical Code

User Defined Attributes

UDF1_CD

City Code

M1_LOCATION.UPPER(CITY)

UDF1_DESCR

City Description

M1_LOCATION.UPPER(CITY)

UDF2_CD

County Code

M1_LOCATION.COUNTY

UDF2_DESCR

County Description

M1_LOCATION.COUNTY

UDF3_CD

Postal Code

M1_LOCATION.POSTAL

UDF3_DESCR

Postal Code

M1_LOCATION.POSTAL

UDF4_CD

State Code

M1_LOCATION.STATE

UDF4_DESCR

State Description

CI_STATE_L.DESCR

UDF5_CD

Country Code

M1_LOCATION.COUNTRY

UDF5_DESCR

Country Description

CI_COUNTRY_L.DESCR

UDF6_CD

Geo Code Value

UDF6_DESCR

Geo Code

UDF7_CD

Premise Char 1 Code

UDF7_DESCR

Premise Char 1

UDF8_CD

Premise Char 2 Code

UDF8_DESCR

Premise Char 2

UDF9_CD

Premise Char 3 Code

UDF9_DESCR

Premise Char 3

UDF10_CD

Premise Char 4 Code

UDF10_DESCR

Premise Char 4 Code

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

EFF_END_DTTM

Effective End Date/Time

EFF_START_DTTM

Effective Start Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Source 3 - POU (M1_POU)

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ADDR_KEY

Address Dimension Surrogate Key

This field is populated from the SPL_ADDR_SEQ sequence.

Natural Keys

SRC_ADDR_ID

Premise ID

M1_POU.ADDRESS1

M1_POU.ADDRESS2

M1_POU.ADDRESS3

M1_POU.ADDRESS4

M1_POU.CROSS_STREET

M1_POU.SUBURB

M1_POU.CITY

M1_POU.COUNTY

M1_POU.STATE

M1_POU.POSTAL

M1_POU.COUNTRY

This natural key is the concatenation of all address attributes, in uppercase.

Attributes

ADDR_LINE1

Address Line 1

M1_POU.ADDRESS1

ADDR_LINE2

Address Line 2

M1_POU.ADDRESS2

ADDR_LINE3

Address Line 3

M1_POU.ADDRESS3

ADDR_LINE4

Address Line 4

M1_POU.ADDRESS4

ADDR_INFO

Address Information

M1_POU.ADDRESS1

M1_POU.CITY

M1_POU.STATE

M1_POU.POSTAL

This field is populated from the POU table based on the concatenation of the address, city, state, and postal code.

CITY

City

M1_POU.UPPER(CITY)

COUNTY

County

M1_POU.COUNTY

POSTAL

Postal Code

M1_POU.POSTAL

STATE_DESCR

State Description

CI_STATE_L.DESCR

COUNTRY_DESCR

Country Description

CI_COUNTRY_L.DESCR

CROSS_STREET

Cross Street

M1_POU.CROSS_STREET

SUBURB

Suburb

M1_POU.SUBURB

STATE_CD

State Code

M1_POU.STATE

COUNTRY_CD

Country Code

M1_POU.COUNTRY

GEO_CODE

Geographical Code

User Defined Attributes

UDF1_CD

City Code

M1_POU.UPPER(CITY)

UDF1_DESCR

City Description

M1_POU.UPPER(CITY)

UDF2_CD

County Code

M1_POU.COUNTY

UDF2_DESCR

County Description

M1_POU.COUNTY

UDF3_CD

Postal Code

M1_POU.POSTAL

UDF3_DESCR

Postal Code Description

M1_POU.POSTAL

UDF4_CD

State Code

M1_POU.STATE

UDF4_DESCR

State Description

CI_STATE_L.DESCR

UDF5_CD

Country Code

M1_POU.COUNTRY

UDF5_DESCR

Country Description

CI_COUNTRY_L.DESCR

UDF6_CD

Geo Code Value

UDF6_DESCR

Geo Code

UDF7_CD

Premise Char 1 Code

UDF7_DESCR

Premise Char 1

UDF8_CD

Premise Char 2 Code

UDF8_DESCR

Premise Char 2

UDF9_CD

Premise Char 3 Code

UDF9_DESCR

Premise Char 3

UDF10_CD

Premise Char 4 Code

UDF10_DESCR

Premise Char 4 Code

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

EFF_END_DTTM

Effective End Date/Time

EFF_START_DTTM

Effective Start Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Source 4 - Resource (M1_RESRC)

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ADDR_KEY

Address Dimension Surrogate Key

This field is populated from the SPL_ADDR_SEQ sequence.

Natural Keys

SRC_ADDR_ID

Premise ID

M1_RESRC.BO_DATA_AREA

This natural key is the concatenation of all address attributes, in uppercase.

Attributes

ADDR_LINE1

Address Line 1

M1_RESRC.BO_DATA_AREA

ADDR_LINE2

Address Line 2

M1_RESRC.BO_DATA_AREA

ADDR_LINE3

Address Line 3

M1_RESRC.BO_DATA_AREA

ADDR_LINE4

Address Line 4

M1_RESRC.BO_DATA_AREA

ADDR_INFO

Address Information

M1_RESRC.BO_DATA_AREA

This field is populated from the Task Address table based on the concatenation of the address, city, state, and postal code.

CITY

City

M1_RESRC.BO_DATA_AREA

COUNTY

County

M1_RESRC.BO_DATA_AREA

POSTAL

Postal Code

M1_RESRC.BO_DATA_AREA

STATE_DESCR

State Description

CI_STATE_L.DESCR

COUNTRY_DESCR

Country Description

CI_COUNTRY_L.DESCR

CROSS_STREET

Cross Street

M1_RESRC.BO_DATA_AREA

SUBURB

Suburb

M1_RESRC.BO_DATA_AREA

STATE_CD

State Code

M1_RESRC.BO_DATA_AREA

COUNTRY_CD

Country Code

M1_RESRC.BO_DATA_AREA

GEO_CODE

Geographical Code

User Defined Attributes

UDF1_CD

City Code

M1_RESRC.BO_DATA_AREA

UDF1_DESCR

City Description

M1_RESRC.BO_DATA_AREA

UDF2_CD

County Code

M1_RESRC.BO_DATA_AREA

UDF2_DESCR

County Description

M1_RESRC.BO_DATA_AREA

UDF3_CD

Postal Code

M1_RESRC.BO_DATA_AREA

UDF3_DESCR

Postal Code Description

M1_RESRC.BO_DATA_AREA

UDF4_CD

State Code

M1_RESRC.BO_DATA_AREA

UDF4_DESCR

State Description

CI_COUNTRY_L.DESCR

UDF5_CD

Country Code

M1_POU.COUNTRY

UDF5_DESCR

Country Description

CI_COUNTRY_L.DESCR

UDF6_CD

Geo Code Value

UDF6_DESCR

Geo Code Description

UDF7_CD

Premise Char 1 Code

UDF7_DESCR

Premise Char 1 Description

UDF8_CD

Premise Char 2 Code

UDF8_DESCR

Premise Char 2 Description

UDF9_CD

Premise Char 3 Code

UDF9_DESCR

Premise Char 3 Description

UDF10_CD

Premise Char 4 Code

UDF10_DESCR

Premise Char 4 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

EFF_END_DTTM

Effective End Date/Time

EFF_START_DTTM

Effective Start Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Crew Dimension

The Crew dimension includes the details of all crews defined in the source system up to three levels of crew hierarchy.

This dimension is also populated by Oracle Utilities Network Management System.

Properties

Property

Value

Target Table

CD_CREW

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

M1_RESRC

M1_RESRC_TYPE

M1_HIERARCHY

Stage Table

STG_CD_CREW

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

This field is populated from the SPL_CREW_SEQ sequence.

Natural Keys

SRC_CREW_ID

Crew ID

M1_RESRC.RESRC_ID

Attributes

CREW_DESCR

Crew Description

M1_RESRC_ID.RESRC_ID_VAL

CREW_TYPE_DESCR

Crew Type Description

M1_RESRC_TYPE_L.DESCR

CREW_CD

Crew Code

Note : This field is populated only for the Oracle Utilities Network Management System source product.

CREW_TYPE_CD

Crew Type Code

M1_RESRC.RESRC_TYPE_CD

ORG_UNIT1_CD

Crew Hierarchy-1 Code

M1_HIER_UPSTREAM.M1_PARENT_HIER_CD

This field is populated from the Hierarchy Upstream table based on the hierarchy and hierarchy level.

Note : The hierarchy is based on the reporting hierarchy stored on the crew’s characteristic (where characteristic type is Crew Hierarchy ‘M1CRHIER’) and for which the hierarchy level is Level 1 (‘M101’).

ORG_UNIT2_CD

Crew Hierarchy-2 Code

M1_HIER_UPSTREAM.M1_PARENT_HIER_CD

This field is populated from the Hierarchy Upstream table based on the hierarchy and hierarchy level.

Note : The hierarchy is based on the reporting hierarchy stored on the crew’s characteristic (where characteristic type is Crew Hierarchy ‘M1CRHIER’) and for which the hierarchy level is Level 1 (‘M102’).

ORG_UNIT3_CD

Crew Hierarchy-3 Code

M1_HIER_UPSTREAM.M1_PARENT_HIER_CD

This field is populated from the Hierarchy Upstream table based on the hierarchy and hierarchy level.

Note : The hierarchy is based on the reporting hierarchy stored on the crew’s characteristic (where characteristic type is Crew Hierarchy ‘M1CRHIER’) and for which the hierarchy level is Level 1 (‘M103’).

ORG_UNIT1_DESCR

Crew Hierarchy-1 Description

M1_HIERARCHY_L.DESCR

This field is populated from the hierarchy description based on the parent hierarchy code and language configured.

Note : To retrieve the parent hierarchy code, see the transformation logic of the ORG_UNIT1_CD column.

ORG_UNIT2_DESCR

Crew Hierarchy-2 Description

M1_HIERARCHY_L.DESCR

This field is populated from the hierarchy description based on the parent hierarchy code and language configured.

Note : To retrieve the parent hierarchy code, see the transformation logic of the ORG_UNIT2_CD column.

ORG_UNIT3_DESCR

Crew Hierarchy-3 Description

M1_HIERARCHY_L.DESCR

This field is populated from the hierarchy description based on the parent hierarchy code and language configured.

Note : To retrieve the parent hierarchy code, see the transformation logic of the ORG_UNIT3_CD column.

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

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_END_DTTM

Effective End Date/Time

EFF_START_DTTM

Effective Start Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

DATA_SOURCE_IND

Data Source Indicator

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Contact Dimension

Though Contact dimension is referenced in the Oracle Utilities Mobile Workforce Management facts, it is populated only by the Oracle Utilities Meter Data Management source product. The ETL processes of the Oracle Utilities Mobile Workforce Management facts map this dimension based on the integration between Oracle Utilities Mobile Workforce Management and Oracle Utilities Meter Data Management.

Since this dimension is not populated by Oracle Utilities Mobile Workforce Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Meter Data Management Data Mapping Guide for the mapping details.

Device Dimension

Though Device dimension is referenced in the Oracle Utilities Mobile Workforce Management facts, it is populated only by the Oracle Utilities Meter Data Management source product. The ETL processes of the Oracle Utilities Mobile Workforce Management facts map this dimension based on the integration between Oracle Utilities Mobile Workforce Management and Oracle Utilities Meter Data Management.

Since this dimension is not populated by Oracle Utilities Mobile Workforce Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Meter Data Management Data Mapping Guide for the mapping details.

Person Dimension

Though Person dimension is referenced in the Oracle Utilities Meter Data Management facts, it is populated only by the Oracle Utilities Customer Care and Billing source product. The ETL processes of the Oracle Utilities Meter Data Management facts map this dimension based on the integration between Oracle Utilities Meter Data Management and Oracle Utilities Customer Care and Billing.

Since this dimension is not populated by Oracle Utilities Meter Data Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing Data Mapping Guide for the mapping details.

Premise Dimension

Though Premise dimension is referenced in the Oracle Utilities Meter Data Management facts, it is populated only by the Oracle Utilities Customer Care and Billing source product. The ETL processes of the Oracle Utilities Meter Data Management facts map this dimension based on the integration between Oracle Utilities Meter Data Management and Oracle Utilities Customer Care and Billing.

Since this dimension is not populated by Oracle Utilities Meter Data Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing Data Mapping Guide for the mapping details.

Service Agreement Dimension

Though Service Agreement dimension is referenced in the Oracle Utilities Meter Data Management facts, it is populated only by the Oracle Utilities Customer Care and Billing source product. The ETL processes of the Oracle Utilities Meter Data Management facts map this dimension based on the integration between Oracle Utilities Meter Data Management and Oracle Utilities Customer Care and Billing.

Since this dimension is not populated by Oracle Utilities Meter Data Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Customer Care and Billing Data Mapping Guide for the mapping details.

Service Point Dimension

Though Service Point dimension is referenced in the Oracle Utilities Mobile Workforce Management facts, it is populated only by the Oracle Utilities Meter Data Management source product. The ETL processes of the Oracle Utilities Mobile Workforce Management facts map this dimension based on the integration between Oracle Utilities Mobile Workforce Management and Oracle Utilities Meter Data Management.

Since this dimension is not populated by Oracle Utilities Mobile Workforce Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Meter Data Management Data Mapping Guide for the mapping details.

Usage Subscription Dimension

Though Usage Subscription dimension is referenced in the Oracle Utilities Mobile Workforce Management facts, it is populated only by the Oracle Utilities Meter Data Management source product. The ETL processes of the Oracle Utilities Mobile Workforce Management facts map this dimension based on the integration between Oracle Utilities Mobile Workforce Management and Oracle Utilities Meter Data Management.

Since this dimension is not populated by Oracle Utilities Mobile Workforce Management, the source mapping details are not provided in the document. See the Oracle Utilities Extractors and Schema for Oracle Utilities Meter Data Management Data Mapping Guide for the mapping details.

Facts

Completed Shift Fact

The Completed Shift fact holds the details of all completed shifts from the Oracle Utilities Mobile Workforce Management system that show the number of crews late to logon and by how much they deviated from the planned time, the number of crews early to logoff and by how much they deviated from the planned time, how much time the crew work overtime, and what are the deviations in estimated travel mileage versus actual mileage. This fact is populated based on completed, real shifts.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CMP_SHIFT

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

M1_CREW_SHFT

M1_RESRC

M1_TASK

M1_LOCATION

Stage Table Name

STG_CF_CMP_SHIFT

ODI Package Name

B1_PKG_CF_CMP_SHIFT

ETL View Name

B1_F_CMP_SHIFT_VW

Materialized View Name

B1_CMP_SHIFT_MON_MV1

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CMP_SHIFT_KEY

Completed Shift Surrogate Key

This field is populated from the OUBI_CMP_SHIFT_SEQ sequence.

Natural Keys

SRC_SHIFT_ID

Shift ID

M1_CREW_SHFT.CREW_SHFT_ID

Measures

PLANNED_DUR

Planned Duration (Seconds)

M1_CREW_SHFT.(PLAN_START_DTTM - M1_CREW_SHFT.PLAN_END_DTTM)

This field is populated with the planned duration of the shift. It is the difference between the planned start date time and end date time (in seconds).

ACTUAL_DUR

Actual Duration (Seconds)

M1_CREW_SHFT. BUS_STATUS_DTTM - M1_CREW_SHFT_LOG. LOG_DTTM

This field is populated with the actual duration of the shift. It is populated with the difference (in seconds) between the actual start date time and actual completion date time.

Depending on the crew shift, the regular logs or the mobile logs are used.

OVERTIME_DUR

Overtime Duration (Seconds)

M1_CREW_SHFT.ACTUAL_DUR

M1_CREW_SHFT.PLANNED_DUR

This field is populated if the shift went overtime. It is calculated from the difference between the actual duration and planned duration (in seconds).

If the difference is greater than 0, overtime duration is populated.

EST_DISTANCE

Estimated Distance

M1_TASK.BO_DATA_AREA

This field is the total of all estimated mileage of all tasks performed by the crew on the shift.

EST_TOT_MILEAGE

Estimated Total Mileage for Vehicles

M1_TASK.RESRC_ID

M1_RESRC.BO_DATA_AREA

This field is the total of estimated mileage of all tasks performed by the crew taking into consideration the resources allocated to the shift. It is populated with the sum of estimated travel distance for all tasks of the shift multiplied by the number of vehicles allocated to the shift.

ACT_TOT_MILEAGE

Actual Total Mileage for Vehicles

M1_CREW_SHFT_RESRC.BO_DATA_AREA

This field is populated by the actual total mileage of all vehicles allocated to the shift. It is populated based on the odometer readings at the start and end of the shift.

VEHICLE_CNT

Number of Vehicles

M1_RESRC.RESRC_ID

This field is populated with the total number of vehicles allocated to the shift. It is retrieved from resources where resource class is Vehicle (‘M1VH’).

FACT_CNT

Count

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

Degenerate Dimensions

SHIFT_PLANNED_START_DTTM

Shift Planned Start Date/Time

M1_CREW_SHFT.PLAN_START_DTTM

This field is populated with the plan start date time associated with the crew shift.

Foreign Keys

CREW_SHIFT_KEY

Shift Dimension Surrogate Key

M1_CREW_SHFT.CREW_SHFT_ID

This field is populated from the Crew Shift dimension based on the crew shift ID associated with the crew.

CREW_KEY

Crew Dimension Surrogate Key

M1_CREW_SHFT.RESRC_ID

This field is populated from the Crew dimension based on the resource ID associated with the crew shift.

LATE_LOGON_TM_KEY

Late Logon Time Dimension Surrogate Key

M1_CREW_SHFT_LOG.LOG_DTTM - M1_CREW_SHFT.PLAN_START_DTTM

This field is populated based on the late logon time calculated as the difference (in minutes) between the shift's actual start date/time and planned start date/time.

Depending on the crew shift, the regular logs or the mobile logs would be used.

EARLY_LOGOFF_TM_KEY

Early Logoff Time Dimension Surrogate Key

M1_CREW_SHFT.PLAN_END_DTTM - M1_CREW_SHFT.B US_STATUS_DTTM

This field is populated baed on the early logoff time calculated as the difference (in minutes) between the shift's actual end date/ time and planned end date/ time.

TRAVEL_DIST_DEV_KEY

Travel Distance Deviation Dimension Surrogate Key

This field is calculated based on the percentage of deviation between actual total mileage and estimated total mileage calculated earlier in the fact.

LOGON_ADDR_KEY

Logon Address Dimension Surrogate Key

M1_LOCATION/M1_RESRC.ADDRESS1

M1_LOCATION/M1_RESRC.ADDRESS2

M1_LOCATION/M1_RESRC.ADDRESS3

M1_LOCATION/M1_RESRC.ADDRESS4

M1_LOCATION/M1_RESRC.CROSS_STRT

M1_LOCATION/M1_RESRC.SUBURB

M1_LOCATION/M1_RESRC.CITY

M1_LOCATION/M1_RESRC.COUNTY

M1_LOCATION/M1_RESRC.STATE

M1_LOCATION/M1_RESRC.POSTAL

M1_LOCATION/M1_RESRC.COUNTRY

M1_LOCATION/M1_RESRC.BO_DATA_AREA

This field is populated based on the logon location entered on the shift.

If there is no logon location specified, but the logon location type is Home, the field is populated based on the home address of the first mobile worker allocated to the shift.

LOGOFF_ADDR_KEY

Logoff Address Dimension Surrogate Key

M1_LOCATION/M1_RESRC.ADDRESS1

M1_LOCATION/M1_RESRC.ADDRESS2

M1_LOCATION/M1_RESRC.ADDRESS3

M1_LOCATION/M1_RESRC.ADDRESS4

M1_LOCATION/M1_RESRC.CROSS_STRT

M1_LOCATION/M1_RESRC.SUBURB

M1_LOCATION/M1_RESRC.CITY

M1_LOCATION/M1_RESRC.COUNTY

M1_LOCATION/M1_RESRC.STATE

M1_LOCATION/M1_RESRC.POSTAL

M1_LOCATION/M1_RESRC.COUNTRY

M1_LOCATION/M1_RESRC.BO_DATA_AREA

This field is populated based on the logoff location entered on the shift.

If there is no logoff location specified, but the logoff location type is Home, the field is populated based on the home address of the first mobile worker allocated to the shift.

SHIFT_PLANNED_START_DATE_KEY

Shift Planned Start Date Key

M1_CREW_SHFT.PLAN_START_DTTM

This field is populated from the Crew Shift table based on the planned start date associated with the crew shift.

SHIFT_PLANNED_START_TIME_KEY

Shift Planned Start Time Key

M1_CREW_SHFT.PLAN_START_DTTM

This field is populated from the Crew Shift table based on the planned start time associated with the crew shift.

CMP_SHIFT_UDD1_KEY

Completed Shift User Defined Dimension 1 Key

CMP_SHIFT_UDD2_KEY

Completed Shift 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

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

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Crew Task Fact

The Crew Tasks fact has measures that show how much time the crews spend on productive versus non-productive tasks; how much time crews spend working on each task, as well as time spent in and out of services; how many times a crew makes or misses appointments, and if missed, by how much; and how many times crews arrive at an activity, but fail to complete it.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_CREW_TASK

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

M1_CREW_SHFT

M1_TASK

M1_LOCATION

M1_POU

Stage Table Name

STG_CF_CREW_TASK

ODI Package Name

B1_PKG_CF_CREW_TASK

ETL View Name

B1_F_CREW_TASK_VW

Materialized View Name

B1_CREW_TASKS_MON_MV1

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

CREW_TASK_KEY

Crew Task Surrogate Key

This field is populated from the OUBI_CREW_TASK_SEQ sequence.

Natural Keys

SRC_SHIFT_ID

Shift ID

M1_CREW_SHFT.CREW_SHFT_ID

SRC_TASK_ID

Activity ID

M1_TASK.TASK_ID

SRC_SHIFT_LOG_SEQ

Shift Log Sequence

M1_CREW_SHFT_LOG.SEQNO

Depending on the crew shift, the regular logs or mobile logs are used.

SRC_TASK_LOG_SEQ

Task Log Sequence

M1_TASK_LOG.SEQNO

Depending on the crew shift, the regular logs or mobile logs are used.

Measures

PREV_STATE_DUR

Time in Previous State

This field is populated with the time spent (in seconds) in the previous state based on the log date/times.

EST_TRAVEL_DUR

Estimated

M1_TASK.BO_DATA_AREA

This field is populated with the estimated travel duration from CLOB in the source Task table.

FACT_CNT

Count

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

Degenerate Dimensions

APPOINTMENT_IND

Appointment Indicator

M1_TASK.APPOINTMENT_FLG

This field is populated based on the appointment flag from the Task table. If the appointment flag is 'Y', the appointment indicator is populated with '1'. Else, with '0'.

EMERGENCY_IND

Emergency Indicator

M1_TASK.QUEUE_FLG

This field is populated based on the queue flag from the source Task table. If the queue flag is 'M1ER', the emergency indicator is populated with '1'. Else, with '0'.

FROM_DTTM

From Date/Time

M1_CREW_SHFT_LOG.LOG_DTTM

M1_TASK_LOG.LOG_DTTM

TO_DTTM

To Date/Time

M1_CREW_SHFT_LOG.LOG_DTTM

M1_TASK_LOG.LOG_DTTM

Foreign Keys

CREW_TM_USG_KEY

Crew Time Usage Dimension Surrogate Key

This field is populated based on the task class and crew shift status or task status.

FROM_SHIFT_BO_STATUS_KEY

From Shift BO Status Dimension Surrogate Key

M1_CREW_SHFT.BUS_OBJ_CD

M1_CREW_SHFT_LOG.BO_STATUS_CD

M1_CREW_SHFT_LOG.BO_STATUS_REASON_CD

This field is populated based on shift status from the log entries.

TO_SHIFT_BO_STATUS_KEY

To Shift BO Status Dimension Surrogate Key

M1_CREW_SHFT.BUS_OBJ_CD

M1_CREW_SHFT_LOG.BO_STATUS_CD

M1_CREW_SHFT_LOG.BO_STATUS_REASON_CD

This field is populated based on shift status from the log entries.

CREW_SHIFT_KEY

Shift Dimension Surrogate Key

M1_CREW_SHFT.CREW_SHFT_ID

This field is populated from the Crew Shift dimension based on the crew shift ID.

CREW_KEY

Crew Dimension Surrogate Key

M1_CREW_SHFT.RESRC_ID

This field is populated from the Crew dimension based on the resource ID associated with the crew shift.

TRAVEL_DUR_DEV_KEY

Travel Duration Deviation Dimension Surrogate Key

This field is calculated based on the percentage of deviation between actual travel duration based on the log date/times and the estimated travel duration.

APPT_TM_KEY

Appointment Time Dimension Surrogate Key

This field is populated based on the difference between task time window start and end date/time associated with the task ID.

APPT_TM_OF_DAY_KEY

Appointment Time of Day Dimension Surrogate Key

This field is populated based on the task time window start date/time associated with the task ID.

FROM_TASK_BO_STATUS_KEY

From Task BO Status Dimension Surrogate Key

M1_TASK.BUS_OBJ_CD

M1_TASK_LOG.BO_STATUS_CD

M1_TASK_LOG.BO_STATUS_REASON_CD

This field is populated based on the task status from log entries.

TO_TASK_BO_STATUS_KEY

To Task BO Status Dimension Surrogate Key

M1_TASK.BUS_OBJ_CD

M1_TASK_LOG.BO_STATUS_CD

M1_TASK_LOG.BO_STATUS_REASON_CD

This field is populated based on the task status from log entries.

TASK_TYPE_KEY

Task Type Dimension Surrogate Key

M1_TASK.TASK_TYPE_CD

This field is populated based on the task type code associated with the task ID.

SERVICE_AREA_KEY

Service Area Dimension Surrogate Key

M1_TASK.SVC_AREA_CD

This field is populated based on the task type code associated with the task ID.

ADDR_KEY

Address Dimension Surrogate Key

This field is populated based on the address constituents associated with the task.

Based on the task class, the address constituents are fetched either from the task, location, depot, or period of unavailability entity.

FROM_DATE_KEY

From Date Dimension Surrogate Key

M1_CREW_SHFT_LOG.LOG_DTTM

M1_TASK_LOG.LOG_DTTM

This field is populated based on the log date from shift log or task log.

TO_DATE_KEY

To Date Dimension Surrogate Key

M1_CREW_SHFT_LOG.LOG_DTTM

M1_TASK_LOG.LOG_DTTM

This field is populated based on the log date from shift log or task log.

FROM_TIME_KEY

From Time Dimension Surrogate Key

M1_CREW_SHFT_LOG.LOG_DTTM

M1_TASK_LOG.LOG_DTTM

This field is populated based on the log date from shift log or task log.

TO_TIME_KEY

To Time Dimension Surrogate Key

M1_CREW_SHFT_LOG.LOG_DTTM

M1_TASK_LOG.LOG_DTTM

This field is populated based on the log date from shift log or task log.

MTR_DEVICE_KEY

Device Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the external identifier value for the meter device stored on the task as part of MWM-MDM integration.

SP_KEY

Service Point Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the service point ID stored on the task as part of MWM-MDM integration.

US_KEY

Usage Subscription Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the usage subscription ID stored on the task as part of MWM-MDM integration.

CONTACT_KEY

Contact Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the contact ID stored on the task as part of MWM-MDM integration.

PER_KEY

Person Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the person ID stored on task as part of MWM-CCB integration.

ACCT_KEY

Account Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the account ID stored on task as part of MWM-CCB integration.

SA_KEY

Service Agreement Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the service agreement ID stored on task as part of MWM-CCB integration.

PREM_KEY

Premise Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the premise ID stored on task as part of MWM-CCB integration.

CREW_TASK_UDD1_KEY

Crew Task User Defined Dimension 1 Key

CREW_TASK_UDD2_KEY

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

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

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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

Field Activity Fact

The Field Activity fact holds the details of all activities (tasks) defined in the Oracle Utilities Extractors and Schema for Oracle Utilities Mobile Workforce Management system with measures around duration quantities, response times, and emergency indicator.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_FLD_ACTIVITY

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

M1_TASK

M1_CREW_SHFT

Stage Table Name

STG_CF_FLD_ACTIVITY

ODI Package Name

B1_PKG_CF_FLD_ACTIVITY

ETL View Name

B1_F_FLD_ACTIVITY_VW

Materialized View Name

B1_FLD_ACTIVITY_MON_MV1

B1_FLD_ACTIVITY_MON_MV2

B1_FLD_ACTIVITY_MON_MV3

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

FLD_ACTIVITY_KEY

Activity Surrogate Key

This field is populated from the OUBI_FLD_ACTIVITY_SEQ sequence.

Natural Keys

SRC_TASK_ID

Activity ID

M1_TASK.TASK_ID

This field is populated from the source Task table based on the tasks for which the Task Flag is set to Activity ('M1AC') and Parent Task ID is set as blank.

Measures

WORK_DUR

Work Duration (Minutes)

M1_TASK_TYPE.AVG_DUR

This field is populated with the average task duration from the Task Type table. If no value is found, '0' is populated.

EST_WORK_DUR

Estimated Work Duration

M1_TASK.ESTIMATED_DURATION

This field is populated with the estimated task duration from the Task Type table. If no value is found, '0' is populated.

ACT_WORK_DUR

Actual Work Duration

M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM

This field is populated with the total time spent ‘onsite’ (in seconds) by the crew. It is calculated based on the log entries.

WORK_ATTEMPT_CNT

Number of Work Attempts

M1_TASK_LOG/M1_TASK_MOB_LOG.BO_STATUS_CD

This field is populated with the number of times crew attempted to do the work (based on the number of times the crew logged with an ‘Onsite’ status).

If there are no log records, it is populated as '0'.

TRAVEL_ATTEMPT_CNT

Number of Travel Attempts

M1_TASK_LOG/M1_TASK_MOB_LOG.BO_STATUS_CD

This field is populated with the number of times the crew traveled to the site to do the work (based on the number of times the crew logged with an ‘Enroute’ status).

If there are no log records, it is populated as '0'.

DISPATCH_RESP_DUR

Creation To Dispatch

M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM

M1_TASK.CRE_DTTM

This field is populated based on the time (in seconds) between the creation of the activity until it was dispatched (based on the difference between the creation date/time and the log date/time of the log entry for 'Dispatched' status).

If the activity has not been dispatched, it is populated as '0'.

ENROUTE_RESP_DUR

Dispatch to Enroute

M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM

This field is populated based on the time (in seconds) it took from dispatching the activity until the crew is en route to the site (based on the difference between the maximum log date time for the entry with ‘Dispatched’ status and the log entry with ‘En Route’ status.

If there are no log records, it is populated as ‘0’.

ARRIVAL_RESP_DUR

Enroute To Onsite

M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM

This field is populated based on the time it took to travel until the crew arrived on site (based on the difference between the maximum log date time for the entry with ‘En Route’ status and the entry with ‘Arrived’ or ‘Onsite’ status).

If there are no log records, it is populated as '0'.

COMPLETED_RESP_DUR

Response Time to Complete

M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM

This field is populated based on the time it took for the crew to arrive on site and finish the task (based on the difference between the maximum log date time for the entry with ‘Arrived’ or ‘Onsite’ and the entry with ‘Work Done’ or ‘Completed’ status).

If there are no log records, it is populated as ‘0’.

TOT_RESP_DUR

Total Response Time (Minutes)

M1_TASK.DISPATCH_RESP_DUR

M1_TASK.ENROUTE_RESP_DUR

M1_TASK.ARRIVAL_RESP_DUR

M1_TASK.COMPLETED_RESP_DUR

This field is populated with the total time it took to complete the task, starting from the time the task was dispatched all the way to the time the task was completed.

ACT_TRAVEL_DUR

Actual Travel Duration

M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM

This field is populated based on the total time the crew spent travelling to the site. Unit of measure is seconds.

FACT_CNT

Count

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

Degenerate Dimensions

EMERGENCY_IND

Emergency Indicator

M1_TASK.QUEUE_FLG

This field is populated from the source Task table based on the identified tasks. If the Queue Flag is set to 'M1ER', then populate the Emergency Indicator with '1'. Else, with '0'.

APPOINTMENT_IND

Appointment Indicator

M1_TASK.APPOINTMENT_FLG

This field is populated from the source Task table based on the identified tasks. If the Appointment Flag is set to 'Y', then populate the Appointment Indicator with '1'. Else, with '0'.

SAME_DAY_APPT_IND

Same Day Appointment

M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM

M1_TASK.CRE_DTTM

This field is populated with '1' if the appointment is made on the same day as the creation date. Else, with '0'.

TASK_INFO

Task Info String

M1_TASK_TYPE_L.DESCR

M1_TASK_ADDR.ADDRESS1, CITY, COUNTY, STATE

F1_BUS_OBJ_STATUS_L.DESCR

This field is populated with the concatenation of task type description, address, status code, task time window and crew for a task.

CRE_DTTM

Create Date/Time

M1_TASK.CRE_DTTM

STATUS_DTTM

Status Date/Time

M1_TASK.STATUS_UPD_DTTM

SCHED_START_DTTM

Scheduled Start Date/Time

M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM

This field is populated with '1' if the appointment is made on the same day as the window start date. Else, with '0'.

SCHED_END_DTTM

Scheduled End Date/Time

M1_TASK_TM_WIND.TSK_TM_WIND_END_DTTM

This field is populated with '1' if the appointment is made on the same day as the window end date. Else, with '0'.

Foreign Keys

TASK_BO_STATUS_KEY

Task BO Status Dimension Surrogate Key

M1_TASK.BUS_OBJ_CD

M1_TASK.BO_STATUS_CD

M1_TASK.BO_STATUS_REASON_CD

This field is populated based on the business object code, status code, and status reason code from the Task table.

TASK_TYPE_KEY

Task Type Dimension Surrogate Key

M1_TASK.TASK_TYPE_CD

SERVICE_AREA_KEY

Service Area Dimension Surrogate Key

M1_TASK.SVC_AREA_CD

ADDR_KEY

Address Dimension Surrogate Key

M1_TASK_ADDR.ADDRESS1

M1_TASK_ADDR.ADDRESS2

M1_TASK_ADDR.ADDRESS3

M1_TASK_ADDR.ADDRESS4

M1_TASK_ADDR.CROSS_STREET

M1_TASK_ADDR.SUBURB

M1_TASK_ADDR.CITY

M1_TASK_ADDR.COUNTY

M1_TASK_ADDR.STATE

M1_TASK_ADDR.POSTAL

M1_TASK_ADDR.COUNTRY

This field is populated based on the location of the task.

CREW_KEY

Crew Dimension Surrogate Key

M1_CREW_SHFT.RESRC_ID

This field is populated from the Crew table based on the resource ID of the Task table.

Identify the resource ID from the Crew Shift table for the child task with 'Dispatched' status and with maximum log date/time.

APPT_TM_KEY

Appointment Time Dimension Surrogate Key

M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM

M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM

This field is populated based on the difference (in minutes) between the appointment start date time and log date time.

APPT_TM_OF_DAY_KEY

Appointment Time Of Day Dimension Surrogate Key

M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM

This field is populated based on the task time window start date/time associated with the task ID.

WORK_DUR_DEV_KEY

Work Duration Deviation Key

M1_TASK_TYPE.ACT_WORK_DUR

M1_TASK_TYPE.EST_WORK_DUR

This field fetches the actual work duration and estimated work duration and calculates the percentage of deviation.

((Actual Work Duration - Estimated Work Duration)/Estimated Work Duration))*100

RESP_TM_DEV_KEY

Response Time Deviation Dimension Surrogate Key

M1_TASK_TYPE.TOT_RESP_DUR

M1_TASK_TYPE.BO_DATA_AREA

This field is populated based on the response time SLA from the Task Type table.

((Overall Response Time - Response Time SLA)/ Response Time SLA))*100

CRE_DATE_KEY

Creation Date Key

M1_TASK.CRE_DTTM

This field is populated based on the creation date of the task.

STATUS_DATE_KEY

Status Date Key

M1_TASK.STATUS_UPD_DTTM

This field is populated based on the status update date of the task.

SCHED_START_DATE_KEY

Scheduled Start Date Key

M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM

This field is populated based on the scheduled start date of the task.

SCHED_END_DATE_KEY

Scheduled End Date Key

M1_TASK_TM_WIND.TSK_TM_WIND_END_DTTM

This field is populated based on the scheduled end date of the task.

CRE_TIME_KEY

Creation Time Key

M1_TASK.CRE_DTTM

This field is populated based on the creation date/time of the task.

STATUS_TIME_KEY

Status Time Key

M1_TASK.STATUS_UPD_DTTM

This field is populated based on the status update date/time of the task.

SCHED_START_TIME_KEY

Scheduled Start Time Key

M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM

This field is populated based on the scheduled start time of the task.

SCHED_END_TIME_KEY

Scheduled End Time Key

M1_TASK_TM_WIND.TSK_TM_WIND_DATE_DTTM

This field fetches the most recent effective time window based on the appointment.

MTR_DEVICE_KEY

Device Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the external identifier value for meter device stored on the task as part of MWM-MDM integration.

SP_KEY

Service Point Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the service point ID stored on the task as part of MWM-MDM integration.

US_KEY

Usage Subscription Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the usage subscription ID stored on the task as part of MWM-MDM integration.

CONTACT_KEY

Contact Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the contact subscription ID stored on the task as part of MWM-MDM integration.

PER_KEY

Person Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the person subscription ID stored on the task as part of MWM-MDM integration.

ACCT_KEY

Account Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the account ID stored on the task as part of MWM-MDM integration.

SA_KEY

Service Agreement Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the service agreement ID stored on the task as part of MWM-MDM integration.

PREM_KEY

Premise Dimension Surrogate Key

M1_TASK.BO_DATA_AREA

This field is populated based on the premise ID stored on the task as part of MWM-MDM integration.

FLD_ACTIVITY_UDD1_KEY

Activity User Defined Dimension 1 Key

FLD_ACTIVITY_UDD2_KEY

Activity 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

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

CI_INSTALLATION.ENV_ID

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

JOB_NBR

Job Number

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