Tickets assigned to DSDS→ Service Type: "User Service Restoration","Security incident","user service request"
Sources:
prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_workorder→
prj-data-dm-dt-dev.DS_DT_Dashboard.V_DIM_Incident
prj-data-dm-dt-dev.DS_DT_Dashboard.V_DIM_Status
prj-data-dm-dt-dev.DS_DT_Dashboard.V_DIM_UserName_Site
The dashboard's refresh frequency is set to hourly, updating every hr.
The data for the dashboard is sourced from a daily snapshot stored in BigQuery.
| Terminology | Description | Source | Notes | Existing/ to develop |
|---|---|---|---|---|
| Backlog incidents | All incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site. | "Status" | Status: Resolved | Existing |
| Mean time to resolve incidents=MTTR=Total time spent resolving incidents/number of incident |
| "Last Resolved Date" "Submit date" |
SELECT Incident_Number, status, Priority, Last_Resolved_Date, Service_Type, Site, Assignee_Login_ID, Assignee, Submit_Date, FROM `prj-data-dm-dt-dev.DS_DT_Dashboard.V_DIM_Incident
| Context | Indicators/graphs / Fields | Description | Visualization block (view) |
|---|---|---|---|
| INC | Indicator: Backlog incidents today | The number of incidents excluding the ones with "Status": "Resolved" AND "Canceled" globally in the back log today | Global |
| INC | Graph: Backlog incident last seven days (daily at global level) | The number of backlog incidents per day globally over the last seven days | Global |
| INC | Indicator: Submitted incidents today | The number of incidents globally with "Submit date" = today | Global |
| INC | Graph: Submitted incident last seven days (daily) | The number of Submitted incidents per day globally over the last seven days | Global |
| INC | Indicator: Backlog incident per site | The number of incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site in the backlog today Site is the site of the user who has logged in | Site |
| INC | Graph: Backlog incident last seven days (daily at site level) | The number of backlog incidents per day in the user site over the last seven days | Site |
| INC | Indicator: Submitted incidents today (per site) | The number of incidents per user site with "Submit date" = today | Site |
| INC | Graph: Submitted incident last seven days (daily at site level) | The number of Submitted incidents per day in the user site over the last seven days | Site |
| INC /WO/ CASE | Indicator: Tickets in-progress | The number of user tickets excluding the ones with "Status": "Resolved" AND "Canceled" | My ticket |
| INC /WO/ CASE | Indicator: My ticket pending action | The number of user tickets excluding the ones with "Status": "Resolved" AND "Canceled" AND "Status_Reason": "Client Action Required", | My ticket |
| INC /WO/ CASE | Ticket type | Being Requester or Approver (how to identify approver?) | My ticket |
| Service one | The hyper link of service one ticket | ||
| Fulfilment reference | WO and Incident reference | ||
| Priority | |||
| Status | Flag tickets with waiting for my action The tickets with "Status": "Resolved" AND "Canceled" AND "Status_Reason": "Client Action Required", | ||
| Description | |||
| Submit date | |||
| Resolution time estimate (forecast) | This is for a ticket with Status<> "Resolved" AND "Canceled" AND Status_Reason= "Client Action Required" For other ticket the value is null Red, Orange, and green icon based on the calculation below: | ||
| Time to complete (per hrs & day) | This is for a ticket with Status= "Resolved" AND "Canceled" 1)Retrieve the ticket numbers with the 'Last Resolved Date' being in the last 6 months. 2)Calculate the Time to Resolve by subtracting the 'Submit Date' from the 'last Resolved date' for each ticket. 3) Calculate mean and standard deviation of the Time to resolve data. The mean can be a baseline average time, while the standard deviation will indicate how much the resolution times vary from the mean. 4) Estimate the resolution time for a pending ticket by using the standard deviation according to the rules below Green= Time since submit date within one standard deviation Orange= Time since submit date within two standard deviation Red= Time since submit date within three standard deviation |