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 | 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 S Red, Orange, and green icon based on the calculation below: 1)Retrieve Ticket Data: tickets with Status<> "Resolved" AND "Canceled" AND Status_Reason= "Client Action Required" AND Last Resolved Date falls within the last 6 months. Note: For other tickets the value is null 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" |