Oracle Utilities Extractors and Schema for Oracle Utilities Work and Asset Management

Data Mapping Guide

Release 2.6.0

E70892-01

May 2016

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

E70892-01

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

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

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

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

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

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

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

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

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

Preface

This guide provides the data mapping information from the Oracle Utilities Work and Asset 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 Work and Asset 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, refer to 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 Utilities Work and Asset 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

Overview

This guide provides the data mapping information from the Oracle Utilities Work and Asset 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.

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

Fact Type

Whether this is a snapshot or transactional fact table.

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

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

Source System Driver Table

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

Oracle Data Integrator Package

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

ETL View

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

Materialized View

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

These materialized views are designed to support all of the OBIEE answers delivered with the product. Refreshing these materialized views will be taken care by separate ETL jobs.

Fields

The Fields table lists the individual properties of each field in the fact or dimension table. It includes the following columns:

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 values based data that is calculated from the source system data and are typically available on fact tables.

Degenerate Dimensions

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

Foreign Keys

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

User Defined Attributes

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

Data Load Attributes

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

Data Maps for Oracle Utilities Work and Asset Management

This section contains data maps for the following Oracle Utilities Extractors and Schema for Oracle Utilities Work and Asset Management tables:

•

Dimensions

•

Shared Dimensions

•

User Defined Dimensions

•

Facts

Dimensions

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

Asset Age Bucket Dimension

The Asset Age Bucket dimension includes the configurable ranges used to group assets based on their ages.

For details about how the buckets are configured, see Configuring Source in Oracle Utilities Analytics Administration Guide.

Properties

Property

Value

Target Table

CD_W_ASSET_AGE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

ODI Package

B1_PKG_CD_W_ASSET_AGE

ETL View

B1_D_AGE_BUCKETS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

W_ASSET_AGE_KEY

Asset Age Dimension

This field is populated from the B1_W_ASSET_AGE_SEQ sequence.

Natural Keys

W_ASSET_AGE_ST_RANGE

Asset Age Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

This field is populated based on the bucket ranges defined for the business object W1-WAssetAgeBuckets.

W_ASSET_AGE_ED_RANGE

Asset Age End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

This field is populated based on the bucket ranges defined for the business object W1-WAssetAgeBuckets.

Attributes

W_ASSET_AGE_DESCR

Asset Age Range Description

F1_BKT_CONFIG_VAL_L.DESCR

This field is populated based on the bucket ranges defined for the business object W1-WAssetAgeBuckets.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date and Time

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.

Asset Disposition Dimension

The Asset Disposition dimension extracts all possible dispositions of an asset entity in the source system.

Properties

Property

Value

Target Table

CD_ASSET_DISP

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_EXT_LOOKUP_VAL_L

ODI Package

B1_PKG_CD_ASSET_DISP

ETL View

B1_D_ASSET_DISP_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ASSET_DISP_KEY

Asset Disposition Dimension

This field is populated from the B1_ASSET_DISP_SEQ sequence.

Natural Keys

ASSET_DISP_CD

Asset Disposition Code

F1_EXT_LOOKUP_VAL_L.F1_EXT_LOOKUP_VALUE

This field is populated with the lookup value for the extendable lookup business object W1-AssetDisposition.

Attributes

ASSET_DISP_DESCR

Asset Disposition Description

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

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

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

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.

Asset Install Age Bucket Dimension

The Asset Install Age Bucket dimension contains configurable ranges used for grouping assets based on their installation ages.

For details about bucket configuration, see Configuring Source in the Oracle Utilities Analytics Administration Guide.

Properties

Property

Value

Target Table

CD_W_ASSET_INSTALL_AGE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

B1_D_W_ASSET_INSTALL_AGE_VW

ODI Package

B1_PKG_CD_W_ASSET_INSTALL_AGE

ETL View

B1_D_AGE_BUCKETS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

W_ASSET_INSTALL_AGE_KEY

Asset Install Age Dimension Surrogate Key

This field is populated from the B1_W_ASSET_INSTALL_AGE_SEQ sequence.

Natural Keys

W_ASSET_INSTALL_ST_RANGE

Asset Install Age Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

This field is populated based on the bucket ranges defined for the business object W1-WAssetInstallAgeBuckets.

W_ASSET_INSTALL_ED_RANGE

Asset Install Age End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

This field is populated based on the bucket ranges defined for the business object W1-WAssetInstallAgeBuckets.

Attributes

W_ASSET_INSTALL_AGE_DESCR

Asset Install Age Range

F1_BKT_CONFIG_VAL_L.DESCR

This field is populated based on the bucket ranges defined for the business object W1-WAssetInstallAgeBuckets.

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.

Number of Cycles Overdue Bucket Dimension

The Number of Cycles Overdue Bucket dimension stores the bucket ranges used for grouping activities based on how many cycles they are overdue.

See Configuring Source in the Oracle Utilities Analytics Administration Guide for details about how to configure the buckets.

Properties

Property

Value

Target Table

CD_NUM_CYC_OD

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BKT_CONFIG

ODI Package

B1_PKG_CD_NUM_CYC_OD

ETL View

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

NUM_CYC_OD_KEY

Number of Cycles Overdue Dimension Surrogate Key

This field is populated from the B1_NUM_CYC_OD_SEQ sequence.

Natural Keys

NUM_CYC_OD_ST_RANGE

Number of Cycles Overdue Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

This field is populated based on the bucket ranges defined for the business object W1-NumberOfCyclesOverdueBucket.

NUM_CYC_OD_ED_RANGE

Number of Cycles Overdue End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

This field is populated based on the bucket ranges defined for the business object W1-NumberOfCyclesOverdueBucket.

Attributes

NUM_CYC_OD_DESCR

Number of Cycles Overdue Description

F1_BKT_CONFIG_VAL_L.DESCR

This field is populated based on the bucket ranges defined for the business object W1-NumberOfCyclesOverdueBucket.

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.

Owning Organization Dimension

The Owning Organization dimension stores all the owning organizations’ details from the source system.

Properties

Property

Value

Target Table

CD_OWNING_ORG

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_ACC_GRP_L

ODI Package

B1_PKG_CD_OWNING_ORG

ETL View

B1_D_OWNING_ORG_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

OWNING_ORG_KEY

Owning Organization Dimension Surrogate Key

This field is populated from the B1_OWNING_ORG_SEQ sequence.

Natural Keys

OWNING_ORG_CD

Owning Organization Code

CI_ACC_GRP_L.ACCESS_GRP_CD

Attributes

OWNING_ORG_DESCR

Owning Organization Description

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

Planner Dimension

The Planner dimension stores all planners from the source system.

Properties

Property

Value

Target Table

CD_W_PLANNER

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

W1_PLANNER_L

ODI Package

B1_PKG_CD_W_PLANNER

ETL View

B1_D_W_PLANNER_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

W_PLANNER_KEY

Planner Dimension Surrogate Key

This field is populated from the B1_W_PLANNER_SEQ sequence.

Natural Keys

W_PLANNER_CD

Planner Code

W1_PLANNER_L.PLANNER_CD

Attributes

W_PLANNER_DESCR

Planner

W1_PLANNER_L.DESCR100

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.

Resource Type Dimension

The Resource Type dimension stores all resource types from the source system.

Properties

Property

Value

Target Table

CD_RESRC_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

W1_RESRC_TYPE

ODI Package

B1_PKG_CD_RESRC_TYPE

ETL View

B1_D_RESRC_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

RESRC_TYPE_KEY

Resource Type Dimension Surrogate Key

This field is populated from the B1_RESRC_TYPE_SEQ sequence.

Natural Keys

RESRC_TYPE_ID

Resource Type ID

W1_RESRC_TYPE.RESRC_TYPE_ID

Attributes

RESRC_TYPE_DESCR

Resource Type Description

W1_RESRC_TYPE_L.DESCR100

RESRC_CLASS_CD

Resource Class Flag

W1_RESRC_TYPE.W1_RESRC_CLASS_FLG

RESRC_CLASS_DESCR

Resource Class Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

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

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

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.

Service History Dimension

The Service History dimension stores all service histories from the source system.

Properties

Property

Value

Target Table

CD_SERVICE_HIST

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

W1_SVC_HIST

ODI Package

B1_PKG_CD_SERVICE_HIST

ETL View

B1_D_SERVICE_HIST_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SERVICE_HIST_KEY

Service History Dimension Surrogate Key

This field is populated from the B1_SERVICE_HIST_SEQ sequence.

Natural Keys

SRC_SERVICE_HIST_ID

Source Service History ID

W1_SVC_HIST.SVC_HIST_ID

Attributes

FAILURE_TYPE_CD

Failure Cause Code

W1_SVC_HIST.FAILURE_TYPE_CD

This field is populated only for failure service histories.

FAILURE_TYPE_DESCR

Failure Cause Description

W1_FAILURE_TYPE_L.DESCR100

This field is populated with the description of the Failure Type.

FAILURE_MODE_CD

Failure Mode Code

W1_SVC_HIST.FAILURE_MODE_CD

This field is populated only for failure service histories.

FAILURE_MODE_DESCR

Failure Mode Description

W1_FAILURE_MODE_L.DESCR100

This field is populated with the description of the Failure Mode Code.

FAILURE_REPAIR_CD

Failure Repair Code

W1_SVC_HIST. FAILURE_REPAIR_CD

This field is populated only for failure service histories.

FAILURE_ REPAIR_DESCR

Failure Repair Description

W1_FAILURE_REPAIR_L.DESCR100

This field is populated with the description of the Failure Repair Code.

FAILURE_ROOT_CAUSE

Failure Root Cause

W1_SVC_HIST.BO_DATA_AREA

This field is populated with the value for the failure root cause XML element from the CLOB column. It is populated only for failure service histories.

FAILURE_COMMENT

Failure Comment

W1_SVC_HIST.BO_DATA_AREA

This field is populated with the value for the comments XML element from the CLOB column. It is populated only for failure service histories.

FAILURE_COMP_CD

Failure Component Code

W1_SVC_HIST.FAILURE_COMP_CD

This field is populated only for the failure service histories.

FAILURE_COMP_DESCR

Failure Component Description

W1_FAILURE_COMP_L.DESCR100

This field is populated with the description of the Failure Component Code.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

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.

Work Order Dimension

The Work Order dimension stores all planned work orders from the source system.

Properties

Property

Value

Target Table

CD_WO

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

W1_WO

ODI Package

B1_PKG_CD_WO

ETL View

B1_D_WO_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

WO_KEY

Work Order Dimension Surrogate Key

This field is populated from the B1_WO_SEQ sequence.

Natural Keys

SRC_WO_ID

Source Work Order ID

W1_WO.WO_ID

Attributes

WO_DESCR

Work Order Description

W1_WO.DESCR100

WORK_PRIORITY_VAL

Work Priority

W1_WO.WORK_PRIORITY_FLG

This field is populated with the work order’s work priority.

PRIORITY_BKT_ST_RANGE

Work Priority Bucket Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

This field is populated with the start range of the work priority bucket for which this work order's work priority falls into. It goes hand in hand with the Work Priority Bucket End Range to constitute the actual bucket range the work order's work priority falls into.

Note : See Configuring Source in the Oracle Utilities Analytics Administration Guide for details about how to configure the buckets.

PRIORITY_BKT_ED_RANGE

Work Priority Bucket End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

This field is populated with the end range of the work priority bucket for which this work order's work priority falls into. It goes hand in hand with the Work Priority Bucket Start Range to constitute the actual bucket range the work order's work priority falls into.

Note : See Configuring Source in the Oracle Utilities Analytics Administration Guide for details about how to configure the buckets.

PRIORITY_BKT_DESCR

Work Priority Bucket Description

F1_BKT_CONFIG_VAL_L.DESCR

This field is populated with the description of the work priority bucket for which this work order's work priority falls into.

Note : See Configuring Source in the Oracle Utilities Analytics Administration Guide for details about how to configure the buckets.

PRIORITY_CAT_CD

Work Priority Category Code

F1_BKT_CONFIG_VAL.BKT_VAL_TYPE_CD

This field is populated with the category of the work priority bucket for which this work order's work priority falls into. An example of a category is 'High'.

PRIORITY_CAT_DESCR

Work Priority Category Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description of the work priority category from Lookup Language table for PRI_CAT_FLG lookup field.

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

WO_NUM

Work Order Number

W1_WO.WO_NUM

WORK_TYPE_CD

Work Type Flag

W1_WO.WORK_TYPE_FLG

WORK_TYPE_DESCR

Work Type Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description of the work type from Lookup Language table for WORK_TYPE_FLG lookup field.

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

WORK_CAT_CD

Work Category Flag

W1_WO.WORK_CATEGORY_FLG

WORK_CAT_DESCR

Work Category

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description of the work category from Lookup Language table for WORK_CATEGORY_FLG lookup field.

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

WORK_CLASS_CD

Work Class Flag

W1_WO.WORK_CLASS_FLG

WORK_CLASS_DESCR

Work Class

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description of the work class from Lookup Language table for WORK_CLASS_FLG lookup field.

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

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

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.

Work Order Activity Dimension

The Work Order Activity dimension contains the activities in the system with work role of planning allowed.

Properties

Property

Value

Target Table

CD_WO_ACTIVITY

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

W1_ACTIVITY

ODI Package

B1_PKG_CD_WO_ACTIVITY

ETL View

B1_D_WO_ACTIVITY_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

WO_ACTIVITY_KEY

Work Order Activity Key

This field is populated from the B1_WO_ACTIVITY_SEQ sequence.

Natural Keys

SRC_WO_ACTIVITY_ID

Source Activity ID

W1_ACTIVITY.ACT_ID

Attributes

HELD_FOR_PARTS_CD

Held for Parts Code

W1_ACTIVITY.HELD_FOR_PARTS_FLG

HELD_FOR_PARTS_DESCR

Held for Parts Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

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

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

WO_ACTIVITY_TYPE_CD

Activity Type Code

W1_ACTIVITY.ACT_TYPE_CD

WO_ACTIVITY_TYPE_DESCR

Activity Type Description

W1_ACTIVITY_TYPE_L.DESCR100

SERVICE_CLASS_CD

Service Class Code

W1_ACTIVITY.SERVICE_CLASS_CD

SERVICE_CLASS_DESCR

Service Class Description

W1_SERVICE_CLASS_L.DESCR100

RENEWAL_CD

Renewal Flag

W1_SERVICE_CLASS.RENEWAL_FLG

RENEWAL_DESCR

Renewal Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

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

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

EMERGENCY_CD

Emergency Flag

W1_ACTIVITY.EMERGENCY_FLG

EMERGENCY_DESCR

Emergency Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

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

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

WO_ACTIVITY_DESCR

Activity Description

W1_ACTIVITY.DESCR100

WO_ACTIVITY_NBR

Activity Number

W1_ACTIVITY.ACT_NUM

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

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.

Work Order Activity BO Status Dimension

The Work Order Activity BO Status dimension contains all combinations of BO/status/status reason that are applicable for instantiable BOs for Activity MO.

Properties

Property

Value

Target Table

CD_WO_ACTIVITY_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1_BUS_OBJ

F1_BUS_OBJ_STATUS_RSN

ODI Package

B1_PKG_CD_WO_ACTIVITY_STATUS

ETL View

B1_D_WO_ACTIVITY_STATUS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

WO_ACTIVITY_STATUS_KEY

Work Order Activity BO Status Key

This field is populated from the B1_WO_ACTIVITY_STATUS_SEQ sequence.

Natural Keys

BUS_OBJ_CD

Business Object

F1_BUS_OBJ.BUS_OBJ_CD

STATUS_CD

BO Status Code

F1_BUS_OBJ_STATUS.BO_STATUS_CD

This field is populated with the statuses available on life cycle business object of the current business object.

STATUS_RSN_CD

Status Reason Code

F1_BUS_OBJ_STATUS. BO_STATUS_REASON_CD

For business objects where status reason is optional, a record with a dummy value of ‘***’ for status reason code is inserted. It is used to map the fact records which do not have a status reason code.

Attributes

STATUS_COND_CD

Status Condition Code

F1_BUS_OBJ_STATUS.BO_STATUS_COND_FLG

STATUS_COND_DESCR

Status Condition Description

CI_LOOKUP_VAL_L.DE

SCR_OVRD

CI_LOOKUP_VAL_L.DE

SCR

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

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

BUS_OBJ_DESCR

Business Object Description

F1_BUS_OBJ_L.DESCR

STATUS_DESCR

Status Description

F1_BUS_OBJ_STATUS_L.DESCR

STATUS_RSN_DESCR

Status Reason Description

F1_BUS_OBJ_STATUS_RSN_L.DESCR

BACKLOG_IND

Backlog Indicator

F1_BUS_OBJ_STATUS_OPT.BO_OPT_VAL

This field is set to ‘1’ if the BO option value for the current BO status is set as backlog (W1BL). Else, it is set to ‘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 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.

Work Order BO Status Dimension

The Work Order BO Status dimension includes all combinations of BO/status/status reason that are applicable for instantiable BOs for Work Order MO.

Properties

Property

Value

Target Table

CD_WO_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

F1-BUS OBJ

F1-STSREASON

ODI Package

B1_PKG_CD_WO_STATUS

ETL View

B1_D_WO_STATUS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

WO_STATUS_KEY

Work Order BO Status Key

This field is populated from the B1_WO_STATUS_SEQ sequence.

Natural Keys

BUS_OBJ_CD

Business Object

F1_BUS_OBJ.BUS_OBJ_CD

STATUS_CD

BO Status Code

F1_BUS_OBJ_STATUS.BO_STATUS_CD

This field is populated with the statuses available on life cycle business object of the current business object.

STATUS_RSN_CD

Status Reason Code

F1_BUS_OBJ_STATUS. BO_STATUS_REASON_FLG

For business objects where status reason is optional, a record with a dummy value of ‘***’ for status reason code is inserted. It is used to map the fact records which do not have a status reason code.

Attributes

STATUS_COND_CD

Status Condition Code

F1_BUS_OBJ_STATUS.BO_STATUS_COND_FLG

STATUS_COND_DESCR

Status Condition Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

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

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

BUS_OBJ_DESCR

Business Object Description

F1_BUS_OBJ_L.DESCR

STATUS_DESCR

Status Description

F1_BUS_OBJ_STATUS_L.DESCR

STATUS_RSN_DESCR

Status Reason Description

F1_BUS_OBJ_STATUS_RSN_L.DESCR

BACKLOG_IND

Backlog Indicator

F1_BUS_OBJ_STATUS_OPT.BO_OPT_VAL

This field is set to ‘1’ if the BO option value for the current BO status is set as backlog (W1BL). Else, it is set to ‘0’.

PLANNED_IND

Planned Indicator

F1_BUS_OBJ_STATUS_OPT.BO_OPT_VAL

This field is set to ‘1’ if the BO option value for the current BO status is set as backlog (W1PL). Else, it is set to ‘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 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.

Shared Dimensions

Shared dimensions are used in the star schemas of other source products as well. 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.

Address Dimension

The Address dimension extracts data from the Location/Node entity in the Oracle Utilities Operational Device Management system. The address constituents from the location are retrieved and stored in the target dimension.

Properties

Property

Value

Target Table

CD_ADDR

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

W1_NODE

ODI Package

B1_PKG_CD_ADDR

ETL View

B1_D_ADDR_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ADDR_KEY

Address Dimension

This field is populated from the SPL_ADDR_SEQ sequence.

Natural Keys

SRC_ADDR_ID

Address ID

Attributes

ADDR_INFO

Address

W1_NODE.ADDRESS1

W1_NODE.CITY

W1_NODE.STATE

W1_NODE.POSTAL

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

ADDR_LINE1

Address Line 1

W1_NODE.ADDRESS1

ADDR_LINE2

Address Line 2

W1_NODE.ADDRESS2

ADDR_LINE3

Address Line 3

W1_NODE.ADDRESS3

ADDR_LINE4

Address Line 4

W1_NODE.ADDRESS4

CITY

City

W1_NODE.CITY

COUNTY

County

W1_NODE.COUNTY

POSTAL

Postal Code

W1_NODE.POSTAL

STATE_CD

State Code

W1_NODE.STATE

STATE_DESCR

State Description

CI_STATE_L.DESCR

COUNTRY_CD

Country Code

W1_NODE.COUNTRY

COUNTRY_DESCR

Country Description

CI_COUNTRY_L.DESCR

CROSS_STREET

Cross Street

W1_NODE.W1_CROSS_STREET

SUBURB

Suburb

W1_NODE.W1_SUBURB

GEO_CODE

Geographical Code

W1_NODE.GEO_CODE

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

UDF11_CD

User Defined Field 11 Code

UDF11_DESCR

User Defined Field 11 Description

UDF12_CD

User Defined Field 12 Code

UDF12_DESCR

User Defined Field 12 Description

UDF13_CD

User Defined Field 13 Code

UDF13_DESCR

User Defined Field 13 Description

UDF14_CD

User Defined Field 14 Code

UDF14_DESCR

User Defined Field 14 Description

UDF15_CD

User Defined Field 15 Code

UDF15_DESCR

User Defined Field 15 Description

UDF16_CD

User Defined Field 16 Code

UDF16_DESCR

User Defined Field 16 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End 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 : Address dimension, being a shared dimension, is populated by the ETL for other source products. As such the ETL for other source products populate UDF1 to UDF6 due to which they are not available for user extension even in Oracle Utilities Work and Asset Management system. Customers can use the other 10 UDF columns (UDF7 to UDF16) for any extension.

Asset Dimension

The Asset dimension extracts all assets defined in the system. The dimension holds all generic asset information, such as the specification details, asset type, and asset class information.

Properties

Property

Value

Target Table

CD_UTIL_ASSET

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

W1_ASSET

ODI Package

B1_PKG_CD_UTIL_ASSET

ETL View

B1_D_UTIL_ASSET_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

UTIL_ASSET_KEY

Utility Asset Dimension

This field is populated from the B1_UTIL_ASSET_SEQ sequence.

Natural Keys

SRC_UTIL_ASSET_ID

Source Utility Asset ID

W1_ASSET.ASSET_ID

Attributes

UTIL_ASSET_INFO

Utility Asset Information

W1_ASSET_IDENTIFIER.W1_ID_VALUE

This field is populated with the badge number retrieved based on the Asset ID populated.

UTIL_ASSET_DESCR

Utility Asset Description

W1_ASSET.DESCRLONG

This field is populated with the first 254 characters from the respective column in the Asset table.

SPEC_CD

Specification Code

W1_ASSET.SPECIFICATION_CD

SPEC_DESCR

Specification Description

W1_SPECIFICATION_L.DESCR100

MANUFACTURER_CD

Manufacturer Code

W1_SPECIFICATION. W1_MANUFACTURER_CD

MANUFACTURER_DESCR

Manufacturer Description

W1_MANUFACTURER_L.DESCR100

MODEL

Model

W1_SPECIFICATION_IDENTIFIER.W1_ID_VALUE

This field is populated based on the specification code on the asset and where the specification identifier flag is W1MD.

UTIL_ASSET_TYPE_CD

Utility Asset Type Code

W1_ASSET.ASSET_TYPE_CD

UTIL_ASSET_TYPE_DESCR

Utility Asset Type Description

W1_ASSET_TYPE_L.DESCR100

UTIL_ASSET_CLASS_CD

Utility Asset Class Code

W1_ASSET_TYPE.ASSET_CLASS_FLG

UTIL_ASSET_CLASS_DESCR

Utility Asset Class Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

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

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

UTIL_ASSET_CAT_CD

Utility Asset Category Code

W1_ASSET_TYPE.ASSET_CAT_FLG

UTIL_ASSET_CAT_DESCR

Utility Asset Category Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

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

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

UTIL_ASSET_COND_SCORE

Asset Condition Score

W1_ASSET.CONDITION_RATING

UTIL_ASSET_CONF_RAT

Confidence Rating

W1_ASSET.CONFIDENCE_RATING

COND_SCORE_BKT_ST_RANGE

Asset Condition Score Bucket Start Range

F1_BKT_CONFIG_VAL.B KT_START_RANGE

This field is populated with the start range of the condition score bucket for which this asset's condition score falls into. It goes hand in hand with the Asset Condition Score Bucket End Range to constitute the actual bucket range the asset's score falls into.

Note : For details about how the buckets are configured, see Configuring Source in the Oracle Utilities Analytics Administration Guide.

COND_SCORE_BKT_ED_RANGE

Asset Condition Score Bucket End Range

F1_BKT_CONFIG_VAL.B KT_END_RANGE

This field is populated with the end range of the condition score bucket for which this asset's condition score falls into. It goes hand in hand with the Asset Condition Score Bucket Start Range to constitute the actual bucket range the asset's score falls into.

Note : For details about how the buckets are configured, see Configuring Source in the Oracle Utilities Analytics Administration Guide.

COND_SCORE_BKT_DESCR

Asset Condition Score Bucket Description

F1_BKT_CONFIG_VAL_ L.DESCR

This field is populated with the description of the condition score bucket for which this asset's condition score falls into.

Note : For details about how the buckets are configured, see Configuring Source in the Oracle Utilities Analytics Administration Guide.

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

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.

Asset Inspection Status Dimension

The Asset Inspection Status dimension is populated with all the possible outcomes for asset inspections. For example: whether it passed, failed, etc.

Properties

Property

Value

Target Table

CD_ASSET_INSP_STATUS

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

CI_LOOKUP_VAL_L

ODI Package

B1_PKG_CD_ASSET_INSP_STATUS

ETL View

B1_D_ASSET_INSP_STATUS_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ASSET_INSP_STATUS_KEY

Asset Inspection Status Dimension Surrogate Key

This field is populated from the B1_ASSET_INSP_STATUS_SEQ sequence.

Natural Keys

ASSET_INSP_STATUS_CD

Asset Inspection Status Code

CI_LOOKUP_VAL_L.FIELD_VALUE

This field is populated with the lookup value for the lookup field INSPECTION_INDICATOR_FLG.

Attributes

ASSET_INSP_STATUS_DESCR

Asset Inspection Status Description

CI_LOOKUP_VAL_L.DESCR

CI_LOOKUP_VAL_L.DESCR_OVRD

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

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

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.

Crew Dimension

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

Properties

Property

Value

Target Table

CD_CREW

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

W1_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

W1_CREW.W1_CREW_ID

Attributes

CREW_CD

Crew Code

This field is populated with a hyphen (-) since it is not used for the Oracle Utilities Work and Asset Management source product. This dimension is a shared dimension, and so the column is used for other source products.

CREW_DESCR

Crew Description

W1_CREW.W1_CREW_NAME

CREW_TYPE_CD

Crew Type Code

W1_CREW.CREW_TYPE_CD

CREW_TYPE_DESCR

Crew Type Description

W1_CREW_TYPE_L.DESCR100

ORG_UNIT1_CD

Crew Hierarchy-1 Code

ORG_UNIT1_DESCR

Crew Hierarchy-1 Description

ORG_UNIT2_CD

Crew Hierarchy-2 Code

ORG_UNIT2_DESCR

Crew Hierarchy-2 Description

ORG_UNIT3_CD

Crew Hierarchy-3 Code

ORG_UNIT3_DESCR

Crew Hierarchy-3 Description

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDF6_CD

User Defined Field 6 Code

UDF6_DESCR

User Defined Field 6 Description

UDF7_CD

User Defined Field 7 Code

UDF7_DESCR

User Defined Field 7 Description

UDF8_CD

User Defined Field 8 Code

UDF8_DESCR

User Defined Field 8 Description

UDF9_CD

User Defined Field 9 Code

UDF9_DESCR

User Defined Field 9 Description

UDF10_CD

User Defined Field 10 Code

UDF10_DESCR

User Defined Field 10 Description

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End 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.

Fiscal Calendar Dimension

The Fiscal Calendar dimension stores the accounting calendar periods defined in the Oracle Utilities Work and Asset Management system.

Properties

Property

Value

Target Table

CD_FISCAL_CAL

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

W1_CALENDAR_PERIOD

ODI Package

B1_PKG_CD_FISCAL_CAL

ETL View

B1_D_FISCAL_CAL_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

FISCAL_CAL_KEY

Fiscal Period Dimension Surrogate Key

This field is populated from the SPL_FISCAL_SEQ sequence.

Natural Keys

FISCAL_CAL_CD

Fiscal Calendar Code

W1_CALENDAR_PERIOD.W1_CALENDAR_CD

FISCAL_YEAR

Fiscal Year

W1_CALENDAR_PERIOD.W1_FISCAL_YEAR

PERIOD_NBR

Fiscal Period Code

W1_CALENDAR_PERIOD.ACCTG_PERIOD

Attributes

FISCAL_CAL_DESCR

Fiscal Calendar Description

W1_CALENDAR_L.DESCR100

ABS_PERIOD_NBR

Absolute Period Number

This field is calculated as an incrementing sequence for each period within the calendar code.

PERIOD_DESCR

Fiscal Period Description

W1_CALENDAR_PERIOD_L.DESCR100

PERIOD_START_DT

Fiscal Period Start Date

W1_CALENDAR_PERIOD.W1_START_DT

PERIOD_END_DT

Fiscal Period End Date

W1_CALENDAR_PERIOD.W1_END_DT

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.

Location Dimension

The Location dimension extracts data from the Location entity in Oracle Utilities Work and Asset Management system. The location hierarchy is extracted up to 10 levels.

Properties

Property

Value

Target Table

CD_LOCATION

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

W1_NODE

ODI Package

B1_PKG_CD_LOCATION

ETL View

B1_D_LOCATION_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

LOCATION_KEY

Location Dimension Surrogate Key

This field is populated from the B1_LOCATION_SEQ sequence.

Natural Keys

SRC_LOCATION_ID

Source Location ID

W1_NODE.NODE_ID

Attributes

LOCATION_INFO

Location Information

W1_NODE.DESCR100

Note : This field can be customized to override the value provided out-of-the-box.

LOCATION_TYPE_CD

Location Type Code

W1_NODE.NODE_TYPE_CD

LOCATION_TYPE_DESCR

Location Type Description

W1_NODE_TYPE_L.DESCR100

LOCATION_CLASS_CD

Location Class Code

W1_NODE.LOCATION_CLASS_FLG

LOCATION_CLASS_DESCR

Location Class Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

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

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

LOCATION_TYPE_CLASS_CD

Location Type Class Code

W1_NODE_TYPE.NODE_CLASS_FLG

LOCATION_TYPE_CLASS_DESCR

Location Type Class Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

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

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

LOCATION_CATEGORY

Location Category

This field is populated with:

•

A - if the location is an Asset Location

•

S - if the location is a Storeroom

•

O - if the location is an Organization

CRITICALITY_VAL

Asset Criticality Flag

W1_NODE.CRITICALITY_FLG

This field is populated with the location's actual criticality.

CRITICALITY_BKT_ST_RANGE

Asset Criticality Bucket Start Range

F1_BKT_CONFIG_VAL.BKT_START_RANGE

This field is populated with the start range of the criticality bucket for which this asset's criticality falls into. It goes hand in hand with the Asset Criticality Bucket End Range to constitute the actual bucket range the asset's criticality falls into.

Note : See Configuring Source in the Oracle Utilities Analytics Administration Guide for details about how to configure the buckets.

CRITICALITY_BKT_ED_RANGE

Asset Criticality Bucket End Range

F1_BKT_CONFIG_VAL.BKT_END_RANGE

This field is populated with the end range of the criticality bucket for which this asset's criticality falls into. It goes hand in hand with the Asset Criticality Bucket Start Range to constitute the actual bucket range the asset's criticality falls into.

Note : See Configuring Source in the Oracle Utilities Analytics Administration Guide for details about how to configure the buckets.

CRITICALITY_BKT_DESCR

Asset Criticality Bucket Description

F1_BKT_CONFIG_VAL_L.DESCR

This field is populated with the description of the criticality bucket for which the location's criticality falls into.

Note : See Configuring Source in the Oracle Utilities Analytics Administration Guide for details about how to configure the buckets.

CRITICALITY_CAT_CD

Location Criticality Category Code

F1_BKT_CONFIG_VAL.BKT_VAL_TYPE_CD

This field is populated with the category of the Criticality Bucket for which this location's criticality falls into.

Examples of criticality category are 'Critical', 'High', 'Medium', and 'Low'.

Note : See Configuring Source in the Oracle Utilities Analytics Administration Guide for details about how to configure the buckets.

CRITICALITY_CAT_DESCR

Location Criticality Category Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description from the Lookup Language table for the lookup field CRIT_CAT_FLG. If override description is not available, regular description is extracted

Note : See Configuring Source in the Oracle Utilities Analytics Administration Guide for details about how to configure the buckets.

ENV_RATING

Environment Rating

This field is populated with the Environment Rating retrieved from the location's characteristic of type "Environment Rating (W1-ENVRT)".

RCM_SYSTEM_CD

RCM System Code

W1_NODE. RCM_SYSTEM_FLG

RCM_SYSTEM_DESCR

RCM System Description

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

PARENT_LOCATION_LVL1_CD

Parent Location Level 1 Code

W1_NODE.PARENT_NODE_ID

This field is populated with the location ID of the parent of the current location.

PARENT_LOCATION_LVL1_DESCR

Parent Location Level 1 Description

W1_NODE.DESCR100

This field is populated with the description of parent 1 location.

PARENT_LOCATION_LVL2_CD

Parent Location Level 2 Code

W1_NODE.PARENT_NODE_ID

This field is populated with the location ID of the parent of the current location.

PARENT_LOCATION_LVL2_DESCR

Parent Location Level 2 Description

W1_NODE.DESCR100

This field is populated with the description of parent 2 location.

PARENT_LOCATION_LVL3_CD

Parent Location Level 3 Code

W1_NODE.PARENT_NODE_ID

This field is populated with the location ID of the parent of the current location.

PARENT_LOCATION_LVL3_DESCR

Parent Location Level 3 Description

W1_NODE.DESCR100

This field is populated with the description of parent 3 location.

PARENT_LOCATION_LVL4_CD

Parent Location Level 4 Code

W1_NODE.PARENT_NODE_ID

This field is populated with the location ID of the parent of the current location.

PARENT_LOCATION_LVL4_DESCR

Parent Location Level 4 Description

W1_NODE.DESCR100

This field is populated with the description of parent 4 location.

PARENT_LOCATION_LVL5_CD

Parent Location Level 5 Code

W1_NODE.PARENT_NODE_ID

This field is populated with the location ID of the parent of the current location.

PARENT_LOCATION_LVL5_DESCR

Parent Location Level 5 Description

W1_NODE.DESCR100

This field is populated with the description of parent 5 location.

PARENT_LOCATION_LVL6_CD

Parent Location Level 6 Code

W1_NODE.PARENT_NODE_ID

This field is populated with the location ID of the parent of the current location.

PARENT_LOCATION_LVL6_DESCR

Parent Location Level 6 Description

W1_NODE.DESCR100

This field is populated with the description of parent 6 location.

PARENT_LOCATION_LVL7_CD

Parent Location Level 7 Code

W1_NODE.PARENT_NODE_ID

This field is populated with the location ID of the parent of the current location.

PARENT_LOCATION_LVL7_DESCR

Parent Location Level 7 Description

W1_NODE.DESCR100

This field is populated with the description of parent 7 location.

PARENT_LOCATION_LVL8_CD

Parent Location Level 8 Code

W1_NODE.PARENT_NODE_ID

This field is populated with the location ID of the parent of the current location.

PARENT_LOCATION_LVL8_DESCR

Parent Location Level 8 Description

W1_NODE.DESCR100

This field is populated with the description of parent 8 location.

PARENT_LOCATION_LVL9_CD

Parent Location Level 9 Code

W1_NODE.PARENT_NODE_ID

This field is populated with the location ID of the parent of the current location.

PARENT_LOCATION_LVL9_DESCR

Parent Location Level 9 Description

W1_NODE.DESCR100

This field is populated with the description of parent 9 location.

PARENT_LOCATION_LVL10_CD

Parent Location Level 10 Code

W1_NODE.PARENT_NODE_ID

This field is populated with the location ID of the parent of the current location.

PARENT_LOCATION_LVL10_DESCR

Parent Location Level 10 Description

W1_NODE.DESCR100

This field is populated with the description of parent 10 location.

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

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End 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.

Service History Type Dimension

The Service History Type dimension extracts the service history types and the respective category details from the source system.

Properties

Property

Value

Target Table

CD_SERVICE_HIST_TYPE

Table Type

Dimension

SCD Type

Type 1

Source System Driver Table

W1_SVC_HIST_TYPE

ODI Package

B1_PKG_CD_SERVICE_HIST_TYPE

ETL View

B1_D_SERVICE_HIST_TYPE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SERVICE_HIST_TYPE_KEY

Service History Type Dimension Surrogate Key

This field is populated from the B1_SERVICE_HIST_TYPE_SEQ sequence.

Natural Keys

SERVICE_HIST_TYPE_CD

Service History Type Code

W1_SVC_HIST_TYPE.SVC_HIST_TYPE_CD

Attributes

SERVICE_HIST_TYPE_DESCR

Service History Type Description

W1_SVC_HIST_TYPE_L.DESCR100

SERVICE_HIST_CAT_CD

Service History Category Code

W1_SVC_HIST_TYPE.SVC_HIST_CATEGORY_FLG

SERVICE_HIST_CAT_DESCR

Service History Category 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 SVC_HIST_CATEGORY_FLG lookup field.

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

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.

User Defined Dimensions

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

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

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

Fields

Target Field

Description

Surrogate Key

<Fact_name_prefix>_UDDn_KEY

For example: The surrogate key for Location Snapshot UDD is LOCATION_SNAP_UDD1_KEY

<Fact name> User Defined Dimension n Dimension Surrogate Key

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

Natural Keys

UDD1_CD

User Defined Dimension 1 Code

Attributes

UDD1_DESCR

User Defined Dimension 1 Description

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDL1_CD

User Defined Field Long 1 Code

UDL1_DESCR

User Defined Field Long 1 Description

UDL2_CD

User Defined Field Long 2 Code

UDL2_DESCR

User Defined Field Long 2 Description

UDL3_CD

User Defined Field Long 3 Code

UDL3_DESCR

User Defined Field Long 3 Description

UDL4_CD

User Defined Field Long 4 Code

UDL4_DESCR

User Defined Field Long 4 Description

UDL5_CD

User Defined Field Long 5 Code

UDL5_DESCR

User Defined Field Long 5 Description

COMMENT1

Comment 1

COMMENT2

Comment 2

COMMENT3

Comment 3

COMMENT4

Comment 4

COMMENT5

Comment 5

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

DATA_SOURCE_IND

Data Source Indicator

JOB_NBR

Job Number

Activity UDD1 Dimension

Property

Value

Target Table

CD_WO_ACTIVITY_UDD1

Table Type

Dimension

SCD Type

Type 2

Activity UDD2 Dimension

Property

Value

Target Table

CD_WO_ACTIVITY_UDD2

Table Type

Dimension

SCD Type

Type 2

Asset FT UDD1 Dimension

Property

Value

Target Table

CD_ASSET_FT_UDD1

Table Type

Dimension

SCD Type

Type 2

Asset FT UDD2 Dimension

Property

Value

Target Table

CD_ASSET_FT_UDD2

Table Type

Dimension

SCD Type

Type 2

Asset Snapshot UDD1 Dimension

Property

Value

Target Table

CD_ASSET_SNAP_UDD1

Table Type

Dimension

SCD Type

Type 2

Asset Snapshot UDD2 Dimension

Property

Value

Target Table

CD_ASSET_SNAP_UDD2

Table Type

Dimension

SCD Type

Type 2

Location Snapshot UDD1 Dimension

Property

Value

Target Table

CD_LOCATION_SNAP_UDD1

Table Type

Dimension

SCD Type

Type 2

Location Snapshot UDD2 Dimension

Property

Value

Target Table

CD_LOCATION_SNAP_UDD2

Table Type

Dimension

SCD Type

Type 2

Work Order UDD1 Dimension

Property

Value

Target Table

CD_WO_UDD1

Table Type

Dimension

SCD Type

Type 2

Work Order UDD2 Dimension

Property

Value

Target Table

CD_WO_UDD2

Table Type

Dimension

SCD Type

Type 2

Facts

Activity Fact

The Activity fact is an accumulation of activities associated to work orders in the source system, with a refresh of certain measures on a day-to-day basis.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_WO_ACTIVITY

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

W1_ACTIVITY

ODI Package Name

B1_PKG_CF_WO_ACTIVITY

ETL View Name

B1_F_WO_ACTIVITY_KVW

Materialized View Name

B1_WO_ACTIVITY_MON_MV1

B1_WO_ACTIVITY_MV1

B1_WO_ACTIVITY_MON_TOPX_MV1

B1_WO_ACTIVITY_MON_TOPX_MV2

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

WO_ACTIVITY_KEY

Activity Surrogate Key

This field is populated from the B1_F_WO_ACTIVITY_SEQ sequence.

Natural Keys

SRC_ACTIVITY_ID

Source Activity ID

W1_ACTIVITY.ACT_ID

Measures

NBR_DAYS_OVERDUE

Number of Days Overdue

This field is calculated for non-finalized activities with Calendar Anniversary/Interval trigger types.

If the original work date has been crossed, the measure is calculated as the difference between the current date and the original work date.

NBR_CYC_OVERDUE

Number of Cycles Overdue

This field is calculated based on the number of days overdue and the trigger frequency.

Trigger frequency is determined in days based on the information on the maintenance trigger.

PLANNED_DUR

Planned Duration

This field is calculated (in hours) based on the duration on the activity's non-canceled labor resource requirements.

FACT_CNT

Count

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

Degenerate Dimensions

TRIGGER_TYPE_CD

Trigger Type Flag

W1_MAINT_TRIGGER. TRIGGER_TYPE_FLG

This field is populated with the trigger type of the maintenance trigger of the activity.

TRIGGER_TYPE_DESCR

Trigger Type Flag Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description of the trigger type from Lookup Language table for TRIGGER_TYPE_FLG lookup field.

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

SCHEDULED_IND

Scheduled Indicator

If at least one labor resource requirement is scheduled in a shift, this field is populated with ‘1’. Else, it is set to ‘0’.

BREAK_IN_IND

Break-in Indicator

If at least one labor resource booked time on this activity but the work spent is not scheduled in a shift, this field is set to 1. Else, it is set to 0.

Note : This field is refreshed if any changes to timesheets occur.

NO_COST_IND

No Cost Indicator

No Cost Indicator is determined by summing the amount of frozen FTs associated with the activity.

If the sum is ‘0’, this field is set to ‘1’. Else, it is set to ‘0’.

Note : This field is refreshed if any changes to timesheets occur.

NO_LABOR_IND

No Labor Indicator

The No Labor Indicator is used to indicate if any labor resources are used for this activity. It is determined by looking for any posted timesheet details for the activity that has resource requirements of Craft.

Note : This field is refreshed if any changes to timesheets occur.

ORIGINAL_WORK_DT

Original Work Date

W1_ACTIVITY. ORIGINAL_WORK_DT

This field is populated based on the original work date of the activity.

REQUIRED_BY_DT

Required By Date

W1_ACTIVITY. REQUIRED_BY_DT

This field is populated based on the required date of the activity.

Foreign Keys

WO_KEY

Work Order Dimension Surrogate Key

W1_ACTIVITY.WO_ID

This is populated with the foreign key of the Work Order dimension based on the work order of the activity.

WO_ACTIVITY_D_KEY

Activity Dimension Surrogate Key

W1_ACTIVITY.ACT_ID

This field is populated with the foreign key of the Work Activity dimension for this activity.

UTIL_ASSET_KEY

Utility Asset Dimension Surrogate Key

W1_ACTIVITY. ASSET_ID

This field is populated with the foreign key of the Utility Asset dimension based on the asset associated with this activity.

LOCATION_KEY

Location Dimension Surrogate Key

W1_ACTIVITY.NODE_ID

This field is populated with the foreign key of the Location dimension based on the node associated with this activity.

ADDR_KEY

Address Dimension Surrogate Key

W1_ACTIVITY.NODE_ID

This field is populated with the foreign key of the Address dimension based on the node associated with this activity.

OWNING_ORG_KEY

Owning Organization Dimension Surrogate Key

W1_ACTIVITY. OWNING_ACCESS_GRP_CD

This field is populated with the foreign key of the Owning Organization dimension based on the owning access group code associated with this activity.

CREW_KEY

Crew Dimension Surrogate Key

W1_TIMESHEET_DETAIL. W1_CREW_ID

W1_CREW_SHIFT.W1_CREW_ID

W1_ACTIVITY.W1_CREW_ID

This field is populated with the foreign key of the Crew dimension based on the crew associated with the activity.

The crew is determined by one of the following:

•

Looking up the crew via the time sheet detail associated with the activity.

•

Looking into the crew shifts associated with the activity's resource requirements.

•

Looking up the recommended crew on the activity.

W_PLANNER_KEY

Planner Dimension Surrogate Key

W1_ACTIVITY. PLANNER_CD

This field is populated with the foreign key of the Planner dimension based on the planner code associated with this activity.

ORIGINAL_WORK_DT_KEY

Original Work Date Key

W1_ACTIVITY. ORIGINAL_WORK_DT

This field is populated from the Date table based on original work date populated into the Original Work Date field.

REQUIRED_BY_DT_KEY

Required By Date Key

W1_ACTIVITY. REQUIRED_BY_DT

This field is populated from the Date table based on the required by date populated into the Required By Date field.

NUM_CYC_OD_KEY

Number of Cycles Overdue Dimension Surrogate Key

This field is populated from the Number of Cycles Overdue table based on the number of cycles overdue start/end bucket range.

WO_ACTIVITY_STATUS_KEY

Activity BO Status Dimension Surrogate Key

W1_ACTIVITY.BUS_OBJ_CD

W1_ACTIVITY.BO_STATUS_CD

W1_ACTIVITY.BO_STATUS_REASON_CD

This field is populated from the Activity table based on the BO code, status, and reason associated with the current activity ID.

WO_ACTIVITY_UDD1_KEY

Activity User Defined Dimension 1 Surrogate Key

WO_ACTIVITY_UDD2_KEY

Activity User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

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.

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.

Asset FT Fact

The Asset FT fact is an accumulation of every financial transactions of an asset. It captures the financial information at the FT and asset level.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_ASSET_FT

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

W1_FT

W1_ACTIVITY

W1_ASSET

ODI Package Name

B1_PKG_CF_ASSET_FT

ETL View Name

Materialized View Name

B1_ASSET_FT_MON_MV1

B1_ASSET_FT_MON_TOPX_MV1

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ASSET_FT_KEY

Asset FT Fact Surrogate Key

This field is populated from the B1_ASSET_FT_SEQ sequence.

Natural Keys

SRC_FT_ID

Source FT ID

W1_FT.W1_FT_ID

SRC_ASSET_ID

Source Utility Asset ID

W1_ACTIVITY_ASSET.ASSET_ID

Measures

PM_LABOR_HRS

PM Labor Hours

If the work order associated with this FT is for Preventive Maintenance, this field is populated with the labor hours associated with the FT's timesheet detail prorated by the asset's percentage in the cost distribution.

The field is mutually exclusive with CM Labor Hours.

Labor Hours = Hours in Timesheet Detail * Asset share percentage on FT Activity

CM_LABOR_HRS

CM Labor Hours

If the work order associated with this FT is for Corrective Maintenance, this field is populated with the labor hours associated with this FT's timesheet detail prorated by the asset's percentage in the cost distribution.

This field is mutually exclusive with PM Labor Hours.

Labor Hours = Hours in Timesheet Detail * Asset share percentage on FT Activity

PM_COST

PM Cost

If the work order associated with this FT is for Preventive Maintenance, this field is populated with the amount associated with this FT prorated by the asset's percentage in the cost distribution.

This field is mutually exclusive with CM Cost.

Cost = (FT amount * percentage)/100

CM_COST

CM Cost

If the work order associated with this FT is for Corrective Maintenance, this field is populated with the amount associated with this FT prorated by the asset's percentage in the cost distribution.

This field is mutually exclusive with PM Cost.

Cost = (FT amount * percentage)/100

FACT_CNT

Count

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

Degenerate Dimensions

OVERTIME _IND

Overtime Indicator

W1_TIMESHEET_DETAIL.REG_OVERTIME_FLG

This field is used to indicate if the FT is for overtime work. This is determined by checking the regular/overtime flag on the timesheet detail associated with the FT.

PARTICIPATION_CD

Participation Flag

W1_ACTIVITY_ASSET.PARTICIPATION_FLG

This field is populated based on the participation flag associated with this FT's activity and asset on the activity cost distribution table.

PARTICIPATION_DESCR

Participation Description

CI_LOOKUP_VAL_L.DESCR_OVRD

CI_LOOKUP_VAL_L.DESCR

This field is populated with the description of the participation flag from Lookup Language table for PARTICIPATION_FLG lookup field.

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

ACCOUNTING_DT

Accounting Date

W1_FT.W1_ACCOUNTING_DT

Foreign Keys

WO_KEY

Work Order Dimension Surrogate Key

W1_ACTIVITY.WO_ID

This field is populated with the foreign key of the Work Order dimension based on the work order of the activity.

WO_ACTIVITY_KEY

Work Order Activity Dimension Surrogate Key

W1_FT.ACT_ID

This field is populated with the foreign key of the Activity table based on the activity associated with the financial transaction.

UTIL_ASSET_KEY

Utility Asset Dimension Surrogate Key

W1_ACTIVITY_ASSET.A SSET_ID

This field is populated with the foreign key of the Asset table based on the activity associated with the financial transaction.

LOCATION_KEY

Location Dimension Surrogate Key

W1_ACTIVITY.NODE_ID

This field is populated with the foreign key of the Location table based on the node ID associated with the financial transaction.

ADDR_KEY

Address Dimension Surrogate Key

W1_ACTIVITY.NODE_ID

This field is populated with the foreign key of the Address table based on the node ID associated with the financial transaction.

RESRC_TYPE_KEY

Resource Type Dimension Surrogate Key

W1_TIMESHEET_DETAIL.RESRC_TYPE_ID

W1_STOCK_ITEM_DTL.RESRC_TYPE_ID

W1_ODC_DTL.RESRC_TYPE_ID

This field is populated based on the resource type on the timesheet detail of the FT.

If unavailable, the resource type of the material issue line's stock detail is used. Else, the resource type on the ODC details is used.

OWNING_ORG_KEY

Owning Organization Dimension Surrogate Key

W1_ASSET. OWNING_ACCESS_GRP_CD

This field is populated based on the owning access group of the asset.

FISCAL_CAL_KEY

Fiscal Period Dimension Surrogate Key

W1_FT.W1_ACCOUNTING_DT

W1_CALENDAR_PERIOD. W1_CALENDAR_CD

W1_CALENDAR_PERIOD. W1_FISCAL_YEAR

W1_CALENDAR_PERIOD. ACCTG_PERIOD

This field is populated based on the accounting date on the financial transaction. The fiscal calendar record where the accounting date falls into is used.

W_PLANNER_KEY

Planner Dimension Surrogate Key

W1_ACTIVITY.PLANNER_CD

This field is populated based on the planner code associated with the activity ID from the Activity table.

ACCOUNTING_DT_KEY

Accounting Date Key

W1_FT.W1_ACCOUNTING_DT

This field is populated from the Date table based on the date part of the accounting date/time (which is populated into the Accounting Date field).

ASSET_FT_UDD1_KEY

Asset FT User Defined Dimension 1 Surrogate Key

ASSET_FT_UDD2_KEY

Asset FT User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

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

Asset Snapshot Fact

The Asset Snapshot fact is a periodic snapshot of the asset details in the source system. It stores information, such as the asset's disposition at the end of the snapshot, how long the asset has been installed, how old since it was received, and more. Only those assets are extracted whose business object names have been configured as part of the extract parameter setup in source system.

For more details, see Configuring Source in the Oracle Utilities Analytics Administration Guide.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_ASSET_SNAP

Table Type

Fact

Fact Type

Snapshot

Source System Driver Table

W1_ASSET

ODI Package Name

B1_PKG_CF_ASSET_SNAP

ETL View Name

Materialized View Name

B1_ASSET_SNAP_MON_MV1

B1_ASSET_SNAP_MON_TOPX_MV1

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ASSET_SNAP_KEY

Asset Snapshot Fact Surrogate Key

This field is populated from the B1_ASSET_SNAP_SEQ sequence.

Natural Keys

SRC_ASSET_ID

Source Asset ID

W1_ASSET.ASSET_ID

SNAP_TYPE_CD

Snapshot Type Code

This field is populated based on the ETL job configuration.

Measures

AGE

Age in Days

W1_ASSET_NODE.EFF_DTTM

This field is populated with the asset’s age that is calculated as the difference (in days) between the earliest asset disposition date and the snapshot period end date.

INSTALL_AGE

Install Age in Days

W1_ASSET_NODE.ASSET_DPOS_FLG

W1_ASSET_NODE.EFF_DTTM

This field is populated with the asset's install age if the asset disposition at the end of the snapshot is ‘Installed’ or ‘In Service’. It is calculated as the difference (in days) between the latest asset disposition date and the snapshot period end date.

FAIL_CNT_LIFETIME

Count of Failures (Lifetime)

This field is populated with the number of times this asset failed in its lifetime.

The failure count is determined by counting the number of completed/closed high priority, regular work orders that were created against the asset from its very first installation until the snapshot period end date.

MTBF_LIFETIME

Mean Time Between Failures (Lifetime)

Mean Time Between Failure is the average length of operating time between failures for an asset.

This field is calculated by looking at the total operating hours of the asset divided by the number of failures.

Duration (in hours) = Snapshot end date - First installation of the asset

MTTR_LIFETIME

Mean Time to Repair (Lifetime)

Mean Time To Repair is the average time needed to restore an asset to its full operational condition upon a failure.

This field is calculated by summing the duration of all asset downtime (in hours) and dividing by the number of failures.

FAIL _CNT_BUCKET1

Count of Failures in Bucket 1

This is the total count of failures that falls within the first configurable bucket range.

FAIL _CNT_BUCKET2

Count of Failures in Bucket 2

This is the total count of failures that falls within the second configurable bucket range.

FAIL _CNT_BUCKET3

Count of Failures in Bucket 3

This is the total count of failures that falls within the third configurable bucket range.

FAIL _CNT_BUCKET4

Count of Failures in Bucket 4

This is the total count of failures that falls within the fourth configurable bucket range.

FAIL _CNT_BUCKET5

Count of Failures in Bucket5

This is the total count of failures that falls within the fifth configurable bucket range.

FAIL _CNT_BUCKET6

Count of Failures in Bucket 6

This is the total count of failures that falls within the sixth configurable bucket range.

FAIL_CNT_BUCKET7

Count of Failures in Bucket 7

This is the total count of failures that falls within the seventh configurable bucket range.

FAIL_CNT_BUCKET8

Count of Failures in Bucket 8

This is the total count of failures that falls within the eighth configurable bucket range.

FAIL_CNT_BUCKET9

Count of Failures in Bucket 9

This is the total count of failures that falls within the ninth configurable bucket range.

FAIL_CNT_BUCKET10

Count of Failures in Bucket 10

This is the total count of failures that falls within the last configurable bucket range.

MTBF_BUCKET1

Mean Time Between Failures in Bucket 1

This is the mean time between failures that falls within the first configurable bucket range.

MTBF_BUCKET2

Mean Time Between Failures in Bucket 2

This is the mean time between failures that falls within the second configurable bucket range.

MTBF_BUCKET3

Mean Time Between Failures in Bucket 3

This is the mean time between failures that falls within the third configurable bucket range.

MTBF_BUCKET4

Mean Time Between Failures in Bucket 4

This is the mean time between failures that falls within the fourth configurable bucket range.

MTBF_BUCKET5

Mean Time Between Failures in Bucket 5

This is the mean time between failures that falls within the fifth configurable bucket range.

MTBF_BUCKET6

Mean Time Between Failures in Bucket 6

This is the mean time between failures that falls within the sixth configurable bucket range.

MTBF_BUCKET7

Mean Time Between Failures in Bucket 7

This is the mean time between failures that falls within the seventh configurable bucket range.

MTBF_BUCKET8

Mean Time Between Failures in Bucket 8

This is the mean time between failures that falls within the eighth configurable bucket range.

MTBF_BUCKET9

Mean Time Between Failures in Bucket 9

This is the mean time between failures that falls within the ninth configurable bucket range.

MTBF_BUCKET10

Mean Time Between Failures in Bucket 10

This is the mean time between failure that falls within the last configurable bucket range.

MTTR_BUCKET1

Mean Time to Repair in Bucket 1

This is the mean time to repair that falls within the first configurable bucket range.

MTTR_BUCKET2

Mean Time to Repair in Bucket 2

This is the mean time to repair that falls within the second configurable bucket range.

MTTR_BUCKET3

Mean Time to Repair in Bucket 3

This is the mean time to repair that falls within the third configurable bucket range.

MTTR_BUCKET4

Mean Time to Repair in Bucket 4

This is the mean time to repair that falls within the fourth configurable bucket range.

MTTR_BUCKET5

Mean Time to Repair in Bucket 5

This is the mean time to repair that falls within the fifth configurable bucket range.

MTTR_BUCKET6

Mean Time to Repair in Bucket 6

This is the mean time to repair that falls within the sixth configurable bucket range.

MTTR_BUCKET7

Mean Time to Repair in Bucket 7

This is the mean time to repair that falls within the seventh configurable bucket range.

MTTR_BUCKET8

Mean Time to Repair in Bucket 8

This is the mean time to repair that falls within the eight configurable bucket range.

MTTR_BUCKET9

Mean Time to Repair in Bucket 9

This is the mean time to repair that falls within the ninth configurable bucket range.

MTTR_BUCKET10

Mean Time to Repair in Bucket 10

This is the mean time to repair that falls within the last configurable bucket range.

FACT_CNT

Count

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

Degenerate Dimensions

SNAPSHOT_DT

Snapshot Date

This field is populated with the last date of the current snapshot period (only the date part is populated).

LAST_FAIL_DTTM

Last Failure Date

W1_WO.CRE_DTTM

W1_WORK_REQ.CRE_DTTM

This field is populated with the date/time of the asset's latest failure.

See Count of Failures (Lifetime) field for definition of failure.

Foreign Keys

SNAPSHOT_DATE_KEY

Snapshot Date Key

This field is populated with the foreign key of the Date table based on the snapshot date/time associated with the asset.

W_ASSET_AGE_KEY

Asset Age Dimension Surrogate Key

This field is populated with the foreign key of the Asset Age Bucket dimension for which the asset's age falls into.

ASSET_DISP_START_KEY

Asset Disposition Start Dimension Surrogate Key

W1_ASSET_NODE.ASSET_DPOS_FLG

W1_ASSET_NODE.EFF_DTTM

This field is populated with the foreign key of the Asset Disposition dimension that was effective on the start date of the snapshot period.

ASSET_DISP_END_KEY

Asset Disposition End Dimension Surrogate Key

W1_ASSET_NODE.ASSET_DPOS_FLG

W1_ASSET_NODE.EFF_DTTM

This field is populated with the foreign key of the Asset Disposition dimension that was effective on the end date of the snapshot period.

UTIL_ASSET_KEY

Asset Dimension Surrogate Key

W1_ASSET.ASSET_ID

This field is populated with the foreign key of the Asset table based on the asset associated.

LOCATION_KEY

Location Dimension Surrogate Key

W1_ASSET_NODE.NODE_ID

W1_ASSET_NODE.EFF_DTTM

This field is populated with the foreign key of the Location table based on the node associated with the asset’s location.

ADDR_KEY

Address Dimension Surrogate Key

W1_ASSET_NODE.NODE_ID

This field is populated with the foreign key of the Address table based on the node associated with the asset’s location.

W_ASSET_INSTALL_AGE_KEY

Asset Install age Dimension Surrogate Key

This field is populated with the foreign key of the Asset Install Age Bucket dimension for which the asset's installation age falls into.

LAST_FAIL_DT_KEY

Last Failure Date Key

W1_WO.CRE_DTTM

W1_WORK_REQ.CRE_DTTM

This field is populated from the Date table based on the date part of the last failure date/time (which is populated in the Last Failure Date field).

LAST_FAIL_TM_KEY

Last Failure Time Key

W1_WO.CRE_DTTM

W1_WORK_REQ.CRE_DTTM

This field is populated from the Time table based on the time part of the last failure date/time (which is populated in the Last Failure Date field).

OWNING_ORG_KEY

Owning Organization Dimension Surrogate Key

W1_ASSET.OWNING_ACCESS_GRP_CD

This field is populated from the Owning Organization table based on the owning access group associated.

ASSET_SNAP_UDD1_KEY

User Defined Dimension Key for Asset Snapshot

ASSET_SNAP_UDD2_KEY

User Defined Dimension Key for Asset Snapshot

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.

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.

Location Snapshot Fact

The Location Snapshot fact captures a weekly/ monthly snapshot of the information related to a location, including mean time between failures and mean time to repair.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_LOCATION_SNAP

Table Type

Fact

Fact Type

Snapshot

Source System Driver Table

W1_NODE

ODI Package Name

B1_PKG_CF_LOCATION_SNAP

ETL View Name

Materialized View Name

B1_LOC_SNAP_MON_TOPX_MV1

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

LOCATION_SNAP_KEY

Location Snapshot Fact Key

This field is populated from the B1_LOCATION_SNAP_SEQ sequence.

Natural Keys

SRC_LOCATION_ID

Source Location ID

W1_NODE.NODE_ID

SNAP_TYPE_CD

Snapshot Type Code

This field is populated based on the ETL job configuration.

Measures

FAIL_CNT_LIFETIME

Count of Failures (Lifetime)

This field is populated with the number of times this asset failed in its lifetime. The failure count is determined by counting the number of completed/closed High priority, regular work orders that were created against the asset from its very first installation until the snapshot period end date.

MTBF_LIFETIME

Mean Time Between Failures (Lifetime)

Mean Time Between Failure is the average length of operating time between failures for an asset.

Duration (in hours) = Snapshot end date - First installation date of the asset

MTTR_LIFETIME

Mean Time to Repair (Lifetime)

Mean Time To Repair is the average time needed to restore an asset to its full operational condition upon a failure.

FAIL _CNT_BUCKET1

Count of Failures in Bucket 1

This is the total count of failures that falls within the first configurable bucket range.

FAIL _CNT_BUCKET2

Count of Failures in Bucket 2

This is the total count of failures that falls within the second configurable bucket range.

FAIL _CNT_BUCKET3

Count of Failures in Bucket 3

This is the total count of failures that falls within the third configurable bucket range.

FAIL _CNT_BUCKET4

Count of Failures in Bucket 4

This is the total count of failures that falls within the fourth configurable bucket range.

FAIL _CNT_BUCKET5

Count of Failures in Bucket5

This is the total count of failures that falls within the fifth configurable bucket range.

FAIL _CNT_BUCKET6

Count of Failures in Bucket 6

This is the total count of failures that falls within the sixth configurable bucket range.

FAIL_CNT_BUCKET7

Count of Failures in Bucket 7

This is the total count of failures that falls within the seventh configurable bucket range.

FAIL_CNT_BUCKET8

Count of Failures in Bucket 8

This is the total count of failures that falls within the eighth configurable bucket range.

FAIL_CNT_BUCKET9

Count of Failures in Bucket 9

This is the total count of failures that falls within the ninth configurable bucket range.

FAIL_CNT_BUCKET10

Count of Failures in Bucket 10

This is the total count of failures that falls within the last configurable bucket range.

MTBF_BUCKET1

Mean Time Between Failures in Bucket 1

This is the mean time between failure that falls within the first configurable bucket range.

MTBF_BUCKET2

Mean Time Between Failures in Bucket 2

This is the mean time between failure that falls within the second configurable bucket range.

MTBF_BUCKET3

Mean Time Between Failures in Bucket 3

This is the mean time between failure that falls within the third configurable bucket range.

MTBF_BUCKET4

Mean Time Between Failures in Bucket 4

This is the mean time between failure that falls within the fourth configurable bucket range.

MTBF_BUCKET5

Mean Time Between Failures in Bucket 5

This is the mean time between failure that falls within the fifth configurable bucket range.

MTBF_BUCKET6

Mean Time Between Failures in Bucket 6

This is the mean time between failure that falls within the sixth configurable bucket range.

MTBF_BUCKET7

Mean Time Between Failures in Bucket 7

This is the mean time between failure that falls within the seventh configurable bucket range.

MTBF_BUCKET8

Mean Time Between Failures in Bucket 8

This is the mean time between failure that falls within the eighth configurable bucket range.

MTBF_BUCKET9

Mean Time Between Failures in Bucket 9

This is the mean time between failure that falls within the ninth configurable bucket range.

MTBF_BUCKET10

Mean Time Between Failures in Bucket 10

This is the mean time between failure that falls within the last configurable bucket range.

MTTR_BUCKET1

Mean Time to Repair in Bucket 1

This is the mean time to repair that falls within the first configurable bucket range.

MTTR_BUCKET2

Mean Time to Repair in Bucket 2

This is the mean time to repair that falls within the second configurable bucket range.

MTTR_BUCKET3

Mean Time to Repair in Bucket 3

This is the mean time to repair that falls within the third configurable bucket range.

MTTR_BUCKET4

Mean Time to Repair in Bucket 4

This is the mean time to repair that falls within the fourth configurable bucket range.

MTTR_BUCKET5

Mean Time to Repair in Bucket 5

This is the mean time to repair that falls within the fifth configurable bucket range.

MTTR_BUCKET6

Mean Time to Repair in Bucket 6

This is the mean time to repair that falls within the sixth configurable bucket range.

MTTR_BUCKET7

Mean Time to Repair in Bucket 7

This is the mean time to repair that falls within the seventh configurable bucket range.

MTTR_BUCKET8

Mean Time to Repair in Bucket 8

This is the mean time to repair that falls within the eight configurable bucket range.

MTTR_BUCKET9

Mean Time to Repair in Bucket 9

This is the mean time to repair that falls within the ninth configurable bucket range.

MTTR_BUCKET10

Mean Time to Repair in Bucket 10

This is the mean time to repair that falls within the last configurable bucket range.

FACT_CNT

Count

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

Degenerate Dimensions

SNAPSHOT_DT

Snapshot Date

This field is populated with the last date of the current snapshot period (only the date part is populated).

LAST_FAIL_DTTM

Last Failure Date

W1_WO.CRE_DTTM

W1_WORK_REQ.CRE_DTTM

This field is populated with the date/time of the asset's latest failure.

See the Count of Failures (Lifetime) field for the definition of failure.

Foreign Keys

ADDR_KEY

Address Dimension Surrogate Key

W1_ASSET_NODE.NODE_ID

This field is populated with the foreign key of the Address dimension based on the node associated with the asset’s location.

LOCATION_KEY

Location Dimension Surrogate Key

W1_ASSET_NODE.NODE_ID

W1_ASSET_NODE.EFF_DTTM

This field is populated with the foreign key of the Location dimension based on the node associated with the asset’s location.

LAST_FAIL_DT_KEY

Last Failure Date Key

W1_WO.CRE_DTTM

W1_WORK_REQ.CRE_DTTM

This field is populated from the Date table based on the date part of the last failure date/time (which is populated in the Last Failure Date field).

LAST_FAIL_TM_KEY

Last Failure Time Key

W1_WO.CRE_DTTM

W1_WORK_REQ.CRE_DTTM

This field is populated from the Time table based on the time part of the last failure date/time (which is populated in the Last Failure Date field).

OWNING_ORG_KEY

Owning Organization Dimension Surrogate Key

W1_ASSET.OWNING_ACCESS_GRP_CD

This field is populated from the Owning Organization table based on the owning access group.

SNAPSHOT_DATE_KEY

Snapshot Date Key

This field is populated from the Date table based on the snapshot date/time.

UTIL_ASSET_KEY

Asset Dimension Surrogate Key

W1_ASSET.ASSET_ID

This field is populated with the foreign key of the Asset table based on the asset associated

LOCATION_SNAP_UDD1_KEY

User Defined Dimension 1 Surrogate Key

LOCATION_SNAP_UDD2_KEY

User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

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

Service History Fact

The Service History fact accumulates the maintenance, inspection, failure, and downtime service histories created in the Oracle Utilities Work and Asset Management source system.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_SERVICE_HIST

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

W1_SVC_HIST

ODI Package Name

B1_PKG_CF_SERVICE_HIST

ETL View Name

B1_F_SERVICE_HIST_VW

Materialized View Name

B1_SERVICE_HIST_MON_MV1

B1_SERVICE_HIST_MON_MV2

B1_SERVICE_HIST_MON_TOPX_MV1

B1_SERVICE_HIST_MON_TOPX_MV2

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

SERVICE_HIST_KEY

Service History Fact Key

This field is populated from the B1_SERVICE_HIST_SEQ sequence.

Natural Keys

SRC_SERVICE_HIST_ID

Source Service History ID

W1_SVC_HIST.SVC_HIST_ID

Measures

PLANNED_DOWNTIME_DUR

Planned Downtime Duration

This field is populated with the planned downtime duration if the service history is categorized as Downtime or Failure.

If it's a Failure, the downtime information is retrieved from its corresponding downtime Service History. The service history is planned if it has a characteristic of type Downtime Reason with a value of Planned.

UNPLANNED_DOWNTIME_DUR

Unplanned Downtime Duration

This field is populated with the planned downtime duration if the service history is categorized as Downtime or Failure.

If it's a Failure, the downtime information is retrieved from its corresponding downtime Service History. The service history is planned if it has a characteristic of type Downtime Reason with a value of Planned.

COST

Cost

This field is populated with the cost incurred for the service history's activity taking into consideration the cost distribution of the service history's asset in the activity.

FACT_CNT

Count

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

Degenerate Dimensions

SERVICE_HIST_END_DTTM

Service History End Date/Time

W1_SVC_HIST.END_DTTM

SERVICE_HIST_START_DTTM

Service History Start Date/Time

W1_SVC_HIST.BO_DATA_AREA

Note : This field is populated only for Downtime service histories.

SERVICE_HIST_DTTM

Service History Date/Time

W1_SVC_HIST.EFF_DTTM

Foreign Keys

SERVICE_HIST_END_DT_KEY

Service History End Date Dimension Surrogate Key

W1_SVC_HIST.END_DT TM

This field is populated with the foreign key of the Service History End Date dimension based on the end date of the service history.

SERVICE_HIST_END_TM_KEY

Service History End Time Dimension Surrogate Key

W1_SVC_HIST.END_DT TM

This field is populated with the foreign key of the Service History End Time dimension based on the end tine of the service history.

SERVICE_HIST_START_DT_KEY

Service History Start Date Dimension Surrogate Key

W1_SVC_HIST.BO_DATA_AREA

This field is populated with the foreign key of the Service History Start Date dimension based on the start date of the service history.

SERVICE_HIST_START_TM_KEY

Service History Start Time Dimension Surrogate Key

W1_SVC_HIST.BO_DATA_AREA

This field is populated with the foreign key of the Service History Start Time dimension based on the start time of the service history.

SERVICE_HIST_D_KEY

Service History Dimension Surrogate Key

This field is populated with the foreign key of the Service History dimension based on the asset associated with the service history.

WO_KEY

Work Order Dimension Surrogate Key

W1_ACTIVITY.WO_ID

This field is populated with the foreign key of the Work Order dimension based on the work order ID of the activity associated with the service history.

WO_ACTIVITY_KEY

Work Order Activity Dimension Surrogate Key

W1_SVC_HIST.ACT_ID

This field is populated with the Work Order Activity dimension based on the activity associated with the service history.

OWNING_ORG_KEY

Owning Organization Dimension Surrogate Key

W1_SVC_HIST. OWNING_ACCESS_GRP_CD

This field is populated from the Owning Organization dimension based on the owning access group.

W_PLANNER_KEY

Planner Dimension Surrogate Key

W1_ACTIVITY.PLANNER_CD

This field is populated with the foreign key from the Planner dimension based on the planner code associated with the activity.

CREW_KEY

Crew Dimension Surrogate Key

This field is populated with the foreign key from the Crew dimension based on the crew ID associated with the activity.

SERVICE_HIST_TYPE_KEY

Service History Type Dimension Surrogate Key

W1_SVC_HIST.SVC_HIST_TYPE_CD

This field is populated with the foreign key from the Service History Type dimension based on the service history type code associated with the service history.

OPR_DEVICE_KEY

Operational Device Dimension Surrogate Key

W1_SVC_HIST.ASSET_ID

W1_SVC_HIST.EFF_DTTM

This field is populated from the Operational Device table.

Note : It is always populated with ‘0’.

UTIL_ASSET_KEY

Utility Asset Dimension Surrogate Key

W1_SVC_HIST.ASSET_ID

W1_SVC_HIST.EFF_DTTM

This field is populated with the foreign key of the Utility Asset dimension based on the asset associated with this activity.

LOCATION_KEY

Location Dimension Surrogate Key

W1_SVC_HIST.EFF_DTTM

W1_ASSET_NODE.NODE_ID

W1_ASSET_NODE.EFF_DTTM

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

This field is populated based on the current location of the asset associated with the Service History.

If it is a component and its current disposition is "Attached", its disposition will follow the disposition of the asset it is currently attached to.

ADDR_KEY

Address Dimension Surrogate Key

W1_ASSET_NODE.NODE_ID

The source for this column will be the asset's location.

ASSET_INSP_STATUS_KEY

Asset Inspection Status Dimension Surrogate Key

W1_SVC_HIST_TYPE.SVC_HIST_CATEGORY_FLG

W1_SVC_HIST_CHAR.CHAR_TYPE_CD

W1_SVC_HIST_CHAR.CHAR_VAL

This field is populated only for Service Histories of Inspection Category. It is populated based on the characteristic value configured for the 'Operational Status' characteristic type.

If no value is configured, then the default value of 'NA' is mapped.

SP_KEY

Service Point Dimension Surrogate Key

W1_NODE_IDENTIFIER.W1_ID_VALUE

This field is not used for Oracle Utilities Work and Asset Management source product.

MTR_DEVICE_KEY

Device Dimension Surrogate Key

This field is not used for Oracle Utilities Work and Asset Management source product.

US_KEY

Usage Subscription Dimension Surrogate Key

This field is not used for Oracle Utilities Work and Asset Management source product.

CONTACT_KEY

Contact Dimension Surrogate Key

This field is not used for Oracle Utilities Work and Asset Management source product.

PER_KEY

Person Dimension Surrogate Key

This field is not used for Oracle Utilities Work and Asset Management source product.

ACCT_KEY

Account Dimension Surrogate Key

This field is not used for Oracle Utilities Work and Asset Management source product.

SA_KEY

Service Agreement Dimension Surrogate Key

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

This field is not used for Oracle Utilities Work and Asset Management source product.

SERVICE_HIST_DATE_KEY

Service History Date Dimension Surrogate Key

This field is populated with the foreign key of the Date dimension based on date associated with the service history.

SERVICE_HIST_TIME_KEY

Service History Time Dimension Surrogate Key

This field is populated with the foreign key of the Time dimension based on time associated with the service history.

SERVICE_HIST_UDD1_KEY

Service History User Defined Dimension 1 Surrogate Key

SERVICE_HIST_UDD2_KEY

Service History User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

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.

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.

Work Order Fact

The Work Order fact is an accumulation of work orders in the source system, with a daily refresh of certain measures.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_WO

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

W1_WO

ODI Package Name

B1_PKG_CF_WO

ETL View Name

B1_F_WO_VW

Materialized View Name

B1_WO_MON_MV1

B1_WO_MON_MV2

B1_WO_MON_MV3

B1_WO_MON_TOPX_MV1

B1_WO_MV1

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

WO_KEY

Work Order Fact Surrogate Key

This field is populated from the B1_F_WO_SEQ sequence.

Natural Keys

SRC_WO_ID

Source Work Order ID

W1_WO. WO_ID

Measures

FACT_CNT

Count

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

Degenerate Dimensions

CRE_DTTM

Creation Date Time

W1_WO. CRE_DTTM

CMP_DTTM

Completion Date Time

W1_WO_LOG.LOG_DTTM

This field is populated with the log date/time when the work order was transitioned to the Completed state.

REQUIRED_BY_DT

Required By Date

W1_WO.REQUIRED_BY_DT

ORIGINAL_WORK_DT

Original Work Date

This field is populated with the original work date of the activity related to work order.

If there are multiple activities for a work order with different original work dates, the earliest original work date is populated.

OVERDUE_IND

Overdue Indicator

This field is used to indicate if the Work Order is overdue.

If the work order is not finalized yet, it is considered overdue if today's date is past the Original Work Date. If the work order has been completed, it is considered overdue if it is completed past the Original Work Date.

The overdue indicator is populated with '1' if the work order is overdue. Else, it is set to '0'.

Note : This if refreshed daily if the Work Order is in a non-finalized state.

NO_COST_IND

No Cost Indicator

This field is populated based on the sum of frozen financial transactions associated with the work order's non-canceled activities.

If the total is 0, the field is set to ‘1’. Else, it is set to ‘0’.

Note : This if refreshed daily if the Work Order is in a non-finalized state.

NO_LABOR_IND

No Labor Indicator

This field is populated based on the timesheet details posted once the work order is closed.

The indicator is set to ‘1’ only when such timesheet details exist. Else, it is set to ‘0’.

Foreign Keys

ORIGINAL_WORK_DT_KEY

Original Work Date Key

W1_ACTIVITY. ORIGINAL_WORK_DT

This field is populated with the foreign key of the Date dimension based on the original work date associated with the activity.

CMP_DT_KEY

Completion Date Key

W1_WO_LOG.LOG_DTTM

This field is populated with the foreign key of the Date dimension based on the completion date associated with the activity.

REQUIRED_BY_DT_KEY

Required By Date Key

W1_WO.REQUIRED_BY_DT

This field is populated with the foreign key of the Date dimension based on the required by date associated with the activity.

CRE_DT_KEY

Creation Date Key

W1_WO.CRE_DTTM

This field is populated with the foreign key of the Date dimension based on the creation date associated with the activity.

CRE_TM_KEY

Creation Time Key

W1_WO.CRE_DTTM

This field is populated with the foreign key of the Date dimension based on the creation time associated with the activity.

CMP_TM_KEY

Completion Time Key

W1_WO_LOG.LOG_DTTM

This field is populated with the foreign key of the Date dimension based on the completion time associated with the activity.

WO_D_KEY

Work Order Dimension Surrogate Key

W1_WO.WO_ID

This field is populated with the foreign key of the Work Order dimension based on the work order ID associated with the activity.

LOCATION_KEY

Location Dimension Surrogate Key

W1_ACTIVITY.NODE_ID

This field is populated based on the node ID of the primary non-cancelled activity for the work order.

ADDR_KEY

Address Dimension Surrogate Key

W1_ACTIVITY.NODE_ID

This field is populated with the foreign key of the Address dimension based on the node ID of the activity.

OWNING_ORG_KEY

Owning Organization Dimension Surrogate Key

W1_WO.OWNING_ACCESS_GRP_CD

This field is populated with the foreign key of the Owning Organization table based on the owning access group.

W_PLANNER_KEY

Planner Dimension Surrogate Key

W1_WO.PLANNER_CD

This field is populated with the foreign key of the Planner dimension based on the planner code.

WO_STATUS_KEY

Work Order BO Status Dimension Surrogate Key

W1_WO.BUS_OBJ_CD

W1_WO.BO_STATUS_CD

W1_WO.BO_STATUS_REASON_CD

This field is populated with the foreign key of the Work Order BO Status dimension based on the BO status code and BO status reason code associated with the work order.

UTIL_ASSET_KEY

Utility Asset Dimension Surrogate Key

W1_ACTIVITY.ASSET_ID

This field is populated based on the asset of the primary activity for the work order.

CREW_KEY

Crew Dimension Surrogate Key

W1_WO. W1_CREW_ID

WO_UDD1_KEY

Work Order User Defined Dimension 1 Surrogate Key

WO_UDD2_KEY

Work Order User Defined Dimension 2 Surrogate Key

User Defined Attributes

UDM1

User Defined Measure 1

UDM2

User Defined Measure 2

UDM3

User Defined Measure 3

UDM4

User Defined Measure 4

UDM5

User Defined Measure 5

UDM6

User Defined Measure 6

UDM7

User Defined Measure 7

UDM8

User Defined Measure 8

UDM9

User Defined Measure 9

UDM10

User Defined Measure 10

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