Scope:
Tickets assigned to DSDS→ 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?What are the applications included in the scope? not relevant
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
| Terminology |
|---|
| Description |
|---|
| Source | Notes | |
|---|---|---|
| Backlog incidents | All incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site. | "Status" |
Total number
Growth rate
Total number
Growth rateStatus: Resolved | |
| Resolved incidents | Backlog incidents with "Status" |
| = "Resolved" and "last resolved date'" falls within the specified calendar day. | "Status" "Last Resolved Date" |
Total number
Growth rate
Total number
Growth rate
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" |
Total number
Growth rate
Total number
Growth rateCritical High Medium Low |
| Created incidents |
| the number of backlog incidents where the "Submit Date" |
| falls within the specified calendar day. | "Submit Date" |
Total number
Growth rate
Total number
Growth rateSubmit date and report date in the data set are same | ||
| Unassigned incidents | Backlog incidents WHERE "Assignee Login ID" is Null | "Assignee Login ID" |
Total number
Growth rate
Total number
Growth rateStatus 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 |
| "Last Resolved Date" "Submit date" |
Time
Growth rate
Time
Growth rate
|
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 |
Master data to link user ID to the site
Joint: User ID
Filters:
Status=1, 2...
Based on the user ID login to the dashboard, apply the filter to count only incidents that are created in the same site as the user
| 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. |
The number of incidents for which the status turned to resolved in each day
(the last seven days to be displayed in the dashboard)
| 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 |