Long, multi-page dashboard required for 4 SLAs, 10 KPIs and 46 measures
Requirement
The client, a global investment bank required a solution to measure and report on a subset of 40 universal Key Performance Indicators (KPIs) for IT providers.
Reports needed drill-down capabilities so the user could interrogate the data themselves, so avoiding the need for any ad hoc reporting.
Challenges
Reports from the IT Service Management (ITSM) system could not be tailored to calculate the SLAs and KPIs, and had to be supplemented with journal history extracts. Such large data volumes threatened to slow the performance of a dynamic dashboard.
Solution
The solution was a custom built Excel solution with 13 VBA procedures. Data was extracted from the ITSM system – over 20,000 data items per month – into a spreadsheet where it was processed and supplemented with other information, then loaded into the dashboard spreadsheet.
The dashboard was made up of 4 SLAs, 14 KPIs and 46 measures with 19 graphs and 23 filter fields for drill-down. Monthly history data was maintained to enable time series graphs as well as snapshot measures.
Design Highlights
The solution used VBA to import a large amount of data and to perform the complicated calculations needed to work out SLA and KPI results. Functions used include: array formulas, scroll bar, conditional formatting, dependent drop downs and data validation.
It also featured an innovative ranking method used to manage the graphs and axis labels, and to create a dynamic, scrollable list of filtered and sorted items.
This example demonstrates some advanced Excel techniques to produce a dynamic dashboard for a very large number of measures and metrics.