Engineering a data hub and live dashboard for an entire city.

Role: Data Engineer
Smart Cities
Christchurch City Council

Achievements:

  • Research and Development of an operational dashboard for realtime information across the whole of council.

The Mayor of Christchurch wanted a way for City Councilors and the executive to understand everything that was happening in the city. I researched the Chicago Smart Grid (built in partnership with the University of Chicago High-Performance Computing Center) as an approach that did just that, and did a proof-of-concept on the Microsoft SQLBI stack.

Responsibilities:

  • SQL Database design and development (tables, views, functions, and stored procedures)
  • Data pipeline design and development for ingesting:
  • real-time external APIs (customer counts, river flows, cycle counters)
  • big data extracts (Strava, Open Street Map)
  • external graph databases (Facebook graph API)
  • internal datasets and databases
  • Data modelling
  • SSIS design and development
  • SSAS tabular model design and development for near realtime analytics
  • SSRS reports development and design
  • 3D spatial modelling and display
  • Power BI dashboard design and development for near real-time analytics

Technology:

  • Leaflet
  • Microsoft SQL Server database engine (MS SQL), Integration Services (SSIS), Analysis Services (SSAS), Report Server and Power BI Service (Power BI).

Outcomes:

  • Data hub & live dashboard presented at the first and second Smart Cities expos.
  • Achieved near-real time performance by leveraging metadata-driven development, and parallel processing through SSIS orchestration.
  • Versions 1 and 2 presented in public at the first and second Smart Cities expos.

2017

Updated: