dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

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:
1) Install the postgres odbc driver
2) Start the H2 server
3) Set up a new data source (odbc connection), and test it!
4) 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 :(
5) Open Excel and create a new data connection to an external data source
using MS Query and ODBC
6) Build a pivot table based on the fields returned from the new connection
7) 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 <username> 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:
1) Click on the Data tab, then on Get External Data->From Other
Sources->From Microsoft Query
2) Double-click on your h2 odbc connection
3) 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
5) Then click on Top menu->File->Retrun data to MS Excel
6) Back in Excel you will get the Import Data dialogue and there you select
Pivot Table Report
7) 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<http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=Vetlandsvn.+95B,+0685+Oslo,+Norway>