Video instructions
Have a look at the video that explains how to set up a new pivot table
in Excel using data from the DHIS 2 database.
Download and install an odbc connector for your database server
Excel will connect to the DHIS 2 database using an odbc connection and if you have not already, you need to install an odbc driver to enable this.
PostgreSQL
Download here
MySQL
Download here
Set up the odbc connection:
Go to Control Panel->Administrative tools->Data Sources (ODBC) and open the tab System DSN, and the 'Add'.
Select your newly installed driver and then provide connection name and fill in the required information:
HINT: Open your hibernate.properties file to look at the connection details for your dhis2 database
PostgreSQL
Data Source: any name, e.g. dhis2_znz
Database: <name of your database>
Server: localhost (if a local database, but can also connect to remote databases by defining the url here)
User name: <your database user name>
Password: <your database password>
Port: Leave the default or follow what it says in hibernate.properties if different from the default (5432)
SSL mode: disable (you may look into other options if you are connecting to a remote database)
MySQL
Data Source Name: any name, e.g. dhis2_znz
Server: localhost (if a local database, but can also connect to remote databases by defining the url here)
User: <your database user name>
Password: <your database password>
Database: Select your database from the drop-down list
Set up database views in your dhis 2 database to make readable data available to Excel
Have a look at the views attached to this page (go to Attachments at the top of this page, just under the page heading), they should be general enough to use with all databases (except for some date formatting).
Some explanation on the data mart and related tables
We recommend that you set up both raw data (based on data elements) and indicator views for each of the orgunit levels you want to analyse. There are two datamart tables, one for raw data linked to data elements and one for indicator values linked to indicators, they are called aggregateddatavalue and aggregatedindicatorvalue respectively. These tables contain only IDs referencing the key tables in the database; data element or indicator, organisationunit, and period and all these need to be joined with your datamart table to provide Excel and the users with readable names. To make useful pivot tables you should also link the value to the whole hierarchy belonging to the organisationunit holding the value, e.g. for a value registered by the facility 'Kizimkazi PHCU' should also join in its orgunit parent 'South District', its grandparent 'Unguja Zone' and its grandgrandparent 'Zanzibar MOHSW'. This hierarchy is used by Excel when zooming in and out of table.
Data element and Indicator groups
The tables for data elements are dataelementgroup and dataelementgroupmembers, join them together and join in the dataelement table
The tables for indicators are indicatorgroup and indicatorgroupmembers, join them together and join in the indicator table
Orgunit groups
orgunitgroup and orgunitgroupmembers
Orgunit groupsets
orgunitgroupset, orgunitgroupsetmembers, join with orgunitgroup and then orgunit and select one groupset per column in your output table
the table orgunitgroupsetstructure can help this joining process
See attachments for some sample queries.
Set up your Excel pivot table using the wizard in Excel
The best way to learn how to make a new pivot table is to open the PivotTable Wizard on on of the existing tables. Select a table and then right-click on it to get to the wizard. In the wizard go <back> and then to <Get data> to look/modify the database connection and query. The <layout> will show you the design of the table, overview of the available fields and their initial positions.
To open the queries you need to have MS Query installed. It comes with MS Office, but is normally not part of the default installation. Run the setup again and select it to add it to your MS Office installation.
Important issues:
- One table should only contain data for one period type. E.g only monthly or only quarterly data and not a mix of both types. The example tables display monthly data.
