Transcript Slide 1

South Africa Data
Warehouse for
PEPFAR
Presented by:
Michael Ogawa
Khulisa Management Services
[email protected]
011-447-6464
Context

PEPFAR South Africa




USG-funded
Over 100 implementing
partners, many with
multiple sites
Need to monitor up to 80
indicators twice a year
2008 Targets for SA



Prevent 1.75m HIV
infections
Treat 500,000 AIDS
patients
Care for 2.5m OVCs &
HIV infected individuals
Monitoring and Evaluation

M&E provides strategic information to
USG and implementing partners to:
 Plan
and monitor programs;
 Document and report on progress; and
 Ensure that resources are used effectively
and efficiently
Challenges

SA PEPFAR program has a large number of partners
who need to report and plan




Reporting burden on USG
MS Word and MS Excel templates were historically used
for capturing reports and annual plans
Office of The US Global AIDS Coordinator (OGAC)
regularly makes changes to the PEPFAR indicator model
Access to the Internet varies among partners
SHARING Website

Secure web portal for
accessing:






Web-based forms for
reporting and planning
templates
Indicator definition guides
Data extracts (CSV files)
Adjustment tool to
eliminate double counts
User administration
Budget/target grids
Solutions

Web-based data warehouse designed using Open
Source software



Forms are database generated



RTF downloads (paper forms)
HTML interface (web forms)
“Live” extracts for reporting indicators


Only Internet browser software required
Data warehouse can be accessed from anywhere in the world
(some partners have parent organizations outside of SA)
Reduced reporting burden for USG
Content Management System


Indicator definitions
Track changes for planning
Solutions

Speeding up web page loading
 Web
page data are compressed before upload
 Data for a web page is separated so that formatting
information is not repeated


One file contains formatting information
Second file contains data that needs to be formatted
 Asynchronous saving of data
 Users can carry on capturing data while the save for a
previous page is being processed
 Users do not have to wait for new form pages to load
Data Warehouse Approach

On Line Analytical Processing (OLAP)
 Measures


Dimensions


Numeric facts (e.g. No. of condoms distributed)
Categories under which measures can be grouped (e.g. Gender,
age, etc.)
OLAP Cubes



OLAP cubes allow users to quickly retrieve queries and reports on
summary data for the organization
The summary information being retrieved typically aggregates
detailed transactions at a lower level
The aggregated totals stored in the OLAP cube can represent
thousands of rows of transactions within the data source from which
it originates
Data Quality Improvements







Input data is checked on client side (cannot enter nonnumeric data into a numeric field)
Full transactional audit history
Data must be verified before it is submitted
Forms locked after submission
Forms can be invalidated for changes by partners
Double count adjustment tool adjusts aggregate total but
does not adjust partner data
System designed to capture down to site level (source
data) and at monthly periods
Future Features: Pivot Table/Cube
Viewer
Future Features: Online Mapping
Future Features

Electronic transfer of data
 From
partner systems to data warehouse
 From data warehouse to USG system in Washington
and SA health information systems



Offline data capture
Customizable indicators/dimensions for partners
Workflow
Lessons Learned

If time is available, understand your user base


Data warehouse needs to move beyond its current role
as a one-way conduit for USG results reporting


Web capturing interface = paper form
Need to improve data utilization tools for complex, analyses and
basic, menu-driven reports, graphs and maps
Need more features (RSS feeds, warehouse more health
data sources, etc.) to bring users to the website on a
regular basis
Questions and Answers