Oracle Utilities Extractors and Schema for Oracle Utilities Operational Device Management

Data Mapping Guide Release 2.7.0,

E83235-01

March 2017

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

E83235-01

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

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

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

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

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

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

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

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

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

Preface

This guide provides the data mapping information from the Oracle Utilities Operational Device 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 Operational Device Management.

Documentation Accessibility

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

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

Access to Oracle Support

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

Related Documents

For more information, see the following documents:

•

Oracle Utilities Analytics Release Notes

•

Oracle Utilities Analytics Getting Started Guide

•

Oracle Utilities Analytics Quick Install Guide

•

Oracle Utilities Analytics Installation Guide

•

Oracle Utilities Analytics Administration Guide

•

Oracle Utilities Analytics Developer’s Guide

See Also :

•

Oracle Utilities Operational Device 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 Extractors and Schema for Oracle Utilities Operational Device 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 (BI 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

The Field Categories 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 Operational Device Management

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

•

Dimensions

•

Shared Dimensions

•

User Defined Dimensions

•

Facts

Dimensions

Asset Disposition Dimension

The Asset Disposition dimension stores all possible dispositions that an asset entity can be in.

Properties

Property

Value

Target Table Name

CD_ASSET_DISP

Table Type

Dimension

SCD Type

Type 1

Driver Table Name

F1_EXT_LOOKUP_VAL_L

ODI Package Name

B1_PKG_CD_ASSET_DISP

ETL View Name

B1_D_ASSET_DISP_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ASSET_DISP_KEY

Asset Disposition Dimension Key

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

Attributes

ASSET_DISP_DESCR

Asset Disposition Description

F1_EXT_LOOKUP_VAL_L.DESCR_OVRD

F1_EXT_LOOKUP_VAL_L.DESCR

F1_EXT_LOOKUP_VAL_L.LANGUAGE_CD

This field is populated with the description of the asset disposition from Lookup Language table for ASSET_DISP_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

DATA_LOAD_DTTM

Data Load Date/Time

This field is populated with the load timestamp value.

UPDATE_DTTM

Update Date/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 Age Dimension

The Asset Age dimension extracts the age buckets for an asset as configured in the source Oracle Utilities Operational Device Management system.

Properties

Property

Value

Target Table Name

CD_ASSET_AGE

Table Type

Dimension

SCD Type

Type 1

Driver Table Name

W1_BKT_CONFIG_VAL

ODI Package Name

B1_PKG_CD_ASSET_AGE

ETL View Name

B1_D_ASSET_AGE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ASSET_AGE_KEY

Asset Age Dimension Key

This field is populated from the B1_ASSET_AGE_SEQ sequence.

Natural Keys

ASSET_TYPE_CD

Asset Type Code

W1_BKT_CONFIG_REL_OBJ.PK_VALUE1

Attributes

ASSET_AGE_DESCR

Asset Age Description

W1_BKT_CONFIG_VAL_L.DESCR

W1_BKT_CONFIG_VAL_L.LANGUAGE_CD

This field is populated with the description of the asset age from Lookup Language table for ASSET_AGE_FLG lookup field.

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

ASSET_AGE_ST_RANGE

Age Start Range

W1_BKT_CONFIG_VAL.START_RANGE

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

ASSET_AGE_ED_RANGE

Age End Range

W1_BKT_CONFIG_VAL.END_RANGE

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

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

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

Asset Install Age Dimension

The Asset Install Age dimension extracts the age buckets for an asset’s installed age as configured in the source Oracle Utilities Operational Device Management system.

Properties

Property

Value

Target Table Name

CD_ASSET_INSTALL_AGE

Table Type

Dimension

SCD Type

Type 1

Driver Table Name

W1_BKT_CONFIG_VAL

ODI Package Name

B1_PKG_CD_ASSET_INSTALL_AGE

ETL View Name

B1_D_ASSET_INSTALL_AGE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ASSET_INSTALL_AGE_KEY

Asset Install Age Dimension Key

This field is populated from the B1_ASSET_INSTALL_AGE_SEQ sequence.

Natural Keys

ASSET_TYPE_CD

Asset Type Code

W1_BKT_CONFIG_REL_OBJ.PK_VALUE1

Attributes

ASSET_INSTALL_AGE_DESCR

Asset Install Age Description

W1_BKT_CONFIG_VAL_L.DESCR

W1_BKT_CONFIG_VAL_L.LANGUAGE_CD

This field is populated with the description of the asset install age from the Lookup Language table for ASSET_INSTALL_AGE_FLG lookup field.

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

ASSET_INSTALL_AGE_ST_RANGE

Asset Install Age Start Range

W1_BKT_CONFIG_VAL.START_RANGE

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

ASSET_INSTALL_AGE_ED_RANGE

Asset Install Age End Range

W1_BKT_CONFIG_VAL.END_RANGE

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

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

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

Asset Instore Age Dimension

The Asset Inventory Age dimension is populated with the age ranges for the days since the asset was put in storage.

Properties

Property

Value

Target Table Name

CD_ASSET_INSTORE_AGE

Table Type

Dimension

SCD Type

Type 1

Driver Table Name

W1_BKT_CONFIG_VAL

ODI Package Name

B1_PKG_CD_ASSET_INSTORE_AGE

ETL View Name

B1_D_ASSET_INSTORE_AGE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

ASSET_INSTORE_AGE_KEY

Asset Instore Age Dimension Key

This field is populated from the B1_ASSET_INSTORE_AGE_SEQ sequence.

Natural Keys

ASSET_TYPE_CD

Asset Type Code

W1_BKT_CONFIG_REL_OBJ.PK_VALUE1

Attributes

ASSET_INSTORE_AGE_DESCR

Asset Instore Age Description

W1_BKT_CONFIG_VAL_L.DESCR

W1_BKT_CONFIG_VAL_L.LANGUAGE_CD

This field is populated with the description of the asset instore age from the Lookup Language table for ASSET_INSTORE_AGE_FLG lookup field.

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

ASSET_INSTORE_AGE_ST_RANGE

Asset Instore Age Start Range

W1_BKT_CONFIG_VAL.START_RANGE

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

ASSET_INSTORE_AGE_ED_RANGE

Asset Instore Age End Range

W1_BKT_CONFIG_VAL.END_RANGE

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

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

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

Operational Device Dimension

The Operational Device dimension extracts asset data related to operational devices from the Oracle Utilities Operational Device Management system. The appropriate firmware versions are retrieved based on the configuration set on the BI Extract Parameters in Oracle Utilities Operational Device Management.

Properties

Property

Value

Target Table

CD_OPR_DEVICE

Table Type

Dimension

SCD Type

Type 2

Source System Driver Table

W1_ASSET

ODI Package

B1_PKG_CD_OPR_DEVICE

ETL View

B1_D_OPR_DEVICE_VW

Fields

Target Field

Description

Source Field

Transformation Logic

Surrogate Key

OPR_DEVICE_KEY

Operational Device Dimension Key

This field is populated from the B1_OPR_DEVICE_SEQ sequence.

Natural Keys

SRC_ASSET_ID

Source Asset ID

This field is populated with only those assets whose BO name (BUS_OBJ_CD) is as configured in the BI config table.

Attributes

FIRMWARE1

Firmware Version 1

W1_ASSET_IDENTIFIER.W1_ID_VALUE

The identifier value is retrieved based on the firmware Identifier Type configured in the Master Configuration for BI extract parameters. The Identifier Type configured for firmware 1 slot is retrieved.

FIRMWARE2

Firmware Version 2

W1_ASSET_IDENTIFIER.W1_ID_VALUE

The identifier value is retrieved based on the firmware Identifier Type configured in the Master Configuration for BI extract parameters. The Identifier Type configured for firmware 2 slot is retrieved.

FIRMWARE3

Firmware Version 3

W1_ASSET_IDENTIFIER.W1_ID_VALUE

The identifier value is retrieved based on the firmware Identifier Type configured in the Master Configuration for BI extract parameters. The Identifier Type configured for firmware 3 slot is retrieved.

FIRMWARE4

Firmware Version 4

W1_ASSET_IDENTIFIER.W1_ID_VALUE

The identifier value is retrieved based on the firmware Identifier Type configured in the Master Configuration for BI extract parameters. The Identifier Type configured for firmware 4 slot is retrieved.

FIRMWARE5

Firmware Version 5

W1_ASSET_IDENTIFIER.W1_ID_VALUE

The identifier value is retrieved based on the firmware Identifier Type configured in the Master Configuration for BI extract parameters. The Identifier Type configured for firmware 5 slot is retrieved.

FIRMWARE6

Firmware Version 6

W1_ASSET_IDENTIFIER.W1_ID_VALUE

The identifier value is retrieved based on the firmware Identifier Type configured in the Master Configuration for BI extract parameters. The Identifier Type configured for firmware 6 slot is retrieved.

FIRMWARE7

Firmware Version 7

W1_ASSET_IDENTIFIER.W1_ID_VALUE

The identifier value is retrieved based on the firmware Identifier Type configured in the Master Configuration for BI extract parameters. The Identifier Type configured for firmware 7 slot is retrieved.

FIRMWARE8

Firmware Version 8

W1_ASSET_IDENTIFIER.W1_ID_VALUE

The identifier value is retrieved based on the firmware Identifier Type configured in the Master Configuration for BI extract parameters. The Identifier Type configured for firmware 8 slot is retrieved.

FIRMWARE9

Firmware Version 9

W1_ASSET_IDENTIFIER.W1_ID_VALUE

The identifier value is retrieved based on the firmware Identifier Type configured in the Master Configuration for BI extract parameters. The Identifier Type configured for firmware 9 slot is retrieved.

FIRMWARE10

Firmware Version 10

W1_ASSET_IDENTIFIER.W1_ID_VALUE

The identifier value is retrieved based on the firmware Identifier Type configured in the Master Configuration for BI extract parameters. The Identifier Type configured for firmware 10 slot is retrieved.

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.

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

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

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.

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.

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 Asset Location UDD is ASSET_LOC_UDD1_KEY

<Fact name> User Defined Dimension n Dimension Surrogate Key

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

Natural Keys

UDD1_CD

User Defined Dimension 1 Code

Attributes

UDD1_DESCR

User Defined Dimension 1 Description

User Defined Attributes

UDF1_CD

User Defined Field 1 Code

UDF1_DESCR

User Defined Field 1 Description

UDF2_CD

User Defined Field 2 Code

UDF2_DESCR

User Defined Field 2 Description

UDF3_CD

User Defined Field 3 Code

UDF3_DESCR

User Defined Field 3 Description

UDF4_CD

User Defined Field 4 Code

UDF4_DESCR

User Defined Field 4 Description

UDF5_CD

User Defined Field 5 Code

UDF5_DESCR

User Defined Field 5 Description

UDL1_CD

User Defined Field Long 1 Code

UDL1_DESCR

User Defined Field Long 1 Description

UDL2_CD

User Defined Field Long 2 Code

UDL2_DESCR

User Defined Field Long 2 Description

UDL3_CD

User Defined Field Long 3 Code

UDL3_DESCR

User Defined Field Long 3 Description

UDL4_CD

User Defined Field Long 4 Code

UDL4_DESCR

User Defined Field Long 4 Description

UDL5_CD

User Defined Field Long 5 Code

UDL5_DESCR

User Defined Field Long 5 Description

COMMENT1

Comment 1

COMMENT2

Comment 2

COMMENT3

Comment 3

COMMENT4

Comment 4

COMMENT5

Comment 5

Data Load Attributes

EFF_START_DTTM

Effective Start Date/Time

EFF_END_DTTM

Effective End Date/Time

DATA_LOAD_DTTM

Data Load Date/Time

DATA_SOURCE_IND

Data Source Indicator

JOB_NBR

Job Number

Asset Location UDD1 Dimension

Property

Value

Target Table

CD_ASSET_LOC_UDD1

Table Type

Dimension

SCD Type

Type 2

Asset Location UDD2 Dimension

Property

Value

Target Table

CD_ASSET_LOC_UDD2

Table Type

Dimension

SCD Type

Type 2

Asset Location UDD2 Dimension

Property

Value

Target Table

CD_ASSET_LOC_UDD2

Table Type

Dimension

SCD Type

Type 2

Operational Device UDD1 Dimension

Property

Value

Target Table

CD_OPR_DEVICE_UDD1

Table Type

Dimension

SCD Type

Type 2

Operational Device UDD2 Dimension

Property

Value

Target Table

CD_OPR_DEVICE_UDD2

Table Type

Dimension

SCD Type

Type 2

Operational Device Snapshot UDD1 Dimension

Property

Value

Target Table

CD_OPR_DEVICE_SNAP_UDD1

Table Type

Dimension

SCD Type

Type 2

Operational Device Snapshot UDD2 Dimension

Property

Value

Target Table

CD_OPR_DEVICE_SNAP_UDD2

Table Type

Dimension

SCD Type

Type 2

Service History UDD1 Dimension

Property

Value

Target Table

CD_SERVICE_HIST_UDD1

Table Type

Dimension

SCD Type

Type 2

Service History UDD2 Dimension

Property

Value

Target Table

CD_SERVICE_HIST_UDD2

Table Type

Dimension

SCD Type

Type 2

Facts

Asset Location Fact

The Asset Location fact is populated with the asset dispositions of all assets from the Oracle Utilities Operational Device Management system. The assets are captured along their life cycle, when they move through various dispositions. The measures captured in this fact include the time spent in prior disposition, an indicator for failure, and the time since last failure.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_ASSET_LOC

Table Type

Fact

Fact Type

Accumulation

Source System Driver Table

W1_ASSET_NODE

ODI Package Name

B1_PKG_CF_ASSET_LOC

ETL View Name

B1_F_ASSET_LOC_VW

Materialized View Name

B1_ASSET_LOC_MON_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

ASSET_LOC_KEY

Operational Device Snapshot Fact Surrogate Key

This field is populated from the B1_ASSET_LOC_SEQ sequence.

Natural Keys

SRC_ASSET_ID

Source Asset ID

W1_ASSET_NODE.ASSET_ID

Measures

PRIOR_DISP_DUR

Days in Prior Disposition

This field is populated as the difference (in days) between the day when the asset entered current disposition and the day when it entered the previous disposition.

DUR_LAST_FAIL

Months Since Last Failure

F1_MST_CONFIG.MST_CONFIG_DATA

W1_ASSET_NODE.FAILURE_FLG

W1_ASSET_NODE.EFF_DTTM

This field is only populated if the asset disposition was marked with failure.

It is calculated as the difference (in months) since the last failure (or if there is no prior failure, it should be calculated from the earliest disposition).

FACT_CNT

Count

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

Installed

This is the number of devices that are installed at customer locations.

Received Devices

This is the number of devices that are in receipt. These devices are tested for usability before installing them at customer locations.

Rejections

This is the number of devices that are rejected during an inspection.

Degenerate Dimensions

FAILED_IND

Failed Indicator

F1_MST_CONFIG.MST_CONFIG_DATA

W1_ASSET_NODE.FAILURE_FLG

This indicator is set to 1 if the asset has a failure when it moved to this disposition. The definition of an asset's failure is configured via "Asset Failure Parameters" in the BI Extract Parameters in ODM.

DISP_DTTM

Disposition Date/Time

W1_ASSET_NODE.EFF_DTTM

This is the disposition date/time of the asset’s current disposition.

PRIOR_DISP_DTTM

Prior Disposition Date/Time

W1_ASSET_NODE.EFF_DTTM

This is the disposition date/time of the asset’s prior disposition.

Foreign Keys

ASSET_DISP_KEY

Asset Disposition Dimension Surrogate Key

W1_ASSET_NODE.ASSET_DPOS_FLG

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

This field is populated based on the asset’s current disposition. 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.

PRIOR_ASSET_DISP_KEY

Prior Disposition Dimension Surrogate Key

This field is populated based on the prior disposition of the asset. 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.

UTIL_ASSET_KEY

Utility Asset Dimension Surrogate Key

W1_ASSET_NODE.ASSET_ID

ATTACHED_TO_ASSET_KEY

Utility Asset Dimension Surrogate Key

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

LOCATION_KEY

Location Dimension Surrogate Key

W1_ASSET_NODE.NODE_ID

This field is populated based on the asset’s current location. 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.

PRIOR_LOCATION_KEY

Prior Location Dimension Surrogate Key

W1_ASSET_NODE.NODE_ID

This field is populated based on the asset’s prior location. 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 current location.

SP_KEY

Service Point Dimension Surrogate Key

W1_NODE_IDENTIFIER.W1_ID_VALUE

This field is populated only for joint ODM-MDM integration. It is populated using the MDM SP natural key stored in the ODM system.

MTR_DEVICE_KEY

Device Dimension Surrogate Key

This field is populated only for joint ODM-MDM integration. It is populated by deriving the MDM device that is linked to the MDM SP natural key stored in the ODM system.

US_KEY

Usage Subscription Dimension Surrogate Key

This field is populated only for joint ODM-MDM integration. It is populated by deriving the MDM usage subscription that is linked to the MDM SP natural key stored in the ODM system.

CONTACT_KEY

Contact Dimension Surrogate Key

This field is populated only for joint ODM-MDM integration. It is populated by deriving the MDM contact that is linked to the MDM SP natural key stored in the ODM system.

PER_KEY

Person Dimension Surrogate Key

This field is populated only for joint ODM-MDM-CCB integration. It is populated by deriving the CCB person that is linked to the MDM SP natural key stored in the ODM system.

ACCT_KEY

Account Dimension Surrogate Key

This field is populated only for joint ODM-MDM-CCB integration. It is populated by deriving the CCB account that is linked to the MDM SP natural key stored in the ODM system.

SA_KEY

Service Agreement Dimension Surrogate Key

This field is populated only for joint ODM-MDM-CCB integration. It is populated by deriving the CCB SA that is linked to the MDM SP natural key stored in the ODM system.

PREM_KEY

Premise Dimension Surrogate Key

This field is populated only for joint ODM-MDM-CCB integration. It is populated by deriving the CCB premise that is linked to the MDM SP natural key stored in the ODM system.

DISP_DATE_KEY

Disposition Date Dimension Surrogate Key

DISP_TIME_KEY

Disposition Time Dimension Surrogate Key

PRIOR_DISP_DATE_KEY

Prior Disposition Date Dimension Surrogate Key

PRIOR_DISP_TIME_KEY

Prior Disposition Time Dimension Surrogate Key

ASSET_LOC_UDD1_KEY

Asset Location User Defined Dimension 1 Surrogate Key

ASSET_LOC_UDD2_KEY

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

Operational Device Fact

The Operational Device fact accumulates all operational devices (assets) from the Oracle Utilities Operational Device Management system. The fact also captures data about the most recent inspection pass and failure events.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_OPR_DEVICE

Table Type

Fact

Fact Type

Accumulation

Driver Table

W1_ASSET

ODI Package Name

B1_PKG_CF_OPR_DEVICE

ETL View Name

B1_F_OPR_DEVICE_VW

Materialized View Name

B1_OPR_DEVICE_MON_MV1

B1_OPR_DEVICE_MON_TOPX_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

OPR_DEVICE_KEY

Operational Device Surrogate Key

This field is populated from the B1_OPR_DEVICE_ACCUM_SEQ sequence.

Natural Keys

SRC_ASSET_ID

Source Asset ID

W1_ASSET.ASSET_ID

Measures

FACT_CNT

Count

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

Installed

This is the number of devices that are installed at customer locations.

Degenerate Dimensions

WNTY_EXP_DTTM

Warranty Expiration Date/Time

W1_ASSET_CHAR.ADHOC_CHAR_VAL

The warranty expiration date/time for an asset which will be configured as a characteristic value.

RCT_INS_PASS_DTTM

Recent Inspection Pass Date/Time

W1_SVC_HIST.ASSET_ID

W1_SVC_HIST_TYPE.SVC_HIST_CATEGORY_FLG

W1_SVC_HIST_CHAR.EFFDT

W1_SVC_HIST_CHAR.CHAR_TYPE_CD

W1_SVC_HIST_CHAR.CHAR_VAL

This field is populated with the effective date/time of the most recent service history with category of 'Inspection', and a passing ‘Operational Status’.

RCT_INS_FAIL_DTTM

Recent Inspection Fail Date/Time

W1_SVC_HIST.ASSET_ID

W1_SVC_HIST_TYPE.SVC_HIST_CATEGORY_FLG

W1_SVC_HIST_CHAR.EFFDT

W1_SVC_HIST_CHAR.CHAR_TYPE_CD

W1_SVC_HIST_CHAR.CHAR_VAL

This field is populated with the effective date/time of the most recent service history with category of 'Inspection', and a failed 'Operational Status'.

Foreign Keys

ASSET_DISP_KEY

Asset Disposition Dimension Surrogate Key

W1_ASSET_NODE.ASSET_DPOS_FLG

W1_ASSET_NODE.EFF_DTTM

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

This field is populated based on the asset’s current disposition.

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.

OPR_DEVICE_D_KEY

Operational Device Dimension Surrogate Key

W1_ASSET.ASSET_ID

UTIL_ASSET_KEY

Utility Asset Dimension Surrogate Key

W1_ASSET.ASSET_ID

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

ATTACHED_TO_ASSET_KEY

Utility Asset Dimension Surrogate Key

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

LOCATION_KEY

Location Dimension Surrogate Key

W1_ASSET_NODE.NODE_ID

W1_ASSET_NODE.EFF_DTTM

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

This field is populated based on the asset’s current location.

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

This field is populated based on the same logic used for the asset’s location. There is no separate entity for address in the ODM system. The source for this column will be the asset's location.

SP_KEY

Service Point Dimension Surrogate Key

W1_NODE_IDENTIFIER.W1_ID_VALUE

This field is populated only for joint ODM-MDM integration. It is populated using the MDM SP natural key stored in the ODM system.

MTR_DEVICE_KEY

Device Dimension Surrogate Key

This field is populated only for joint ODM-MDM integration. It is populated by deriving the MDM device that is linked to the MDM SP natural key stored in the ODM system.

US_KEY

Usage Subscription Dimension Surrogate Key

This field is populated only for joint ODM-MDM integration. It is populated by deriving the MDM usage subscription that is linked to the MDM SP natural key stored in the ODM system.

CONTACT_KEY

Contact Dimension Surrogate Key

This field is populated only for joint ODM-MDM integration. It is populated by deriving the MDM contact that is linked to the MDM SP natural key stored in the ODM system.

PER_KEY

Person Dimension Surrogate Key

This field is populated only for joint ODM-MDM-CCB integration. It is populated by deriving the CCB person that is linked to the MDM SP natural key stored in the ODM.

ACCT_KEY

Account Dimension Surrogate Key

This field is populated only for joint ODM-MDM-CCB integration. It is populated by deriving the CCB account that is linked to the MDM SP natural key stored in the ODM system.

SA_KEY

Service Agreement Dimension Surrogate Key

This field is populated only for joint ODM-MDM-CCB integration. It is populated by deriving the CCB SA that is linked to the MDM SP natural key stored in the ODM system.

PREM_KEY

Premise Dimension Surrogate Key

This field is populated only for joint ODM-MDM-CCB integration. It is populated by deriving the CCB premise that is linked to the MDM SP natural key stored in the ODM system.

WNTY_EXP_DATE_KEY

Warranty Expiration Date Dimension Surrogate Key

WNTY_EXP_TIME_KEY

Warranty Expiration Time Dimension Surrogate Key

RCT_INS_PASS_DATE_KEY

Recent Inspection Pass Date Dimension Surrogate Key

RCT_INS_PASS_TIME_KEY

Recent Inspection Pass Time Dimension Surrogate Key

RCT_INS_FAIL_DATE_KEY

Recent Inspection Fail Date Dimension Surrogate Key

RCT_INS_FAIL_TIME_KEY

Recent Inspection Fail Time Dimension Surrogate Key

OPR_DEVICE_UDD1_KEY

Operation Device Snapshot User Defined Dimension 1 Surrogate Key

OPR_DEVICE_UDD2_KEY

Operation Device Snapshot 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.

Operational Device Snapshot Fact

The Operational Device Snapshot fact provides a snapshot of operational devices (assets) in the Oracle Utilities Operational Device Management system. The fact calculates the ages for an asset, along with its failure and warranty status. The identification of operational devices will be based on the list of business objects (BOs) configured in the Master Configuration for BI extract parameters.

Entity Relationship Diagram

Properties

Property

Value

Target Table Name

CF_OPR_DEVICE_SNAP

Table Type

Fact

Fact Type

Snapshot

Driver Table

W1_ASSET

ODI Package Name

B1_PKG_CF_OPR_DEVICE_SNAP

ETL View Name

B1_F_OPR_DEVICE_SNAP_VW

Materialized View Name

B1_OPR_DEVICE_SNAP_MON_MV1

B1_OPR_DEVICE_SNP_MON_TOPX_MV1

Fields

Target Field

OBIEE Field

Source Field

Transformation Logic

Surrogate Key

OPR_DEVICE_SNAP_KEY

Operational Device Snapshot Fact Key

Natural Keys

SRC_ASSET_ID

Source Asset ID

W1_ASSET.ASSET_ID

Measures

AGE

Age in Days

W1_ASSET_NODE.EFF_DTTM

This field is calculated as the difference, in days, between the effective date of the first asset disposition and the end date of the current snapshot. The value is stored in days.

INSTALL_AGE

Install Age in Days

W1_ASSET_NODE.ASSET_DPOS_FLG

W1_ASSET_NODE.EFF_DTTM

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

This field is populated only if the asset is currently installed. It is calculated as the difference between the effective date of the installed disposition and the end date of the current snapshot. The value is stored in days.

INSTORE_AGE

Instore Age in Days

W1_ASSET_NODE.ASSET_DPOS_FLG

W1_ASSET_NODE.EFF_DTTM

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

This field is populated only if the asset is currently in storage. It is calculated as the difference, in days, between the effective date of the in-store disposition and the end date of the current snapshot. The value is stored in days.

FACT_CNT

Count

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

Devices In Storage

This is the number of devices that are currently in storage.

Devices In Warranty

This is the number of devices that are currently in warranty.

Installed Devices

This is the number of devices that are currently installed at customer locations.

Repair

This is the number of devices that are currently in repair.

Retired Devices

This is number of devices that are retired.

Degenerate Dimensions

FAILED_ONCE_IND

Failed Once Indicator

F1_MST_CONFIG.MST_CONFIG_DATA

W1_ASSET_NODE.FAILURE_FLG

W1_ASSET_NODE.EFF_DTTM

This indicator is set if the asset has failed at least once during the snapshot period. The asset failure condition to be considered will have to be defined in 'Extract Parameters' in the BI Configuration Portal available in the ODM system.

WNTY_EXP_IND

Warranty Expired Indicator

This indicator is set if the warranty date is earlier than or same as the end date of the snapshot period.

SNAP_TYPE_CD

Snapshot Frequency Type

SNAPSHOT_DT

Snapshot Date

WNTY_EXP_DTTM

Warranty Expiration Date/Time

W1_ASSET_CHAR.ADHOC_CHAR_VAL

This field is populated with the warranty expiration date/time of the asset, which is configured as a characteristic on the asset in ODM (characteristic type is "Warranty Expiration Date [W2-WEXDT]").

Foreign Keys

ASSET_DISP_START_KEY

Asset Disposition Start Dimension Surrogate Key

W1_ASSET_NODE.ASSET_DPOS_FLG

W1_ASSET_NODE.EFF_DTTM

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

This field is populated based on the asset disposition that is effective at the start of the snapshot period. 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.

ASSET_DISP_END_KEY

Asset Disposition End Dimension Surrogate Key

W1_ASSET_NODE.ASSET_DPOS_FLG

W1_ASSET_NODE.EFF_DTTM

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

This field is populated based on the asset disposition that will be effective at the end of the snapshot period. 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.

OPR_DEVICE_KEY

Operational Device Dimension Surrogate Key

W1_ASSET.ASSET_ID

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

UTIL_ASSET_KEY

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

ATTACHED_TO_ASSET_KEY

Utility Asset Dimension Surrogate Key

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

LOCATION_KEY

Location Dimension Surrogate Key

W1_ASSET_NODE.NODE_ID

W1_ASSET_NODE.EFF_DTTM

W1_ASSET_NODE.ATTCH_TO_ASSET_ID

This field is populated based on the location where the asset is at the end of the snapshot period. If it is a component and its current disposition is "Attached", its location will be the location of the asset where it is attached.

ADDR_KEY

Address Dimension Surrogate Key

W1_ASSET_NODE.NODE_ID

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

ASSET_AGE_KEY

Asset Age Dimension Surrogate Key

W1_ASSET.ASSET_TYPE_CD

This field is populated with the appropriate dimension key based on the matching age bucket record for asset's age.

ASSET_INSTALL_AGE_KEY

Asset Install Age Dimension Surrogate Key

W1_ASSET.ASSET_TYPE_CD

This field is populated with the appropriate dimension key based on the matching age bucket record for asset's install age.

ASSET_INSTORE_AGE_KEY

Asset Instore Age Dimension Surrogate Key

W1_ASSET.ASSET_TYPE_CD

This field is populated with the appropriate dimension key based on the matching age bucket record for asset's in-storage age.

SP_KEY

Service Point Dimension Surrogate Key

W1_NODE_IDENTIFIER.W1_ID_VALUE

This field is populated by deriving the MDM usage subscription that is linked to the MDM SP natural key stored in the ODM system.

MTR_DEVICE_KEY

Device Dimension Surrogate Key

This field is populated only if the ODM-MDM integration exists for the Service Point entity.

US_KEY

Usage Subscription Dimension Surrogate Key

This field is populated only if the ODM-MDM integration exists for the Service Point entity.

CONTACT_KEY

Contact Dimension Surrogate Key

This field is populated only if the ODM-MDM integration exists for the Service Point entity.

PER_KEY

Person Dimension Surrogate Key

This field is populated only if the ODM-MDM integration exists for the Service Point entity.

ACCT_KEY

Account Dimension Surrogate Key

This field is populated only if the ODM-MDM integration exists for the Service Point entity.

SA_KEY

Service Agreement Dimension Surrogate Key

This field is populated only if the ODM-MDM integration exists for the Service Point entity.

PREM_KEY

Premise Dimension Surrogate Key

This field is populated only if the ODM-MDM integration exists for the Service Point entity.

DATE_KEY

Date Dimension Surrogate Key

WNTY_EXP_DATE_KEY

Warranty Expiration Date Dimension Surrogate Key

WNTY_EXP_TIME_KEY

Warranty Expiration Time Dimension Surrogate Key

OPR_DEVICE_SNAP_UDD1_KEY

Operation Device Snapshot User Defined Dimension 1 Surrogate Key

OPR_DEVICE_SNAP_UDD2_KEY

Operation Device Snapshot 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.