Multidimensional conceptual model

https://app.diagrams.net/#G14hQqUkgXBgM4hesg9sv0FiDEmPQAqIlj#%7B%22pageId%22%3A%22KbAqNpjhRAR2dXfDgz9a%22%7D

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'

Data, Analytics & AI Platform > 1. USER VIEW MAPPING > image-2024-5-28_17-33-27.png

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

Data, Analytics & AI Platform > 1. USER VIEW MAPPING > image-2024-5-28_18-7-4.png

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'

Data, Analytics & AI Platform > 1. USER VIEW MAPPING > image-2024-5-28_18-0-33.png

Data, Analytics & AI Platform > 1. USER VIEW MAPPING > image-2024-5-28_11-24-58.png

Measure description

Contxet - context	Indicators/graphs / Fields	Description	Visualization block (view)	Source ODS tables	Technical fiels
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. This indicator should be the sum of A, B and C below	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 'Assigned To 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	Retrieve Ticket Data: WO tickets with "Status": "Resolved" AND "Closed" AND Last Resolved Date falls within the last 3 months in the user site. Calculate the Time to Resolve by subtracting the 'Submit Date' from the "completed date" for each ticket. Calculate Mean of the Time to resolve data:	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 This indicator should be the sum of E, F and G below	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 'Assigned To 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. 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. In this section only represents ticket id at service one level. The service one tickets with status= "waiting for approval" has not yet a corresponding ticket in Helix. When a service one ticket is approved, a Helix ticket will be created. FOR ALL, WHEN APPROVAL IS NEEDED. The service one ticket ID should appear in the dashboard as soon as created in the service one (no matter if the corresponding ticket in Helix is created yet. As soon as the corresponding helix ticket created, it should be considered for the related calculations in the dashboard. In the dashboard, the hyperlink should land the user to the service one to see the details. 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.	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 In the dashboard, there should be a flag in the status column for records corresponding to tickets that require user action (service one tickets for which at least one of the related helix tickets is with the "Status_Reason": "Client Action Required" )	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 within avg: Time since Submit Date is less than MTTR passed avg: Time since Submit Date is greater than MTTR 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