Creating a data analytics and reporting database solution for a health and fitness provider.

Mantalus partnered with a leading Australian health and lifestyle provider, Fitness Passport, to develop a Cloud-based solution to separate the analytics function from their production database. To achieve this, Mantalus implemented a new AWS Relational Database Service (RDS) that automatically refreshed from a snapshot of their production database each evening for reporting purposes.

Following the success of this initial project, Fitness Passport further engaged Mantalus to design and implement a Data Warehousing solution. This new solution allowed their analysts to compare point-in-time data against their legacy data sets.

Both projects presented similar challenges.

• Access and permission issues to deploy resources in their environment with the AWS least-privilege permissions – Mantalus worked with their 3rd party vendor across multiple time zones to resolve the access and permission issues.
• Data volume— the volume of Fitness Passport’s data is extensive and still growing. To reduce costs, Mantalus implemented a solution that compressed data up to 35%.

Fitness Passport is a health and fitness platform that gives corporate members and their families access to a wide range of fitness facilities near where they live and work. Through working with a network of employers and fitness professionals, they now provide access to over 2000 fitness facilities across Australia and New Zealand.

The reach and scale of the Fitness Passport footprint extends across multiple fitness providers, hundreds of employers and locations, and more than 200,000 members. With so many data sources and activities specific to the Fitness Passport platform, the existing data tables containing historical data could not complete all queries or produce the reporting required for optimising their services and activities.

Fitness Passport’s data analytics team wanted to report both on point-in-time data, as well as on trends in their historical data, requiring an optimal and cost-effective solution to keep this data and run reports against it.

Point In Time Data

Using the existing transactional database for reporting provided challenges and potential performance implications with an impact to operations. The process involved either running a report from Power BI directly into production or exporting data from production and loading it into Power BI via Excel. Mantalus identified discrepancies in the data and performance overhead on production during business hours as a result of this approach.

The Mantalus solution provides flexibility by removing manual intervention and creating a segregated reporting database that only updates outside of business hours. After a snapshot backup for the data is created, it uses this snapshot instead of engaging the production database to prevent detrimental effects on performance.

Historical Data Trend Reporting

To enable the Fitness Passport team to report on historical data, Mantalus architected a data warehousing solution to ensure that data was consolidated in a unified repository. This was achieved by implementing data pipelines that orchestrated Extract, Transform, and Load (ETL) processes using AWS Glue workflows. The solution leveraged AWS Glue crawlers for schema discovery and ETL PySpark jobs to perform data transformation, compression, and columnarisation. Additionally, Athena was employed as a query engine. This combination resulted in an optimised, industry-leading, cost-effective solution tailored to meet customer requirements. The entire solution was deployed via Infrastructure as Code (IaC), facilitating the client’s ability to extend the pipelines in the future.

Fitness Passport faced a challenge in running complex reporting against its production database, with three different approaches employed:

Power BI running in Azure, establishing a direct connection to production databases hosted in AWS.

• Extracting a subset of database tables from the production database and populating them into the UAT environment.
• Extracting data from the production database and importing it into Excel spreadsheets.
• However, these reporting solutions did not align with the organisation’s growth trajectory as they were not sustainable or scalable, resulting in database timeouts and frequent memory limitations. Furthermore, they were time-consuming manual processes, leading to performance issues in the production database.