The Client
With its roots in a campaign to give women the right to control their own fertility, 8 national family planning associations founded the International Planned Parenthood Federation (IPPF) in 1952.
Family planning as a human right challenged many social conventions but today the charity is a Federation of 132 Member Associations working in 164 countries, delivering over 223 million sexual and reproductive health services.
The Challenge
The IPPF Central Office wanted to create a single data repository for reporting and analytics. Intouch was commissioned to design a technical architecture for a corporate data warehouse and implement the data warehouse itself. Fed by a variety of enterprise business systems, the data warehouse needed to be flexible, manageable and scalable to support an increasing number of systems and reporting requirements. These source systems had no accompanying technical documentation.
Our Solution
Our solution focused on 3 areas
- understanding and documenting the individual source systems, their data structures and data access methods
- designing a data warehouse architecture that could accommodate these diverse systems
- developing and implementing the data warehouse
The systems included traditional databases, generic configurable cloud-based applications and Intranet/document management systems, eg SharePoint. Data was visible by direct access and via APIs. Our initial analysis task was to create data dictionaries for each of the source systems and develop a common data model for the aggregated data in the warehouse. Discovery workshops were held with system stakeholders to create this ‘new’ knowledge.
We developed an architecture alongside this to support the warehouse itself and the ETL processes required to host and aggregate the data within the warehouse. The ETL processes ranged from simple direct data transfer to complex metadata interpretation using a staging area.
Lastly we developed the data warehouse itself. A Microsoft SQL Server based solution augmented by PowerBI to create a business representation of the underlying aggregated data. Agreed automatic refresh mechanisms were incorporated into the data warehouse implementation.
The Benefits
- a fully documented understanding of the data within the key IPPF enterprise systems
- the ability to report and analyse data across a number of key business areas including outcomes, performance, learning, member accreditation and project management
- a supportable management information system using standard Microsoft products aligned with the IPPF’s IT strategy