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 

https://app.diagrams.net/#G14hQqUkgXBgM4hesg9sv0FiDEmPQAqIlj#%7B%22pageId%22%3A%22KbAqNpjhRAR2dXfDgz9a%22%7D

TerminologyDescriptionSource NotesExisting/ to develop
Backlog incidents All incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site."Status"

Status: 

Resolved    
Assigned    
Pending    
In Progress    
Cancelled



Existing 

 Mean time to resolve incidents=MTTR=Total time spent resolving incidents​/number of incident  
  1. Get the "incidents numbers" with the  "Last Resolved Date" in yesterday 
  2. Find the "Submit date" of the incidents
  3. Calculate Time to resolve: Substract submit date- created date per incident 
  4. Sum time to resolve for all the incidents resolved in yesterday
  5. Calculate Mean 

"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 todayGlobal 
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/ CASEIndicator: Tickets in-progress

The number of user tickets excluding the ones with "Status": "Resolved" AND "Canceled" 

My ticket 

INC /WO/ CASEIndicator: 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/ CASETicket 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

Light (we are in time line), medium (we are slightly approaching the time line) , dark orange (we passed the timeline) 

1)Retrieve Ticket Data: tickets with

Status= "Resolved" AND "Canceled" 

AND 

Last Resolved Date falls within the last 6 months.

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:

    • Green: Time since Submit Date is within one standard deviation of the mean.
    • Orange: Time since Submit Date is within two standard deviations of the mean.
    • Red: Time since Submit Date is within three standard deviations of the mean.

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"