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.ODS.ODS_HLX_0000_F001_I_H_workorder
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 hourly, updating every hr.
The data for the dashboard is sourced from a daily snapshot stored in BigQuery.
Multidimensional conceptual model
| Terminology | Description | Source | Notes | Existing/ to develop |
|---|---|---|---|---|
| Backlog incidents | All incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site. | "Status" | Status: Resolved | Existing |
| Mean time to resolve incidents=MTTR=Total time spent resolving incidents/number of incident |
| "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
| Context | Indicators/graphs | Description | Visualization block (view) |
|---|---|---|---|
| Incident | Indicator: Backlog incidents today | The number of incidents excluding the ones with "Status": "Resolved" AND "Canceled" globally in the back log today | Global |
| Incident | Graph: Backlog incident last seven days (daily at global level) | The number of backlog incidents per day globally over the last seven days | Global |
| Incident | Indicator: Submitted incidents today | The number of incidents globally with "Submit date" = today | Global |
| Incident | Graph: Submitted incident last seven days (daily) | The number of Submitted incidents per day globally over the last seven days | Global |
| Incident | Indicator: Backlog incident per site | The number of incidents 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 |
| Incident | Graph: Backlog incident last seven days (daily at site level) | The number of backlog incidents per day in the user site over the last seven days | Site |
| Incident | Indicator: Submitted incidents today (per site) | The number of incidents per user site with "Submit date" = today | Site |
| Incident | Graph: Submitted incident last seven days (daily at site level) | The number of Submitted incidents per day in the user site over the last seven days | Site |
| 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 |