Tickets assigned to DSDS→ Service Type: "User Service Restoration","Security incident","user service request"
Only two sites to be considered for the reporting. 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
| Facts | Measures | Dimensions/fields | Calculation per day | Calculation per week | Notes | ||
|---|---|---|---|---|---|---|---|
| Incidents | Backlog incidents | All incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site. | "Status" | Total number Growth rate | Total number Growth rate | Status: Resolved | SELECT * FROM `prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_HD_incidents` where Status in ('In progress','Pending', 'assigned') |
| Resolved incidents | Backlog incidents with "Status": "Resolved" | "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 rate | Critical High Medium Low | SELECT * FROM `prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_HD_incidents` where Status in ('In progress','Pending', 'assigned') AND Priority='Critical' | |
| Created incidents | Backlog incidents with "Submit Date" in the last two days | "Submit Date" | Total number Growth rate | Total number Growth rate | Submit 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 rate | Status can be 'Assigned' but it is not reliable, we have many records with assigned status but assignee column is null. | ||
| MTTR | 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 |
| Measure | Indicators | Description | Source table technical name | Source field technical name | Calculations/Flow |
|---|---|---|---|---|---|
| The number of incident | Backlog incidents | Number of ongoing incidents: all incidents excluding "Status": "Closed" AND "Resolved" per site. | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents Master data to link user ID to the site Joint: User ID | customer_login_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 |
| Incident Growth | Incident daily growth rate | Number of ongoing incidents at the reporting time divided by the number of open incidents reported in the mid night of the day before. For that, real time refresh and daily snapshots from the source are required. | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | ||
| Incident weekly growth rate | Number of ongoing incidents at the reporting time divided by the number of ongoing incidents reported in the mid night of the 7 day before. For that, real time refresh and daily snapshots from the source are required. | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |||
| Unassigned backlog | Number of ongoing incidents at the reporting time with no assignee | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |||
| Unassigned incident weekly growth rate | Number of ongoing incidents without assignee at the reporting time divided by the number of ongoing incidents without assignment in the mid night of the 7 day before. For that, real time refresh and daily snapshots from the source are required. | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |||
| Critical incidents | Number of ongoing incidents with priority as critical | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |||
| Critical incidents weekly growth rate | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | ||||
| Oldest critical incidents (Time) | The time window between the creation date of the latest ongoing incidents with priority as critical and the current time | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |||
| Opened ticket last 48 hrs | The tickets with creation date in the last 2 days (How to show a trend ? number per day, per hrs? | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |||
| Resolved incidents | The number of Resolved tickets in what time period? Resolved today? | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |||
| Resolved incident daily | The number of incidents for which the status turned to resolved in each day (the last seven days to be displayed in the dashboard) | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |||
| MRTT of resolved incident | Mean time to resolve incidents=MTTR=Total time spent resolving incidents/number of incident | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |||
| MRTT daily growth rate | ? | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |||
| MRTT weekly growth rate | ? | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents |