This application was for senior managers wanting to monitor the current global status of major incidents… ones significantly impacting a global financial company.
It has been my experience that when working with executives, they generally was as little fuss as possible. No user id’s no passwords, at most a URL bookmarked.
So this effort was a hand-coded application that ran some significantly complex SQL to aggregate outages broken down by region and company organization. The data is odd in that one outage can actually impact different regions and thus be multi-counted. However, the need was to show raw outages and impacts in the same dashboard.
The dashboard was also interactive and they could add time filters, change the measures to raw, Month to date, year over year etc. All this was hand coded with .Net. One trick I did was to pull two years of data and apply the filters locally using code to avoid requerying the database.
The “complex” SQL contained windowed aggregates.
The data was ETL’ed hourly and this application was aware of that. For performance, I added logic to rerun the 20-30 second query only hourly and then to cache the data on the webserver so all subsequently presentations of the data would be nearly instantaneous. The first user per hour would (unknowingly) requery the data.
I used C#.NET, T-SQL, server-side caching, HTML, CSS and more to render this custom tool.