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.
Service one ticket: the ticket that user creates in service one
One service one ticket will have one to many corresponding tickets in Helix in categories such as WO, INC, and CASE.
an INC in Helix is mapped to only one Service ticket. However, more than one WO ticket can be associated to one service one ticket.
For global and site section
dev.ODS.ODS_HLX_0000_F001_I_H_workorder
Ticket number→ for INC "Incident_Number" and for WO "Request_ID"
Submitter id → "submitter"
Submitter name
user site name → "site"
Resolved date→ for INC "last resolved date" for WO "Completed Date"
"Status"
"Submit date"
For my ticket section
"Status_Reason"→ to detect tickets pending user actions
"Escalated Fastlane"
"Priority"
Ticket detailed Description→ "Detailed_Description"
Ticket short description→ for INC "Description" , for WO "summary"
Fastlane → For WO "Escalated Fastlane" is missing (INC is the same technical name)
Service one link → for incident "DWP_SRID"
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) | Source ODS tables | Technical fiels |
|---|---|---|---|---|---|
| INC | Indicator: Backlog incidents today | The number of incidents excluding the ones with "Status": "Resolved","Closed" AND "Canceled" globally in the back log today | Global | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_workorder | "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": "Status": "Resolved","Closed" AND "Canceled" per site in the backlog today | Site Site is the site of the user who has logged in | WO prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_workorder INC prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_workorder CASE missing | "Status" "submitter" "site" |
| INC | Indicator: Backlog incident tickets per site | The number of incident tickets excluding the ones with "Status": "Resolved","Closed" AND "Canceled" per site in the backlog today | Site | ||
| WO | Indicator: Backlog workorder tickets per site | The number of workorder tickets excluding the ones with "Status":"Status": "Resolved","Closed" AND "Canceled" per site in the backlog today | Site | ||
| CASE | Indicator: Backlog case tickets per site | The number of case tickets excluding the ones with "Status": "Status": "Resolved","Closed" AND "Canceled" per site in the backlog today | Site | CASE missing | |
| WO | Indicator: Average Time to Resolve per site |
(Note: For INC: "last resolved date", for WO ""Completed Date" to consider. )
| Site | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_HD_incidents | For INC: "last resolved date", for WO "Completed Date" to consider. |
| INC /WO/ CASE | Indicator: Submitted tickets today per site | The number of all tickets per user site with "Submit date" = today | Site | ||
| INC | Indicator: Submitted incident tickets today per site | The number of incident tickets per user site with "Submit date" = today | Site | ||
| WO | Indicator: Submitted workorder tickets today per site | The number of workorder tickets per user site with "Submit date" = today | Site | ||
| 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 | for INC and WO status "pending" AND "Status_Reason": "Client Action Required" CASE status "pending" AND "Status_Reason": "Customer response " | My ticket | Source CASE is not available (S2) | "Status_Reason" "Status" |
| INC /WO | Indicator: My fastlane tickets | "Escalated Fastlane": "YES", (not for case tickets) | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_workorder | "Escalated Fastlane" | |
| 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 | ||||
| INC /WO/ CASE | Resolved 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. |