View Source

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

Terminology

Description

Source

Notes

Existing/ 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

Get the "incidents numbers" with the "Last Resolved Date" in yesterday
Find the "Submit date" of the incidents
Calculate Time to resolve: Substract submit date- created date per incident
Sum time to resolve for all the incidents resolved in yesterday
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 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	Retrieve Ticket Data: tickets with Status= "Resolved" AND "Canceled" AND Last Resolved Date falls within the last 6 months. Calculate the Time to Resolve by subtracting the 'Submit Date' from the 'last Resolved date' for each ticket. Calculate Mean of the Time to resolve data:	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",	My ticket
INC /WO/ CASE	Indicator: My fastlane tickets

	Service one	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
	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"