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