Tickets assigned to DSDS→ Service Type: "User Service Restoration","Security incident","user service request"
Service one ticket: the ticket that user creates in service one
HELIX tickets: One service one ticket will have one to many corresponding tickets in Helix in categories such as WO, INC, and CASE. An INC ticket in Helix is mapped to only one Service one ticket. However, more than one WO tickets can be associated to one service one ticket.
IMPORTANT NOTE: Calculations in Global and Site section only includes corresponding tickets in Helix. However, My ticket sections includes attributes of Service one tickets
WO & INC tickets
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"
Service one link → for incident "DWP_SRID"
"Status_Reason"→ to detect tickets pending user actions
Fastlane → "Escalated Fastlane"
Service one tickets
Missing table in ODS (Anon, Kasemvilas )
Service one reference
Service one ticket title
Submit date
Service one link to Helix tickets (note: corresponding field for INC "DWP_SRID" )
Joints of service one tickets and WO, INC and Case tables
TBD
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. |