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:
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
Missing table in ODS (Anon, Kasemvilas )
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-data-dm-dt-dev.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 `prj-data-dm-dt-dev.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 by subtracting the 'Submit Date' from the '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 |
|
|
|