http://208.76.222.114/confluence/display/RandD/General+multi-dimensional+model
On Fri, Sep 25, 2009 at 11:55 AM, Abyot Gizaw <[email protected]> wrote: > The one-to-one relationship mentioned between dataelement and categorycombo > is not correct ! > > The realtionship is one-to-many. A categorycombo can be assigned for many > dataelements. But a dataelement can have only one categorycombo. > > Thank you > Abyot. > > > On Fri, Sep 25, 2009 at 11:44 AM, Jason Pickering < > [email protected]> wrote: > >> Hi there. >> >> My basic issue with the category/category combo is that it appears to be a >> one-to-one relationship with data elements. If I look at the data model, >> there is a one-to-one relationship between dataelement and categorycomboid. >> For a given category combo, you can have multiple options. So, you can >> establish a relationship for a given data element and a group of category >> options. >> >> Let me try and describe the issue. We have a set of data elements related >> to malaria for this example. We would like to be able to pivot the data on >> other dimensions dimensions (Data element, age, disease, patient status). >> Obviously there are other dimensions that are pivotable (orgunit, period, >> dataset) >> >> The data elements look like this. I have put the dimensions in square >> brackets, and the dimensional elements into curly brackets. >> >> [Data element, Age, Disease, Patient status] >> Deaths Confirmed Malaria total (composed of) {All ages, Malaria Cases, >> Deaths} >> Deaths Confirmed Malaria 1 to Under 5 Years {1-5, Malaria Cases, Deaths} >> Deaths Confirmed Malaria Over 5 Years {Over 5, Malaria Cases, Deaths} >> Deaths Confirmed Malaria Under 1 Year {Under 1, Malaria Cases, Deaths} >> IP Discharge Confirmed Malaria total (composed of) {All ages, Malaria >> Cases, IP} >> IP Discharge Confirmed Malaria 1 to Under 5 Years {1-5, Malaria Cases, IP} >> IP Discharge Confirmed Malaria Over 5 Years {Over 5, Malaria Cases, >> Deaths} >> IP Discharge Confirmed Malaria Under 1 Year {Under 1, Malaria Cases, >> Deaths} >> OPD 1st Attendance Confirmed Malaria total (composed of) {All ages, >> Malaria Cases, OPD} >> OPD 1st Attendance Confirmed Malaria 1 to Under 5 Years {1-5, Malaria >> Cases, OPD} >> OPD 1st Attendance Confirmed Malaria Over 5 Years {Over 5, Malaria Cases, >> OPD} >> OPD 1st Attendance Confirmed Malaria Under 1 Year {Under 1, Malaria Cases, >> OPD} >> >> OK, I hope this is pretty clear. Obviously, there are more data elements >> (Typhoid, Yellow fever, etc). I might want to know how many Under 1 deaths I >> have had for all diseases, or how many OPD cases I have had for each >> disease. How can I do this with the existing data model? It is not obvious >> to me because there is no relationship between dimensional elements >> (categoryoptions) to each other. Category options can be related through a >> cateogry combination, but since data elements can only be assigned a single >> category option, the dimensionality is broken once it gets time to pull the >> data into a pivot table. >> >> In the incomplete example that I gave yesterday, I established a >> one-to-many relationship between a data element and a dimension. If I >> understand the current data model, I would have to create a separate >> categorycombo for each of these data elements, and assign this >> categorycombo to the data element. Now, I might be able to unfold the >> dimensions using the categories and categorycombos. I it is not apparent how >> the dimensional elements correspond themselves to a particular dimension, as >> there is no relation for this in the database as I can see it. >> >> As (Johan pointed out a few mails ago, if I understand him correctly) is >> different categorycombo's can be created for individual data elements, and >> assigned to these elements. However, this seems to be 1) incredibly >> inefficient and 2) does not establish any relationship between dimensional >> elements and dimensions. Perhaps it is there, and maybe it has been done in >> SL, but the SQL is not apparent to me at all. >> >> It would appear to me, looking from an SQL perspective, that a >> one-to-many relationship between a data element, a dimension (category) and >> dimensional element (category combo) would be much more effieicnet, and >> highly usable from an SQL perspective. As I mentioned in my mail, I am not >> sure how easy this would be to implement in a procedural language like Java, >> but I assume it should be possible to either do it this way, or rewrite my >> Postgres proprietary query in standard SQL (which there are ways to do with >> ANSI SQL). This would require modification to the data model (similar to >> the table I provided yesterday) and modification to the UI to allow users to >> 1) select a dimension (category) 2) Select a dimensional element for the >> given dimension. This would populate the table with a dataelementid, a >> dimensionid (categoryid) and a dimensional element (cateogryoptionid). >> >> My gut feeling this is exactly the same functionality as has currently >> been implemented for organizational units. Users can define a hierarchy for >> organizational units, and then assign them to >> categories/dimension/organizational group sets, decide whether the groups >> are compulsory and exlusive, and then assign a particular organizational >> unit to a particular group (which is analogous to a dimensional element). >> Organizational group sets define the dimension, and one-to-one assignment of >> an organizational unit to a particular organizational group defines which >> dimensional element the organizational unit is a member of. These dimensions >> can then be used in PivotTable analyses, where the orgunitgroupsets become >> dimensions, and orgunitgroups become dimensional elements.. >> >> I beleive that data elements are no different than organizational units. >> They should be able to be grouped into some sort of hierarchy and pivoted on >> any dimension. Data elements groups establish a one-to-many relationship >> between data elements and a data element group, but there is no concept of >> how data element groups relate to each other. >> I think this is perhaps the same concept you mention, ReportSet. >> >> I suspect we would need to potentially rethink the entire concept of >> multidimensionality if we really wanted to get it right. It would see to me >> that the DHIS datamodel and associated aggregation methods have been >> hardwired into aggregation across time (period) and geography (orgunit). >> What we can do with PivotTables and (and OLAP) is to aggregate across any >> possible dimension, slicing as you mention ,on any dimension . I am not sure >> this will be so simple to implement but I think there is a way to do it, >> without major modifications. >> >> I am not sure it solves the SDMX issue. There are potential issues related >> to "ragged" dimensions and how these get handled. Some data elements might >> have three dimensions, while others may have more. I have not thought about >> this in detail, but know it is an issue with cross-tab queries in SQL. You >> normally have to know how many dimensions you are working with in order to >> perform a cross-tab, but there are dynamic solutions. Perhaps this could be >> dealt with somehow in SDMX. >> >> Anyway ,I am rambling. Hope this mail helps though to push my point >> further. Once I get the SQL from SL, I will see if perhaps it has been done >> already, and that I am just writing long emails for nothing. :) >> >> Regards, >> Jason >> >> >> >> >> >> On Fri, Sep 25, 2009 at 10:44 AM, Bob Jolliffe <[email protected]> >> wrote: >> > Hi Jason and Johan >> > >> > I'm really pleased to see you having this discussion as I have been >> > grappling with a similar issue which involves unravelling categories, >> > category options and combos into something more familiar. I have >> reached >> > similar conclusions regarding nomenclature: >> > >> > category = dimension >> > categorycombo - I have been calling a dimension set (it bears a strong, >> and >> > useful, resemblance to xslt:attribute-set) >> > category option - I like your suggestion of DimensionalElement. I am >> going >> > to start calling it that too. >> > >> > In my case I need to export (and import data) into a standard format >> called >> > sdmx. So whereas in the DHIS2 native DXF we export datavalues with >> > effectively three dimensions (source, period, categorycombooption) the >> last >> > dimension is a sort of uber-dimension. Like a peppercorn or a cardamon >> > seed, when you break it open it explodes its rich complexity of >> dimensions. >> > >> > In sdmx we need the dimensions exploded. So data values look like: >> > >> > <dataset> >> > <datavalue name="TB test given" uid="44344 ...44" gender="Male" >> age="0-5" >> > value="32" /> >> > <datavalue name="TB test given" uid="44344 ...44" gender="Female" >> > age="0-5" value="38" /> >> > .. >> > </dataset> >> > >> > My approach to unpicking the dimensions from the dxf file is to >> transform it >> > with an xslt transformation which is still incomplete but seems to work >> > well. >> > >> > One other nomenclatures issue which has surfaced as a result is what we >> call >> > a "dataset". In DHIS2, if I understand correctly, a dataset corresponds >> > roughly to all the dataelements which might occur on a datacollection >> form. >> > If we view all dataelements as having just the three "dimensions" then >> all >> > is well, but if we explode the actual dimensions then we have an issue. >> In >> > the sdmx model a dataset consists only of dataelements with the same >> > dimensionset. After discussing this with Ola we have reached the >> conclusion >> > that we need another level of grouping, primarily for the UI - eg >> FormSet or >> > ReportSet which allows us to group related datasets. But that is an >> aside >> > from what you are talking about. >> > >> > I know that you guys can do magic with sql, but it seems that we should >> try >> > to capture some of this and place it down in the datamodel API. It >> occurs >> > to me that for a multidimensional dataelement we might benefit from some >> > utility methods to retrieve slices and dices which might assist in >> > constructing the pivot tables around dimensions. Does this sound like >> the >> > right thing to do. >> > >> > Regards >> > Bob >> > >> > 2009/9/24 <[email protected]> >> >> >> >> 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 >> >> > <[email protected]> wrote: >> >> >> >> >> >> >> >> >> On Wed, Sep 16, 2009 at 10:13 PM, <[email protected]> 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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> >> >> >> >> >> >> >> _______________________________________________ >> >> Mailing list: >> >> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs> >> >> Post to : [email protected] >> >> Unsubscribe : >> >> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs> >> >> More help : https://help.launchpad.net/ListHelp >> > >> > >> >> >> _______________________________________________ >> Mailing list: >> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs> >> Post to : [email protected] >> Unsubscribe : >> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs> >> More help : https://help.launchpad.net/ListHelp >> >> > > _______________________________________________ > Mailing list: https://launchpad.net/~dhis2-devs > Post to : [email protected] > Unsubscribe : https://launchpad.net/~dhis2-devs > More help : https://help.launchpad.net/ListHelp > > -- Cheers, Knut Staring
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
