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
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 |
| WO/ CASE | Indicator: Average Time to Resolve per site |
| 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: All my tickets | The number of user tickets | My ticket |
| INC /WO/ CASE | Indicator: My pending tickets | The number of user tickets excluding the ones with "Status": "Resolved" AND "Canceled" AND "Status_Reason": "Client Action Required", Is it about in progress tickets or tickets with pending actions | My ticket |
| INC /WO/ CASE | Indicator: My fastlane tickets | The number of user tickets | |
| Service one reference | The hyper link of service one ticket | ||
| 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 | |||
| WO/ CASE | Time to complete | Two scenarios: Actual: Applies on the tickets with Status= "Resolved" AND "Canceled" Calculate the Time to complete by subtracting the 'Submit Date' from the 'last Resolved date' for the ticket. Estimate : Applies on tickets not resolved yet. 1) calculate "Average Time to Resolve per site"=MTTR (see the calculation above) 2) calculate the days passed since the Submit Date and compare it with the MTTR as the baseline. 3) Specify two states including: "within avg" and "passed avg" as result of comparison
5)Assign Color Codes /visuals: Based on the states identified, assign the appropriate visuals to each pending ticket indicating the likelihood of resolution. | |
| Time to complete (per day) |