Remove a full level from the pyramid

Hello,
We have a DHIS2 instance we support that already has data (Health Pyramid, Data Elements, DataValues). The system takes into account a level that is actually not used by most people and more importantly not by systems we need to integrate with.

On a business side, it make sense removing it altogether - I’m wondering if someone already did something like that and how ?

I can imagine something such as (supposing last level is 4 and we remove level 3):

  • Check any data linked to Org Unit of level 3 and backup/delete

  • Reattach all org unit of level 4 to their grand parent (level 2)

  • Delete level 3 entities

  • Regenerate or update the level 4 to 3

I suppose this would be easier done in the DB than with the API.

Anyone with experience or advice on a process like that ?

Thanks,

Martin

···

**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com

Hi Martin,

I do not think we have anything specifically for that, but I think you could write a short script

  1. Reassign the direct descendants of the level you want to remove to the desired level. This could be done through the API or SQL I guess.

  2. Use this script to remove the level. You would need to loop through all of the levels to remove of course.

  3. Clear the cache and rebuild the orgunit paths.

Regards,

Jason

···

On Mon, Sep 18, 2017 at 1:22 PM, Martin Van Aken martin@joyouscoding.com wrote:

Hello,
We have a DHIS2 instance we support that already has data (Health Pyramid, Data Elements, DataValues). The system takes into account a level that is actually not used by most people and more importantly not by systems we need to integrate with.

On a business side, it make sense removing it altogether - I’m wondering if someone already did something like that and how ?

I can imagine something such as (supposing last level is 4 and we remove level 3):

  • Check any data linked to Org Unit of level 3 and backup/delete
  • Reattach all org unit of level 4 to their grand parent (level 2)
  • Delete level 3 entities
  • Regenerate or update the level 4 to 3

I suppose this would be easier done in the DB than with the API.

Anyone with experience or advice on a process like that ?

Thanks,

Martin

**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com


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. What I don’t know is how the OrgUnitLevel would react - we’ll need to update those too. I think they are linked through a simple number (and not a foreign key to the level table), but I may be wrong.

The script you shared is about deleting all data linked to a specific org unit, right ? Not sure how it related to levels.

Martin

···

On Mon, Sep 18, 2017 at 2:37 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Martin,

I do not think we have anything specifically for that, but I think you could write a short script

  1. Reassign the direct descendants of the level you want to remove to the desired level. This could be done through the API or SQL I guess.
  1. Use this script to remove the level. You would need to loop through all of the levels to remove of course.
  1. Clear the cache and rebuild the orgunit paths.

Regards,

Jason

On Mon, Sep 18, 2017 at 1:22 PM, Martin Van Aken martin@joyouscoding.com wrote:

Hello,
We have a DHIS2 instance we support that already has data (Health Pyramid, Data Elements, DataValues). The system takes into account a level that is actually not used by most people and more importantly not by systems we need to integrate with.

On a business side, it make sense removing it altogether - I’m wondering if someone already did something like that and how ?

I can imagine something such as (supposing last level is 4 and we remove level 3):

  • Check any data linked to Org Unit of level 3 and backup/delete
  • Reattach all org unit of level 4 to their grand parent (level 2)
  • Delete level 3 entities
  • Regenerate or update the level 4 to 3

I suppose this would be easier done in the DB than with the API.

Anyone with experience or advice on a process like that ?

Thanks,

Martin

**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com


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

**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com

Don’t worry about the level. This should be regenerated when you regenerate the path, but you better check. Otherwise, it should be pretty simple to assign this with SQL.

Where the delete_orgunit_with_data function comes in may be more apparent from the following example which should move all children of Badjia chiefdom up a level and then deletes Badjia (from the Sierra Leone DB). I did not include the loop here.

…Loop over all leaf nodes and move them up a level.

UPDATE organisationunit

SET parentid = (SELECT organisationunitid from organisationunit

WHERE path = ‘/ImspTQPwCqd/O6uvpzGd5pu’)

WHERE path ~(’/ImspTQPwCqd/O6uvpzGd5pu/YuQRtpLP10I’)

AND hierarchylevel = 4;

… Delete the Badjia which no longer has any children.

sierra-leone=# SELECT * FROM delete_orgunit_with_data(‘YuQRtpLP10I’);

delete_orgunit_with_data

···

On Sep 18, 2017 15:10, “Martin Van Aken” martin@joyouscoding.com wrote:

Thanks. What I don’t know is how the OrgUnitLevel would react - we’ll need to update those too. I think they are linked through a simple number (and not a foreign key to the level table), but I may be wrong.

The script you shared is about deleting all data linked to a specific org unit, right ? Not sure how it related to levels.

Martin

On Mon, Sep 18, 2017 at 2:37 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Martin,

I do not think we have anything specifically for that, but I think you could write a short script

  1. Reassign the direct descendants of the level you want to remove to the desired level. This could be done through the API or SQL I guess.
  1. Use this script to remove the level. You would need to loop through all of the levels to remove of course.
  1. Clear the cache and rebuild the orgunit paths.

Regards,

Jason


**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com

On Mon, Sep 18, 2017 at 1:22 PM, Martin Van Aken martin@joyouscoding.com wrote:

Hello,
We have a DHIS2 instance we support that already has data (Health Pyramid, Data Elements, DataValues). The system takes into account a level that is actually not used by most people and more importantly not by systems we need to integrate with.

On a business side, it make sense removing it altogether - I’m wondering if someone already did something like that and how ?

I can imagine something such as (supposing last level is 4 and we remove level 3):

  • Check any data linked to Org Unit of level 3 and backup/delete
  • Reattach all org unit of level 4 to their grand parent (level 2)
  • Delete level 3 entities
  • Regenerate or update the level 4 to 3

I suppose this would be easier done in the DB than with the API.

Anyone with experience or advice on a process like that ?

Thanks,

Martin

**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com


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 a lot, that clarify it. When you say the level should be regenerated, is this some kind of SQL trigger or it is a check/update the app does itself on start ? I was thinking to update those manually too (does not looks like a lot).

Your script is actually handy to already check any link between the org unit to be deleted and the rest of the application - I suppose your list of delete should be all links there, so I can use it as a check.

Martin

···

On Mon, Sep 18, 2017 at 3:35 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Don’t worry about the level. This should be regenerated when you regenerate the path, but you better check. Otherwise, it should be pretty simple to assign this with SQL.

Where the delete_orgunit_with_data function comes in may be more apparent from the following example which should move all children of Badjia chiefdom up a level and then deletes Badjia (from the Sierra Leone DB). I did not include the loop here.

…Loop over all leaf nodes and move them up a level.

UPDATE organisationunit

SET parentid = (SELECT organisationunitid from organisationunit

WHERE path = ‘/ImspTQPwCqd/O6uvpzGd5pu’)

WHERE path ~(’/ImspTQPwCqd/O6uvpzGd5pu/YuQRtpLP10I’)

AND hierarchylevel = 4;

… Delete the Badjia which no longer has any children.

sierra-leone=# SELECT * FROM delete_orgunit_with_data(‘YuQRtpLP10I’);

delete_orgunit_with_data


1

(1 row)

That SQL would need to be tweaked a bit I think, but maybe its a bit clearer?

Regards,

Jason

On Sep 18, 2017 15:10, “Martin Van Aken” martin@joyouscoding.com wrote:

Thanks. What I don’t know is how the OrgUnitLevel would react - we’ll need to update those too. I think they are linked through a simple number (and not a foreign key to the level table), but I may be wrong.

The script you shared is about deleting all data linked to a specific org unit, right ? Not sure how it related to levels.

Martin

On Mon, Sep 18, 2017 at 2:37 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Martin,

I do not think we have anything specifically for that, but I think you could write a short script

  1. Reassign the direct descendants of the level you want to remove to the desired level. This could be done through the API or SQL I guess.
  1. Use this script to remove the level. You would need to loop through all of the levels to remove of course.
  1. Clear the cache and rebuild the orgunit paths.

Regards,

Jason


**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com

On Mon, Sep 18, 2017 at 1:22 PM, Martin Van Aken martin@joyouscoding.com wrote:

Hello,
We have a DHIS2 instance we support that already has data (Health Pyramid, Data Elements, DataValues). The system takes into account a level that is actually not used by most people and more importantly not by systems we need to integrate with.

On a business side, it make sense removing it altogether - I’m wondering if someone already did something like that and how ?

I can imagine something such as (supposing last level is 4 and we remove level 3):

  • Check any data linked to Org Unit of level 3 and backup/delete
  • Reattach all org unit of level 4 to their grand parent (level 2)
  • Delete level 3 entities
  • Regenerate or update the level 4 to 3

I suppose this would be easier done in the DB than with the API.

Anyone with experience or advice on a process like that ?

Thanks,

Martin

**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com


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

**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com

I mean that the level and path should be regenerated by DHIS2, and does not need to be generated by the SQL script. The path and level should be properties which are “figured out” by DHIS2 based on the position of the orgunit in the hierarchy.

···

On Mon, Sep 18, 2017 at 3:42 PM, Martin Van Aken martin@joyouscoding.com wrote:

Thanks a lot, that clarify it. When you say the level should be regenerated, is this some kind of SQL trigger or it is a check/update the app does itself on start ? I was thinking to update those manually too (does not looks like a lot).

Your script is actually handy to already check any link between the org unit to be deleted and the rest of the application - I suppose your list of delete should be all links there, so I can use it as a check.

Martin

On Mon, Sep 18, 2017 at 3:35 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Don’t worry about the level. This should be regenerated when you regenerate the path, but you better check. Otherwise, it should be pretty simple to assign this with SQL.

Where the delete_orgunit_with_data function comes in may be more apparent from the following example which should move all children of Badjia chiefdom up a level and then deletes Badjia (from the Sierra Leone DB). I did not include the loop here.

…Loop over all leaf nodes and move them up a level.

UPDATE organisationunit

SET parentid = (SELECT organisationunitid from organisationunit

WHERE path = ‘/ImspTQPwCqd/O6uvpzGd5pu’)

WHERE path ~(’/ImspTQPwCqd/O6uvpzGd5pu/YuQRtpLP10I’)

AND hierarchylevel = 4;

… Delete the Badjia which no longer has any children.

sierra-leone=# SELECT * FROM delete_orgunit_with_data(‘YuQRtpLP10I’);

delete_orgunit_with_data


1

(1 row)

That SQL would need to be tweaked a bit I think, but maybe its a bit clearer?

Regards,

Jason


**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com

On Sep 18, 2017 15:10, “Martin Van Aken” martin@joyouscoding.com wrote:

Thanks. What I don’t know is how the OrgUnitLevel would react - we’ll need to update those too. I think they are linked through a simple number (and not a foreign key to the level table), but I may be wrong.

The script you shared is about deleting all data linked to a specific org unit, right ? Not sure how it related to levels.

Martin

On Mon, Sep 18, 2017 at 2:37 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Martin,

I do not think we have anything specifically for that, but I think you could write a short script

  1. Reassign the direct descendants of the level you want to remove to the desired level. This could be done through the API or SQL I guess.
  1. Use this script to remove the level. You would need to loop through all of the levels to remove of course.
  1. Clear the cache and rebuild the orgunit paths.

Regards,

Jason


**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com

On Mon, Sep 18, 2017 at 1:22 PM, Martin Van Aken martin@joyouscoding.com wrote:

Hello,
We have a DHIS2 instance we support that already has data (Health Pyramid, Data Elements, DataValues). The system takes into account a level that is actually not used by most people and more importantly not by systems we need to integrate with.

On a business side, it make sense removing it altogether - I’m wondering if someone already did something like that and how ?

I can imagine something such as (supposing last level is 4 and we remove level 3):

  • Check any data linked to Org Unit of level 3 and backup/delete
  • Reattach all org unit of level 4 to their grand parent (level 2)
  • Delete level 3 entities
  • Regenerate or update the level 4 to 3

I suppose this would be easier done in the DB than with the API.

Anyone with experience or advice on a process like that ?

Thanks,

Martin

**Martin Van Aken - **Freelance Enthusiast Developer

Mobile : +32 486 899 652

Follow me on Twitter : @martinvanaken

Call me on Skype : vanakenm

Hang out with me : martin@joyouscoding.com

Contact me on LinkedIn : http://www.linkedin.com/in/martinvanaken

Company website : www.joyouscoding.com


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