Scope:
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
Refresh frequency
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.
Multidimensional conceptual model
| 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" |
Relevant tables in the BQ for the measure calculations
Relevant fields for the calculations
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
Measure description
| 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 | Indicator: Submitted incidents today | The number of incidents globally with "Submit date" = today | Global |
| INC /WO/ CASE | Indicator: Backlog tickets per site | The number of all tickets 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 /WO/ CASE | Indicator: Backlog incident tickets per site | The number of incident tickets 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 /WO/ CASE | Indicator: Backlog workorder tickets per site | The number of workorder tickets 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 /WO/ CASE | Indicator: Backlog case tickets per site | The number of case tickets 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 /WO/ CASE | Indicator: Submitted tickets today per site | The number of all tickets per user site with "Submit date" = today | Site |
| INC /WO/ CASE | Indicator: Submitted incident tickets today per site | The number of incident tickets per user site with "Submit date" = today | Site |
| INC /WO/ CASE | Indicator: Submitted workorder tickets today per site | The number of workorder tickets per user site with "Submit date" = today | Site |
| INC /WO/ CASE | Indicator: Submitted case tickets today per site | The number of case tickets per user site with "Submit date" = today | 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 S Light (we are in time line), medium (we are slightly approaching the time line) , dark orange (we passed the timeline) 1)Retrieve Ticket Data: tickets with Status= "Resolved" AND "Canceled" AND Last Resolved Date falls within 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 pending tickets: For pending tickets, calculate the time since the Submit Date. Then, estimate the resolution time based on the standard deviation:
5)Assign Color Codes: Based on the estimated resolution time, assign the appropriate color code (Green, Orange, or Red) to each pending ticket indicating the likelihood of resolution. | ||
| Time to complete (per hrs & day) | This is for a ticket with Status= "Resolved" AND "Canceled" |