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) | Source ODS tables | Technical fiels |
|---|---|---|---|---|---|
| INC | Indicator: Backlog incidents today | The number of incidents excluding the ones with "Status": "Resolved" AND "Canceled" globally in the back log today | Global | "Status" | |
| INC | Indicator: Submitted incidents today | The number of incidents globally with "Submit date" = today | Global | Submit date | |
| 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 | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_HD_incidents | |
| 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" for INC and WO CASE status "pending" AND "Status_Reason": "Customer response " | My ticket | Source CASE is not available (S2) | |
| INC /WO | Indicator: My fastlane tickets | The number of user tickets (not for case ticket) | |||
| INC /WO/ CASE | Service one reference | The hyper link of service one ticket | |||
| INC /WO/ CASE | Priority | ||||
| INC /WO/ CASE | Status | ||||
| INC /WO/ CASE | Description | ||||
| INC /WO/ CASE | Submit date | ||||
| WO/ CASE | Time to complete | Two scenarios: Actual: Applies on the tickets with Status= "Resolved" AND "Canceled" 1)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. |