GIS Analytics

Overview

My role: Design and development.

Challenge: Rapidly develop custom analytics for a client of a Fortune 500 GIS provider.

Solution: Built an Excel-based solution in VBA to query the provider API, cache the data and generate the analytics on-demand

Impact: Delivered analytics fast by choosing the right constrained tool: Excel/VBA over Google Sheets for performance and capacity.

Technologies: Microsoft Excel, Visual Basic for Applications (VBA)

Rapid Analytics Under Constraints

Even the best analytics portals might miss just that specific view the client really needs.

And as the old saying goes, "all data ends up in Excel".

The requirements originally envisioned a Google Sheets + Google Apps Script (GAS) solution, but initial testing during systems design showed it wasn't viable due to performance and capacity constraints.

Excel is primarily limited by the computer it runs on and, for better or worse, VBA is a very stable language that has remained largely unchanged for the past two decades. In addition, Excel is available on pretty much all corporate computers, so it became the ideal platform to deliver the required analytics.

The delivered tool would locally cache the relevant data to minimise the number of queries to the GIS API. This also cut the update time from 20-30 minutes to a few seconds of incremental updates, as the total amount of data used was quite substantial.

The analytics could then be run quickly using the cached data.