Feature story from Belgium: COVID-19 Dashboard
Confronted with the unprecedented public spending, in response to the economic backlash of the COVID-19 health crisis, the Belgian Court of Audit launched a performance audit into all socio-economic support measures aimed at relieving the impact of lockdowns and other restricting regulations. The audit proved to be a comprehensive enterprise in federal Belgium, which urged the Court of Audit to innovate its way of working and to publish in a more interactive way.
The first challenge the audit team faced was to collect information about the various socio-economic policy measures. In a federal country like Belgium, this meant collecting information on 433 measures issued by eight different governments, redacted in three official languages. Some measures at the federal level were applicable to the whole territory, whereby some measures had a financial impact over 4 billion euros. At the same time, all 15 measures of the German-speaking community together had a combined financial impact of 10 million euros. The audit team had to come up with both a technical solution and a modus operandi to collect and take stock of this wide variety of information.
The second question the team had to tackle was how this information would be published. As with all performance audits, the end product would be a public report containing the findings, conclusions and recommendations, delivered to the respective parliaments and published on our website. Given the impact for the public budget, the amount of information collected and the public interest, the Court of Audit decided to publish its first interactive dashboard: https://covid19.courtofaudit.be (will be available from mid-December 2021).
In what follows we describe first the process of collecting information from the audited entities, performing quality checks and standardizations and post-processing information. Second, we describe the elaboration of the online dashboard.
Information and quality
A whole teamwork built from scratch
Producing an interactive dashboard in addition to the audit report has never been done before by the Court of Audit. Therefore, new procedures were created especially for this situation.
From an organizational point of view, the implementation of this project was transferred to a specific working group, consisting of a delegation of some performance auditors in charge of the report and a data analyst responsible for the dashboard.
Specific working group
The first mission of the working group was to define a modus operandi that would harmonize as much as possible the way of gathering and encoding all the audit information. With this in mind, the group:
- Determined metadata and data that should be included in the dashboard, which was sometimes challenging (common definitions of the target groups, the financial parameters, a special approach for loans and guarantees, and so on);
- Drew up a manual for all the auditors in charge of the project, explaining which data should be collected and how to encode them.
From a technical point of view, the working group proposed to use a SharepointOnline list, because of its adequacy with the needs of the project:
- from day one, all auditors worked simultaneously on the same program, which was safer than working on several separate files that would later have to be merged into one;
- modifications by several users at the same time were possible;
- the lists work as a relational database, whereby predefined categories could be selected by the users;
- the lists allow value validation, making sure that for instance amounts are encoded as numbers;
- the automatic versioning system allowed the traceability of every modification.
In order to make the tool user-friendly and accessible, the working group proposed a specific encoding interface (PowerApps) and provided trainings to the auditors on how to use it.
Focus on data quality
Throughout the whole process, data collection and review was realized on the basis of the following data quality criteria, implemented in practice through several actions:
Creating an online dashboard
The requirements of the online dashboard were comprehensive:
1. displaying information on the number of measures, the estimated cost, date of entry into force, etc.;
2. offering the possibility to explore the different categories of information that was collected in the first phase;
3. offering the possibility to filter the information;
4. enabling the user to both explore the full scope of all measures and the total financial impact as well as the details of an individual measure;
5. making it available in four different languages;
6. making it as interactive and visual as possible;
7. making a strong link with the published reports.
Development initiated using an RShiny server on premises. One of the main advantages of RShiny is that creating interactive graphs is relatively easy. With a few lines of R code you can create your first dashboard. The fact that lots of data analysts have experience with R is another bonus. RShiny takes care of the interaction, creating an interface and the server.
These advantages turned out to be the main disadvantages as well. First of all, the flexibility of RShiny is limited, in terms of layout of the dashboard as well as the available types of graphs and its interactivity. Moreover, the demand on the server infrastructure appeared relatively heavy. Each time a page opens or someone interacts with a graph, the server interprets the request, processes the R code to deliver the graphs and serves them to the user. In the case of high demand, RShiny might become slow.
Therefore, we looked for another solution and eventually ended up with D3.js. D3 is a javascript library aimed at data visualization. The benefit of using D3 is that it is extremely flexible: every aspect of a graph can be customized, made interactive and animated. This is however also its main disadvantage: as everything is customizable, every aspect of a graph needs to be defined separately. With RShiny you define what needs to be plotted, with D3, you have to define first all the axes, the scales, the labels, etc. The learning curve for D3 is therefore pretty steep. Another advantage is that all processing of data, graphs, and interactions happens client-side. The D3 javascript runs in the client browser and every graph is processed in the browser. This off course also means that D3.js is open data by design: as the graphs are built in the browser of the user, the data needs to be available to the user. Although the data is stored in the background of the browser processes, users with a technical background can find the data in a matter of seconds.
Both packages are open source and free. Therefore the only cost of the website was the development time and the hosting. The website is hosted in the cloud, via a managed solution. This offers flexibility, i.e. additional resources can be allocated when traffic is high and vice versa. We opted for a managed solution so that all aspects of backups, firewalls, security, etc. is managed by the hosting partner.
Monitoring
The use of an interactive dashboard is also a testcase to see whether more interactive ways of publishing attract more attention to our audit reports. Therefore, monitoring was set up to track how users interact with the website and how often a visit to the dashboard leads users to visit the website with the audit reports.
Conclusion
The COVID-19 crisis is challenging in many ways. However, it also offered opportunities to test new ways of interactive publishing for the Belgian Court of Audit. The results of this experiment with the COVID-19 audit and the analysis of its use, might offer interesting insights for our publishing strategy in the coming years.
By Koen Van der Bracht, Hélène Mastrodicasa and Laurent Werbrouck, performance auditors at the Belgian Court of Audit