dhis2-devs team mailing list archive

["jason.p.pickering" <jason.p.pickering@xxxxxxxxx>]

Hi there. It would be very good to get those views, as they may
satisfy what I need.

Can you help out?

Best regards,
Jason


On Thu, Sep 24, 2009 at 5:55 PM,  <johansa@xxxxxxxxxx> wrote:
> 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
>>>
>>>
>>>
>>>
>>>
>>
>
>
>