TfGM Move More Analytics Workloads to the Cloud

The ‘Under the Stairs’ project was a data migration project, moving 50+ data feeds in 9 different file formats and 6 different third party APIs into existing cloud data warehouses so that the client could create broader and deeper analytics about customer journeys.

The Challenge

Following the successful implementation of Transport for Greater Manchester’s (TfGM) Enterprise Insights and Analytics (I&A) platform to support the rollout of contactless ticketing across the region, TfGM wished to take further advantage of this strategic capability by moving broader workloads onto the platform.

The local client name of the on-premise server ‘Under The Stairs’, that was being migrated, was adopted as the project name. The key requirement was to move a significant on-premise workload focused towards the strategic analysis of passenger journey analytics.

Why Amazon Web Services

TfGM adopted Amazon Web Services (AWS) because it had all the services and providers needed to do the initial tasks as well as any envisioned for the solution in the future. They wanted their environments to be fully scripted from end to end to allow repeatable patterns and so Terraform was used not only to create all the native AWS services but was extended to create all database objects too.

To enable maximum automation, as with the earlier contactless project, flat file imports would have the row counts, column counts and data types verified using AWS Lambda functions (failures would automatically raise help desk tickets with the supplier without any ETL jobs commencing on the TfGM side). Once verified as before, the ETL environment would be initiated using SQS; this orchestrated the instantiation of Matillion as well as other tasks.

Outside of the native AWS tools, other AWS partners would be used including Matillion for ETL, Snowflake (which is hosted on AWS) for the data warehouse and Tableau Online (which is also hosted on AWS) for reporting and visualisation.

The Benefits

TfGM adopted AWS because it had all the services and providers needed to do the initial tasks as well as any envisioned for the solution in the future. This was proven to be the case when TfGM sought to migrate a significant number of strategic planning analytical workloads from their on-premise SQL server to the new powered by AWS Enterprise solution.

TfGM were able to use Snowflake and Tableau to give them to new insights into travel patterns and behaviors for TfGM to adapt the network and add improvements to contactless services. These new insights came first from the ability to store and perform analysis on any volume of data required in the new EDW and secondly from the new visualisation capability.