Facts
Important! As of the v2.7.0.1 release, materialized views are removed from Oracle Utilities Analytics Warehouse.
Asset Location
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
Fields
Target Field
OAS Field
Source Field
Transformation Logic
Surrogate Key
ASSET_LOC_KEY
Asset Location 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
 
 
UDM11
User Defined Measure 11
 
 
UDM12
User Defined Measure 12
 
 
UDM13
User Defined Measure 13
 
 
UDM14
User Defined Measure 14
 
 
UDM15
User Defined Measure 15
 
 
UDM16
User Defined Measure 16
 
 
UDM17
User Defined Measure 17
 
 
UDM18
User Defined Measure 18
 
 
UDM19
User Defined Measure 19
 
 
UDM20
User Defined Measure 20
 
 
UDDGEN1
User Defined Degenerate Dimension 1
 
 
UDDGEN2
User Defined Degenerate Dimension 2
 
 
UDDGEN3
User Defined Degenerate Dimension 3
 
 
UDDGEN4
User Defined Degenerate Dimension 4
 
 
UDDGEN5
User Defined Degenerate Dimension 5
 
 
UDDGEN6
User Defined Degenerate Dimension 6
 
 
UDDGEN7
User Defined Degenerate Dimension 7
 
 
UDDGEN8
User Defined Degenerate Dimension 8
 
 
UDDGEN9
User Defined Degenerate Dimension 9
 
 
UDDGEN10
User Defined Degenerate Dimension 10
 
 
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
 
 
UDDGENL6
User Defined Long Degenerate Dimension 6
 
 
UDDGENL7
User Defined Long Degenerate Dimension 7
 
 
UDDGENL8
User Defined Long Degenerate Dimension 8
 
 
UDDGENL9
User Defined Long Degenerate Dimension 9
 
 
UDDGENL10
User Defined Long Degenerate Dimension 10
 
 
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
 
 
UDDFK6_KEY
User Defined Dimension Foreign Key 6
 
 
UDDFK7_KEY
User Defined Dimension Foreign Key 7
 
 
UDDFK8_KEY
User Defined Dimension Foreign Key 8
 
 
UDDFK9_KEY
User Defined Dimension Foreign Key 9
 
 
UDDFK10_KEY
User Defined Dimension Foreign Key 10
 
 
UD_DATE1
User Defined Field Date 1
 
 
UD_DATE2
User Defined Field Date 2
 
 
UD_DATE3
User Defined Field Date 3
 
 
UD_DATE4
User Defined Field Date 4
 
 
UD_DATE5
User Defined Field Date 5
 
 
UD_DATE1_KEY
User Defined Field Date Key 1
 
 
UD_DATE2_KEY
User Defined Field Date Key 2
 
 
UD_DATE3_KEY
User Defined Field Date Key 3
 
 
UD_DATE4_KEY
User Defined Field Date Key 4
 
 
UD_DATE5_KEY
User Defined Field Date 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
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
Fields
Target Field
OAS 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
 
 
UDM11
User Defined Measure 11
 
 
UDM12
User Defined Measure 12
 
 
UDM13
User Defined Measure 13
 
 
UDM14
User Defined Measure 14
 
 
UDM15
User Defined Measure 15
 
 
UDM16
User Defined Measure 16
 
 
UDM17
User Defined Measure 17
 
 
UDM18
User Defined Measure 18
 
 
UDM19
User Defined Measure 19
 
 
UDM20
User Defined Measure 20
 
 
UDDGEN1
User Defined Degenerate Dimension 1
 
 
UDDGEN2
User Defined Degenerate Dimension 2
 
 
UDDGEN3
User Defined Degenerate Dimension 3
 
 
UDDGEN4
User Defined Degenerate Dimension 4
 
 
UDDGEN5
User Defined Degenerate Dimension 5
 
 
UDDGEN6
User Defined Degenerate Dimension 6
 
 
UDDGEN7
User Defined Degenerate Dimension 7
 
 
UDDGEN8
User Defined Degenerate Dimension 8
 
 
UDDGEN9
User Defined Degenerate Dimension 9
 
 
UDDGEN10
User Defined Degenerate Dimension 10
 
 
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
 
 
UDDGENL6
User Defined Long Degenerate Dimension 6
 
 
UDDGENL7
User Defined Long Degenerate Dimension 7
 
 
UDDGENL8
User Defined Long Degenerate Dimension 8
 
 
UDDGENL9
User Defined Long Degenerate Dimension 9
 
 
UDDGENL10
User Defined Long Degenerate Dimension 10
 
 
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
 
 
UDDFK6_KEY
User Defined Dimension Foreign Key 6
 
 
UDDFK7_KEY
User Defined Dimension Foreign Key 7
 
 
UDDFK8_KEY
User Defined Dimension Foreign Key 8
 
 
UDDFK9_KEY
User Defined Dimension Foreign Key 9
 
 
UDDFK10_KEY
User Defined Dimension Foreign Key 10
 
 
UD_DATE1
User Defined Field Date 1
 
 
UD_DATE2
User Defined Field Date 2
 
 
UD_DATE3
User Defined Field Date 3
 
 
UD_DATE4
User Defined Field Date 4
 
 
UD_DATE5
User Defined Field Date 5
 
 
UD_DATE1_KEY
User Defined Field Date Key 1
 
 
UD_DATE2_KEY
User Defined Field Date Key 2
 
 
UD_DATE3_KEY
User Defined Field Date Key 3
 
 
UD_DATE4_KEY
User Defined Field Date Key 4
 
 
UD_DATE5_KEY
User Defined Field Date 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
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 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
Fields
Target Field
OAS 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
 
 
UDM11
User Defined Measure 11
 
 
UDM12
User Defined Measure 12
 
 
UDM13
User Defined Measure 13
 
 
UDM14
User Defined Measure 14
 
 
UDM15
User Defined Measure 15
 
 
UDM16
User Defined Measure 16
 
 
UDM17
User Defined Measure 17
 
 
UDM18
User Defined Measure 18
 
 
UDM19
User Defined Measure 19
 
 
UDM20
User Defined Measure 20
 
 
UDDGEN1
User Defined Degenerate Dimension 1
 
 
UDDGEN2
User Defined Degenerate Dimension 2
 
 
UDDGEN3
User Defined Degenerate Dimension 3
 
 
UDDGEN4
User Defined Degenerate Dimension 4
 
 
UDDGEN5
User Defined Degenerate Dimension 5
 
 
UDDGEN6
User Defined Degenerate Dimension 6
 
 
UDDGEN7
User Defined Degenerate Dimension 7
 
 
UDDGEN8
User Defined Degenerate Dimension 8
 
 
UDDGEN9
User Defined Degenerate Dimension 9
 
 
UDDGEN10
User Defined Degenerate Dimension 10
 
 
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
 
 
UDDGENL6
User Defined Long Degenerate Dimension 6
 
 
UDDGENL7
User Defined Long Degenerate Dimension 7
 
 
UDDGENL8
User Defined Long Degenerate Dimension 8
 
 
UDDGENL9
User Defined Long Degenerate Dimension 9
 
 
UDDGENL10
User Defined Long Degenerate Dimension 10
 
 
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
 
 
UDDFK6_KEY
User Defined Dimension Foreign Key 6
 
 
UDDFK7_KEY
User Defined Dimension Foreign Key 7
 
 
UDDFK8_KEY
User Defined Dimension Foreign Key 8
 
 
UDDFK9_KEY
User Defined Dimension Foreign Key 9
 
 
UDDFK10_KEY
User Defined Dimension Foreign Key 10
 
 
UD_DATE1
User Defined Field Date 1
 
 
UD_DATE2
User Defined Field Date 2
 
 
UD_DATE3
User Defined Field Date 3
 
 
UD_DATE4
User Defined Field Date 4
 
 
UD_DATE5
User Defined Field Date 5
 
 
UD_DATE1_KEY
User Defined Field Date Key 1
 
 
UD_DATE2_KEY
User Defined Field Date Key 2
 
 
UD_DATE3_KEY
User Defined Field Date Key 3
 
 
UD_DATE4_KEY
User Defined Field Date Key 4
 
 
UD_DATE5_KEY
User Defined Field Date 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
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
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
 
 
UDM11
User Defined Measure 11
 
 
UDM12
User Defined Measure 12
 
 
UDM13
User Defined Measure 13
 
 
UDM14
User Defined Measure 14
 
 
UDM15
User Defined Measure 15
 
 
UDM16
User Defined Measure 16
 
 
UDM17
User Defined Measure 17
 
 
UDM18
User Defined Measure 18
 
 
UDM19
User Defined Measure 19
 
 
UDM20
User Defined Measure 20
 
 
UDDGEN1
User Defined Degenerate Dimension 1
 
 
UDDGEN2
User Defined Degenerate Dimension 2
 
 
UDDGEN3
User Defined Degenerate Dimension 3
 
 
UDDGEN4
User Defined Degenerate Dimension 4
 
 
UDDGEN5
User Defined Degenerate Dimension 5
 
 
UDDGEN6
User Defined Degenerate Dimension 6
 
 
UDDGEN7
User Defined Degenerate Dimension 7
 
 
UDDGEN8
User Defined Degenerate Dimension 8
 
 
UDDGEN9
User Defined Degenerate Dimension 9
 
 
UDDGEN10
User Defined Degenerate Dimension 10
 
 
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
 
 
UDDGENL6
User Defined Long Degenerate Dimension 6
 
 
UDDGENL7
User Defined Long Degenerate Dimension 7
 
 
UDDGENL8
User Defined Long Degenerate Dimension 8
 
 
UDDGENL9
User Defined Long Degenerate Dimension 9
 
 
UDDGENL10
User Defined Long Degenerate Dimension 10
 
 
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
 
 
UDDFK6_KEY
User Defined Dimension Foreign Key 6
 
 
UDDFK7_KEY
User Defined Dimension Foreign Key 7
 
 
UDDFK8_KEY
User Defined Dimension Foreign Key 8
 
 
UDDFK9_KEY
User Defined Dimension Foreign Key 9
 
 
UDDFK10_KEY
User Defined Dimension Foreign Key 10
 
 
UD_DATE1
User Defined Field Date 1
 
 
UD_DATE2
User Defined Field Date 2
 
 
UD_DATE3
User Defined Field Date 3
 
 
UD_DATE4
User Defined Field Date 4
 
 
UD_DATE5
User Defined Field Date 5
 
 
UD_DATE1_KEY
User Defined Field Date Key 1
 
 
UD_DATE2_KEY
User Defined Field Date Key 2
 
 
UD_DATE3_KEY
User Defined Field Date Key 3
 
 
UD_DATE4_KEY
User Defined Field Date Key 4
 
 
UD_DATE5_KEY
User Defined Field Date 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.