HomeWorksPassenger Transport & TravelPassenger Information System

Passenger Information SystemA Data Platform for Passenger Transport Information & Analytics

pexels-shtefutsa-18004083

Problem Statement

The client provides transportation systems to government agencies in UK, Australia and Malaysia. They had designed a software that would have a scheduled time table of bus transport system and devices installed in buses, traffic signals and bus stops that would send real-time bus information. They had the following challenges:

  • Large Data Volume made the database server slow.
  • Non-Performant queries due to growing data.
  • No Analytics on KPIs due to lack of historical data reporting.
  • No Scalability due to no data architecture in-place.

Solution Design & Implementation

  1. Database Optimization
    • Optimized CPU, RAM, and IO performance by configuring database and server settings, upgrading Windows Server, and scaling MDF, LDF, and tempdb files.
    • Restructured schemas to reduce redundancy and improve query performance, resolving deadlocks and blocking issues.
    • Partitioned large tables, automated indexing jobs, and replaced ORM queries with native SQL stored procedures, reducing query times (e.g., from 2 minutes to 3 seconds).
  2. Scalable SaaS Database Architecture
    • Redesigned the system to follow a SaaS model, distributing data across multiple client-specific databases for better load balancing and management.
    • Introduced a master database to route connections dynamically and ensure scalability across over 120 active databases in the OLTP instance.
  3. Archival and Data Warehousing
    • Designed an archival strategy to offload historical data, reducing the primary database size and maintaining performance.
    • Built a SaaS-based data warehouse with separate warehouses for each client, enabling historical data analysis without burdening the transactional system.
    • Implemented high-performance ETLs to extract, clean, and transform OLTP data into staging, preparing it for analytics.
  4. OLAP and Reporting
    • Processed transformed data into SSAS multi-dimensional databases, leveraging MDX queries for fast data aggregation.
    • Embedded these queries into SSRS reports, delivering insightful dashboards with granular and summary-level analytics for stakeholders.
  5. Infrastructure Enhancements
    • Added SSDs for IO optimization and configured RAID for fault tolerance and write performance.
    • Defined a data retention policy for the database and data warehouse, balancing performance and hardware limitations.

Technology Stack

  • Microsoft SQL Server
  • Microsoft SQL Server Integration Services (SSIS)
  • Microsoft T-SQL
  • Microsoft SQL Server Reporting Services (SSRS)
  • Microsoft SQL Server Analysis Services (Multi-Dimensional)
  • MDX

Key Expertise

  • ETL (Extract, Transform & Load) Architecture & Development
  • SQL Performance Tuning
  • Data Warehousing & Modelling
  • Database Architecture & Development
  • Data Archival Strategy
  • Database Administration
  • OLAP Designing
  • Business Intelligence
  • Report Design & Development

Results

Here’s what we achieved with the new system:

  • 95% improvement in app query performance.
  • 10+ years of historical data in DW helped them do comprehensive reporting to analyze critical business metrics.
  • 4500+ active bus schedules managed in the system running across UK.
  • 400+ databases operationally online on the primary server.
  • 2TB+ data being managed & growing every day.
  • 800GB+ sized SSAS database managed for historical data analysis.
  • 600+ SSRS reports created for analyzing critical business metrics.
  • 350+ High-Performance SSIS ETL Packages created for extracting, transforming, loading & archiving data from OLTP to various target zones.
pexels-mart-production-7252581