A CRM rebuild using the KI Datamart system
30/01/2020

KEY Intelligence has just completed an end-to-end project to transform the reporting in an East London borough.

The client had initially asked KEY Intelligence simply to replace SSRS reports with Power BI dashboards, but it was soon evident to the KI Team that the data being called upon to populate the new dashboards was poorly implemented and not fit for purpose, after recent changes to the data warehouse made by a third-party contractor.

To facilitate the necessary data extraction repairs, KI was asked to document the existing data warehouse and identify the sources of the issues, but the scale and complexity of the necessary fixes quickly made clear to both sides that the most cost-effective way forward was to scrap the existing Data Warehouse and start again from scratch, implementing a full KI DataMart Solution (KIDS) to feed the Power BI presentation layer.

With what was now a clean slate between the Borough’s CMS and the required reporting, KI began working from both ends: backwards from desired Dashboards to build the Datasets that would feed into them (populated tables) and forwards, extracting data from the Azeus CMS database to populate KI Staging Tables with clean, labelled and pre-calculated data, already with the reporting end in mind.

With the Staging Tables filled, all that remained was a straightforward ETL process to load the reporting Datasets and complete the end-to-end process.

As always, KI left the Client with a clear and precise document set, so that maintenance and changes to required reporting can be quickly achieved, and, as the KI Staging Tables cover all current and foreseen reporting data requirements, a lower order of SQL skill is needed than if each new process required a fresh extraction from the CMS.

From a complex collection of reports, developed on the older architecture, and provisioned from a legacy system with some unresolved data quality issues, the Borough now has the reporting it needs, when it needs it, clearly presented as intuitive and robust dashboards in Power BI that can be linked in the future into a wider corporate data warehouse.

KI delivered the Project within tight time and budget constraints, and without a single day's loss of reporting.

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