Milestone 7

The Milestone 7 maintenance release 1 was released April 16, and should be used in stead.

Release date: April 2. 2007.

The release is available on the Downloads page.

Detailed user specification
Database changes

Detailed user specification

1. Much faster aggregation of data values and indicator calculations - Lars Helge, Stian, Ole Kristian

Aggregation of data values
Follow two parallel approaches for aggregation of datavalues:

Improve the current object-oriented approach
#Test a database-centred approach with aggregation-queries directly to the database

Indicator calculations

Test performance of calculations with improved aggregation, might be the aggregation that is the bottleneck.

Datamart population
Roughly speaking, the datamart is a set of tables that stores the aggregated datavalues and the indicator values. Today?s solution to populate the datamart based on user requests in the GUI, has proven very slow when it comes to moderate and large amounts of data. An improved aggregation process will of course affect this, but this workload could also be distributed somehow:

When should the indicators be calculated and the datavalues aggregated?
Test approach with triggers and continuous calculations (continuously building up the datamart triggered by data entry)
What about scheduled batch jobs to populate datamart?
We should also consider simplifying the datamart population / aggregation processes by reducing dimensions and options (e.g not combining periodTypes, limiting number of levels of aggregated data etc.)

2. Improved import/export of data values (memory problem solved) - Hans and Anders

Find a way to reduce the number of objects in memory when doing export, allowing export of larger ammounts of data. Find a way to import data in chunks rather than read an entire XML-file. Generally tune and improve existing export functionality to make it faster and less resource hungry.

Goals:

Reduce export and import time
Reduce the memory usage during export to allow export of larger amount of data

3. Basic version of validation rules on data elements (as in DHIS 1.3/1.4) - Margrethe

Validation rule are made to ensure the quality of the entred data.

Make new validation rules (e.g. data element A < data elemenet B)
Run validation checks. Validations will check the entries against a set of rules to check that there is consistency in the data.

4. Basic DHIS 1.4 import - Lars Helge

Import of the DHIS 1.4 datamart; indicator values and data values aggregated to various orgunit levels (the contents of the PivotSource_RoutineDataOUx and PivotSource_IndicatorOUx queries). Need to test whether there is a need for separate tables for each OU level in the DHIS 2 datamart as it is in DHIS 1.4. This includes a mapping/export of group sets and groups of orgunits and data elements.
The main purpose with this first step of DHIS 1.4 integration is to utilise the DHIS 2 portal and web modules for reporting and analysis on data captured in DHIS 1.4. By importing the DHIS 1.4 datamart into DHIS 2, data integration with other web applications such as KIDS and Pentaho will be more streamlined in both DHIS 1.4 and 2 using the same interface (DHIS 2 export to IXF or similar).

Import of metadata and raw data values (from the DHIS 1.4 data files) will be supported in later releases.

5. Integration with KIDS - manual on how to set up - Knut and Ola

A manual on how to set up KIDS (see http://kids.fao.org/, REP:KIDS ) with data from DHIS 2 will be provided, both via the IXF interface (see further down) and by directly accessing the database. KIDS will run as a separate web application displaying the data captured and calculated in the DHIS using thematic maps, web tables and graphs.

6. Integration with Pentaho - Ola

In this release a manual will be provided on how to set up the Pentaho application with DHIS 2 data. Pentaho offers a wide range of tools for web-based data analysis including pivot tables, dashboards, and report generation. This first integration with DHIS 2 will include configuration of Modrian-based cubes for faster access to DHIS 2 data, preconfigured pivot tables and dashboards, and guidelines on how to design reports (most likely) using the BIRT designer (see ) and then how these can be viewed in Pentaho.
See more information on Pentaho and its web based pivot tables (jpivot) here:

7. Improved period handling - HISP VN, Zegaye, Knut, HISP India

A GUI for automatic generation of data periods will be provided (the current release requires this to be manually inserted into the database). Periods will be generated based on different PeriodTypes (Monthly, Quarterly etc.,) however there is a question whether these PeriodTypes should be predefined and ?locked? by the system or flexible to user customisation through the period-GUI.
Period names should throughout the application be displayed using their names (March 2006, Q2 2007, Week 31 2006 etc.) and not using their start and end dates. When selecting periods in e.g. data entry or report generation the period names should be provided in drop-down lists in addition to a calendar selection.
Furthermore, it has also been requested by HISP India that the calendars not only shows the days/dates, but are adapted to the current PeriodType, e.g. a calendar for data entry for a dataset with Monthly data entry should display the 12 months as the most fine-grained units, while a weekly periodType should trigger a calendar displaying the 52 weeks.

8. Various integration (mostly through import/export) with Indian national systems

Knut, please elaborate here? Since Knut hasnt elaborated I(Stian) will try to write something.
We have been working on a IDSP exporter for a while now and hopefully we will be able to test it with real indian data some time next week. If so this might be a possibility for M7 even though it will be a bit on the outside of the system.
Ole has started looking at integration with RIMS, but I doubt this will be ready for M7.

9. Extensive re-factoring of the core module ? Torgeir

cleaned up and simplified core module
improved architecture in terms of cleaner and more separate layers
a more easily accessible service layer to other modules
Torgeir, please elaborate here?

10. Installation package - HISP VN

This installation package should include easy-to-use setup for the most common configurations of the DHIS when it comes to OS (Windows/Linux), database (MySQL, PostGreSQL, more?) and web servers/servlet containers (jetty and tomcat).

11. i18n support in the database layer ? Øyvind

Multilanguage support of all metadata in the application; data elements, indicators, groups, orgunits, etc.
Øyvind, please elaborate here...

12. Full support for export and import to the IXF format - Hans and Knut

Implement support for import and export of the XML-based protocol called the Indicator eXchange format (IXF). IXF has a well defined schema that allows for multi-dimensional datasets, complex indicator relationships and source information at the data node level. The protocol has already been adopted by several thematic mapping applications, including KIDS (FAO), DevInfo (UNICEF) and HealthMapper (WHO). These systems can import indicator data from an IXF source, and also share their data in the proposed format.
See more details on IXF here:

Database changes

15. December 2006 (revision 2632) (dhis-database-fixer updated)

Table dataelements renamed to datasetmembers

MySQL and PostgreSQL:

alter table dataelements rename to datasetmembers;

5. January 2007 (revision 2649) (dhis-database-fixer updated)

In table datasetmembers, column id renamed to datasetid
In table datasetmembers, column elt renamed to dataelementid

MySQL:

alter table datasetmembers change id datasetid int(11) not null;
alter table datasetmembers change elt dataelementid int(11) not null;

PostgreSQL:

alter table datasetmembers rename id to datasetid;
alter table datasetmembers rename elt to dataelementid;

22. January 2007 (revision 2717) Averted 2. March 2007 (revision 2956)

If undoing this change:
The source table must be split into two, moving out the organisation unit part of it. The source table will then only contain an id column, while the organisationunit table will contain everything but the sourcetype column. Remember that if you rename the source table to organisationunit, all the foreign keys, which still should be to a parent source table, will point to the organisation unit table. So some of the foreign keys to the organisationunit table must be removed after the renaming, so that Hibernate can re-create the ones that should point to the parent source table. Deleting all foreign keys is safe, as Hibernate will re-create them.

The original changes:

Table organisationunit renamed to source
In table source, all entries in column sourcetype must be set to "OU"

MySQL and PostgreSQL:

alter table organisationunit rename to source;
alter table source add column sourcetype varchar(2) not null;
update source set sourcetype = 'OU';

2. February 2007 (revision 2810)

In table datavalue, dataregisteringassociation, and frequencyoverrideassociation: Column source changed from varchar to integer (all entries must be converted). The type of the column must match the type of the id column in the source table (MySQL: int(11), PostgreSQL: integer). Hibernate should add the foreign keys from the source columns to the source.id column automatically.

MySQL:

alter table datavalue modify source int(11) not null;
alter table dataregisteringassociation modify source int(11) not null;
alter table frequencyoverrideassociation modify source int(11) not null;

PostgreSQL:

alter table datavalue alter column source type integer using to_number(source, '9999999999');
alter table dataregisteringassociation alter column source type integer using to_number(source, '9999999999');
alter table frequencyoverrideassociation alter column source type integer using to_number(source, '9999999999');

2. March 2007 (revision 2952)

The values in the name column of the periodtype table must be a subset of:
- - Daily
  - Weekly
  - Monthly
  - Quarterly
  - SixMonthly
  - Yearly
  - TwoYearly
  - OnChange
- The character casing is important.
- Each value must only occur once.
- The column length has been reduced from 255 to 15 characters.

3. March 2007 (revision 2969)

The uniqueness constraint on period.startdate and period.enddate is changed to include the period.periodtype column too, so that two periods of different type can have the same start and end dates.

16.-20 march 2006 (revision TBD)

Notes. Do not apply yet.

Major modifications to the database table and column names as discussed on the dev list.

update dataelement set shortname = '' where shortname is NULL;
update dataelement set name = substring(name,1,160), 
  shortname = substring(shortname,1,25), 
  code = substring(code,1,25);
alter table dataelement change column id dataelementid int(11) NOT NULL auto_increment; *primary key?*
alter table dataelement modify column name varchar(160) NOT NULL;
alter table dataelement change column shortName shortname varchar(25) NOT NULL;
alter table dataelement modify column description varchar(360);
alter table dataelement change column type valuetype varchar(16) NOT NULL;
alter table dataelement change column aggregationOperator aggregationtype varchar(16) NOT NULL;
alter table dataelement change column parent parentid int(11) default NULL;
--alter table dataelement change column alternativeName alternativename varchar(25) default NULL;
alter table dataelement modify column code varchar(25) default NULL;
update dataelement set aggregationtype = 'sum' where aggregationtype = '1';
update dataelement set aggregationtype = 'average' where aggregationtype = '2';
update dataelement set aggregationtype = 'count' where aggregationtype = '3';
/* delete alternative name */

alter table dataelementgroup change column id dataelementgroupid int(11) NOT NULL auto_increment; *primary key?*
alter table dataelementgroup modify name varchar(160);
alter table dataelementgroup drop column flag;
/* description */

alter table dataelementgroupmembers change column groupid dataelementgroupid int(11) NOT NULL;

alter table period change column id periodid int(11) NOT NULL auto_increment; *primary key?*
alter table period change column periodType periodtypeid int(11) NOT NULL;
alter table period change column startDate startdate datetime NOT NULL;
alter table period change column endDate enddate datetime NOT NULL;

alter table periodtype change column id periodtypeid int(11) NOT NULL auto_increment; *primary key?*

alter table source change column id sourceid int(11) NOT NULL auto_increment; *primary key?*

/* datavalue here */

update organisationunit set name = substring(name,1,160), shortname = substring(shortname,1,25), organisationunitcode = substring(organisationunitcode,1,25);
alter table organisationunit change column id organisationunitid int(11) NOT NULL auto_increment; *primary key?*
alter table organisationunit modify column name varchar(160) NOT NULL;
alter table organisationunit change column parent parentid int(11) default NULL;
alter table organisationunit modify column shortname varchar(25) NOT NULL;
alter table organisationunit change column organisationunitcode code varchar(25) default NULL;
alter table organisationunit change column openingDate openingdate datetime NOT NULL;
alter table organisationunit change column closedDate closeddate datetime default NULL;
alter table organisationunit modify column comment varchar(360) default NULL;
/* delete alternative name*/

drop table if exists orgunitgroup;
alter table organisationunitgroup rename to orgunitgroup;
alter table orgunitgroup change column id orgunitgroupid int(11) NOT NULL auto_increment; *primary key?*
alter table orgunitgroup modify name varchar(160) NOT NULL;
/* description! */

drop table if exists orgunitgroupmembers;
alter table organisationunitgroupmembers rename to orgunitgroupmembers;
alter table orgunitgroupmembers change column groupid orgunitgroupid int(11) NOT NULL;

drop table if exists orgunitgroupset;
alter table organisationunitgroupset rename to orgunitgroupset;
alter table orgunitgroupset change column id orgunitgroupsetid int(11) NOT NULL auto_increment; *primary key?*
alter table orgunitgroupset modify column name varchar(160) NOT NULL;
alter table orgunitgroupset modify column description varchar(360) default NULL;

drop table if exists orgunitgroupsetmembers;
alter table groupsetmembers rename to orgunitgroupsetmembers;
alter table orgunitgroupsetmembers change column groupsetid orgunitgroupsetid int(11) NOT NULL auto_increment; *primary key?*
alter table orgunitgroupsetmembers change column organisationunitgroupid orgunitgroupid int(11) NOT NULL;

alter table dataset change column id datasetid int(11) NOT NULL auto_increment; *primary key?*
alter table dataset modify column name varchar(160) NOT NULL;
alter table dataset change column periodType periodtypeid int(11) NOT NULL;
/* type and description */

drop table if exists datasetsource;
alter table dataregisteringassociation rename to datasetsource;
alter table datasetsource change column source sourceid int(11) NOT NULL;
alter table datasetsource change column dataSet datasetid int(11) NOT NULL;

/* change name */
alter table frequencyoverrideassociation  change column source sourceid int(11) NOT NULL;
alter table frequencyoverrideassociation  change column dataSet datasetid int(11) NOT NULL;
alter table frequencyoverrideassociation  change column periodType periodtypeid int(11) NOT NULL;

alter table minmaxdataelement change column id minmaxdataelementid int(11) NOT NULL auto_increment; *primary key?*
alter table minmaxdataelement change dataElement dataelementid int(11) NOT NULL;
alter table minmaxdataelement change organisationUnit sourceid int(11) NOT NULL;

update indicator set name = substring(name,1,160), 
  shortname = substring(shortname,1,25), 
  code = substring(code,1,25);
alter table indicator change column id indicatorid int(11) NOT NULL auto_increment; *primary key?*
alter table indicator modify column name varchar(160) NOT NULL;
alter table indicator modify column description varchar(360) default NULL;
alter table indicator change column indicatorType indicatortypeid int(11) NOT NULL;
alter table indicator change column numeratorDescription numeratordescription varchar(360) default NULL;
alter table indicator change column numeratorAggregationOperator numeratoraggregationtype varchar(16) NOT NULL;
alter table indicator change column denominatorDescription denominatordescription varchar(360) default NULL;
alter table indicator change column denominatorAggregationOperator denominatoraggregationtype varchar(16) NOT NULL;
alter table indicator modify column code varchar(25) default NULL;
alter table indicator change column shortName shortname varchar(25) default NULL;
update indicator set numeratoraggregationtype = 'sum' where numeratoraggregationtype = '1';
update indicator set numeratoraggregationtype = 'average' where numeratoraggregationtype = '2';
update indicator set numeratoraggregationtype = 'count' where numeratoraggregationtype = '3';
update indicator set denominatoraggregationtype = 'sum' where denominatoraggregationtype = '1';
update indicator set denominatoraggregationtype = 'average' where denominatoraggregationtype = '2';
update indicator set denominatoraggregationtype = 'count' where denominatoraggregationtype = '3';
/* delete alternative name */

alter table indicatortype change column id indicatortypeid int(11) NOT NULL auto_increment; *primary key?*
alter table indicatortype modify column name varchar(160) NOT NULL;
alter table indicatortype change column factor indicatorfactor int(11) NOT NULL;

alter table indicatorgroup change column id indicatorgroupid int(11) NOT NULL auto_increment; *primary key?*
alter table indicatorgroup modify column name varchar(160) NOT NULL;
/* description */

alter table indicatorgroupmembers change column groupid indicatorgroupid int(11) NOT NULL;

alter table aggregateddatavalue change column dataElementId dataelementid int(11) NOT NULL;
alter table aggregateddatavalue change column periodId periodid int(11) NOT NULL;
alter table aggregateddatavalue change column periodTypeId periodtypeid int(11) NOT NULL;
alter table aggregateddatavalue change column organisationUnitId organisationunitid int(11) NOT NULL;
alter table aggregateddatavalue modify column value double NOT NULL;

update aggregatedindicatorvalue set numeratorValue = 0 where numeratorValue is NULL;
update aggregatedindicatorvalue set denominatorValue = 0 where denominatorValue is NULL;
alter table aggregatedindicatorvalue change column indicatorId indicatorid int(11) NOT NULL;
alter table aggregatedindicatorvalue change column periodId periodid int(11) NOT NULL;
alter table aggregatedindicatorvalue change column periodTypeId periodtypeid int(11) NOT NULL;
alter table aggregatedindicatorvalue change column organisationUnitId organisationunitid int(11) NOT NULL;
alter table aggregatedindicatorvalue modify column value double NOT NULL;
alter table aggregatedindicatorvalue change column numeratorValue numeratorvalue double NOT NULL;
alter table aggregatedindicatorvalue change column denominatorValue denominatorvalue double NOT NULL;
/* uncertain! factor => indicatorfactor? */

drop table if exists orgunithierarchy;
alter table organisationunithierarchy rename to orgunithierarchy;
alter table orgunithierarchy change column id orgunithierarchyid int(11) NOT NULL auto_increment; *primary key?*
/* uncertain! change date, shorter level*/

drop table if exists orgunitstructure;
alter table organisationunitstructure rename to orgunitstructure;
alter table orgunitstructure change column id orgunitstructureid int(11) NOT NULL auto_increment; *primary key?*
alter table orgunitstructure change column organisationUnitId organisationunitid int(11) NOT NULL;
alter table orgunitstructure change column idLevel1 idlevel1 int(11) default NULL;
alter table orgunitstructure change column idLevel2 idlevel2 int(11) default NULL;
alter table orgunitstructure change column idLevel3 idlevel3 int(11) default NULL;
alter table orgunitstructure change column idLevel4 idlevel4 int(11) default NULL;
alter table orgunitstructure change column idLevel5 idlevel5 int(11) default NULL;
alter table orgunitstructure change column idLevel6 idlevel6 int(11) default NULL;
alter table orgunitstructure change column idLevel7 idlevel7 int(11) default NULL;
/* uncertain! shorter level*/

alter table structure change column id orgunithierarchyid int(11) NOT NULL;

drop table if exists orgunitgroupsetstructure;
alter table groupsetstructure rename to orgunitgroupsetstructure;
alter table orgunitgroupsetstructure change column id orgunitgroupsetstructureid int(11) NOT NULL auto_increment; *primary key?*
alter table orgunitgroupsetstructure change column organisationUnitName organisationunitname varchar(160) NOT NULL;
alter table orgunitgroupsetstructure change column groupName orgunitgroupname varchar(160) NOT NULL;
alter table orgunitgroupsetstructure change column groupSetName orgunitgroupsetname varchar(160) NOT NULL;
alter table orgunitgroupsetstructure change column organisationUnitId organisationunitid int(11) NOT NULl;
alter table orgunitgroupsetstructure change column groupId orgunitgroupid int(11) NOT NULL;
alter table orgunitgroupsetstructure change column groupSetId orgunitgroupsetid int(11) NOT NULL;
/* uncertain! */

drop table if exists userinfo;
alter table users rename to userinfo;
alter table userinfo change column id userinfoid int(11) NOT NULL auto_increment; *primary key?* /*?*/
alter table userinfo modify column surname varchar(160) NOT NULL;
alter table userinfo modify column firstname varchar(160) NOT NULL;
alter table userinfo modify column email varchar(160) default NULL;
alter table userinfo change column organisationUnit organsationunitid int(11) default NULL;
/* sourceid? */

alter table usercredentials rename to users;
alter table users change column id userid int(11) NOT NULL;
alter table users modify column username varchar(32) NOT NULL;
alter table users modify column password varchar(32) NOT NULL;

drop table if exists userrole;
alter table userauthoritygroup rename to userrole;
alter table userrole change column id userroleid int(11) NOT NULL auto_increment; *primary key?*
alter table userrole modify column name varchar(160) NOT NULL;

drop table if exists userrolemembers;
alter table userauthoritygroupmembers rename to userrolemembers;
alter table userrolemembers change column userauthoritygroup userroleid int(11) NOT NULL;
alter table userrolemembers change column usercredentials userid int(11) NOT NULL;

drop table if exists userroleauthorities;
alter table userauthoritygroupauthorities rename to userroleauthorities;
alter table userroleauthorities change column userauthoritygroup userroleid int(11) NOT NULL;
alter table userroleauthorities modify column authority varchar(160) default NULL;

drop table if exists userroledataset;
alter table userauthoritygroupdataset rename to userroledataset; *Table doesn't exist in m6?*
alter table userroledataset change column userauthoritygroup userroleid int(11) NOT NULL;
alter table userroledataset change column dataset datasetid int(11) NOT NULL;

alter table usersetting change column userid userinfoid int(11) NOT NULL;
alter table usersetting modify column name varchar(160) NOT NULL;

alter table datavalue change column dataElement dataelementid int(11) NOT NULL;
alter table datavalue change column period periodid int(11) NOT NULL;
alter table datavalue change column source sourceid int(11) NOT NULL;
alter table datavalue modify column value varchar(160) default NULL;
alter table datavalue change column storedBy storedby varchar(31) default NULL;
alter table datavalue change column timestamp lastupdated datetime default NULL;