Facts
Completed Shift
The Completed Shift fact holds the details of all completed shifts from the Oracle Utilities Mobile Workforce Management system that show the number of crews late to logon and by how much they deviated from the planned time, the number of crews early to logoff and by how much they deviated from the planned time, how much time the crew work overtime, and what are the deviations in estimated travel mileage versus actual mileage. This fact is populated based on completed, real shifts.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_CMP_SHIFT |
Table Type | Fact |
Fact Type | Accumulation |
Source System Driver Table | M1_CREW_SHFT M1_RESRC M1_TASK M1_LOCATION |
Stage Table Name | STG_CF_CMP_SHIFT |
ODI Package Name | B1_PKG_CF_CMP_SHIFT |
ETL View Name | B1_F_CMP_SHIFT_VW |
Materialized View Name | B1_CMP_SHIFT_MON_MV1 |
Fields
Target Field | Description | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
CMP_SHIFT_KEY | Completed Shift Surrogate Key | This field is populated from the OUBI_CMP_SHIFT_SEQ sequence. | |
Natural Keys | |||
SRC_SHIFT_ID | Shift ID | M1_CREW_SHFT.CREW_SHFT_ID | |
Measures | |||
PLANNED_DUR | Planned Duration (Seconds) | M1_CREW_SHFT.(PLAN_START_DTTM - M1_CREW_SHFT.PLAN_END_DTTM) | This field is populated with the planned duration of the shift. It is the difference between the planned start date time and end date time (in seconds). |
ACTUAL_DUR | Actual Duration (Seconds) | M1_CREW_SHFT. BUS_STATUS_DTTM - M1_CREW_SHFT_LOG. LOG_DTTM | This field is populated with the actual duration of the shift. It is populated with the difference (in seconds) between the actual start date time and actual completion date time. Depending on the crew shift, the regular logs or the mobile logs are used. |
OVERTIME_DUR | Overtime Duration (Seconds) | M1_CREW_SHFT.ACTUAL_DUR M1_CREW_SHFT.PLANNED_DUR | This field is populated if the shift went overtime. It is calculated from the difference between the actual duration and planned duration (in seconds). If the difference is greater than 0, overtime duration is populated. |
EST_DISTANCE | Estimated Distance | M1_TASK.BO_DATA_AREA | This field is the total of all estimated mileage of all tasks performed by the crew on the shift. |
EST_TOT_MILEAGE | Estimated Total Mileage for Vehicles | M1_TASK.RESRC_ID M1_RESRC.BO_DATA_AREA | This field is the total of estimated mileage of all tasks performed by the crew taking into consideration the resources allocated to the shift. It is populated with the sum of estimated travel distance for all tasks of the shift multiplied by the number of vehicles allocated to the shift. |
ACT_TOT_MILEAGE | Actual Total Mileage for Vehicles | M1_CREW_SHFT_RESRC.BO_DATA_AREA | This field is populated by the actual total mileage of all vehicles allocated to the shift. It is populated based on the odometer readings at the start and end of the shift. |
VEHICLE_CNT | Number of Vehicles | M1_RESRC.RESRC_ID | This field is populated with the total number of vehicles allocated to the shift. It is retrieved from resources where resource class is Vehicle (‘M1VH’). |
FACT_CNT | Count | This field is populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
SHIFT_PLANNED_ START_DTTM | Shift Planned Start Date/Time | M1_CREW_SHFT.PLAN_START_DTTM | This field is populated with the plan start date time associated with the crew shift. |
Foreign Keys | |||
CREW_SHIFT_KEY | Shift Dimension Surrogate Key | M1_CREW_SHFT.CREW_SHFT_ID | This field is populated from the Crew Shift dimension based on the crew shift ID associated with the crew. |
CREW_KEY | Crew Dimension Surrogate Key | M1_CREW_SHFT.RESRC_ID | This field is populated from the Crew dimension based on the resource ID associated with the crew shift. |
LATE_LOGON_TM_KEY | Late Logon Time Dimension Surrogate Key | M1_CREW_SHFT_LOG.LOG_DTTM - M1_CREW_SHFT.PLAN_START_DTTM | This field is populated based on the late logon time calculated as the difference (in minutes) between the shift's actual start date/time and planned start date/time. Depending on the crew shift, the regular logs or the mobile logs would be used. |
EARLY_LOGOFF_TM_ KEY | Early Logoff Time Dimension Surrogate Key | M1_CREW_SHFT.PLAN_END_DTTM - M1_CREW_SHFT.B US_STATUS_DTTM | This field is populated baed on the early logoff time calculated as the difference (in minutes) between the shift's actual end date/ time and planned end date/ time. |
TRAVEL_DIST_DEV_ KEY | Travel Distance Deviation Dimension Surrogate Key | This field is calculated based on the percentage of deviation between actual total mileage and estimated total mileage calculated earlier in the fact. | |
LOGON_ADDR_KEY | Logon Address Dimension Surrogate Key | M1_LOCATION/M1_RESRC.ADDRESS1 M1_LOCATION/M1_RESRC.ADDRESS2 M1_LOCATION/M1_RESRC.ADDRESS3 M1_LOCATION/M1_RESRC.ADDRESS4 M1_LOCATION/M1_RESRC.CROSS_STRT M1_LOCATION/M1_RESRC.SUBURB M1_LOCATION/M1_RESRC.CITY M1_LOCATION/M1_RESRC.COUNTY M1_LOCATION/M1_RESRC.STATE M1_LOCATION/M1_RESRC.POSTAL M1_LOCATION/M1_RESRC.COUNTRY M1_LOCATION/M1_RESRC.BO_DATA_AREA | This field is populated based on the logon location entered on the shift. If there is no logon location specified, but the logon location type is Home, the field is populated based on the home address of the first mobile worker allocated to the shift. |
LOGOFF_ADDR_KEY | Logoff Address Dimension Surrogate Key | M1_LOCATION/M1_RESRC.ADDRESS1 M1_LOCATION/M1_RESRC.ADDRESS2 M1_LOCATION/M1_RESRC.ADDRESS3 M1_LOCATION/M1_RESRC.ADDRESS4 M1_LOCATION/M1_RESRC.CROSS_STRT M1_LOCATION/M1_RESRC.SUBURB M1_LOCATION/M1_RESRC.CITY M1_LOCATION/M1_RESRC.COUNTY M1_LOCATION/M1_RESRC.STATE M1_LOCATION/M1_RESRC.POSTAL M1_LOCATION/M1_RESRC.COUNTRY M1_LOCATION/M1_RESRC.BO_DATA_AREA | This field is populated based on the logoff location entered on the shift. If there is no logoff location specified, but the logoff location type is Home, the field is populated based on the home address of the first mobile worker allocated to the shift. |
SHIFT_PLANNED_ START_DATE_KEY | Shift Planned Start Date Key | M1_CREW_SHFT.PLAN_START_DTTM | This field is populated from the Crew Shift table based on the planned start date associated with the crew shift. |
SHIFT_PLANNED_ START_TIME_KEY | Shift Planned Start Time Key | M1_CREW_SHFT.PLAN_START_DTTM | This field is populated from the Crew Shift table based on the planned start time associated with the crew shift. |
CMP_SHIFT_UDD1_KEY | Completed Shift User Defined Dimension 1 Key | ||
CMP_SHIFT_UDD2_KEY | Completed Shift User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system. |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. | |
Crew Task
The Crew Tasks fact has measures that show how much time the crews spend on productive versus non-productive tasks; how much time crews spend working on each task, as well as time spent in and out of services; how many times a crew makes or misses appointments, and if missed, by how much; and how many times crews arrive at an activity, but fail to complete it.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_CREW_TASK |
Table Type | Fact |
Fact Type | Accumulation |
Source System Driver Table | M1_CREW_SHFT M1_TASK M1_LOCATION M1_POU |
Stage Table Name | STG_CF_CREW_TASK |
ODI Package Name | B1_PKG_CF_CREW_TASK |
ETL View Name | B1_F_CREW_TASK_VW |
Materialized View Name | B1_CREW_TASKS_MON_MV1 |
Fields
Target Field | Description | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
CREW_TASK_KEY | Crew Task Surrogate Key | This field is populated from the OUBI_CREW_TASK_SEQ sequence. | |
Natural Keys | |||
SRC_SHIFT_ID | Shift ID | M1_CREW_SHFT.CREW_SHFT_ID | |
SRC_TASK_ID | Activity ID | M1_TASK.TASK_ID | |
SRC_SHIFT_LOG_SEQ | Shift Log Sequence | M1_CREW_SHFT_LOG.SEQNO | Depending on the crew shift, the regular logs or mobile logs are used. |
SRC_TASK_LOG_SEQ | Task Log Sequence | M1_TASK_LOG.SEQNO | Depending on the crew shift, the regular logs or mobile logs are used. |
Measures | |||
PREV_STATE_DUR | Time in Previous State | This field is populated with the time spent (in seconds) in the previous state based on the log date/times. | |
EST_TRAVEL_DUR | Estimated | M1_TASK.BO_DATA_AREA | This field is populated with the estimated travel duration from CLOB in the source Task table. |
FACT_CNT | Count | This field is populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
APPOINTMENT_IND | Appointment Indicator | M1_TASK.APPOINTMENT_FLG | This field is populated based on the appointment flag from the Task table. If the appointment flag is 'Y', the appointment indicator is populated with '1'. Else, with '0'. |
EMERGENCY_IND | Emergency Indicator | M1_TASK.QUEUE_FLG | This field is populated based on the queue flag from the source Task table. If the queue flag is 'M1ER', the emergency indicator is populated with '1'. Else, with '0'. |
FROM_DTTM | From Date/Time | M1_CREW_SHFT_LOG.LOG_DTTM M1_TASK_LOG.LOG_DTTM | |
TO_DTTM | To Date/Time | M1_CREW_SHFT_LOG.LOG_DTTM M1_TASK_LOG.LOG_DTTM | |
Foreign Keys | |||
CREW_TM_USG_KEY | Crew Time Usage Dimension Surrogate Key | This field is populated based on the task class and crew shift status or task status. | |
FROM_SHIFT_BO_ STATUS_KEY | From Shift BO Status Dimension Surrogate Key | M1_CREW_SHFT.BUS_OBJ_CD M1_CREW_SHFT_LOG.BO_STATUS_CD M1_CREW_SHFT_LOG.BO_STATUS_REASON_CD | This field is populated based on shift status from the log entries. |
TO_SHIFT_BO_STATUS_KEY | To Shift BO Status Dimension Surrogate Key | M1_CREW_SHFT.BUS_OBJ_CD M1_CREW_SHFT_LOG.BO_STATUS_CD M1_CREW_SHFT_LOG.BO_STATUS_REASON_CD | This field is populated based on shift status from the log entries. |
CREW_SHIFT_KEY | Shift Dimension Surrogate Key | M1_CREW_SHFT.CREW_SHFT_ID | This field is populated from the Crew Shift dimension based on the crew shift ID. |
CREW_KEY | Crew Dimension Surrogate Key | M1_CREW_SHFT.RESRC_ID | This field is populated from the Crew dimension based on the resource ID associated with the crew shift. |
TRAVEL_DUR_DEV_ KEY | Travel Duration Deviation Dimension Surrogate Key | This field is calculated based on the percentage of deviation between actual travel duration based on the log date/times and the estimated travel duration. | |
APPT_TM_KEY | Appointment Time Dimension Surrogate Key | This field is populated based on the difference between task time window start and end date/time associated with the task ID. | |
APPT_TM_OF_DAY_KEY | Appointment Time of Day Dimension Surrogate Key | This field is populated based on the task time window start date/time associated with the task ID. | |
FROM_TASK_BO_ STATUS_KEY | From Task BO Status Dimension Surrogate Key | M1_TASK.BUS_OBJ_CD M1_TASK_LOG.BO_STATUS_CD M1_TASK_LOG.BO_STATUS_REASON_CD | This field is populated based on the task status from log entries. |
TO_TASK_BO_STATUS_KEY | To Task BO Status Dimension Surrogate Key | M1_TASK.BUS_OBJ_CD M1_TASK_LOG.BO_STATUS_CD M1_TASK_LOG.BO_STATUS_REASON_CD | This field is populated based on the task status from log entries. |
TASK_TYPE_KEY | Task Type Dimension Surrogate Key | M1_TASK.TASK_TYPE_CD | This field is populated based on the task type code associated with the task ID. |
SERVICE_AREA_KEY | Service Area Dimension Surrogate Key | M1_TASK.SVC_AREA_CD | This field is populated based on the task type code associated with the task ID. |
ADDR_KEY | Address Dimension Surrogate Key | This field is populated based on the address constituents associated with the task. Based on the task class, the address constituents are fetched either from the task, location, depot, or period of unavailability entity. | |
FROM_DATE_KEY | From Date Dimension Surrogate Key | M1_CREW_SHFT_LOG.LOG_DTTM M1_TASK_LOG.LOG_DTTM | This field is populated based on the log date from shift log or task log. |
TO_DATE_KEY | To Date Dimension Surrogate Key | M1_CREW_SHFT_LOG.LOG_DTTM M1_TASK_LOG.LOG_DTTM | This field is populated based on the log date from shift log or task log. |
FROM_TIME_KEY | From Time Dimension Surrogate Key | M1_CREW_SHFT_LOG.LOG_DTTM M1_TASK_LOG.LOG_DTTM | This field is populated based on the log date from shift log or task log. |
TO_TIME_KEY | To Time Dimension Surrogate Key | M1_CREW_SHFT_LOG.LOG_DTTM M1_TASK_LOG.LOG_DTTM | This field is populated based on the log date from shift log or task log. |
MTR_DEVICE_KEY | Device Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the external identifier value for the meter device stored on the task as part of MWM-MDM integration. |
SP_KEY | Service Point Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the service point ID stored on the task as part of MWM-MDM integration. |
US_KEY | Usage Subscription Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the usage subscription ID stored on the task as part of MWM-MDM integration. |
CONTACT_KEY | Contact Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the contact ID stored on the task as part of MWM-MDM integration. |
PER_KEY | Person Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the person ID stored on task as part of MWM-CCB integration. |
ACCT_KEY | Account Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the account ID stored on task as part of MWM-CCB integration. |
SA_KEY | Service Agreement Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the service agreement ID stored on task as part of MWM-CCB integration. |
PREM_KEY | Premise Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the premise ID stored on task as part of MWM-CCB integration. |
CREW_TASK_UDD1_ KEY | Crew Task User Defined Dimension 1 Key | ||
CREW_TASK_UDD2_ KEY | Crew Task User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This 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. | |
Field Activity
The Field Activity fact holds the details of all activities (tasks) defined in the Oracle Utilities Extractors and Schema for Oracle Utilities Mobile Workforce Management system with measures around duration quantities, response times, and emergency indicator.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_FLD_ACTIVITY |
Table Type | Fact |
Fact Type | Accumulation |
Source System Driver Table | M1_TASK M1_CREW_SHFT |
Stage Table Name | STG_CF_FLD_ACTIVITY |
ODI Package Name | B1_PKG_CF_FLD_ACTIVITY |
ETL View Name | B1_F_FLD_ACTIVITY_VW |
Materialized View Name | B1_FLD_ACTIVITY_MON_MV1 B1_FLD_ACTIVITY_MON_MV2 B1_FLD_ACTIVITY_MON_MV3 |
Fields
Target Field | Description | Source Field | Transformation Logic |
|---|---|---|---|
Surrogate Key | |||
FLD_ACTIVITY_KEY | Activity Surrogate Key | This field is populated from the OUBI_FLD_ACTIVITY_SEQ sequence. | |
Natural Keys | |||
SRC_TASK_ID | Activity ID | M1_TASK.TASK_ID | This field is populated from the source Task table based on the tasks for which the Task Flag is set to Activity ('M1AC') and Parent Task ID is set as blank. |
Measures | |||
WORK_DUR | Work Duration (Minutes) | M1_TASK_TYPE.AVG_DUR | This field is populated with the average task duration from the Task Type table. If no value is found, '0' is populated. |
EST_WORK_DUR | Estimated Work Duration | M1_TASK.ESTIMATED_DURATION | This field is populated with the estimated task duration from the Task Type table. If no value is found, '0' is populated. |
ACT_WORK_DUR | Actual Work Duration | M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM | This field is populated with the total time spent ‘onsite’ (in seconds) by the crew. It is calculated based on the log entries. |
WORK_ATTEMPT_CNT | Number of Work Attempts | M1_TASK_LOG/M1_TASK_MOB_LOG.BO_STATUS_CD | This field is populated with the number of times crew attempted to do the work (based on the number of times the crew logged with an ‘Onsite’ status). If there are no log records, it is populated as '0'. |
TRAVEL_ATTEMPT_CNT | Number of Travel Attempts | M1_TASK_LOG/M1_TASK_MOB_LOG.BO_STATUS_CD | This field is populated with the number of times the crew traveled to the site to do the work (based on the number of times the crew logged with an ‘Enroute’ status). If there are no log records, it is populated as '0'. |
DISPATCH_RESP_DUR | Creation To Dispatch | M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM M1_TASK.CRE_DTTM | This field is populated based on the time (in seconds) between the creation of the activity until it was dispatched (based on the difference between the creation date/time and the log date/time of the log entry for 'Dispatched' status). If the activity has not been dispatched, it is populated as '0'. |
ENROUTE_RESP_DUR | Dispatch to Enroute | M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM | This field is populated based on the time (in seconds) it took from dispatching the activity until the crew is en route to the site (based on the difference between the maximum log date time for the entry with ‘Dispatched’ status and the log entry with ‘En Route’ status. If there are no log records, it is populated as ‘0’. |
ARRIVAL_RESP_DUR | Enroute To Onsite | M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM | This field is populated based on the time it took to travel until the crew arrived on site (based on the difference between the maximum log date time for the entry with ‘En Route’ status and the entry with ‘Arrived’ or ‘Onsite’ status). If there are no log records, it is populated as '0'. |
COMPLETED_RESP_ DUR | Response Time to Complete | M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM | This field is populated based on the time it took for the crew to arrive on site and finish the task (based on the difference between the maximum log date time for the entry with ‘Arrived’ or ‘Onsite’ and the entry with ‘Work Done’ or ‘Completed’ status). If there are no log records, it is populated as ‘0’. |
TOT_RESP_DUR | Total Response Time (Minutes) | M1_TASK.DISPATCH_RESP_DUR M1_TASK.ENROUTE_RESP_DUR M1_TASK.ARRIVAL_RESP_DUR M1_TASK.COMPLETED_RESP_DUR | This field is populated with the total time it took to complete the task, starting from the time the task was dispatched all the way to the time the task was completed. |
ACT_TRAVEL_DUR | Actual Travel Duration | M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM | This field is populated based on the total time the crew spent travelling to the site. Unit of measure is seconds. |
FACT_CNT | Count | This field is populated with the standard value of ‘1’. | |
Degenerate Dimensions | |||
EMERGENCY_IND | Emergency Indicator | M1_TASK.QUEUE_FLG | This field is populated from the source Task table based on the identified tasks. If the Queue Flag is set to 'M1ER', then populate the Emergency Indicator with '1'. Else, with '0'. |
APPOINTMENT_IND | Appointment Indicator | M1_TASK.APPOINTMENT_FLG | This field is populated from the source Task table based on the identified tasks. If the Appointment Flag is set to 'Y', then populate the Appointment Indicator with '1'. Else, with '0'. |
SAME_DAY_APPT_IND | Same Day Appointment | M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM M1_TASK.CRE_DTTM | This field is populated with '1' if the appointment is made on the same day as the creation date. Else, with '0'. |
TASK_INFO | Task Info String | M1_TASK_TYPE_L.DESCR M1_TASK_ADDR.ADDRESS1, CITY, COUNTY, STATE F1_BUS_OBJ_STATUS_L.DESCR | This field is populated with the concatenation of task type description, address, status code, task time window and crew for a task. |
CRE_DTTM | Create Date/Time | M1_TASK.CRE_DTTM | |
STATUS_DTTM | Status Date/Time | M1_TASK.STATUS_UPD_DTTM | |
SCHED_START_DTTM | Scheduled Start Date/Time | M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM | This field is populated with '1' if the appointment is made on the same day as the window start date. Else, with '0'. |
SCHED_END_DTTM | Scheduled End Date/Time | M1_TASK_TM_WIND.TSK_TM_WIND_END_DTTM | This field is populated with '1' if the appointment is made on the same day as the window end date. Else, with '0'. |
Foreign Keys | |||
TASK_BO_STATUS_KEY | Task BO Status Dimension Surrogate Key | M1_TASK.BUS_OBJ_CD M1_TASK.BO_STATUS_CD M1_TASK.BO_STATUS_REASON_CD | This field is populated based on the business object code, status code, and status reason code from the Task table. |
TASK_TYPE_KEY | Task Type Dimension Surrogate Key | M1_TASK.TASK_TYPE_CD | |
SERVICE_AREA_KEY | Service Area Dimension Surrogate Key | M1_TASK.SVC_AREA_CD | |
ADDR_KEY | Address Dimension Surrogate Key | M1_TASK_ADDR.ADDRESS1 M1_TASK_ADDR.ADDRESS2 M1_TASK_ADDR.ADDRESS3 M1_TASK_ADDR.ADDRESS4 M1_TASK_ADDR.CROSS_STREET M1_TASK_ADDR.SUBURB M1_TASK_ADDR.CITY M1_TASK_ADDR.COUNTY M1_TASK_ADDR.STATE M1_TASK_ADDR.POSTAL M1_TASK_ADDR.COUNTRY | This field is populated based on the location of the task. |
CREW_KEY | Crew Dimension Surrogate Key | M1_CREW_SHFT.RESRC_ID | This field is populated from the Crew table based on the resource ID of the Task table. Identify the resource ID from the Crew Shift table for the child task with 'Dispatched' status and with maximum log date/time. |
APPT_TM_KEY | Appointment Time Dimension Surrogate Key | M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM M1_TASK_LOG/M1_TASK_MOB_LOG.LOG_DTTM | This field is populated based on the difference (in minutes) between the appointment start date time and log date time. |
APPT_TM_OF_DAY_KEY | Appointment Time Of Day Dimension Surrogate Key | M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM | This field is populated based on the task time window start date/time associated with the task ID. |
WORK_DUR_DEV_KEY | Work Duration Deviation Key | M1_TASK_TYPE.ACT_WORK_DUR M1_TASK_TYPE.EST_WORK_DUR | This field fetches the actual work duration and estimated work duration and calculates the percentage of deviation. ((Actual Work Duration - Estimated Work Duration)/Estimated Work Duration))*100 |
RESP_TM_DEV_KEY | Response Time Deviation Dimension Surrogate Key | M1_TASK_TYPE.TOT_RESP_DUR M1_TASK_TYPE.BO_DATA_AREA | This field is populated based on the response time SLA from the Task Type table. ((Overall Response Time - Response Time SLA)/ Response Time SLA))*100 |
CRE_DATE_KEY | Creation Date Key | M1_TASK.CRE_DTTM | This field is populated based on the creation date of the task. |
STATUS_DATE_KEY | Status Date Key | M1_TASK.STATUS_UPD_DTTM | This field is populated based on the status update date of the task. |
SCHED_START_DATE_ KEY | Scheduled Start Date Key | M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM | This field is populated based on the scheduled start date of the task. |
SCHED_END_DATE_ KEY | Scheduled End Date Key | M1_TASK_TM_WIND.TSK_TM_WIND_END_DTTM | This field is populated based on the scheduled end date of the task. |
CRE_TIME_KEY | Creation Time Key | M1_TASK.CRE_DTTM | This field is populated based on the creation date/time of the task. |
STATUS_TIME_KEY | Status Time Key | M1_TASK.STATUS_UPD_DTTM | This field is populated based on the status update date/time of the task. |
SCHED_START_TIME_ KEY | Scheduled Start Time Key | M1_TASK_TM_WIND.TSK_TM_WIND_START_DTTM | This field is populated based on the scheduled start time of the task. |
SCHED_END_TIME_ KEY | Scheduled End Time Key | M1_TASK_TM_WIND.TSK_TM_WIND_DATE_DTTM | This field fetches the most recent effective time window based on the appointment. |
MTR_DEVICE_KEY | Device Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the external identifier value for meter device stored on the task as part of MWM-MDM integration. |
SP_KEY | Service Point Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the service point ID stored on the task as part of MWM-MDM integration. |
US_KEY | Usage Subscription Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the usage subscription ID stored on the task as part of MWM-MDM integration. |
CONTACT_KEY | Contact Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the contact subscription ID stored on the task as part of MWM-MDM integration. |
PER_KEY | Person Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the person subscription ID stored on the task as part of MWM-MDM integration. |
ACCT_KEY | Account Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | This field is populated based on the account ID stored on the task as part of MWM-MDM integration. |
SA_KEY | Service Agreement Dimension Surrogate Key | M1_TASK.BO_DATA_AREA | 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 | M1_TASK.BO_DATA_AREA | This field is populated based on the premise ID stored on the task as part of MWM-MDM integration. |
FLD_ACTIVITY_UDD1_KEY | Activity User Defined Dimension 1 Key | ||
FLD_ACTIVITY_UDD2_KEY | Activity User Defined Dimension 2 Key | ||
User Defined Attributes | |||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
Data Load Attributes | |||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This 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. | |