Test, Trace, Protect
ISD supported the interim TTP solution through provision of spreadsheet lists to Local Authority tracing teams, based on results data from LIMS for Welsh labs, together with other results data from the Lighthouse Laboratory Network.
ISD subsequently spent a considerable amount of time understanding the complex data structures which sit below the live TTP system, which have been developed using the Microsoft CRM. This contains over a hundred database tables and several thousands of columns, and so in order to simplify this for analysis and reporting, this data has been distilled from into three easy-to-use summary database views, located in an Azure SQL database on the Cloud, which is updated close to real time, as information is added to the TTP system.
These views are also extracted to the DHCW data warehouse every 6 hours, making them available to health boards, Public Health Wales and other NHS Wales organisations, in an environment within which records can be linked to other sources of health.
Uniquely for TTP data, DHCW, PHW and the Local Authorities are all joint data controllers, and so data has started to be made available to local authorities via the Azure SQL database, and Cardiff local authority staff have actually been supporting the development of Power BI reports using this mechanism.
In relation to reporting, ISD (with support from Cardiff LA and Betsi Cadwaladr UHB) have made a suite of dashboards available to NHS and Local Authority staff via two Power BI apps. The first contains high-level statistics about numbers of index and contact cases, the numbers (and percentages) of those which have been followed up successfully, and the numbers who have been followed up within 24 and 48 hours. The second app is more restricted in terms of access, allowing operational users and public health surveillance teams to drill down into individual cases, and identify hotspots or so-called super-spreaders.
The reports contained within the two PowerBI apps are updated automatically every 2 hours, using ISD’s newly installed PowerBI gateway. Access is managed via a series of active directory groups, with a mechanism having been developed to add non-NHS users to active directory in order for these apps to be accessed securely by nominated local authority and WG staff. Due to security restrictions of the WG side, there remain issues in terms of access for WG staff, but staff from all 22 local authorities are able to access these reports successfully.
ISD continue to work with WG, PHW and LA to add to the range of reports available through the two PowerBI apps.
