Data Sources & Assumptions

Data Availability by Discipline

Comparison of data received across all five disciplines. Source data is from GPM/MTM Ellipse exports and SAP cost extracts.

Data Type	Signals	Electrical	Track	Structures	Facilities
12-month plan (work orders)	Yes	Yes	Yes	Yes - Dec 2024 to Dec 2025, 11,012 WOs	Yes - Jul 2025 to Jul 2026, 34,794 WOs
Multi-year history (work orders)	Yes	Yes	Yes	Yes - 4 yr (Jan 2022 - Jan 2026), 71,918 WOs	Yes - 3 yr (Jan 2022 - Jan 2026), 72,954 WOs
TMP / standard job definitions	Yes	Yes	Yes	Yes - 30 unique standard jobs, 112 rows	Yes - 199 unique standard jobs, 352 rows
Corrective orders	Yes	Yes	Yes	Yes - 8,668 orders	Yes - 2,099 orders
Fault / defect orders	Yes	Yes	Yes	Yes - 28,163 orders (Jan 2017 - Jul 2026)	No - requested from GPM
Asset register	Yes	Yes	Yes	Yes - via shared asset register (SR, BG, BS, BP, CT, CU, PF classes)	Yes - via shared asset register (BD, FE, EA, EM, LE, ED, SI classes)
AIWP / long-term plan	-	-	Yes	No	No
Competency / qualification matrix	-	-	-	Yes	No
Work group-level WO data	-	-	-	-	Yes - 38 files, ~142k rows (Jan 2017 - Jun 2026)
Workforce / FTE (SAP costs)	Yes	Yes	Yes	Yes - via SAP cost extract	Yes - via SAP cost extract

Outstanding data request: Fault/defect orders for Facilities have been requested from GPM. The existing asset register (342,100 rows) covers all disciplines including structures and facilities equipment classes.

Workforce FTE data for all disciplines comes from a single SAP cost extract covering all parent projects. The asset register (342,100 rows) is a single Ellipse extract covering all disciplines. Dash (-) indicates data type is not applicable or was not expected for that discipline.

Signals - Source Data

Work Order Schedule: Signal Maint. Sched. Burnley - Ringwood Filtered (010126 - 301226) (1).xlsx

Field	Description	How Used
Work Order No.	Unique WO identifier (8-digit)	Primary key for tracking individual jobs
Work Order Desc	Description including standard specification reference and frequency	Identifying job type and frequency
Equipment Class	Asset category (e.g. Trainstops, Point Mechanism, Enclosures)	Grouping and filtering
Date Raised	Date the WO was created in the source system	Understanding scheduling lead time
Min Planned / Max Planned	Earliest and latest permissible dates for the work	Defines the maintenance window for tolerance analysis
Planned Start	Target/due date for the work	Current scheduled date - basis of daily analysis
Depot	Home depot for the assigned work group	Travel analysis and depot-level reporting
Work Group	Team assigned to the work (e.g. Dandenong Signals Section 4301)	Team-level scheduling analysis
Equipment Location	Station or area name	Geographic analysis and visit consolidation
Standard Job Number	Links to the TMP schedule (e.g. SP209, SP311)	Join key to maintenance requirements
Minimum Competency	Required qualification for the job	Capability analysis
Coordinates	MGA Zone 55 easting/northing of the asset	Mapping, travel distance, geographic analysis

Coverage: 16,575 work orders across 244 locations, 44 work groups, 14 depots. Date range: Feb 2026 to Sep 2026.

TMP Schedule: 12.5.1.5.37 Signals TMP Schedule - Version 8_057442.XLSX

Field	Description	How Used
Standard Job	Job identifier (e.g. SP209)	Join key to work orders
Asset Class / Sub Asset / Component	Asset hierarchy	Understanding what is being maintained
Task Type	Nature of the work (e.g. Inspect, Clean & Test)	Confirming all work is inspection-based
Frequency (days)	How often the task must be performed	Context for maintenance windows
Tolerance (days)	Allowable deviation from the due date	Cross-reference with work order windows

Coverage: 176 standard job entries covering 107 unique standard jobs.

Structures - Source Files

File	Type	Rows	Notes
Structure TMP - MTM Insights.xlsx	TMP definitions	112	30 unique standard jobs with frequencies, tolerances, asset counts
Structures Planned Maint. 1 YR Data (30 Dec 2024 - 30 Dec 2025).xlsx	12-month plan	11,012	Standard Ellipse format. Extra sheets: WG Poss Loc (depot locations), Notes on Min Qual
Structures Planned Maint. 4 YR Data (01 Jan 2022 - 01 Jan 2026).xlsx	4-year history	71,918	Same format as 1YR plan
Structures All Corrective WO.xlsx	Corrective orders	8,668	Standard export format
Structures All Fault Work Orders (01 Jan 2017 - 21 Jul 2026).xlsx	Fault orders	28,163	3 sheets: Summary, Open (150), Closed (28,013). Non-standard header format (row 3)
A15710 - MTM Structures Competency Matrix.xlsx	Competency matrix	-	Role-based qualification requirements (4 work groups)

Facilities - Source Files

File	Type	Rows	Notes
Facilities TMP - MTM Insights.xlsx	TMP definitions	352	199 unique standard jobs with frequencies, tolerances, asset counts
Facilities Planned Maint. 1 YR Data (01 Jul 2025 - 01 Jul 2026).xlsx	12-month plan	34,794	Standard Ellipse format. WG Info sheet lists 33 work groups (31 with data extracts)
Facilities Planned Maint. 3 YR Data (01 Jan 2022 - 01 Jan 2026).xlsx	3-year history	72,954	Same format as 1YR plan
Facilities All Corrective Maint..xlsx	Corrective orders	2,099	Standard export format
FAC Work Order Data per Work Group/ (38 files)	WG-level history	~142,029	MTM Works Management Template format. Headers row 4, sample rows on 5-6 (to be removed). Date range mostly Jan 2017 - Jun 2026

FAC work group file notes: All 38 work group files contain a type descriptor row (row 5) and a sample row starting with "e.g." (row 6) that must be stripped before import. FCFSSAB has only 1 month of data (Jan 2026). SFVCG filename shows impossible date range (Jan 2027 - Jun 2026) - likely a typo.

Database Structure - mr5_poc.db

All disciplines share a single SQLite database. The ELN-specific database (eln_poc.db) is being consolidated into this main database over time.

Table	Rows	Discipline	Key Columns
Signals
work_orders	16,575	Signals	work_order_no, work_order_desc, equipment_class, planned_start, standard_job, work_group, location
historic_orders	150,003	Signals	work_order_no, work_order_desc, equipment_class, standard_job, work_group, depot, completed_date
sig_corrective_orders	1,557	Signals	wo_no, status, work_group, line_section, plant_no, std_job, wo_desc
sig_fault_orders	19,344	Signals	fault_no, date, reported_equip, fault_description, action_taken, responsibility
standard_jobs	176	Signals	egi_ref_no, asset_class, sub_asset, frequency_days, description, standard_job_number
capability_matrix	1,075	Signals	work_group, standard_job_number, wo_count
min_competency	176	Signals	standard_job, minimum_qualification
work_group_classification	44	Signals	work_group, depot, job_count, wo_count, tier
bundles	6,362	Signals	bundle_id, version, work_group, depot, bundle_date, wo_count, locations
Electrical Network
eln_historic_orders	116,598	Electrical	wo_no, equipment_name, plant_number, wo_desc, standard_job, work_group, completed_date
eln_corrective_orders	1,247	Electrical	wo_no, status, work_group, line_section, plant_no, std_job, wo_desc
Track
track_daily_orders	77,839	Track	work_order_no, line_section, work_group, egi, wo_desc, plan_date, seg_from, seg_to
track_corrective_orders	105,089	Track	wo_no, work_group, line_section, egi, wo_desc, std_job, plan_start_date
track_tmp	187	Track	egi, asset, sub_asset, frequency, tolerance, standard_job, description
track_segments	323	Track	line_code, segment_name, ch_start, ch_end, length_km, lat, lng
track_lines	18	Track	code, name, colour, ch_start, ch_end, trimmed_length_km
Rail Flaw Defects
rfd_defects	17,713	Track	defect_id, status, track_code, chainage, line_code, defect_type, recommended_action
rfd_all_tests	193,003	Track	defect_id, testing_date, defect_type, test_magnitude, test_size
rfd_latest_tests	17,715	Track	defect_id, testing_date, defect_type, test_magnitude, test_size
rfd_fault_orders	824	Track	work_order, plant_no, wo_desc, corridor, plan_start_date
Cross-Discipline
asset_register	342,100	All	equip_no, plant_no, equip_class, item_name_1, district_code, lat, lng, egi
sap_costs	531,396	All	financial_year, date, parent_project, project_no, work_order, cost_element, amount, ref_no
workforce_fte	84	All	discipline, parent_project, project_no, primary_heads, proportional_heads, ordinary_cost
station_chainages	213	All	line_code, loc_code, loc_name, chainage_km, lat, lng
incidents	310,118	All	incident_date, fault_no, incident_code, incident_cause, location, affected_line
initiative_items	22	All	initiative_code, item_name, std_job, change_desc, base_orders_yr, reduction_pct
ellipse_sap_mapping	15	All	discipline, ellipse_sj, ellipse_desc, sap_wo, sap_desc, match_basis
sap_wo_classification	115	All	work_order, wo_desc, project_no, category, subcategory, discipline

Total database size: 28 tables. The legacy eln_poc.db is being consolidated into mr5_poc.db. All analysis queries should target mr5_poc.db.

Key Assumptions

Area	Assumption
Data provenance	All source data originates from GPM's Ellipse CMMS and SAP financial systems. The existing maintenance schedule was produced by the incumbent operator (MTM), not system-generated.
Workforce FTE	FTE figures are derived from SAP cost data using two methods: Primary (headcount from highest-cost project) and Proportional (hours distributed across projects). The Proportional method is used as the basis for initiative savings calculations.
Possession classification	The possession_required flag in the database is Think Transit's classification based on work type analysis, not a field from GPM source data.
SAP employee data	Employee names are redacted in the SAP cost extract. The ref_no field is used as the employee identifier for headcount analysis.
Asset register coverage	The asset register (342,100 rows) is a single Ellipse extract covering all disciplines. Structures assets include BG (742 bridges), BS (2,778 spans), SR (35,757 structures). Facilities assets include BD (6,384 buildings), FE (21,692 fire equipment), EA (8,006 HVAC), EM (20,733 emergency lighting).
Terminology	This analysis uses "bundling" (not pairing), "visits" (not trips), and "priority groups" (not tiers) throughout.

Detailed Assumptions - Signals Analysis

Maintenance Windows

The min_planned and max_planned dates from the work order data represent the allowable maintenance window - the period within which the work can be completed without breaching compliance requirements.
These windows are used as-is from the source data. They are close to but not exactly equal to the TMP tolerance values, suggesting the source system may apply slightly different tolerance calculations.
Work can be moved to any date within this window without affecting compliance. This is the basis for the tolerance window consolidation analysis.

Work Groups and Capability

Each work group is assumed to be a single team that operates from its assigned depot.
A work group's capability is inferred from the standard jobs it has been assigned across the year. If a group performs SP311 at any point, it is assumed capable of performing SP311 at any location within its territory.
Specialist groups (Interlocking Section 6201, CBCT Group, Loop sections, Technical Support, Maintenance Support OIC) are excluded from the overlap analysis as their work requires dedicated competencies.

Travel and Geography

Depot locations are based on the station coordinates of each depot's name (e.g. Carrum depot is at Carrum station). Actual depot facilities may be at slightly different locations.
Travel distances are straight-line (haversine) calculations between coordinates. Actual road distances would be 20-40% further.
11% of work orders had no coordinates in the source data. These were geocoded from their station/location name using OpenStreetMap (Nominatim) and are flagged in the database as inferred.
65 work orders (0.4%) had coordinates outside the Melbourne metropolitan area, indicating data entry errors in the source system. These were excluded from geographic analysis.

Scheduling

The analysis assumes the current schedule has not been optimised for geographic proximity. This is supported by the data patterns (same team visiting same location on consecutive days, multiple teams at the same station).
Daily team capacity is not modelled - the analysis identifies how many visits could theoretically be consolidated but does not limit the number of work orders per team per day. All scheduled work is inspection-based, and inspections typically generate follow-up corrective work on-site.