Multidimensional conceptual model
Terminology
Service one request: the ticket that user creates in service one (missing view)
HELIX tickets: One service one ticket will have one to many corresponding tickets in Helix in categories such as WO, INC, and CASE. An INC ticket in Helix is mapped to only one Service one ticket. However, more than one WO tickets can be associated to one service one ticket. (INC and WO views)
IMPORTANT NOTE:
Calculations in Global and Site section only includes corresponding tickets in Helix. However, My ticket sections includes attributes of Service one tickets
The scope for S1 & S2 is to include only HELIX tickets for which there is a link with Service request plus tickets in SR for which there is NO link to helix.
Relevant fields from ODS source tables:
| Google Drive Live Link | ||
|---|---|---|
|
WO & INC tickets
Currently there are two separate ODS tables
Ticket number→ for INC " Incident_Number" and for WO "Work_order_id"
Submitter id → "submitter"
Submitter name
user site name → "site"
Resolved date→ for INC "last resolved date" f or WO "Completed Date"
"Status"
"Submit date"
"Status_Reason"→ to detect tickets pending user actions
Fastlane → "Escalated Fastlane" for WO and "Total_Escalation_Level" for INC (if it is between 0 to 13, the corresponding ticket is a escalated ticket)
Service One Request
Google Drive Live Link url https://drive.google.com/file/d/1mZZ91j1UXEMzd0sGsO1DyxqUqXh9IKh2MSoXqV7nkKI/view
Missing table in ODS (Anon, Kasemvilas
)Jira server Jira serverId bbf5f9ee-7f3e-3274-bb6e-be12673f2166 key DTDASH-71
SELECT result , create_date , created_by_external_system , short_description , request_for_user_id , request_for_user_site , requesting_user_site , sbservice_request_id , target_application_request_display_id FROM `prj
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
...
Status:
Resolved
Assigned
Pending
In Progress
Cancelled
...
Existing
...
- 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
.DPL.V_FACT_hlx_service_request_stub`
Service one reference= "SBServiceRequestID " for WO / "target_application_request_display_id" for INC (see the Joint specification below)
Service one ticket title → to use the corresponding WO or INC title
Submit date= "create_date"
status="result"
User site='requesting_user_site'
Table Joints
Joints of service one tickets and WO, INC and Case tables
Join: INC with Service request
V_DIM_hlx_incident
hlx_service_request_stub
V_DIM_hlx_incident.SRID=hlx_service_request_stub. Target Application Request Display ID
hlx_service_request_stub. Target Application Request Display ID ,= Service request id to present in the dashboard
Join example from BQ views
V_DIM_hlx_incident
hlx_service_request_stub
Join: WO with Service request
V_FACT_hlx_work_order
hlx_service_request_stub
V_FACT_hlx_work_order. Work_order_id= hlx_service_request_stub. Target Application Request Display ID
hlx_service_request_stub.SBServiceRequestID,= Service request id to present in the dashboard
SELECT request_id, result, create_date, created_by_external_system, short_description, request_for_user_id, request_for_user_site, requesting_user_site,sbservice_request_id, target_application_request_display_id FROM 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
.DPL.V_FACT_hlx_service_request_stub` where sbservice_request_id='2373613'
Measure description
| Contxet - context | Indicators/graphs / Fields | Description | Visualization block (view) | Source ODS tables | Technical fiels | View table - qlick |
|---|---|---|---|---|---|---|
| INC | Indicator: Backlog incidents today |
| The number of incidents excluding the ones with "Status": "Resolved","Closed" AND " |
| Cancelled" globally in the back log today | Global | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_workorder | "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 total number of open INC/WO/CASE tickets excluding the ones with "Status": "Resolved","Closed" AND "Cancelled" (any other status indicating the ticket is closed) per site in the backlog today.
| Site Site is the site of the user who has logged in | WO Json format INC prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_HD_incidents CASE missing | "Status" "submitter" "site" Work_order_id |
|
| INC | A) Indicator: Backlog incident tickets per site | The number of incident tickets excluding the ones with "Status": "Resolved","Closed" AND "Cancelled" per site in the backlog today | Site | |||
| WO | B) Indicator: Backlog work order tickets per site | The number of work order tickets excluding the ones with "Status":"Status": "Resolved","Closed" AND "Cancelled" per site in the backlog today AND excluding tickets with the time between "Submit date" and "Completed date" is less than 5 mins [this is meant to exclude the WO 'Dummy' that are created and closed automatically in helix - identified as ' TSA Dummy'] | Site | Work_order_id "Status" | ||
| CASE | C) Indicator: Backlog case tickets per site | The number of case tickets excluding the ones with "Status": "Resolved","Closed" AND "Cancelled" per site in the backlog today | Site | CASE missing | ||
| WO | Indicator: Average Time to Resolve per site |
| Site | |||
| INC /WO/ CASE | Indicator: Submitted tickets today per site | The total number of INC/WO/CASE tickets per user site with "Submit date" = today
| Site | |||
| INC | E) Indicator: Submitted incident tickets today per site | The number of incident tickets per user site with "Submit date" = today | Site |
|
|
|
| WO | F) Indicator: Submitted work order tickets today per site | The number of WO tickets per user site with "Submit date" = today Exclude be AND [this is meant to exclude the WO 'Dummy' that are created and closed automatically in helix - identified as ' TSA Dummy'] | Site |
|
|
|
| CASE | G) 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 - tickets raised by the user | My ticket |
|
|
|
| INC /WO/ CASE | Indicator: My pending tickets | Calculation is at Helix ticket level: The number of INC and WO where status "pending" AND "Status_Reason": "Client Action Required" And (Below not for S1: The number of CASE tickets where status "pending" AND "Status_Reason": "Customer response ") | My ticket | Source CASE is not available (S2) | "Status_Reason" "Status" |
|
| INC /WO/CASE | Indicator: My Fastlane tickets | Fastlane is defined at WO and INC ticket level The number of service request ticket for which the corresponding WO in helix is "Escalated Fastlane"= "YES", AND corresponding INC is "Total_Escalation_Level"= between 0 to 13 (not applicable for case tickets) If the Service one ticket refers to WOs in helix, at least one of the WO associated should be "Escalated Fastlane"= "YES" to be counted as Fastlane. | My ticket | "Escalated Fastlane" for WO "Total_Escalation_Level" for INC |
| |
| Service one ticket | Service one request reference | The ID of the service one ticket.
| My ticket | prj-data-dm-dt-dev.DPL.V_FACT_hlx_service_request_stub | "request_id" |
|
| INC /WO/ CASE | Priority | Calculation: if the service one ticket is an INC ticket in helix, "Priority"= the priority of the helix ticket if the service one ticket is WO ticket in helix, "Priority"= Fastlane or Normal based on "Escalated Fastlane" attribute of WO Exception: Incident tickets might become Fastlane. In this situation "Priority"= Fastlane or Normal based on "Total_Escalation_Level"" attribute of INC. | My ticket |
|
|
|
| Service on request | Status | The status of the service one tickets
| My ticket | prj-data-dm-dt-dev.DPL.V_FACT_hlx_service_request_stub |
|
|
| Service on ticket | Description | The title of the service one ticket | My ticket | prj-data-dm-dt-dev.DPL.V_FACT_hlx_service_request_stub |
|
|
| Service one ticket | Submit date | The submit date of the service one ticket | My ticket | prj-data-dm-dt-dev.DPL.V_FACT_hlx_service_request_stub |
|
|
| INC /WO/ CASE | Resolved date | Calculation: if the service one ticket is an INC ticket in helix, "Resolved date"= the "last resolved date" of the INC ticket if the service one ticket refers to one/many WO tickets in helix, "resolved date"= Max "Completed_date" of all WO associated AND all WOs' "status"= "Resolved","Closed" OR "Canceled" | My ticket |
|
| |
| WO/ CASE | Time to complete | Two scenarios: Actual: Applies on the tickets with Status= "Resolved" AND "Canceled" 1)Calculate the Time to complete |
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.
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
Backlog incidents with "last resolved day" falls within the specified calendar day. To be reported in the dashboard for the last seven days
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 Resolveby subtracting the 'Submit Date' from the ' |
4) Sum up the time to resolve for all incidents resolved yesterday.
5)Calculate the Mean Time to Resolve
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
5)Assign Color Codes /visuals: Based on the states identified, assign the appropriate visuals to each pending ticket indicating the likelihood of resolution. | in S3 |
|
|
|