Introduction

A large multi-police force collaboration identified the need for a Data Warehouse and Business Intelligence (BI) solution to bolster their serious crime unit’s operations to deliver insights around serious violence and knife crime, including better awareness of hotspots of crime and violent individuals. The data for this solution was to be sourced from Niche RMS and other internal systems. However, the project faced a significant setback when the primary developer left, leading to delays and a loss of critical knowledge. This departure created a knowledge gap that threatened the successful delivery of the solution.

To address these challenges, our consultant was engaged to take over the project, and asked to to navigate the complexities of the project, leveraging their expertise to fill the knowledge gap, and ultimately deliver the solution that the force required. This marked a new phase in the project, setting the foundations for the next phase of the project, a transition to Azure Synapse Analytics.

Project Scope

The project scope encompassed the following key deliverables:

• Delivery of an ETL Process: The project involved the delivery of an ETL (Extract, Transform, Load) process. This process extracted data from Niche RMS operational system, and other internal systems.
• Development of a Data Warehouse: A significant part of the project was the development of a data warehouse. This data warehouse stored and transformed the data extracted from various systems, providing a centralised repository for downstream consumption.
• Creation of a Power BI Model: The project also included the creation of a Power BI model. This model would serve as the reporting layer for serious crime data, enabling easy access to and analysis of this data.

These deliverables define the scope of the project, outlining the key tasks that would be undertaken to deliver a comprehensive data warehouse and BI solution.

Objectives

The primary objectives were as follows:

• Continue Development: Tasked with continuing the development of the data warehouse and BI solution, picking up where the previous developer left off.
• Improve Performance: A key objective was to enhance the performance of, both of the ETL solution, and of the presentation layer. Both of which had been identified as areas of concern.
• Improve Code Quality and Consistency: To improve the quality of deliveries as several changes were being revisited due to poor quality code.

Challenges and Solutions

The project faced several challenges, each of which was addressed with a specific solution:

Challenge: Low Code Quality

The remaining team were not adhering to coding standards, and multiple deliveries were being revisited due to poor quality code.

Solution: Implementing Code Reviews

A structured code review processes and coding standards was implemented. This ensured that all code adhered to high-quality standards, improving the overall quality of the code, and reducing the amount of revisions post delivery.

Challenge: Poor Performance of the Presentation Layer

The presentation layer was struggling to handle the complex data associated with serious crime, specifically in relation to the many to many relationships between various attributes in RMS.

Solution: Revision of Data Model

An optimised presentation layer was implemented by refining the data model to better support the nature of the data. This significantly improved the performance of the presentation layer whilst not compromising the integrity of the data.

Challenge: Poor Performance of the ETL

The ETL process was taking longer than expected to load data into the data warehouse, leading to delays in data availability and a reduced ability to rerun the data on demand.

Solution: Introduction of incremental loading pattern

The ETL process was optimised by implementing an incremental loading pattern. Due to the varying nature of the way in which data is stored in RMS, a combination of watermarks was identified to achieve this.

Challenge: Loss of Personnel Led to Loss of Knowledge

There was no handover between the departing developer and our incoming consultant which led to a loss of knowledge. In addition, very little documentation of requirements and business logic existed.

Solution: Workshops

Workshops were organised with business users to understand business processes and their requirements when compared to the existing solution. They also conducted a thorough review of the existing codebase to understand the solution architecture and design. Whilst maintaining the flow of the project, a knowledge transfer process was introduced, including comprehensive documentation and cross-training among team members to ensure continuity of knowledge with the internal team.

Challenge: Teams new to Power BI

The early phases of the project had focused on requirements and data integration, with little focus on the end user experience. One of the challenges faced was that the end users had limited familiarity with Power BI. Whilst the users were capable data analysts, they had not used Power BI before and user adoption had not been considered.

Solution: Training Sessions

Training sessions were conducted to help the end users understand the data model and how to utilise Power BI to meet their needs. This helped to bridge the gap between the technical solution and the end users, ensuring that the solution was effectively utilised.

Implementation

The project implementation was carried out with several key areas of focus:

• Establishing Code Repository in Azure DevOps: A code repository was established in Azure DevOps. This served as the central location for all code, ensuring easy access and management.
• Implementing Code Control Process: A code control process was implemented to ensure that all code was reviewed and approved before deployment. This helped maintain the quality and integrity of the code.
• Establishing Deployment Mechanism: A repeatable deployment process was defined to ensure consistency and quality. This process ensured that code was deployed in a controlled and predictable manner.
• Conducting Review Sessions with Stakeholders: Review sessions were conducted with stakeholders on a regular cadence, to ensure that the data model was aligned with business requirements. These sessions were used as a high value feedback loop, allowing for changes to be made iteratively, and evaluated early.
• Implementing Performance Improvements to the Presentation Layer: These improvements enhanced the user experience by making the application more responsive and efficient.
• User engagement sessions: As well as feedback sessions, out consultant also conducted user engagement sessions, to help the end users understand the data model and how to utilise Power BI to meet their needs.

Results and Evaluation

The project yielded several significant results:

• Timely Delivery: The solution was delivered within the originally agreed timeline. This demonstrated efficiency and adherence to project management best practices.
• Improved Performance: By optimising the ETL process, the data load process duration was reduced by 50%, allowing for quicker data availability and reruns.
• Positive Reception: The initial release of the solution was well received by stakeholders. This indicated that the solution met the expectations and requirements of the stakeholders.
• Faster Insights: The developed solution allowed quicker provision of information to drive activity, such as the planning of operations, and management of dangerous individuals

Future Prospects

The project had several future opportunities:

• Second phase underway: The second phase of the project involved transitioning to Azure Synapse Analytics as part of a wider enterprise wide data platform solution.
• Evolution of data model: With the data model established and in use, the business began to consider how to evolve the data model to increase data availability.
• Design for cross-team CI/CD processes: As demand grew and more teams were planning to become involved in development, the need to increase the robustness of code control and deployment processes was recognised. Designs were established for the progression of this area to ensure products can be managed effectively in the future.

Conclusion

Our consultant successfully established a robust Data Warehouse and Power BI reporting solution tailored to address serious crime within the force. Noteworthy accomplishments include implementing an industry-recognised code control process, aligning the data model with the underlying structure, fostering deeper stakeholder engagement, enhancing ETL performance, and elevating overall code quality. As a result, the force now benefits from streamlined data access, actionable insights, and improved decision-making capabilities.