Hi,
I’ve done some work on preparing a set of pivot tables for the new DHIS2 demo database (H2) and here is a howto and everything for download. I will merge this into the user manual at some point, but first we need to find a more user-friendly approach (e.g. for the H2 server).
First the downloads:
-new demo database in H2 (db login is dhis/dhis, DHIS2 login is admin/district):
http://folk.uio.no/olati/filer/demo_21092010.h2.7z (10 mb)
-excel file with pivots connecting to the demo db in H2:
http://folk.uio.no/olati/filer/h2_demo_pivots.7z
-sql views used by the pivot tables:
http://folk.uio.no/olati/filer/PivotSourceViews_H2.sql
And the the HOWTO:
Brief:
-
Install the postgres odbc driver
-
Start the H2 server
-
Set up a new data source (odbc connection), and test it!
-
Make sure you have created/generated the necessary resource tables and views in DHIS2, via DHIS2 GUI (Data Admin->Sql views) is recommended.
- Shut down DHIS2, yes this seems to be needed
-
Open Excel and create a new data connection to an external data source using MS Query and ODBC
-
Build a pivot table based on the fields returned from the new connection
-
Repeat 5 and 6 until you have all the pivots you need
More detailed:
Download,install and set up the ODBC driver for postgres/h2:
http://www.h2database.com/html/advanced.html#odbc_driver
I have used the following string for database:
d:/tools/dhis2/database/demo;AUTO_SERVER=TRUE
NOTE: the password cannot be empty! (not even ‘’)
Passwords are set either on creation of the database (in hibernate.properties) or using ALTER USER SET PASSWORD ‘<NEW_PASSWORD>’ in the H2 console (when connected to the database).
The H2 server
The H2 server needs to be running (and have access to your database file) before you can connect using odbc. The server is inside the h2.jar and can be started using the command:
$java -cp h2*.jar org.h2.tools.Server -baseDir ~
Then both DHIS2 and ODBC can connect to the h2 database using the absolute path to the h2 db file and the AUTO_SERVER=TRUE option. For some reason ODBC only works when no one else is connected, so you need to shut down DHIS2 before doing the odbc connection (creating or refreshing pivots). DHIS2 can start the same way as before, I did not change anything in hibernate.properties after running H2 server. Ideally the user should not be bothered with this server at all, and e.g. the DHIS2 live package should start and stop the H2 server automatically. If we control how the Excel file is opened, e.g. linking to it from a DHIS2 folder on the start menu, we might be able to start the H2 server before opening the Excel file, somehow… BUT ideally we should find out how odbc and other connections such as DHIS2 can work together, since this seems to be the intention.
Views
I recommend using the new built in SQL views feature in DHIS2 as these are automatically dropped and recreated when you update the resource table (which the views depend on) and save you a lot of hassle.
The new demo database linked here already includes the views used by the pivot tables I have created, and the file PivotSourceViews_H2.sql contains all the SQL if you want to create these again on your database. I prefer to use the H2 console to check the result of the views before using them in Excel. I haven’t gotten the table view (of sql views) in DHIS2 to work yet.
Pivots and H2 connections in Excel 2007
For some reason I have only gotten the odbc connections to work in Excel if I first create a new data connection with MS Query and then create a pivot. Using a non_MS Query connection doesn’t seem to work, but that doesn’t really matter as long as we know that before we get started (MS Query should be the preferred way of querying the odbc connections anyway). Here are the steps needed in Excel when you have an odbc connection working and a DHIS2 database running in H2 server with the views in place:
-
Click on the Data tab, then on Get External Data->From Other Sources->From Microsoft Query
-
Double-click on your h2 odbc connection
-
If you get the Query Wizard - Choose columdn dialogue, click Cancel,and the Yes to continue in MS Query
4)Add your view in Add tables, close that window, and select al fields by clicking on the ‘*’, optionally you can remove fields you don’t want by selecting and deleting them column by column
-
Then click on Top menu->File->Retrun data to MS Excel
-
Back in Excel you will get the Import Data dialogue and there you select Pivot Table Report
-
Design your pivot table by dragging and dropping fields in the filter/column/row/data areas
Reusing connections across pivots:
When you need the same data in another pivot table you can 1) create a new Pivot from the Insert Tab, and then 2) choose connection and select your connection in the list of “connections used in this workbook”.
Using the pivots distributed in this email:
The pivots in the Excel file linked to above all link to a odbc connection called ‘dhis2_demo_h2’. To directly reuse the connection in Excel on your computer, you must create an odbc connection with the same name (hopefully the path to the database file doesn’t matter, I am not sure…). If the connection fails, then you can create a new set of connections in excel and then change the data source for each pivot to use your new connections. As long as they select * from the same views it works fine to replace them. You can e.g create multiple new data connections (using the approach described above) and just put the results in tables (not pivots) in a temporary worksheet in the same workbook, and then use these new connections when you replace the connection for the pivots in the other worksheets. Make sure to give your data connections good names so that it is easy to identify then when you need them later.
That’s it. Good luck and please share your improvements to this approach, I know it is still quite rough.
Ola,
···
Ola Hodne Titlestad (Mr)
HISP
Department of Informatics
University of Oslo
Mobile: +47 48069736
Home address: Vetlandsvn. 95B, 0685 Oslo, Norway. Googlemaps link