Current Dis-aggregation vs Historical via API

Hello DHIS2 Devs!

API question for the API Gurus:

During configuration on a recent project, maybe dis-aggregation groups were changed and updated after their Category Option Combos were initially generated. They do not have data associated with them, but I think that is beside the point for my question. Even if they did have data, I’m asking for a why to get the “current” CatOptCombos, rather than “every” CatOptCombo that has ever been assigned to this Catcombo.

Using the demo server, say I change:
Morbidity Cases

to
Morbidity Cases + Gender

https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn

The API response for this one will go from:


0-11m


15y+


12-59m


5-14y

To:


0-11m, Female


12-59m


0-11m, Male


5-14y, Female


15y+, Female


12-59m, Female


12-59m, Male


5-14y


0-11m


15y+


5-14y, Male


15y+, Male

So rightly, it retains its older CatOptCombos, because of course there might be data associated with those in the datavalue table. So I’m not looking to prune these. Without writing something to crawl through the api to figure out the “current” state, is there an existing API call that can do this? I know there must be, or someone has done it in JAVA because the data entry forms all can tell the “current” dis-aggregations without issue. Thanks and let me know if I can clarify my ask any better.

Cheers!

image

···

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007

Proof reading isn’t my strong suit today:

image

···

On Mon, Nov 16, 2015 at 11:17 AM, Timothy Harding tharding@baosystems.com wrote:

Hello DHIS2 Devs!

API question for the API Gurus:

During configuration on a recent project, maybe dis-aggregation groups were changed and updated after their Category Option Combos were initially generated. They do not have data associated with them, but I think that is beside the point for my question. Even if they did have data, I’m asking for a why to get the “current” CatOptCombos, rather than “every” CatOptCombo that has ever been assigned to this Catcombo.

Using the demo server, say I change:
Morbidity Cases

to
Morbidity Cases + Gender

https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn

The API response for this one will go from:


0-11m


15y+


12-59m


5-14y

To:


0-11m, Female


12-59m


0-11m, Male


5-14y, Female


15y+, Female


12-59m, Female


12-59m, Male


5-14y


0-11m


15y+


5-14y, Male


15y+, Male

So rightly, it retains its older CatOptCombos, because of course there might be data associated with those in the datavalue table. So I’m not looking to prune these. Without writing something to crawl through the api to figure out the “current” state, is there an existing API call that can do this? I know there must be, or someone has done it in JAVA because the data entry forms all can tell the “current” dis-aggregations without issue. Thanks and let me know if I can clarify my ask any better.

Cheers!

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007

Hi Tim,

I think the short answer to your question is there is no API to tell you this directly. The database does not version category option combos, so there is no real way to know what is historical versus what is current. In your particular case, you might be able to leverage the “created” time stamp, which differs between the two sets of dis-aggregations, but again, you might have to make some assumptions there.

I think the design, as it is at the moment, is not how the category option combos are intended to be used, because you have overlapping options, namely 12-59m contains “12-59m, Male” and “12-59m Female”. If all three of these are reported (which they should be from the data entry form looking at the design), you will end up with duplicates in the analytics. Category option combos should be exclusive, and not overlap.

So, I think what should have been done in this case, is to create a totally new category combo, and then change the data element’s disaggregation to the new combo. Changing category combos after the fact usually leads to all sorts of problems, but creating new ones is pretty easy. DHIS2 will not track this change, but you could implement a trigger function on “dataelement” to audit when a category combo changes for a given data element, to give you a historical view of the data element, and how its disaggregations may have changed over time. But, there is no API in DHIS2 which would do this for you.

Regards,

Jason

image

···

On Mon, Nov 16, 2015 at 8:17 PM, Timothy Harding tharding@baosystems.com wrote:

Hello DHIS2 Devs!

API question for the API Gurus:

During configuration on a recent project, maybe dis-aggregation groups were changed and updated after their Category Option Combos were initially generated. They do not have data associated with them, but I think that is beside the point for my question. Even if they did have data, I’m asking for a why to get the “current” CatOptCombos, rather than “every” CatOptCombo that has ever been assigned to this Catcombo.

Using the demo server, say I change:
Morbidity Cases

to
Morbidity Cases + Gender

https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn

The API response for this one will go from:


0-11m


15y+


12-59m


5-14y

To:


0-11m, Female


12-59m


0-11m, Male


5-14y, Female


15y+, Female


12-59m, Female


12-59m, Male


5-14y


0-11m


15y+


5-14y, Male


15y+, Male

So rightly, it retains its older CatOptCombos, because of course there might be data associated with those in the datavalue table. So I’m not looking to prune these. Without writing something to crawl through the api to figure out the “current” state, is there an existing API call that can do this? I know there must be, or someone has done it in JAVA because the data entry forms all can tell the “current” dis-aggregations without issue. Thanks and let me know if I can clarify my ask any better.

Cheers!

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Thanks Jason,

It is unfortunate there is no turnkey. I’ve added a few responses inline:

  • I think the design, as it is at the moment, is not how the category option combos are intended to be used, because you have overlapping options*

Yup, this is an artifact of changing a catcombo, never will someone be reporting to all 12, just the 8 “current” ones.

there is no real way to know what is historical versus what is current

There has to be, because the data entry forms all adjust to the “current” after you’ve edited the catcombo. The older ones are there just in case someone has entered data towards them. If we check the data entry page for an element using the above catcombo, we find only the ‘newest’:

Before:

After:

Instead of 12 CatOptCombos (the 8 “current” ones and 4 old ones) we only see the 8 current ones. If you look at the catcombo in the API, however, it will still show all 12. This makes sense, because there could be back data associated with the previous catoptioncombos.

So the only way I can figure to do this with a purely API approach is to:

  1. Ask a Cat Combo what Options are assigned to it’s categories
    https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn.xml?fields=name,id,categories[name,categoryOptions]

















  2. Multiply the categories together, Example: FbLZS3ueWbQ (0-11m) & apsOixVZlf1 (Female). If we do this down the line we will have 8 possibles.

  3. Query the entire list of optionCombos ever associated with that Catcombo
    https://apps.dhis2.org/demo/api/categoryOptionCombos.xml?paging=false&fields=name,id,categoryCombo[name,id],categoryOptions[name,id]&filter=categoryCombo.id:eq:t3aNCvHsoSn

  4. Parse through the results looking for an optionCombo with the two UIDs above

Inline image 2

  1. Once found, note the id, use for whatever you needed it for, repeat for all possible combinations.

When you are done, you will have a list of 8 “current” CatOptionCombos vs, all 12 that the API initially reports. I don’t know if the dataentry module has hooks into the database to run direct SQL, but if not, something very similar must be happening via the API and Java. Sounds like a blue print opportunity to have a Boolean for “current” optionCombos to skip all 5 steps above and have the results immediately. It would certainly be useful for all the apps that are being written to not have to do this process every time they want to pull down a data entry page.

Changing category combos after the fact usually leads to all sorts of problems

This is a normal use case during configuration work though. You might need to make many changes before you are happy with the final product. Should there be some kind of warning on the Category Combo creator warning not to edit existing combinations?

I appreciate you talking this out with me, I was hoping to find some sort of “trick” to get to the end of the process quicker, but I guess once written once, with proper caching, it is only a few API calls.

image

···

On Mon, Nov 16, 2015 at 11:52 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,

I think the short answer to your question is there is no API to tell you this directly. The database does not version category option combos, so there is no real way to know what is historical versus what is current. In your particular case, you might be able to leverage the “created” time stamp, which differs between the two sets of dis-aggregations, but again, you might have to make some assumptions there.

I think the design, as it is at the moment, is not how the category option combos are intended to be used, because you have overlapping options, namely 12-59m contains “12-59m, Male” and “12-59m Female”. If all three of these are reported (which they should be from the data entry form looking at the design), you will end up with duplicates in the analytics. Category option combos should be exclusive, and not overlap.

So, I think what should have been done in this case, is to create a totally new category combo, and then change the data element’s disaggregation to the new combo. Changing category combos after the fact usually leads to all sorts of problems, but creating new ones is pretty easy. DHIS2 will not track this change, but you could implement a trigger function on “dataelement” to audit when a category combo changes for a given data element, to give you a historical view of the data element, and how its disaggregations may have changed over time. But, there is no API in DHIS2 which would do this for you.

Regards,

Jason

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007

On Mon, Nov 16, 2015 at 8:17 PM, Timothy Harding tharding@baosystems.com wrote:

Hello DHIS2 Devs!

API question for the API Gurus:

During configuration on a recent project, maybe dis-aggregation groups were changed and updated after their Category Option Combos were initially generated. They do not have data associated with them, but I think that is beside the point for my question. Even if they did have data, I’m asking for a why to get the “current” CatOptCombos, rather than “every” CatOptCombo that has ever been assigned to this Catcombo.

Using the demo server, say I change:
Morbidity Cases

to
Morbidity Cases + Gender

https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn

The API response for this one will go from:


0-11m


15y+


12-59m


5-14y

To:


0-11m, Female


12-59m


0-11m, Male


5-14y, Female


15y+, Female


12-59m, Female


12-59m, Male


5-14y


0-11m


15y+


5-14y, Male


15y+, Male

So rightly, it retains its older CatOptCombos, because of course there might be data associated with those in the datavalue table. So I’m not looking to prune these. Without writing something to crawl through the api to figure out the “current” state, is there an existing API call that can do this? I know there must be, or someone has done it in JAVA because the data entry forms all can tell the “current” dis-aggregations without issue. Thanks and let me know if I can clarify my ask any better.

Cheers!

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

So, I guess the question in this case is why are the other ones not showing up when they should? This is in fact not what should happen I think, but a dev who is familiar with the section forms could answer why this is the case. I can see no reason from the database perspective how DHIS2 might be able to “know” what is current versus what is new.

Did you try a custom data entry form? Are the “old” ones still available?

I am pretty certain it is not intentional, and if the catcombos are still there, you can still enter/import data against them (which is likely not intentional).

Also, Did you regenerate your category combos after making changes through the maintenance functionality?

Regards,

Jason

image

image

image

image

···

On Tue, Nov 17, 2015 at 3:15 PM, Timothy Harding tharding@baosystems.com wrote:

Thanks Jason,

It is unfortunate there is no turnkey. I’ve added a few responses inline:

  • I think the design, as it is at the moment, is not how the category option combos are intended to be used, because you have overlapping options*

Yup, this is an artifact of changing a catcombo, never will someone be reporting to all 12, just the 8 “current” ones.

there is no real way to know what is historical versus what is current

There has to be, because the data entry forms all adjust to the “current” after you’ve edited the catcombo. The older ones are there just in case someone has entered data towards them. If we check the data entry page for an element using the above catcombo, we find only the ‘newest’:

Before:

After:

Instead of 12 CatOptCombos (the 8 “current” ones and 4 old ones) we only see the 8 current ones. If you look at the catcombo in the API, however, it will still show all 12. This makes sense, because there could be back data associated with the previous catoptioncombos.

So the only way I can figure to do this with a purely API approach is to:

  1. Ask a Cat Combo what Options are assigned to it’s categories
    https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn.xml?fields=name,id,categories[name,categoryOptions]

















  1. Multiply the categories together, Example: FbLZS3ueWbQ (0-11m) & apsOixVZlf1 (Female). If we do this down the line we will have 8 possibles.
  1. Query the entire list of optionCombos ever associated with that Catcombo
    https://apps.dhis2.org/demo/api/categoryOptionCombos.xml?paging=false&fields=name,id,categoryCombo[name,id],categoryOptions[name,id]&filter=categoryCombo.id:eq:t3aNCvHsoSn
  1. Parse through the results looking for an optionCombo with the two UIDs above
  1. Once found, note the id, use for whatever you needed it for, repeat for all possible combinations.

When you are done, you will have a list of 8 “current” CatOptionCombos vs, all 12 that the API initially reports. I don’t know if the dataentry module has hooks into the database to run direct SQL, but if not, something very similar must be happening via the API and Java. Sounds like a blue print opportunity to have a Boolean for “current” optionCombos to skip all 5 steps above and have the results immediately. It would certainly be useful for all the apps that are being written to not have to do this process every time they want to pull down a data entry page.

Changing category combos after the fact usually leads to all sorts of problems

This is a normal use case during configuration work though. You might need to make many changes before you are happy with the final product. Should there be some kind of warning on the Category Combo creator warning not to edit existing combinations?

I appreciate you talking this out with me, I was hoping to find some sort of “trick” to get to the end of the process quicker, but I guess once written once, with proper caching, it is only a few API calls.

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007

On Mon, Nov 16, 2015 at 11:52 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,

I think the short answer to your question is there is no API to tell you this directly. The database does not version category option combos, so there is no real way to know what is historical versus what is current. In your particular case, you might be able to leverage the “created” time stamp, which differs between the two sets of dis-aggregations, but again, you might have to make some assumptions there.

I think the design, as it is at the moment, is not how the category option combos are intended to be used, because you have overlapping options, namely 12-59m contains “12-59m, Male” and “12-59m Female”. If all three of these are reported (which they should be from the data entry form looking at the design), you will end up with duplicates in the analytics. Category option combos should be exclusive, and not overlap.

So, I think what should have been done in this case, is to create a totally new category combo, and then change the data element’s disaggregation to the new combo. Changing category combos after the fact usually leads to all sorts of problems, but creating new ones is pretty easy. DHIS2 will not track this change, but you could implement a trigger function on “dataelement” to audit when a category combo changes for a given data element, to give you a historical view of the data element, and how its disaggregations may have changed over time. But, there is no API in DHIS2 which would do this for you.

Regards,

Jason

On Mon, Nov 16, 2015 at 8:17 PM, Timothy Harding tharding@baosystems.com wrote:

Hello DHIS2 Devs!

API question for the API Gurus:

During configuration on a recent project, maybe dis-aggregation groups were changed and updated after their Category Option Combos were initially generated. They do not have data associated with them, but I think that is beside the point for my question. Even if they did have data, I’m asking for a why to get the “current” CatOptCombos, rather than “every” CatOptCombo that has ever been assigned to this Catcombo.

Using the demo server, say I change:
Morbidity Cases

to
Morbidity Cases + Gender

https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn

The API response for this one will go from:


0-11m


15y+


12-59m


5-14y

To:


0-11m, Female


12-59m


0-11m, Male


5-14y, Female


15y+, Female


12-59m, Female


12-59m, Male


5-14y


0-11m


15y+


5-14y, Male


15y+, Male

So rightly, it retains its older CatOptCombos, because of course there might be data associated with those in the datavalue table. So I’m not looking to prune these. Without writing something to crawl through the api to figure out the “current” state, is there an existing API call that can do this? I know there must be, or someone has done it in JAVA because the data entry forms all can tell the “current” dis-aggregations without issue. Thanks and let me know if I can clarify my ask any better.

Cheers!

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

It looks to me like a consequence of the fact that from a given categorycombo there are two ways to derive the underlying categoryoptioncombos.

The “direct” way, which is almost certainly what the rest api call does, is to look to the relation between categorycombo and categoryoptioncombo (the categorycombo_optioncombos table in the database). And it finds the 12 culprits you mention.

The indirect way, which is probably being used by the section form, looks first for the categories in the categorycombo (it needs to do this to layout the form correctly) and then finds the categoryoptions within those categories. These are then used to look up the categoryoptioncombo through the categoryoptioncombos_categoryoptions database table. This is the route you then followed in your explanation.

So starting from the same categorycombo you have two ways to get the categoryoptioncombos, each utilizing completely different database tables for the lookup (that is why in the past i have described this relationship as a lattice, rather than say a tree). In “theory” they should arrive at the same result, but I think your example shows that there are cases when they don’t. Though in fact there is no theoretical reason that they should agree as their is no relationship between the two routes at the database level.

My guess is the 12 catoptcombos were generated once upon a time. Then 4 of the categoryoptions were removed from the participating categories, but not deleted. So following the first lookup route you get all 12. Following the second you get 8.

What maybe should have happened was that the category edit handler code should have removed (or attempted to remove) the redundant categoryoptioncombos. If not it is maybe something of a bug - though as you point out there might be some merit in keeping history, I don’t think that is by design. I suspect that if the categoryoptions themselves were deleted this would trigger the deletion of the catoptcombos. If they are allowed to lie around, orphaned, then you can have this inconsistency.

Bob

PS. At the risk of resurrecting a very old discussion …

Perhaps the underlying flaw/complication is that the same categoryoption can appear in multiple unrestricted categories. This is what makes implementing consistency difficult, despite a bundle of java code trying to apply governance to the database. There is a solution to that, but it requires some remodelling.

image

image

···

On 17 November 2015 at 14:33, Jason Pickering jason.p.pickering@gmail.com wrote:

So, I guess the question in this case is why are the other ones not showing up when they should? This is in fact not what should happen I think, but a dev who is familiar with the section forms could answer why this is the case. I can see no reason from the database perspective how DHIS2 might be able to “know” what is current versus what is new.

Did you try a custom data entry form? Are the “old” ones still available?

I am pretty certain it is not intentional, and if the catcombos are still there, you can still enter/import data against them (which is likely not intentional).

Also, Did you regenerate your category combos after making changes through the maintenance functionality?

Regards,

Jason


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

On Tue, Nov 17, 2015 at 3:15 PM, Timothy Harding tharding@baosystems.com wrote:

Thanks Jason,

It is unfortunate there is no turnkey. I’ve added a few responses inline:

  • I think the design, as it is at the moment, is not how the category option combos are intended to be used, because you have overlapping options*

Yup, this is an artifact of changing a catcombo, never will someone be reporting to all 12, just the 8 “current” ones.

there is no real way to know what is historical versus what is current

There has to be, because the data entry forms all adjust to the “current” after you’ve edited the catcombo. The older ones are there just in case someone has entered data towards them. If we check the data entry page for an element using the above catcombo, we find only the ‘newest’:

Before:

After:

Instead of 12 CatOptCombos (the 8 “current” ones and 4 old ones) we only see the 8 current ones. If you look at the catcombo in the API, however, it will still show all 12. This makes sense, because there could be back data associated with the previous catoptioncombos.

So the only way I can figure to do this with a purely API approach is to:

  1. Ask a Cat Combo what Options are assigned to it’s categories
    https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn.xml?fields=name,id,categories[name,categoryOptions]

















  1. Multiply the categories together, Example: FbLZS3ueWbQ (0-11m) & apsOixVZlf1 (Female). If we do this down the line we will have 8 possibles.
  1. Query the entire list of optionCombos ever associated with that Catcombo
    https://apps.dhis2.org/demo/api/categoryOptionCombos.xml?paging=false&fields=name,id,categoryCombo[name,id],categoryOptions[name,id]&filter=categoryCombo.id:eq:t3aNCvHsoSn
  1. Parse through the results looking for an optionCombo with the two UIDs above
  1. Once found, note the id, use for whatever you needed it for, repeat for all possible combinations.

When you are done, you will have a list of 8 “current” CatOptionCombos vs, all 12 that the API initially reports. I don’t know if the dataentry module has hooks into the database to run direct SQL, but if not, something very similar must be happening via the API and Java. Sounds like a blue print opportunity to have a Boolean for “current” optionCombos to skip all 5 steps above and have the results immediately. It would certainly be useful for all the apps that are being written to not have to do this process every time they want to pull down a data entry page.

Changing category combos after the fact usually leads to all sorts of problems

This is a normal use case during configuration work though. You might need to make many changes before you are happy with the final product. Should there be some kind of warning on the Category Combo creator warning not to edit existing combinations?

I appreciate you talking this out with me, I was hoping to find some sort of “trick” to get to the end of the process quicker, but I guess once written once, with proper caching, it is only a few API calls.


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007

On Mon, Nov 16, 2015 at 11:52 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,

I think the short answer to your question is there is no API to tell you this directly. The database does not version category option combos, so there is no real way to know what is historical versus what is current. In your particular case, you might be able to leverage the “created” time stamp, which differs between the two sets of dis-aggregations, but again, you might have to make some assumptions there.

I think the design, as it is at the moment, is not how the category option combos are intended to be used, because you have overlapping options, namely 12-59m contains “12-59m, Male” and “12-59m Female”. If all three of these are reported (which they should be from the data entry form looking at the design), you will end up with duplicates in the analytics. Category option combos should be exclusive, and not overlap.

So, I think what should have been done in this case, is to create a totally new category combo, and then change the data element’s disaggregation to the new combo. Changing category combos after the fact usually leads to all sorts of problems, but creating new ones is pretty easy. DHIS2 will not track this change, but you could implement a trigger function on “dataelement” to audit when a category combo changes for a given data element, to give you a historical view of the data element, and how its disaggregations may have changed over time. But, there is no API in DHIS2 which would do this for you.

Regards,

Jason

On Mon, Nov 16, 2015 at 8:17 PM, Timothy Harding tharding@baosystems.com wrote:

Hello DHIS2 Devs!

API question for the API Gurus:

During configuration on a recent project, maybe dis-aggregation groups were changed and updated after their Category Option Combos were initially generated. They do not have data associated with them, but I think that is beside the point for my question. Even if they did have data, I’m asking for a why to get the “current” CatOptCombos, rather than “every” CatOptCombo that has ever been assigned to this Catcombo.

Using the demo server, say I change:
Morbidity Cases

to
Morbidity Cases + Gender

https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn

The API response for this one will go from:


0-11m


15y+


12-59m


5-14y

To:


0-11m, Female


12-59m


0-11m, Male


5-14y, Female


15y+, Female


12-59m, Female


12-59m, Male


5-14y


0-11m


15y+


5-14y, Male


15y+, Male

So rightly, it retains its older CatOptCombos, because of course there might be data associated with those in the datavalue table. So I’m not looking to prune these. Without writing something to crawl through the api to figure out the “current” state, is there an existing API call that can do this? I know there must be, or someone has done it in JAVA because the data entry forms all can tell the “current” dis-aggregations without issue. Thanks and let me know if I can clarify my ask any better.

Cheers!

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Thanks Bob and Jason!

I had suspicions of a dichotomy in the DB surrounding the CatOptionCombos, but the confirmation is extremely helpful, I appreciate the walkthrough Bob.

  • What maybe should have happened was that the category edit handler code should have removed (or attempted to remove) the redundant categoryoptioncombos. If not it is maybe something of a bug - though as you point out there might be some merit in keeping history, I don’t think that is by design. I suspect that if the categoryoptions themselves were deleted this would trigger the deletion of the catoptcombos. If they are allowed to lie around, orphaned, then you can have this inconsistency.*

I will investigate this more and report back. During initial config we were making updates to catcombos left and right, so some have many extras that are no longer needed. I can’t verify if they did or did not have test data in them at the time of the change, (I suspect no) so that will take some testing on my part to see how it works. Pruning will only go so far though, as there might be older data associated with a catcombo. As Jason noted this is frowned upon (making a change to a production level catcombo), the system currently allows it, so it is bound to happen.

Did you regenerate your category combos after making changes through the maintenance functionality?

Every time! :smiley:

I was able to make a single statement that gives all the necessary information to ‘build & match’ these catOptionCombos, then it is just a few loops of whatever language is preferred:

https://apps.dhis2.org/demo/api/categoryCombos.xml?paging=false&fields=name,id,categoryOptionCombos[id,name,categoryOptions[name,id]],categories[name,id,categoryOptions[name,id]]

You could probably even back this up further, to the data element, or even dataset level to get a snapshot of how to build your form, the API is wonderfully flexible in this regard.

Thanks for walking me though this!

image

image

···

On Tue, Nov 17, 2015 at 9:52 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

It looks to me like a consequence of the fact that from a given categorycombo there are two ways to derive the underlying categoryoptioncombos.

The “direct” way, which is almost certainly what the rest api call does, is to look to the relation between categorycombo and categoryoptioncombo (the categorycombo_optioncombos table in the database). And it finds the 12 culprits you mention.

The indirect way, which is probably being used by the section form, looks first for the categories in the categorycombo (it needs to do this to layout the form correctly) and then finds the categoryoptions within those categories. These are then used to look up the categoryoptioncombo through the categoryoptioncombos_categoryoptions database table. This is the route you then followed in your explanation.

So starting from the same categorycombo you have two ways to get the categoryoptioncombos, each utilizing completely different database tables for the lookup (that is why in the past i have described this relationship as a lattice, rather than say a tree). In “theory” they should arrive at the same result, but I think your example shows that there are cases when they don’t. Though in fact there is no theoretical reason that they should agree as their is no relationship between the two routes at the database level.

My guess is the 12 catoptcombos were generated once upon a time. Then 4 of the categoryoptions were removed from the participating categories, but not deleted. So following the first lookup route you get all 12. Following the second you get 8.

What maybe should have happened was that the category edit handler code should have removed (or attempted to remove) the redundant categoryoptioncombos. If not it is maybe something of a bug - though as you point out there might be some merit in keeping history, I don’t think that is by design. I suspect that if the categoryoptions themselves were deleted this would trigger the deletion of the catoptcombos. If they are allowed to lie around, orphaned, then you can have this inconsistency.

Bob

PS. At the risk of resurrecting a very old discussion …

Perhaps the underlying flaw/complication is that the same categoryoption can appear in multiple unrestricted categories. This is what makes implementing consistency difficult, despite a bundle of java code trying to apply governance to the database. There is a solution to that, but it requires some remodelling.

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007

On 17 November 2015 at 14:33, Jason Pickering jason.p.pickering@gmail.com wrote:

So, I guess the question in this case is why are the other ones not showing up when they should? This is in fact not what should happen I think, but a dev who is familiar with the section forms could answer why this is the case. I can see no reason from the database perspective how DHIS2 might be able to “know” what is current versus what is new.

Did you try a custom data entry form? Are the “old” ones still available?

I am pretty certain it is not intentional, and if the catcombos are still there, you can still enter/import data against them (which is likely not intentional).

Also, Did you regenerate your category combos after making changes through the maintenance functionality?

Regards,

Jason


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

On Tue, Nov 17, 2015 at 3:15 PM, Timothy Harding tharding@baosystems.com wrote:

Thanks Jason,

It is unfortunate there is no turnkey. I’ve added a few responses inline:

  • I think the design, as it is at the moment, is not how the category option combos are intended to be used, because you have overlapping options*

Yup, this is an artifact of changing a catcombo, never will someone be reporting to all 12, just the 8 “current” ones.

there is no real way to know what is historical versus what is current

There has to be, because the data entry forms all adjust to the “current” after you’ve edited the catcombo. The older ones are there just in case someone has entered data towards them. If we check the data entry page for an element using the above catcombo, we find only the ‘newest’:

Before:

After:

Instead of 12 CatOptCombos (the 8 “current” ones and 4 old ones) we only see the 8 current ones. If you look at the catcombo in the API, however, it will still show all 12. This makes sense, because there could be back data associated with the previous catoptioncombos.

So the only way I can figure to do this with a purely API approach is to:

  1. Ask a Cat Combo what Options are assigned to it’s categories
    https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn.xml?fields=name,id,categories[name,categoryOptions]

















  1. Multiply the categories together, Example: FbLZS3ueWbQ (0-11m) & apsOixVZlf1 (Female). If we do this down the line we will have 8 possibles.
  1. Query the entire list of optionCombos ever associated with that Catcombo
    https://apps.dhis2.org/demo/api/categoryOptionCombos.xml?paging=false&fields=name,id,categoryCombo[name,id],categoryOptions[name,id]&filter=categoryCombo.id:eq:t3aNCvHsoSn
  1. Parse through the results looking for an optionCombo with the two UIDs above
  1. Once found, note the id, use for whatever you needed it for, repeat for all possible combinations.

When you are done, you will have a list of 8 “current” CatOptionCombos vs, all 12 that the API initially reports. I don’t know if the dataentry module has hooks into the database to run direct SQL, but if not, something very similar must be happening via the API and Java. Sounds like a blue print opportunity to have a Boolean for “current” optionCombos to skip all 5 steps above and have the results immediately. It would certainly be useful for all the apps that are being written to not have to do this process every time they want to pull down a data entry page.

Changing category combos after the fact usually leads to all sorts of problems

This is a normal use case during configuration work though. You might need to make many changes before you are happy with the final product. Should there be some kind of warning on the Category Combo creator warning not to edit existing combinations?

I appreciate you talking this out with me, I was hoping to find some sort of “trick” to get to the end of the process quicker, but I guess once written once, with proper caching, it is only a few API calls.


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007

On Mon, Nov 16, 2015 at 11:52 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,

I think the short answer to your question is there is no API to tell you this directly. The database does not version category option combos, so there is no real way to know what is historical versus what is current. In your particular case, you might be able to leverage the “created” time stamp, which differs between the two sets of dis-aggregations, but again, you might have to make some assumptions there.

I think the design, as it is at the moment, is not how the category option combos are intended to be used, because you have overlapping options, namely 12-59m contains “12-59m, Male” and “12-59m Female”. If all three of these are reported (which they should be from the data entry form looking at the design), you will end up with duplicates in the analytics. Category option combos should be exclusive, and not overlap.

So, I think what should have been done in this case, is to create a totally new category combo, and then change the data element’s disaggregation to the new combo. Changing category combos after the fact usually leads to all sorts of problems, but creating new ones is pretty easy. DHIS2 will not track this change, but you could implement a trigger function on “dataelement” to audit when a category combo changes for a given data element, to give you a historical view of the data element, and how its disaggregations may have changed over time. But, there is no API in DHIS2 which would do this for you.

Regards,

Jason

On Mon, Nov 16, 2015 at 8:17 PM, Timothy Harding tharding@baosystems.com wrote:

Hello DHIS2 Devs!

API question for the API Gurus:

During configuration on a recent project, maybe dis-aggregation groups were changed and updated after their Category Option Combos were initially generated. They do not have data associated with them, but I think that is beside the point for my question. Even if they did have data, I’m asking for a why to get the “current” CatOptCombos, rather than “every” CatOptCombo that has ever been assigned to this Catcombo.

Using the demo server, say I change:
Morbidity Cases

to
Morbidity Cases + Gender

https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn

The API response for this one will go from:


0-11m


15y+


12-59m


5-14y

To:


0-11m, Female


12-59m


0-11m, Male


5-14y, Female


15y+, Female


12-59m, Female


12-59m, Male


5-14y


0-11m


15y+


5-14y, Male


15y+, Male

So rightly, it retains its older CatOptCombos, because of course there might be data associated with those in the datavalue table. So I’m not looking to prune these. Without writing something to crawl through the api to figure out the “current” state, is there an existing API call that can do this? I know there must be, or someone has done it in JAVA because the data entry forms all can tell the “current” dis-aggregations without issue. Thanks and let me know if I can clarify my ask any better.

Cheers!

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 404, Washington D.C. 20007


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049