dhis2-users team mailing list archive

["Wilson,Randy" <rwilson@xxxxxxx>]

Hi Mark,

This is exactly what we're doing in Rwanda.  We've set up one instance of DHIS-2 as our HMIS (for routine data entry by health facilities across the country) and a second instance as a national data warehouse/dashboard - more intended for program managers, implementing partners and donors.  Bob Jolliffe has been here helping us put together scripts to automatically synchronize sub-sets of the data between the two instances as new data is entered in the HMIS (I created a special dataset called datawarehouse in HMIS that gets pushed across).  We're also going to use the extended attributes for dataelements and indicators in the data warehouse instance to maintain our metadata dictionary with additional fields such as: primary data source, precise definition, intended use, staff responsible for collection, etc....

Bringing data in from other systems is still not easy - though now that many of our other data sources are web enabled it is practical.  As you note, you need to use the code field in each of the major data entities (dataelement, indicator, orgunit) that all systems share.  It is not difficult to create a view of the period table that can be used to translate periodids when importing data - for example here is the sql that gives you the year, month and quarter for all periods in your period table:

SELECT periodtype.name AS periodtype, period.periodid, period.startdate, period.enddate, date_part('Year'::text, period.startdate) AS periodyear, date_part('month'::text, period.startdate) AS periodmonth,
        CASE
            WHEN date_part('month'::text, period.startdate) = ANY (ARRAY[1, 2, 3]) THEN 1
            WHEN date_part('month'::text, period.startdate) = ANY (ARRAY[4, 5, 6]) THEN 2
            WHEN date_part('month'::text, period.startdate) = ANY (ARRAY[7, 8, 9]) THEN 3
            ELSE 4
        END AS periodquarter
   FROM period, periodtype
  WHERE period.periodtypeid = periodtype.periodtypeid;

Bob relies on DXF or similar XML import mechanisms - partly because of Postgres' requirement to assign a unique id to each record across all tables whose current value is maintained in the hibernate_sequence object and it is definitely the safest way to go.   I've found it is also relatively easy to do with a combination of Excel and a visual query designer like Access - linked to the Postgres tables - as long as I check and increment the current value before and after imports (and nobody else is working with the database)!  Of course it depends upon how similar in structure your source data is to DHIS - otherwise you may need to do multiple transformations of the data before hand.  If you are using a lot of category combinations (age/gender, etc...) as opposed to just the default categorycombo, it is also more difficult as well, because they also need to be mapped to the categorycomboids.

A drag and drop interface would be great... but we're far from it now.

Randy



From: dhis2-users-bounces+rwilson=msh.org@xxxxxxxxxxxxxxxxxxx [mailto:dhis2-users-bounces+rwilson=msh.org@xxxxxxxxxxxxxxxxxxx] On Behalf Of Mark Spohr
Sent: Saturday, March 17, 2012 1:25 AM
To: dhis2-users@xxxxxxxxxxxxxxxxxxx
Subject: [Dhis2-users] Importing data from external system?

DHIS seems to do a good job of importing data from another DHIS system.
However, I would like to use the DHIS as a data warehouse to suck up data from other systems in the country (vertical programs).
I've spent some time looking at the xml format and it looks like it could be emulated by another system but will need to have the id codes for periods, facilities, data elements, etc.  so it will be a bit tedious.
Has anyone done work on this problem.?.. I'm thinking of some tool to map the external data to the DHIS dataset which would allow a "drag and drop" match.


Regards,
Mark

--
Mark Spohr, MD