Facts
Important! As of the 2.7.0.1 release, materialized views are removed from Oracle Utilities Analytics Warehouse.
Activity
An accumulation of activities associated to work orders in the source system, with a refresh of certain measures on a day-to-day basis.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_WO_ACTIVITY |
Table Type | Fact |
Fact Type | Accumulation |
Source System Driver Table | W1_ACTIVITY |
ODI Package Name | B1_PKG_CF_WO_ACTIVITY |
ETL View Name | B1_F_WO_ACTIVITY_KVW |
Fields
Target Field | Description | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
WO_ACTIVITY_KEY | Activity Surrogate Key | This field is populated from the B1_F_WO_ACTIVITY_SEQ sequence. | |
Natural Keys | |||
SRC_ACTIVITY_ID | Source Activity ID | W1_ACTIVITY.ACT_ID | |
Measures | |||
NBR_DAYS_OVERDUE | Number of Days Overdue | This field is calculated for non-finalized activities with Calendar Anniversary/Interval trigger types. If the original work date has been crossed, the measure is calculated as the difference between the current date and the original work date. | |
NBR_CYC_OVERDUE | Number of Cycles Overdue | This field is calculated based on the number of days overdue and the trigger frequency. Trigger frequency is determined in days based on the information on the maintenance trigger. | |
PLANNED_DUR | Planned Duration | This field is calculated (in hours) based on the duration on the activity's non-canceled labor resource requirements. | |
FACT_CNT | Count | This field is populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
TRIGGER_TYPE_CD | Trigger Type Flag | W1_MAINT_TRIGGER. TRIGGER_TYPE_FLG | This field is populated with the trigger type of the maintenance trigger of the activity. |
TRIGGER_TYPE_DESCR | Trigger Type Flag Description | CI_LOOKUP_VAL_L.DESCR_OVRD CI_LOOKUP_VAL_L.DESCR | This field is populated with the description of the trigger type from Lookup Language table for TRIGGER_TYPE_FLG lookup field. If an override description is not available, the regular description is extracted. |
SCHEDULED_IND | Scheduled Indicator | If at least one labor resource requirement is scheduled in a shift, this field is populated with ‘1’. Else, it is set to ‘0’. | |
BREAK_IN_IND | Break-in Indicator | If at least one labor resource booked time on this activity but the work spent is not scheduled in a shift, this field is set to 1. Else, it is set to 0. Note: This field is refreshed if any changes to timesheets occur. | |
NO_COST_IND | No Cost Indicator | No Cost Indicator is determined by summing the amount of frozen FTs associated with the activity. If the sum is ‘0’, this field is set to ‘1’. Else, it is set to ‘0’. Note: This field is refreshed if any changes to timesheets occur. | |
NO_LABOR_IND | No Labor Indicator | The No Labor Indicator is used to indicate if any labor resources are used for this activity. It is determined by looking for any posted timesheet details for the activity that has resource requirements of Craft. Note: This field is refreshed if any changes to timesheets occur. | |
ORIGINAL_WORK_DT | Original Work Date | W1_ACTIVITY. ORIGINAL_WORK_DT | This field is populated based on the original work date of the activity. |
REQUIRED_BY_DT | Required By Date | W1_ACTIVITY. REQUIRED_BY_DT | This field is populated based on the required date of the activity. |
Foreign Keys | |||
WO_KEY | Work Order Dimension Surrogate Key | W1_ACTIVITY.WO_ID | This is populated with the foreign key of the Work Order dimension based on the work order of the activity. |
WO_ACTIVITY_D_KEY | Activity Dimension Surrogate Key | W1_ACTIVITY.ACT_ID | This field is populated with the foreign key of the Work Activity dimension for this activity. |
UTIL_ASSET_KEY | Utility Asset Dimension Surrogate Key | W1_ACTIVITY. ASSET_ID | This field is populated with the foreign key of the Utility Asset dimension based on the asset associated with this activity. |
LOCATION_KEY | Location Dimension Surrogate Key | W1_ACTIVITY.NODE_ID | This field is populated with the foreign key of the Location dimension based on the node associated with this activity. |
ADDR_KEY | Address Dimension Surrogate Key | W1_ACTIVITY.NODE_ID | This field is populated with the foreign key of the Address dimension based on the node associated with this activity. |
OWNING_ORG_KEY | Owning Organization Dimension Surrogate Key | W1_ACTIVITY. OWNING_ACCESS_GRP_CD | This field is populated with the foreign key of the Owning Organization dimension based on the owning access group code associated with this activity. |
CREW_KEY | Crew Dimension Surrogate Key | W1_TIMESHEET_DETAIL. W1_CREW_ID W1_CREW_SHIFT.W1_CREW_ID W1_ACTIVITY.W1_CREW_ID | This field is populated with the foreign key of the Crew dimension based on the crew associated with the activity. The crew is determined by one of the following: • Looking up the crew via the time sheet detail associated with the activity. • Looking into the crew shifts associated with the activity's resource requirements. • Looking up the recommended crew on the activity. |
W_PLANNER_KEY | Planner Dimension Surrogate Key | W1_ACTIVITY. PLANNER_CD | This field is populated with the foreign key of the Planner dimension based on the planner code associated with this activity. |
ORIGINAL_WORK_DT_KEY | Original Work Date Key | W1_ACTIVITY. ORIGINAL_WORK_DT | This field is populated from the Date table based on original work date populated into the Original Work Date field. |
REQUIRED_BY_DT_KEY | Required By Date Key | W1_ACTIVITY. REQUIRED_BY_DT | This field is populated from the Date table based on the required by date populated into the Required By Date field. |
NUM_CYC_OD_KEY | Number of Cycles Overdue Dimension Surrogate Key | This field is populated from the Number of Cycles Overdue table based on the number of cycles overdue start/end bucket range. | |
WO_ACTIVITY_STATUS_KEY | Activity BO Status Dimension Surrogate Key | W1_ACTIVITY.BUS_OBJ_CD W1_ACTIVITY.BO_STATUS_CD W1_ACTIVITY.BO_STATUS_REASON_CD | This field is populated from the Activity table based on the BO code, status, and reason associated with the current activity ID. |
WO_ACTIVITY_UDD1_ KEY | Activity User Defined Dimension 1 Surrogate Key | ||
WO_ACTIVITY_UDD2_ KEY | Activity User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
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. | |
Asset FT
An accumulation of every financial transactions of an asset. It captures the financial information at the FT and asset level.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_ASSET_FT |
Table Type | Fact |
Fact Type | Accumulation |
Source System Driver Table | W1_FT W1_ACTIVITY W1_ASSET |
ODI Package Name | B1_PKG_CF_ASSET_FT |
ETL View Name | n/a |
Fields
Target Field | Description | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
ASSET_FT_KEY | Asset FT Fact Surrogate Key | This field is populated from the B1_ASSET_FT_SEQ sequence. | |
Natural Keys | |||
SRC_FT_ID | Source FT ID | W1_FT.W1_FT_ID | |
SRC_ASSET_ID | Source Utility Asset ID | W1_ACTIVITY_ASSET.ASSET_ID | |
Measures | |||
PM_LABOR_HRS | PM Labor Hours | If the work order associated with this FT is for Preventive Maintenance, this field is populated with the labor hours associated with the FT's timesheet detail prorated by the asset's percentage in the cost distribution. The field is mutually exclusive with CM Labor Hours. Labor Hours = Hours in Timesheet Detail * Asset share percentage on FT Activity | |
CM_LABOR_HRS | CM Labor Hours | If the work order associated with this FT is for Corrective Maintenance, this field is populated with the labor hours associated with this FT's timesheet detail prorated by the asset's percentage in the cost distribution. This field is mutually exclusive with PM Labor Hours. Labor Hours = Hours in Timesheet Detail * Asset share percentage on FT Activity | |
PM_COST | PM Cost | If the work order associated with this FT is for Preventive Maintenance, this field is populated with the amount associated with this FT prorated by the asset's percentage in the cost distribution. This field is mutually exclusive with CM Cost. Cost = (FT amount * percentage)/100 | |
CM_COST | CM Cost | If the work order associated with this FT is for Corrective Maintenance, this field is populated with the amount associated with this FT prorated by the asset's percentage in the cost distribution. This field is mutually exclusive with PM Cost. Cost = (FT amount * percentage)/100 | |
FACT_CNT | Count | This field is populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
OVERTIME _IND | Overtime Indicator | W1_TIMESHEET_DETAIL.REG_OVERTIME_ FLG | This field is used to indicate if the FT is for overtime work. This is determined by checking the regular/overtime flag on the timesheet detail associated with the FT. |
PARTICIPATION_CD | Participation Flag | W1_ACTIVITY_ASSET. PARTICIPATION_FLG | This field is populated based on the participation flag associated with this FT's activity and asset on the activity cost distribution table. |
PARTICIPATION_DESCR | Participation Description | CI_LOOKUP_VAL_L. DESCR_OVRD CI_LOOKUP_VAL_L. DESCR | This field is populated with the description of the participation flag from Lookup Language table for PARTICIPATION_FLG lookup field. If an override description is not available, the regular description is extracted. |
ACCOUNTING_DT | Accounting Date | W1_FT.W1_ ACCOUNTING_DT | |
Foreign Keys | |||
WO_KEY | Work Order Dimension Surrogate Key | W1_ACTIVITY.WO_ID | This field is populated with the foreign key of the Work Order dimension based on the work order of the activity. |
WO_ACTIVITY_KEY | Work Order Activity Dimension Surrogate Key | W1_FT.ACT_ID | This field is populated with the foreign key of the Activity table based on the activity associated with the financial transaction. |
UTIL_ASSET_KEY | Utility Asset Dimension Surrogate Key | W1_ACTIVITY_ASSET.A SSET_ID | This field is populated with the foreign key of the Asset table based on the activity associated with the financial transaction. |
LOCATION_KEY | Location Dimension Surrogate Key | W1_ACTIVITY.NODE_ID | This field is populated with the foreign key of the Location table based on the node ID associated with the financial transaction. |
ADDR_KEY | Address Dimension Surrogate Key | W1_ACTIVITY.NODE_ID | This field is populated with the foreign key of the Address table based on the node ID associated with the financial transaction. |
RESRC_TYPE_KEY | Resource Type Dimension Surrogate Key | W1_TIMESHEET_DETAIL.RESRC_TYPE_ID W1_STOCK_ITEM_DTL.RESRC_TYPE_ID W1_ODC_DTL.RESRC_TYPE_ID | This field is populated based on the resource type on the timesheet detail of the FT. If unavailable, the resource type of the material issue line's stock detail is used. Else, the resource type on the ODC details is used. |
OWNING_ORG_KEY | Owning Organization Dimension Surrogate Key | W1_ASSET. OWNING_ACCESS_GRP_CD | This field is populated based on the owning access group of the asset. |
FISCAL_CAL_KEY | Fiscal Period Dimension Surrogate Key | W1_FT.W1_ACCOUNTING_DT W1_CALENDAR_PERIOD. W1_CALENDAR_CD W1_CALENDAR_PERIOD. W1_FISCAL_YEAR W1_CALENDAR_PERIOD. ACCTG_PERIOD | This field is populated based on the accounting date on the financial transaction. The fiscal calendar record where the accounting date falls into is used. |
W_PLANNER_KEY | Planner Dimension Surrogate Key | W1_ACTIVITY.PLANNER_CD | This field is populated based on the planner code associated with the activity ID from the Activity table. |
ACCOUNTING_DT_KEY | Accounting Date Key | W1_FT.W1_ACCOUNTING_DT | This field is populated from the Date table based on the date part of the accounting date/time (which is populated into the Accounting Date field). |
ASSET_FT_UDD1_KEY | Asset FT User Defined Dimension 1 Surrogate Key | ||
ASSET_FT_UDD2_KEY | Asset FT User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
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 | ||
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 1 Key | ||
UD_DATE2_KEY | User Defined Field Date 2 Key | ||
UD_DATE3_KEY | User Defined Field Date 3 Key | ||
UD_DATE4_KEY | User Defined Field Date 4 Key | ||
UD_DATE5_KEY | User Defined Field Date 5 Key | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. The table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system. |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. | |
Asset Snapshot
A periodic snapshot of the asset details in the source system. It stores information, such as the asset's disposition at the end of the snapshot, how long the asset has been installed, how old since it was received, and more. Only those assets are extracted whose business object names have been configured as part of the extract parameter setup in source system.
For more details, see Configuring Source in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_ASSET_SNAP |
Table Type | Fact |
Fact Type | Snapshot |
Source System Driver Table | W1_ASSET |
ODI Package Name | B1_PKG_CF_ASSET_SNAP |
ETL View Name | n/a |
Fields
Target Field | Description | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
ASSET_SNAP_KEY | Asset Snapshot Fact Surrogate Key | This field is populated from the B1_ASSET_SNAP_SEQ sequence. | |
Natural Keys | |||
SRC_ASSET_ID | Source Asset ID | W1_ASSET.ASSET_ID | |
SNAP_TYPE_CD | Snapshot Type Code | This field is populated based on the ETL job configuration. | |
Measures | |||
AGE | Age in Days | W1_ASSET_NODE.EFF_DTTM | This field is populated with the asset’s age that is calculated as the difference (in days) between the earliest asset disposition date and the snapshot period end date. |
INSTALL_AGE | Install Age in Days | W1_ASSET_NODE.ASSET_DPOS_FLG W1_ASSET_NODE.EFF_DTTM | This field is populated with the asset's install age if the asset disposition at the end of the snapshot is ‘Installed’ or ‘In Service’. It is calculated as the difference (in days) between the latest asset disposition date and the snapshot period end date. |
FAIL_CNT_LIFETIME | Count of Failures (Lifetime) | This field is populated with the number of times this asset failed in its lifetime. The failure count is determined by counting the number of completed/closed high priority, regular work orders that were created against the asset from its very first installation until the snapshot period end date. | |
MTBF_LIFETIME | Mean Time Between Failures (Lifetime) | Mean Time Between Failure is the average length of operating time between failures for an asset. This field is calculated by looking at the total operating hours of the asset divided by the number of failures. Duration (in hours) = Snapshot end date - First installation of the asset | |
MTTR_LIFETIME | Mean Time to Repair (Lifetime) | Mean Time To Repair is the average time needed to restore an asset to its full operational condition upon a failure. This field is calculated by summing the duration of all asset downtime (in hours) and dividing by the number of failures. | |
FAIL _CNT_BUCKET1 | Count of Failures in Bucket 1 | This is the total count of failures that falls within the first configurable bucket range. | |
FAIL _CNT_BUCKET2 | Count of Failures in Bucket 2 | This is the total count of failures that falls within the second configurable bucket range. | |
FAIL _CNT_BUCKET3 | Count of Failures in Bucket 3 | This is the total count of failures that falls within the third configurable bucket range. | |
FAIL _CNT_BUCKET4 | Count of Failures in Bucket 4 | This is the total count of failures that falls within the fourth configurable bucket range. | |
FAIL _CNT_BUCKET5 | Count of Failures in Bucket5 | This is the total count of failures that falls within the fifth configurable bucket range. | |
FAIL _CNT_BUCKET6 | Count of Failures in Bucket 6 | This is the total count of failures that falls within the sixth configurable bucket range. | |
FAIL_CNT_BUCKET7 | Count of Failures in Bucket 7 | This is the total count of failures that falls within the seventh configurable bucket range. | |
FAIL_CNT_BUCKET8 | Count of Failures in Bucket 8 | This is the total count of failures that falls within the eighth configurable bucket range. | |
FAIL_CNT_BUCKET9 | Count of Failures in Bucket 9 | This is the total count of failures that falls within the ninth configurable bucket range. | |
FAIL_CNT_BUCKET10 | Count of Failures in Bucket 10 | This is the total count of failures that falls within the last configurable bucket range. | |
MTBF_BUCKET1 | Mean Time Between Failures in Bucket 1 | This is the mean time between failures that falls within the first configurable bucket range. | |
MTBF_BUCKET2 | Mean Time Between Failures in Bucket 2 | This is the mean time between failures that falls within the second configurable bucket range. | |
MTBF_BUCKET3 | Mean Time Between Failures in Bucket 3 | This is the mean time between failures that falls within the third configurable bucket range. | |
MTBF_BUCKET4 | Mean Time Between Failures in Bucket 4 | This is the mean time between failures that falls within the fourth configurable bucket range. | |
MTBF_BUCKET5 | Mean Time Between Failures in Bucket 5 | This is the mean time between failures that falls within the fifth configurable bucket range. | |
MTBF_BUCKET6 | Mean Time Between Failures in Bucket 6 | This is the mean time between failures that falls within the sixth configurable bucket range. | |
MTBF_BUCKET7 | Mean Time Between Failures in Bucket 7 | This is the mean time between failures that falls within the seventh configurable bucket range. | |
MTBF_BUCKET8 | Mean Time Between Failures in Bucket 8 | This is the mean time between failures that falls within the eighth configurable bucket range. | |
MTBF_BUCKET9 | Mean Time Between Failures in Bucket 9 | This is the mean time between failures that falls within the ninth configurable bucket range. | |
MTBF_BUCKET10 | Mean Time Between Failures in Bucket 10 | This is the mean time between failure that falls within the last configurable bucket range. | |
MTTR_BUCKET1 | Mean Time to Repair in Bucket 1 | This is the mean time to repair that falls within the first configurable bucket range. | |
MTTR_BUCKET2 | Mean Time to Repair in Bucket 2 | This is the mean time to repair that falls within the second configurable bucket range. | |
MTTR_BUCKET3 | Mean Time to Repair in Bucket 3 | This is the mean time to repair that falls within the third configurable bucket range. | |
MTTR_BUCKET4 | Mean Time to Repair in Bucket 4 | This is the mean time to repair that falls within the fourth configurable bucket range. | |
MTTR_BUCKET5 | Mean Time to Repair in Bucket 5 | This is the mean time to repair that falls within the fifth configurable bucket range. | |
MTTR_BUCKET6 | Mean Time to Repair in Bucket 6 | This is the mean time to repair that falls within the sixth configurable bucket range. | |
MTTR_BUCKET7 | Mean Time to Repair in Bucket 7 | This is the mean time to repair that falls within the seventh configurable bucket range. | |
MTTR_BUCKET8 | Mean Time to Repair in Bucket 8 | This is the mean time to repair that falls within the eight configurable bucket range. | |
MTTR_BUCKET9 | Mean Time to Repair in Bucket 9 | This is the mean time to repair that falls within the ninth configurable bucket range. | |
MTTR_BUCKET10 | Mean Time to Repair in Bucket 10 | This is the mean time to repair that falls within the last configurable bucket range. | |
FACT_CNT | Count | This field is populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
SNAPSHOT_DT | Snapshot Date | This field is populated with the last date of the current snapshot period (only the date part is populated). | |
LAST_FAIL_DTTM | Last Failure Date | W1_WO.CRE_DTTM W1_WORK_REQ.CRE_DTTM | This field is populated with the date/time of the asset's latest failure. See Count of Failures (Lifetime) field for definition of failure. |
Foreign Keys | |||
SNAPSHOT_DATE_KEY | Snapshot Date Key | This field is populated with the foreign key of the Date table based on the snapshot date/time associated with the asset. | |
W_ASSET_AGE_KEY | Asset Age Dimension Surrogate Key | This field is populated with the foreign key of the Asset Age Bucket dimension for which the asset's age falls into. | |
ASSET_DISP_START_ KEY | Asset Disposition Start Dimension Surrogate Key | W1_ASSET_NODE.ASSET_DPOS_FLG W1_ASSET_NODE.EFF_DTTM | This field is populated with the foreign key of the Asset Disposition dimension that was effective on the start date of the snapshot period. |
ASSET_DISP_END_KEY | Asset Disposition End Dimension Surrogate Key | W1_ASSET_NODE.ASSET_DPOS_FLG W1_ASSET_NODE.EFF_DTTM | This field is populated with the foreign key of the Asset Disposition dimension that was effective on the end date of the snapshot period. |
UTIL_ASSET_KEY | Asset Dimension Surrogate Key | W1_ASSET.ASSET_ID | This field is populated with the foreign key of the Asset table based on the asset associated. |
LOCATION_KEY | Location Dimension Surrogate Key | W1_ASSET_NODE.NODE_ID W1_ASSET_NODE.EFF_DTTM | This field is populated with the foreign key of the Location table based on the node associated with the asset’s location. |
ADDR_KEY | Address Dimension Surrogate Key | W1_ASSET_NODE.NODE_ID | This field is populated with the foreign key of the Address table based on the node associated with the asset’s location. |
W_ASSET_INSTALL_AGE_KEY | Asset Install age Dimension Surrogate Key | This field is populated with the foreign key of the Asset Install Age Bucket dimension for which the asset's installation age falls into. | |
LAST_FAIL_DT_KEY | Last Failure Date Key | W1_WO.CRE_DTTM W1_WORK_REQ.CRE_DTTM | This field is populated from the Date table based on the date part of the last failure date/time (which is populated in the Last Failure Date field). |
LAST_FAIL_TM_KEY | Last Failure Time Key | W1_WO.CRE_DTTM W1_WORK_REQ.CRE_DTTM | This field is populated from the Time table based on the time part of the last failure date/time (which is populated in the Last Failure Date field). |
OWNING_ORG_KEY | Owning Organization Dimension Surrogate Key | W1_ASSET.OWNING_ACCESS_GRP_CD | This field is populated from the Owning Organization table based on the owning access group associated. |
ASSET_SNAP_UDD1_ KEY | User Defined Dimension Key for Asset Snapshot | ||
ASSET_SNAP_UDD2_ KEY | User Defined Dimension Key for Asset Snapshot | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
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. | |
Location Snapshot
Captures a weekly/monthly snapshot of the information related to a location, including mean time between failures and mean time to repair.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_LOCATION_SNAP |
Table Type | Fact |
Fact Type | Snapshot |
Source System Driver Table | W1_NODE |
ODI Package Name | B1_PKG_CF_LOCATION_SNAP |
ETL View Name | n/a |
Fields
Target Field | Description | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
LOCATION_SNAP_KEY | Location Snapshot Fact Key | This field is populated from the B1_LOCATION_SNAP_SEQ sequence. | |
Natural Keys | |||
SRC_LOCATION_ID | Source Location ID | W1_NODE.NODE_ID | |
SNAP_TYPE_CD | Snapshot Type Code | This field is populated based on the ETL job configuration. | |
Measures | |||
FAIL_CNT_LIFETIME | Count of Failures (Lifetime) | This field is populated with the number of times this asset failed in its lifetime. The failure count is determined by counting the number of completed/closed High priority, regular work orders that were created against the asset from its very first installation until the snapshot period end date. | |
MTBF_LIFETIME | Mean Time Between Failures (Lifetime) | Mean Time Between Failure is the average length of operating time between failures for an asset. Duration (in hours) = Snapshot end date - First installation date of the asset | |
MTTR_LIFETIME | Mean Time to Repair (Lifetime) | Mean Time To Repair is the average time needed to restore an asset to its full operational condition upon a failure. | |
FAIL _CNT_BUCKET1 | Count of Failures in Bucket 1 | This is the total count of failures that falls within the first configurable bucket range. | |
FAIL _CNT_BUCKET2 | Count of Failures in Bucket 2 | This is the total count of failures that falls within the second configurable bucket range. | |
FAIL _CNT_BUCKET3 | Count of Failures in Bucket 3 | This is the total count of failures that falls within the third configurable bucket range. | |
FAIL _CNT_BUCKET4 | Count of Failures in Bucket 4 | This is the total count of failures that falls within the fourth configurable bucket range. | |
FAIL _CNT_BUCKET5 | Count of Failures in Bucket5 | This is the total count of failures that falls within the fifth configurable bucket range. | |
FAIL _CNT_BUCKET6 | Count of Failures in Bucket 6 | This is the total count of failures that falls within the sixth configurable bucket range. | |
FAIL_CNT_BUCKET7 | Count of Failures in Bucket 7 | This is the total count of failures that falls within the seventh configurable bucket range. | |
FAIL_CNT_BUCKET8 | Count of Failures in Bucket 8 | This is the total count of failures that falls within the eighth configurable bucket range. | |
FAIL_CNT_BUCKET9 | Count of Failures in Bucket 9 | This is the total count of failures that falls within the ninth configurable bucket range. | |
FAIL_CNT_BUCKET10 | Count of Failures in Bucket 10 | This is the total count of failures that falls within the last configurable bucket range. | |
MTBF_BUCKET1 | Mean Time Between Failures in Bucket 1 | This is the mean time between failure that falls within the first configurable bucket range. | |
MTBF_BUCKET2 | Mean Time Between Failures in Bucket 2 | This is the mean time between failure that falls within the second configurable bucket range. | |
MTBF_BUCKET3 | Mean Time Between Failures in Bucket 3 | This is the mean time between failure that falls within the third configurable bucket range. | |
MTBF_BUCKET4 | Mean Time Between Failures in Bucket 4 | This is the mean time between failure that falls within the fourth configurable bucket range. | |
MTBF_BUCKET5 | Mean Time Between Failures in Bucket 5 | This is the mean time between failure that falls within the fifth configurable bucket range. | |
MTBF_BUCKET6 | Mean Time Between Failures in Bucket 6 | This is the mean time between failure that falls within the sixth configurable bucket range. | |
MTBF_BUCKET7 | Mean Time Between Failures in Bucket 7 | This is the mean time between failure that falls within the seventh configurable bucket range. | |
MTBF_BUCKET8 | Mean Time Between Failures in Bucket 8 | This is the mean time between failure that falls within the eighth configurable bucket range. | |
MTBF_BUCKET9 | Mean Time Between Failures in Bucket 9 | This is the mean time between failure that falls within the ninth configurable bucket range. | |
MTBF_BUCKET10 | Mean Time Between Failures in Bucket 10 | This is the mean time between failure that falls within the last configurable bucket range. | |
MTTR_BUCKET1 | Mean Time to Repair in Bucket 1 | This is the mean time to repair that falls within the first configurable bucket range. | |
MTTR_BUCKET2 | Mean Time to Repair in Bucket 2 | This is the mean time to repair that falls within the second configurable bucket range. | |
MTTR_BUCKET3 | Mean Time to Repair in Bucket 3 | This is the mean time to repair that falls within the third configurable bucket range. | |
MTTR_BUCKET4 | Mean Time to Repair in Bucket 4 | This is the mean time to repair that falls within the fourth configurable bucket range. | |
MTTR_BUCKET5 | Mean Time to Repair in Bucket 5 | This is the mean time to repair that falls within the fifth configurable bucket range. | |
MTTR_BUCKET6 | Mean Time to Repair in Bucket 6 | This is the mean time to repair that falls within the sixth configurable bucket range. | |
MTTR_BUCKET7 | Mean Time to Repair in Bucket 7 | This is the mean time to repair that falls within the seventh configurable bucket range. | |
MTTR_BUCKET8 | Mean Time to Repair in Bucket 8 | This is the mean time to repair that falls within the eight configurable bucket range. | |
MTTR_BUCKET9 | Mean Time to Repair in Bucket 9 | This is the mean time to repair that falls within the ninth configurable bucket range. | |
MTTR_BUCKET10 | Mean Time to Repair in Bucket 10 | This is the mean time to repair that falls within the last configurable bucket range. | |
FACT_CNT | Count | This field is populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
SNAPSHOT_DT | Snapshot Date | This field is populated with the last date of the current snapshot period (only the date part is populated). | |
LAST_FAIL_DTTM | Last Failure Date | W1_WO.CRE_DTTM W1_WORK_REQ.CRE_DTTM | This field is populated with the date/time of the asset's latest failure. See the Count of Failures (Lifetime) field for the definition of failure. |
Foreign Keys | |||
ADDR_KEY | Address Dimension Surrogate Key | W1_ASSET_NODE.NODE_ID | This field is populated with the foreign key of the Address dimension based on the node associated with the asset’s location. |
LOCATION_KEY | Location Dimension Surrogate Key | W1_ASSET_NODE.NODE_ID W1_ASSET_NODE.EFF_DTTM | This field is populated with the foreign key of the Location dimension based on the node associated with the asset’s location. |
LAST_FAIL_DT_KEY | Last Failure Date Key | W1_WO.CRE_DTTM W1_WORK_REQ.CRE_DTTM | This field is populated from the Date table based on the date part of the last failure date/time (which is populated in the Last Failure Date field). |
LAST_FAIL_TM_KEY | Last Failure Time Key | W1_WO.CRE_DTTM W1_WORK_REQ.CRE_DTTM | This field is populated from the Time table based on the time part of the last failure date/time (which is populated in the Last Failure Date field). |
OWNING_ORG_KEY | Owning Organization Dimension Surrogate Key | W1_ASSET.OWNING_ACCESS_GRP_CD | This field is populated from the Owning Organization table based on the owning access group. |
SNAPSHOT_DATE_KEY | Snapshot Date Key | This field is populated from the Date table based on the snapshot date/time. | |
UTIL_ASSET_KEY | Asset Dimension Surrogate Key | W1_ASSET.ASSET_ID | This field is populated with the foreign key of the Asset table based on the asset associated |
LOCATION_SNAP_UDD1_KEY | User Defined Dimension 1 Surrogate Key | ||
LOCATION_SNAP_UDD2_KEY | User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
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 5 | ||
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. | |
Work Order
An accumulation of work orders in the source system, with a daily refresh of certain measures.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_WO |
Table Type | Fact |
Fact Type | Accumulation |
Source System Driver Table | W1_WO |
ODI Package Name | B1_PKG_CF_WO |
ETL View Name | B1_F_WO_VW |
Fields
Target Field | Description | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
WO_KEY | Work Order Fact Surrogate Key | This field is populated from the B1_F_WO_SEQ sequence. | |
Natural Keys | |||
SRC_WO_ID | Source Work Order ID | W1_WO. WO_ID | |
Measures | |||
FACT_CNT | Count | This field is populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
CRE_DTTM | Creation Date Time | W1_WO. CRE_DTTM | |
CMP_DTTM | Completion Date Time | W1_WO_LOG.LOG_DTTM | This field is populated with the log date/time when the work order was transitioned to the Completed state. |
REQUIRED_BY_DT | Required By Date | W1_WO.REQUIRED_BY_DT | |
ORIGINAL_WORK_DT | Original Work Date | This field is populated with the original work date of the activity related to work order. If there are multiple activities for a work order with different original work dates, the earliest original work date is populated. | |
OVERDUE_IND | Overdue Indicator | This field is used to indicate if the Work Order is overdue. If the work order is not finalized yet, it is considered overdue if today's date is past the Original Work Date. If the work order has been completed, it is considered overdue if it is completed past the Original Work Date. The overdue indicator is populated with '1' if the work order is overdue. Else, it is set to '0'. Note: This if refreshed daily if the Work Order is in a non-finalized state. | |
NO_COST_IND | No Cost Indicator | This field is populated based on the sum of frozen financial transactions associated with the work order's non-canceled activities. If the total is 0, the field is set to ‘1’. Else, it is set to ‘0’. Note: This if refreshed daily if the Work Order is in a non-finalized state. | |
NO_LABOR_IND | No Labor Indicator | This field is populated based on the timesheet details posted once the work order is closed. The indicator is set to ‘1’ only when such timesheet details exist. Else, it is set to ‘0’. | |
Foreign Keys | |||
ORIGINAL_WORK_DT_KEY | Original Work Date Key | W1_ACTIVITY. ORIGINAL_WORK_DT | This field is populated with the foreign key of the Date dimension based on the original work date associated with the activity. |
CMP_DT_KEY | Completion Date Key | W1_WO_LOG.LOG_DTTM | This field is populated with the foreign key of the Date dimension based on the completion date associated with the activity. |
REQUIRED_BY_DT_KEY | Required By Date Key | W1_WO.REQUIRED_BY_DT | This field is populated with the foreign key of the Date dimension based on the required by date associated with the activity. |
CRE_DT_KEY | Creation Date Key | W1_WO.CRE_DTTM | This field is populated with the foreign key of the Date dimension based on the creation date associated with the activity. |
CRE_TM_KEY | Creation Time Key | W1_WO.CRE_DTTM | This field is populated with the foreign key of the Date dimension based on the creation time associated with the activity. |
CMP_TM_KEY | Completion Time Key | W1_WO_LOG.LOG_DTTM | This field is populated with the foreign key of the Date dimension based on the completion time associated with the activity. |
WO_D_KEY | Work Order Dimension Surrogate Key | W1_WO.WO_ID | This field is populated with the foreign key of the Work Order dimension based on the work order ID associated with the activity. |
LOCATION_KEY | Location Dimension Surrogate Key | W1_ACTIVITY.NODE_ID | This field is populated based on the node ID of the primary non-cancelled activity for the work order. |
ADDR_KEY | Address Dimension Surrogate Key | W1_ACTIVITY.NODE_ID | This field is populated with the foreign key of the Address dimension based on the node ID of the activity. |
OWNING_ORG_KEY | Owning Organization Dimension Surrogate Key | W1_WO.OWNING_ACCESS_GRP_CD | This field is populated with the foreign key of the Owning Organization table based on the owning access group. |
W_PLANNER_KEY | Planner Dimension Surrogate Key | W1_WO.PLANNER_CD | This field is populated with the foreign key of the Planner dimension based on the planner code. |
WO_STATUS_KEY | Work Order BO Status Dimension Surrogate Key | W1_WO.BUS_OBJ_CD W1_WO.BO_STATUS_CD W1_WO.BO_STATUS_REASON_CD | This field is populated with the foreign key of the Work Order BO Status dimension based on the BO status code and BO status reason code associated with the work order. |
UTIL_ASSET_KEY | Utility Asset Dimension Surrogate Key | W1_ACTIVITY.ASSET_ID | This field is populated based on the asset of the primary activity for the work order. |
CREW_KEY | Crew Dimension Surrogate Key | W1_WO. W1_CREW_ID | |
WO_UDD1_KEY | Work Order User Defined Dimension 1 Surrogate Key | ||
WO_UDD2_KEY | Work Order User Defined Dimension 2 Surrogate Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
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. | |