Migrating and Integrating Data at the Independent Office for Police Conduct

At the 2024 Data Vault Conference, Alessia Pulieri (Business Intelligence Manager) shared the Independent Office for Police Conduct’s (IOPC) journey of migrating and integrating data using Data Vault. If you missed it, here’s your friendly recap—and a reminder to join our forum to keep the conversation going!

Where We Started: The Challenge

A year ago, the IOPC team was deep in the trenches:

• Refactoring business rules

• Building new information marts

• Integrating additional data sources

• Plumbing the EDW (Enterprise Data Warehouse)

• Managing legacy marts and “lift and shift” migrations

• Integrating a new Case Management System (CMS)

• Migrating data and integrating police data sources

The Goal: Seamless Migration and Integration

With just seven months to go-live, the team faced big questions:

• Will it work technically?

• Can we deliver on time?

• What’s the backup plan?

• Is the team big enough?

The mission: migrate data, integrate systems, and ensure business continuity for reporting and analytics.

The Team: Collaboration in Action

The project brought together a diverse team of BI managers, data engineers, and CMS delivery managers from both IOPC and Datavault. This collaborative approach was key to tackling the technical and organizational challenges of migration and integration.

Data Migration: Quality, Continuity, and Flexibility

• Code Conversion: Managed 80+ lookup tables to convert coding structures from old to new systems, including mapping one big lookup to several smaller ones.

• Data Quality: Monitored for data drift to ensure essential reporting data wasn’t lost.

• Data Mart Creation: Built mock-up target tables and enabled ad-hoc migration for seamless reporting.

• Reporting Continuity: Ensured new system coding didn’t disrupt existing reports.

• Seamless Transition: Exposed the Data Mart to the CMS, populated it with transformed data, and ensured users could see their data post-migration.

• Scale: Migrated 10,000+ cases and 20,000+ stages

Data Vault Integration: Automation and Agility

• Built new load, staging, and satellite tables

• Integrated hubs and links, including “Same As” links for system-generated IDs

• Developed and integrated over 60 new business rules

• Automated the creation of 250+ tables

• Enabled reporting from information marts to Power BI

• Maintained a contingency plan for CMS rollback if needed

Document Migration: 4.5 Million Files in 4 Months

• Migrated 4.5 million documents to SharePoint before CMS go-live

• Overcame SharePoint throttling with a dynamic migration strategy

• Developed utilities for rapid transfer and reconciliation

• Integrated document metadata into the EDW for analytics

• Used incremental loads for ongoing synchronization

• Leveraged metadata for document pattern analysis

Outcomes and Reflections

• Project Management: Careful tracking of tasks and decisions, with AI tools (like Copilot) suggested for future efficiency.

• Change Management: Controlled approach to frequent changes, with lineage tools supporting daily schema/ETL checks.

• Team Size: Optimal team size ensured effective communication and workload management.

• Business Improvement: New CMS features are now delivered faster and cheaper, more data is sourced into the EDW, and reporting/analytics capabilities have expanded.

Join the Data Community!

• Sign up to our forum: Share your experiences, ask questions, and connect with fellow data enthusiasts.

• Stay up to date: We host regular webinars, workshops, and meetups—don’t miss out!

• Shape the future: Your feedback and participation help us build better tools and resources for everyone.

Final Thoughts

The IOPC’s journey shows that with the right team, tools, and approach, even the most complex data migrations and integrations are possible. Ready to start your own data journey? Join the conversation, sign up for our next webinar, and let’s build the future of data together!