Executive Summary
A multi-police force collaboration required a data warehouse and business intelligence solution to support their serious crime unit’s operations, providing insights into serious violence and knife crime. Following the unexpected departure of the primary developer, the project faced significant delays and knowledge gaps. Our consultant was engaged to recover the initiative, ultimately delivering an optimised data warehouse and Power BI solution that reduced ETL processing time by 50% and established a foundation for future expansion to Azure Synapse Analytics.
Challenge
The serious crime unit required better awareness of crime hotspots and violent individuals to support operational planning and public safety. Data was sourced from Niche RMS and other internal systems, but the project to deliver this capability faced multiple challenges that threatened successful delivery.
Knowledge Gap
The departure of the primary developer created a critical knowledge gap. There was no handover, and minimal documentation of requirements and business logic existed. This threatened the project’s continuity and the team’s ability to maintain momentum.
Performance Issues
The existing ETL process was taking longer than expected to load data into the data warehouse, delaying data availability and limiting the ability to rerun processes on demand. The presentation layer struggled to handle complex data relationships, particularly the many-to-many relationships inherent in RMS data.
Code Quality
The remaining team members were not adhering to coding standards, resulting in multiple deliveries being revisited due to poor quality code. This created a cycle of rework that compounded the existing delays.
User Adoption
Early project phases had focused on requirements and data integration with little attention to the end user experience. The data analysts who would use the solution had limited familiarity with Power BI, and user adoption had not been considered.
Solution
Our consultant took a structured approach to project recovery, focusing on knowledge transfer, process improvement, and stakeholder engagement.
Knowledge Recovery
Workshops were organised with business users to understand business processes and their requirements. A thorough review of the existing codebase established understanding of the solution architecture and design. A knowledge transfer process was introduced, including comprehensive documentation and cross-training among team members.
Scope and Objectives
The project scope encompassed three key deliverables:
- ETL Process: Extraction of data from Niche RMS and other internal systems
- Data Warehouse: A centralised repository for storing and transforming extracted data
- Power BI Model: A reporting layer for serious crime data enabling analysis and insights
The primary objectives were to continue development from where the previous developer left off, enhance performance of both the ETL solution and presentation layer, and improve code quality and consistency.
Technical Implementation
The implementation focused on establishing solid development practices whilst addressing performance concerns.
Development Practices
- Code Repository: A code repository was established in Azure DevOps as the central location for all code
- Code Control Process: Structured code review processes and coding standards were implemented to ensure all code was reviewed and approved before deployment
- Deployment Mechanism: A repeatable deployment process was defined to ensure consistency and controlled releases
Performance Optimisation
The ETL process was optimised through an incremental loading pattern. Due to the varying nature of data storage in RMS, a combination of watermarks was identified to achieve efficient incremental loads rather than full refreshes.
The presentation layer was improved by refining the data model to better support the complex data relationships. This significantly improved performance whilst maintaining data integrity.
Stakeholder Engagement
Review sessions were conducted with stakeholders on a regular cadence to ensure the data model aligned with business requirements. These sessions provided a high-value feedback loop, enabling iterative changes to be evaluated early.
Training sessions were conducted to help end users understand the data model and how to utilise Power BI effectively. This bridged the gap between the technical solution and the analysts who would use it.
Results and Benefits
The project delivered measurable improvements across multiple dimensions.
Key Benefits
- Timely Delivery: The solution was delivered within the originally agreed timeline despite the earlier setbacks
- Performance Improvement: ETL processing time was reduced by 50%, enabling quicker data availability and on-demand reruns
- Positive Reception: The initial release was well received by stakeholders, meeting their expectations and requirements
- Faster Insights: The solution enabled quicker provision of information to drive operational activity, including planning of operations and management of dangerous individuals
Foundation for Growth
The project established foundations for future development:
- Azure Synapse Analytics: The second phase involved transitioning to Azure Synapse Analytics as part of a wider enterprise data platform
- Data Model Evolution: With the model established and in use, the business began considering how to evolve it to increase data availability
- Cross-team CI/CD: As demand grew and more teams planned to become involved, designs were established for stronger code control and deployment processes
Lessons Learned
- Knowledge Documentation: The impact of the primary developer’s departure reinforced the critical importance of comprehensive documentation and shared knowledge across the team
- Early User Engagement: Investing in user adoption from the outset, rather than treating it as an afterthought, accelerates the realisation of business value
- Incremental Loading Design: Designing ETL processes around incremental loading patterns from the start avoids the need for costly rework as data volumes grow
- Code Quality as a Practice: Establishing coding standards and review processes early prevents the accumulation of technical debt that compounds delivery delays
Wrapping Up
This engagement demonstrated how structured intervention can recover a troubled project whilst establishing sustainable practices for ongoing development. By focusing on knowledge transfer, process improvement, and stakeholder engagement alongside technical delivery, the project was brought back on track.
The multi-police force collaboration now benefits from streamlined data access, actionable insights into serious crime patterns, and improved decision-making capabilities. The reduction in ETL processing time and the optimised presentation layer ensure that analysts can access current data when they need it.
The success of this phase has led directly to the second phase of development, transitioning to Azure Synapse Analytics as part of a broader enterprise data platform strategy. This represents the kind of sustainable growth that comes from combining technical excellence with effective knowledge transfer and team enablement.
