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)	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		"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 is the site of the user who has logged in	Site
INC	Indicator: Backlog incident tickets per site	The number of incident tickets excluding the ones with "Status": "Status": "Resolved","Closed" AND "Canceled" per site in the backlog today Site is the site of the user who has logged in	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 is the site of the user who has logged in	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 is the site of the user who has logged in	Site
WO/ CASE	Indicator: Average Time to Resolve per site	Retrieve Ticket Data: WO and CASE tickets with "Status": "Resolved" AND "Closed" 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. (Note: For INC: "last resolved date", for WO ""Completed Date" to consider. ) Calculate Mean of the Time to resolve data:	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 /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	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)
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
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 within avg: Time since Submit Date is less than MTTR passed avg: Time since Submit Date is greater than MTTR 5)Assign Color Codes /visuals: Based on the states identified, assign the appropriate visuals to each pending ticket indicating the likelihood of resolution.