View Source

Scope:

Tickets assigned to DSDS→ Service Type: "User Service Restoration","Security incident","user service request"

For the site view, based on the user log_in, the site of the user will be detected and the incidents created only in the similar site will be considered for the calculation.

Sources:

prj-data-dm-dt-dev.DS_DT_Dashboard.V_DIM_Incident

prj-data-dm-dt-dev.DS_DT_Dashboard.V_DIM_UserName_Site

Joint:

Dashboard User log in id with Incident submitter id

Note: Only two sites to be considered for the reporting at this stage. For that, we need to consider tickets that are raised by the users based on the corresponding site. "Site ID": "STE000000003884", What are the codes of the sites to involve?

Refresh frequency

The dashboard's refresh frequency is set to daily, updating every midnight.

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
Backlog incidents	All incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site.	"Status"	Status: Resolved Assigned Pending In Progress Cancelled
Resolved incidents	Backlog incidents with "Status"= "Resolved" and "last resolved date'" falls within the specified calendar day.	"Status" "Last Resolved Date"	We don't have status closed Closed date is only for cancelled incidents last resolved date is relevant for the calculation
Critical incidents	Backlog incidents with "Priority"= "Critical"	"Priority"	Critical High Medium Low
Created incidents	the number of backlog incidents where the "Submit Date" falls within the specified calendar day.	"Submit Date"	Submit date and report date in the data set are same
Unassigned incidents	Backlog incidents WHERE "Assignee Login ID" is Null	"Assignee Login ID"	Status can be 'Assigned' but it is not reliable, we have many records with assigned status but assignee column is null.
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

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

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

Indicators	Description
Backlog incidents	The number of incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site.
backlog Incident daily growth rate	Calculates the number of backlog incidents in yesterday and the number of the day before yesterday. Then, computes the ratio by dividing the count of yesterday's backlog incidents by the count of backlog incidents of the day before yesterday. If the count of backlog incidents for the day before yesterday is 0, it returns NULL for the backlog ratio to avoid division by zero.
backlog Incident weekly growth rate	Calculates the number of backlog incidents in yesterday and the number of the day before yesterday. Then, computes the ratio by dividing the count of yesterday's backlog incidents by the count of backlog incidents of the day before yesterday. Repeat the calculation for the last seven day
Unassigned incident	Number of ongoing incidents at the reporting time with no assignee Backlog incidents WHERE "Assignee Login ID" is Null
Unassigned incident weekly growth rate	Calculates the number of unassigned incidents in yesterday and the number by the day before yesterday. Then, computes the ratio by dividing the count of yesterday's unassigned incidents by the count of backlog incidents of the day before yesterday. Repeat the calculation for the seven last day.
Critical incidents	The number of Backlog incidents with "Priority"= "Critical"
Critical incidents weekly growth rate	Calculates the number of critical incidents in yesterday and the number reported by the day before yesterday. Then, computes the ratio by dividing the count of yesterday's critical incidents by the count of backlog incidents of the day before yesterday. Repeat the calculation for the seven last day.
Oldest critical incidents (Time)	Descoped
Created tickets last 7 days	Created incidents: the number of backlog incidents where the "Submit Date" falls within the specified calendar day. To be reported in the dashboard for the last seven days
Resolved incidents	Backlog incidents with "last resolved day" falls within the specified calendar day.
Resolved incident daily - last 7 days	Backlog incidents with "last resolved day" falls within the specified calendar day. To be reported in the dashboard for the last seven days
MRTT of resolved incident	Mean time to resolve incidents=MTTR=Total time spent resolving incidents/number of incident 11)Retrieve the incident numbers with the 'Last Resolved Date' being yesterday. 2)Obtain the 'Submit Date' of each incident. 3)Calculate the Time to Resolve by subtracting the 'Submit Date' from the 'Created Date' for each incident. 4) Sum up the time to resolve for all incidents resolved yesterday. 5)Calculate the Mean Time to Resolve
MRTT daily growth rate