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

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 in the user site. 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", Is it about in progress tickets or tickets with pending actions	My ticket
INC /WO/ CASE	Indicator: My fastlane tickets	The number of user tickets
	Service one reference	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
WO/ CASE	Time to complete	Two scenarios: Actual: Applies on the tickets with Status= "Resolved" AND "Canceled" 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" (see the calculation above) 2) calculate the days passed since the Submit Date and compare it with the "average time to resolve per site" as the baseline. 3) Specify two states including: "within avg" and "passed avg" within avg: Time since Submit Date is less than passed avg: Time since Submit Date is within two standard deviations of the mean. 5)Assign Color Codes (visuals): 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 day)