dhis2-devs team mailing list archive

[johansa@xxxxxxxxxx]

Jason,
I will leave to others to comment the code, but I have a few comments...

> I have done a bit more thinking on this, and would like to offer some
> more examples up for discussion.
>
> Basically, we have a lot of data elements that are somehow related to
> each other, similar to my kooky example in my original mail. I assume
> this is fairly common throughout other HMIS systems. Here,  malaria
> attendance is broken down into various dimensions/category by patient
> type (outpatient, inpatient, and deaths) and by age (under 1 ,1-5 and
> over 5). But say you want to be able to pivot to look at outpatient,
> inpatient and deaths totals (i.e. summed up by age). Well, you could
> create a separate data element for this, but it sure would be nice to
> be able to Pivot the data somehow.

In the Sierra Leone db, Edem and Romain set up views that pulled the
categories through into a "Category" pivot field, which you can then use
to get what you want. Simply tick the categories (see below) you want to
see, and group them together in excel. Maybe Edem and Romain can help
further here.


> Dimension ? Category
> Dimensional element ? Category option ? Category combo ( I think)

The right symbol disappeared from my reply-mail here, but some clarification:

Crosstab Dimension (age AND gender) = Category combo
Dimension (age, gender) = Category
Dimensional element (inpatient, outpatient, death, under1, 1-5, and over
5) = Category option

So by assigning a DE the category combo of "gender_age", you get 9
dimensional elements, 3 category options (in category age) by 3 category
options (in category gender)

Johan




> Anyway, here is the helper table I created.
>
> CREATE TABLE test_dataelementcategorycombo
> (
>   test_dataelementid integer NOT NULL,
>   test_dataelementcategoryid integer NOT NULL,
>   test_dataelementcategorycomboid integer NOT NULL,
>   CONSTRAINT pk_testdataelementcategory PRIMARY KEY
> (test_dataelementid, test_dataelementcategoryid,
> test_dataelementcategorycomboid)
> )
> WITH (OIDS=FALSE);
>
> So this is  a real simple table which references a data element, a
> data element category, and a data element combo. The reference to a
> data element category may be redundant, but anyway, lets leave it in
> for now.
>
> I populated the table with some data, which will be used to assign
> dimensions to data elements. It looks like this in my DB, which looks
> like this.
>
> 309;25250;25251
> 309;25257;25255
> 348;25250;25252
> 348;25257;25255
> 455;25250;25253
> 455;25257;25255
>
> but of course this is meaningless to you. What do these values correspond
> to?
>
> "OPD 1st Attendance Clinical Case of Malaria Under 1 Year";"Age";"Under 1"
> "OPD 1st Attendance Clinical Case of Malaria 1 to Under 5
> Years";"Age";"Age 1-5"
> "OPD 1st Attendance Clinical Case of Malaria Over 5 Years";"Age";"Over 5"
> "OPD 1st Attendance Clinical Case of Malaria Under 1 Year";"Patient
> status";"OPD"
> "OPD 1st Attendance Clinical Case of Malaria 1 to Under 5
> Years";"Patient status";"OPD"
> "OPD 1st Attendance Clinical Case of Malaria Over 5 Years";"Patient
> status";"OPD"
>
> which can be produced by the following view.
>
> CREATE OR REPLACE VIEW vw_dataelements_dimensions AS
>  SELECT dataelement.name, dataelementcategory.name AS dimension,
> dataelementcategoryoption.name AS dimension_element
>    FROM dataelement
>    JOIN test_dataelementcategorycombo ON
> test_dataelementcategorycombo.test_dataelementid =
> dataelement.dataelementid
>    JOIN dataelementcategory ON dataelementcategory.categoryid =
> test_dataelementcategorycombo.test_dataelementcategoryid
>    JOIN dataelementcategoryoption ON
> test_dataelementcategorycombo.test_dataelementcategorycomboid =
> dataelementcategoryoption.categoryoptionid;
>
> So, that view just provides a human readable view of those integers
> that I populated in the the test_dataelementcategorycombo table I
> created above.  This table just assigns particular data elements to
> different category options (dimensional elements).
>
> OK, so far so good, but the problem now is, how to use this with the
> aggregatedatavalue table? If we try and join this table directly, we
> will have issues with duplicates in the pivot table, so we need to
> transform the data slightly.
>
> This should do the trick.
>
> SELECT * FROM crosstab
> 	(
> 'SELECT name, dimension, dimension_element FROM
> vw_dataelements_dimensions ORDER BY 1,2,3',
> 'SELECT DISTINCT dimension from vw_dataelements_dimensions ORDER BY 1 ASC'
> 	)
> as
> (
> name character varying(230),
> age character varying(160),
> status character varying(160)
> );
>
>
> which returns this record set
>
> "OPD 1st Attendance Clinical Case of Malaria 1 to Under 5 Years";"Age
> 1-5";"OPD"
> "OPD 1st Attendance Clinical Case of Malaria Over 5 Years";"Over 5";"OPD"
> "OPD 1st Attendance Clinical Case of Malaria Under 1 Year";"Under 1";"OPD"
>
>
> OK, admittedly, I cheated a bit and used the crosstab function of
> Postgresql, but I assume that this query could be rewritten with a few
> more lines of code in standard SQL or some procedural language like
> Java. Now, this record set looks like something that I can almost use
> with the aggregateddatavalue table simply by joining up the table on
> the appropriate dataelementid and pulling everything into a pivot
> table.  I would not have any duplicated values and would have columns
> like data element name, period, orgunit, age, patient status and of
> course the value of the data element.  I hope that part is pretty
> clear. Just join up that table to the aggregateddata table, and you
> have pretty much what is needed to pull the data directly into a
> PivotTable for further analysis.
>
> This is not a complete example, but it is very close to what I need
> here ,and I think this type of functionality would be much more useful
> than the current data element categories functionality. Basically, all
> that would be required, at least initially, would be another user
> interface screen to allow the definition of which category(ies) and
> category options a data element is a member of. The rest could ,in the
> first instance be executed with custom SQL (obviously, I am partial to
> this language and hobbled by the fact that I do not know Java), but
> eventually this would need to be implemented somehow in Java.
>
>  I am not sure if this really solves all of the issues surrounding
> multidimensional analysis of data elements, but it seems to solve the
> issues that I am having by trying to assign some sort of dimensional
> hierarchy to data elements (similar to the exclusive/compulsory
> functionality of orgunits). Any thoughts on this?
>
> Best regards,
> Jason
>
>
>
>
> On Wed, Sep 16, 2009 at 10:28 PM, Jason Pickering
> <jason.p.pickering@xxxxxxxxx> wrote:
>>
>>
>> On Wed, Sep 16, 2009 at 10:13 PM, <johansa@xxxxxxxxxx> wrote:
>>>
>>> >> However, there does seem to be the ability to assign dimensions,
>>> there
>>> >> does
>>> >> not seem to be the ability to assign particular elements within
>>> those
>>> >> dimensions to a particular DHIS data element.
>>>
>>>
>>> Just some more clarification here: you can make category combos which
>>> you
>>> assign to data elements. However, it is not possible to assign just
>>> specific parts of a category combo (only some of the category options)
>>> to
>>> a data element.
>>
>> Yes, this was exactly what I wanted. Assigning different categories
>> would
>> seem to break the dimensionality.
>>
>>>
>>> Then you must make a specific category (as the only one in
>>> or part of a new category combo) with just those options. It can be
>>> hell;
>>> in Tajikistan there were way over 20 categories I think, at least 10
>>> just
>>> on various age groups.
>>>
>>> Johan
>>>
>>
>> This was my fear.
>>
>> I will need to do some testing and see. I still fear it is not exactly
>> the
>> intended functionality.
>>
>> Basically, I think I need something akin to the exclusive/compulsory
>> groups
>> that are in place for organizational units, but instead, for arbitrary
>> dimensions. I will give a try and see what happens.
>>
>> Thanks,
>> Jason
>>
>>
>>
>>
>>
>