The Client

A large public sector transport company running a plethora of transport services including trains, buses and car sharing schemes. Their trains business includes several Train Operating Companies (TOCs) and wanted to analyse the full lifecycle of passenger journey data – from the moment a ticket is purchased to the time the passenger has arrived at his destination – into comprehensive, useful intelligence.

The Challenge

A vast majority of national rail tickets purchased in the UK is available to the TOCs through Lennon – the UK rail industry’s central ticketing database. To derive any useful intelligence, the ticket data from Lennon needs to be enriched with business rules and earnings data that are exclusive to the TOC.

This enriched data can also be used to meet some common objectives across the industry – improve services, compliance adherence and provide a basis to the bid teams.

Once a combined dataset emerges, each TOC should ideally be able to:

  • utilise this data to broaden business insights.
  • utilise this data for bid analysis
  • track adoption of government led initiatives (for example Smart Tickets).
  • provide flexible options and more choices
  • combine it with other operational datasets of other TOCs
  • vendors to assess compliance /performance.
  • In the absence of a combined data set, TOCs build several siloed reports which attempt to provide the basis for analysis. The following technical challenges prevent the TOCs from benefiting from a quick and reliable source of information:

  • Fragmented data models lead to complex data joins being included in the reporting
  • layer resulting in slow running reports.
  • Adding new business requirements becomes cumbersome since the data models
  • have limited scalability.
  • Reporting platforms not optimally used.
  • Disparate and unfriendly Dashboards
  • The Solution

    UXLI was approached for a scalable, flexible and performant solution that would allow business analysis in multiple dimensions. Our solution involved the use of multiple cloud-based technologies architected to work seamlessly. Technologies used for the solution included SnapLogic, AWS S3 Service, AWS RedShift, and BIRST.

    Solution Highlights

    Performance was improved in three ways:

  • By redesigning the data model in AWS Redshift and ensuring performant joins between facts and dimensions
  • By including all the complex joins within the AWS Redshift layer
  • This redesign also proved advantageous to BIRST’s Star Schema thereby providing another boost to performance
  • Scalability was addressed on two fronts:

  • New business contexts / attributes can be directly added to the AWS Redshift data model and processing only the impacted dimensions in BIRST/li>
  • A Networked BI layer was created by introducing the Space Networking concept. LOB wide data spaces for Transaction & Master Data provide a reusable foundation for a single source of truth for all future reporting initiatives allowed the creation of a LOB wide corporate reporting portal
  • Improved Usability

  • Self-service BI enabled by creating new Custom Subject Areas in BIRST, making it easy for the users to search for information and perform their analysis
  • Data-driven Interactive Dashboards designed to tell a story on a single page. Users can also filter and navigate to the details to gain further insights
  • Additional improvements included standardisation of the trains calendar, time classification into slots of 15, 30, and 45 minutes, and Peak & Off-peak windows has provided an edge to the train industry’s analytics
  • The Benefits

    benefits to this business case
  • Single source of truth for all sales and earnings data
  • Enables corporate “Citizen X” to explore and discover information in a self-service model
  • Enabled a value chain from sales to resolution
  • Track and measure adoption of government led initiatives (example smart tickets)
  • Evaluate performance across TOCs
  • Reduction in overhead costs – by optimising ticket office opening hours and balancing it with TVM capacity
  • BUSINESS CASES