dhis2-devs team mailing list archive

[Calle Hedberg <calle.hedberg@xxxxxxxxx>]

Hi

We encountered an interesting scenario yesterday on a Namibia DHIS2
instance. It started with the fact that all newly created indicators
yielded no data whatsoever - nothing was created during the analytics
process. After a lot of digging around in the database (using
pgremote), I discovered the following:

1.
The instance itself was initially created as a conversion from DHIS
1.4, with a lot of data elements, indicators, and data. ALL of those
indicators worked as expected. Any additional indicators did not.

2.
After creating this instance, I imported two data sets with some data
from an old database created by Busoye last year. Despite some
hassles, I managed to get the data over but I had to import around 140
categorycombooptions with it.

3.
What I did not know is that DHIS2 have no import checks on category or
option NAMES, only (presumably) UIDs. So the import of Busoye's data
actually created multiple categories with identical names - including
the 'default' category. There is no import check or integrity check
for this - I only found out by digging into the database.

4.
What then happened was that when creating new indicators, the
numerator/denominator formula editor would seemingly randomly select
any of the 'default' categories - and it selected the wrong one (no
data in the datavalue table for it). Again - no warning given that
there are two identically named categories in the system. So while my
formula should look like this:
#{cpZKSrwB8oc.HJjwkbcRm9o}+#{zHIlRIp2I67.HJjwkbcRm9o} (HJjwkbcRm9o
being the UID of the original 'default' category), the system would
insert #{cpZKSrwB8oc.x45yUxTE7U8}+#{zHIlRIp2I67.x45yUxTE7U8} - with
x45yUxTE7U8 being the UID of a copycat 'default' not used for any
data. Net result: indicator silently fails.

5.
Having identified the root cause of the problem, I wanted to delete
the 'duplicate' categories that are actually not used.
- I look at Category Option Combo - remove is not an option
- I look at Data Element Category Option management - remove is there,
but any attempt at removal triggers message "Object not deleted
because it is associated by objects of type
DataElementCategoryOptionCombo"
- Ditto with data element category management
- Ditto with data element category combination management
and so on.

I know which categories I want to get rid of, and none of them have
any data in the datavalues table - but it seems the only way to
actually get rid of them is to painstakingly dig through a lot of
database tables and delete them manually.

For me, there are two lessons here:

Firstly, the importance of being VERY structured when implementing
categories and options. Ending up with hundreds of combos, each groups
sort of created from scratch on a dataset by dataset basis - i.e.
dataset A has age as category and gender as option, whereas dataset B
is vice versa - is very difficult to maintain and even just
describe/explain to other people.

Secondly, never export/import category/option meta-data without first
comparing them using database queries - this because the import
functions in DHIS2 has been made very simplistic with limited checks
and no options for "matching" incoming data with existing data on
names, UIDs etc. (In the case above, I HAD to import many cat/combo
options because otherwise the data import failed - the problem was
that when exporting/importing that meta-data I had no way of
eliminating 'duplicate' or 'non-used' cat/options/combos.

If anybody know of an efficient method to get rid of such
unused/duplicated categories/options/combos, I would really appreciate
a heads up :-)

Best regards
Calle



*******************************************

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@xxxxxxxxx

Skype: calle_hedberg

*******************************************