Breaking the shackles to fix the reports

It should not really be surprising that, over time, a Council’s reporting structure can become tangled and inconsistent. Over several years, staff and objectives naturally change, and a system – one that has been repeatedly extended and modified by a succession of developers who may not have been involved from the outset, often faced with incomplete documentation and duplicated labels, as they try to meet the needs of a changing cast of managers who are themselves looking to understand an ever-shifting list of performance measures and government reporting obligations – can start to creak. This is where a well documented, highly configurable Datamart can really help.

One London Borough recently found itself in precisely this situation, spending hours each week manipulating and reconciling reports that were no longer representative of the known facts, sometimes even giving two distinct answers to what should have been the same question. So when a neighbouring Borough expressed an interest in working more closely and sharing resources and information, it was clear that major improvements and repairs would be needed before the two systems could be aligned and they called in Key Intelligence to make the necessary changes.

Rather than waste valuable time and the Council’s money trying to disentangle and repair such a complex system, Key Intelligence proposed, and was given the go-ahead for, a radical new approach: to build a completely new Datamart, tailored precisely to the current reporting needs of the Council, and entirely independent of everything that had gone before and of the Case Management System vendor’s own in-built datamarts.

Working with the consumers of the planned reports to establish their needs and understand their unique set of business rules, Key Intelligence drew from the source data in the CMS to populate 13 tables with pre-processed and defined data, calculations and filters from which the Council’s Performance team can extract whatever their changing requirements dictate without needing ever to go ‘behind’ the Key Intelligence Datamart. SQL scripting reports have been simplified as required information is available from this handful of tables rather than extensive back-end structures or complex CRM Vendor supplied datamarts.

With all reports being drawn from a single Datamart, with all business rules, calculations and filters captured ‘Behind the scenes’, managers in all areas and at all levels in the Council are seeing the same consistent, reliable information, at the level of detail they need.

The need for post-report ‘fixes’ has diminished significantly, leaving performance staff free for other, often more important, work. The need for SQL skills in building reports has been removed, allowing more flexibility in staffing and faster creation of new reports.

With the training provided, robust documentation, and Key Intelligence on hand for ongoing support if needed, the Council can expect years of reliable reporting without the need for any further major development work. New data objects, even from different, non-CMS, sources, can be added or removed and calculations changed with a straightforward process and following simple rules.

And because the Datamart is independent of the CMS, even that can be changed or upgraded in the future, without the need to re-script every report, as, once the Datamart is redirected to the new data sources, all like-for-like reports will work ‘straight out of the box’.

Key Intelligence has now begun work on creating an equivalent new Datamart in the neighbouring Borough, to provide the same clean reporting reliability and consistency across the border and so ensure that the two Councils speak the same language when they share and compare information.

By using, you agree to our use of cookies to enhance your experience.