When Durham County Council chose to migrate its Social Care case records to a new system, it aimed to take the opportunity that would present to bring together data from multiple sources to identify deeper patterns and trends and better inform its decision-makers.
With the Council itself moving concurrently to PowerBI across all services, the Social Care Performance Team looked to KEY Intelligence to undertake this key project, on the strength of its experience with CRM system migrations and data warehouse builds, and established reputation for PowerBI visualisations of Social Care data.
To achieve the aim of providing clear information combined from multiple sources, it was agreed that KI would implement its KIDS2 datamart system ready for ‘Go Live’ of the new Azeus records system.
KIDS 2 comprises a database of staging tables, processing scripts and several output datamarts, each logically tailored to provide the datasets required for either Statutory Returns or KPI reports. Raw data can be drawn from a variety of sources, including databases, spreadsheets, and the internet, and the presented outputs can be extracted by any appropriate tool, including, as in this instance, SSRS and PowerBI.
Several Authorities have already engaged with KI to implement KIDS for them, as it frees their reporting from the ‘one size fits all’ generic data structures of the CRM, allowing them fully and freely to tailor their data reporting and presentation to their own processes and workflows.
In this instance, the client’s CRM system was housed in the cloud, with no development access for the Council’s Performance staff, and the internal team was still trialling options for how best to set up and use the new system, meaning the data structures were unfixed, and any reporting would need to be developed against data that was, effectively, a moving target.
The lack of access was resolved by copying the Azeus transactional tables directly onto the Council’s internal servers, copying and refreshing from the host Oracle database to SQL server, and populating the KIDS staging tables from there. The three-layered structure of KIDS also allowed desired output datamarts to be developed without prior knowledge of final data locations, effectively working backwards towards the data sources as they became more fixed. When a process or a definition, a label or a location, is changed, KIDS can be simply ‘re-mapped’ and all report outputs are updated at a stroke, without piecemeal re-writes and unavoidable human error.
By engaging KI and deploying KIDS2, even in advance of ‘Go Live’ and with the data picture still developing, the council already has datasets for more than 100 report output tables built and ready.
"The early PowerBI visualisations KI has developed are allowing our users to see and understand decision-appropriate information in depth and detail not previously thought possible" said Dave Knighton, Durham's Data and Systems Team Manager for Adults and Health, “and everyone involved here is agreed that the working relationship with KI has been the easiest we’ve ever had with an IT service supplier”.
Picture: by Mattbuck. Used under Licence. https://creativecommons.org/licenses/by-sa/2.0/legalcode
