When the Municipal Demarcation Board (MDB) carried out the 2011 Municipal Capacity Assessment, webfresh was sub-contracted to PDG to develop a web-based analysis and reporting system to manage the data.
The primary dataset was collected using an online survey. This data was made available to us in a proprietary format, from which it was loaded into a more ‘normalised’ data structure. It was then complemented with data from Census, Community Survey, Local Government Budget & Expenditure data, and Blue Drop data.
The data structure we used was essentially a star-schema, a data structure common to data warehousing projects, and suitable to these sort of reporting systems.
The website that we developed included a number of charts and graphs for data analysis, and three different PDF reports: a data verification report, a municipal comparison report, and a district-level report card. Each municipality was categorised into one of seven categories, and their results were compared to the average of that category of municipality. The produced a more realistic comparison for similar municipalities, rather than comparing (for example) a metro with a rural municipality.
Part of the complexity in the system we developed was the inclusion of formulae in the indicators; these formulae were made up of the source data points, described above. Running the calculations for an indicator would process the formulae, in much the same way that you would do in an Excel spreadsheet, using Excel’s formula functions.