dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

On Mon, Nov 30, 2009 at 9:16 PM, Ola Hodne Titlestad
<olatitle@xxxxxxxxx>wrote:

> The aggregation problems are most likely database specific because of
> the changes made to the multidimensional model:
>
> Lars wrote:
> The only change (not including additions) we did to the database schema was
> to make the relationship between category and category option one-to-many
> (was many-to-many). We did this to be able to know which dimensions
> (categories) belonged to a data value. If there were category options which
> belonged to more than one category, we need to create new ones and move the
> data correspondingly.
>
> I will look into this in more detail, but have already noticed that
> there are a few categoryoptions that are references in more than one
> category. There are also categories that appear in different
> categorycombos, like Gender, but not sure that this will also create
> problems (but I have already reported that 'Total Male' is empty in
> example 3 above).
>


Thanks a lot for providing the detailed feedback.

The 3 first points are valid and I will try to fix them tomorrow.

My thought was also that the aggregation problems was caused by the
database. My version of the SL database also has 9 category options which
appear in more than one category. The "migration" will be a bit tricky and I
am sorry for that but this change was needed to make the dimensional report
table work. This query will reveal the mentioned category options:

---
select d.name, c.categoryoptionid, count(c.categoryoptionid)
from categories_categoryoptions as c
left join dataelementcategoryoption as d
on c.categoryoptionid=d.categoryoptionid
group by c.categoryoptionid, d.name
having count(c.categoryoptionid) > 1
---

-- 
ReportTable: DataElement dimension tables do not work correctly
https://bugs.launchpad.net/bugs/490393
You received this bug notification because you are a member of DHIS 2
developers, which is subscribed to DHIS.

Status in DHIS 2 - District Health Information Software: New

Bug description:
I have tested the new type of report table called Data Element Dimension table in Reports->Report Tables and have discovered a few issues:

* The viewing of subtotals and totals in html, excel, and csv has not been implemented although the actual table generated in the database has these. Not sure whether this was an intentional shortcut for the 2.0.3 release or not.

* When there is only one dimension (category) the subtotals are still shown in the table which means that all numbers are simply repeated for every option in that category since these subtotals are then the same as the raw values. So when only one category is used there should be no subtotals, just the total.

* The naming of the columns for the optioncombos is confusing since it seems random which category that comes first in the name. E.g a age+gender table can look like this. "Male <5y", "Female <5y", ">5 Male", ">5 Female" which is really difficult to work with. I know data entry deals with this issue and seems to solve it, even with custom sorting, why don't we use the same approach here?

* There seems to be some problems with the calculation of subtotals:

Example 1) The following report table is based on the Sierra Leone database, you can see the in SQL here (http://pastebin.com/m30143e7c) that one of the two dimensions (fixed/outreach) do not have any values for its subtotals (total fixed and total outreach) as the values are simply '0' although the raw values in the same table clearly show that there are values reported for these dimensions. The other dimension (less than 12 months, 12-59 months) works as expected for the subtotals and the total is also fine.

Example 2) This table (http://pastebin.com/m61beb2d8) is based on a category combo consisting of the two categories (and their options) "preg&nonpreg 15-49y" (preg 15-49y, nonpreg 15-49y) and "fixed/outreach" (fixed, outreach), so totally 4 raw values, 4 subtotals, and 1 total are displayed in the report table. 
This one turned out to be a bit more correct than example 1) as it has got all its 4 subtotals populated, but only 3 of them are correct. The subtotals for column total_1549_yrs_nonpreg has wrong values systematically for all the data elements (rows). It is clear that only one of the two raw values that includes this dimension option are used. You can see that the subtotal equals the value of the _1549_yrs_nonpreg_fixed column and for some reason excludes the other raw value in the _1549_yrs_nonpreg_outreach column.

Example 3)
This table (http://pastebin.com/m410454f3) for morbidity age group + gender shows some other aggregation problems for subtotals. Haven't spent that much time looking at all its problems, but quickly notices that the two subtotals 'Total Male' and 'Total 15-24y' are both '0' for all the rows while e.g the raw values for the combination 'Male 15-24 y' has values for most of the rows.