Upgrade to 2.42. Query to view parameters for Category Configuration Limit options

Implementation - Implémentation
1

Hi all,

I thought this might be helpful to other DHIS2 users.
We are planning to upgrade a number of instances from 2.40 to 2.42 and need to check the limits we have for Category configurations which are now limited in 2.42 but can be set if you are needing to exceed them.

It is quite easy in 2.42 to know when you exceed the default limits as you will get a message as soon as your new configuration will exceed those limits but we needed to check that existing configurations do not exceed these limits.

So we created a SQL view which gives these numbers which might be useful for others or perhaps Oslo can consider displaying this somewhere in the system, e.g. in a tab under Categories in the new Maintenance app

Property Description Default
metadata.categories.max_options Maximum number of category options in a single category 50 (was 31 in v42.0)
metadata.categories.max_per_combo Maximum number of categories per category combination 5
metadata.categories.max_combinations Maximum number of possible category option combinations (computed as the product of all options across categories in a combo) 500 
SELECT 
  -- 1. Maximum number of Category Options in any single Category
  (
    SELECT COALESCE(MAX(option_count), 0)
    FROM (
      SELECT categoryid, COUNT(categoryoptionid) AS option_count
      FROM categories_categoryoptions
      GROUP BY categoryid
    ) AS cat_options
  ) AS max_categoryoptions_in_a_category,

  -- 2. Maximum number of Categories in any single Category Combo (catcombo)
  (
    SELECT COALESCE(MAX(category_count), 0)
    FROM (
      SELECT categorycomboid, COUNT(categoryid) AS category_count
      FROM categorycombos_categories
      GROUP BY categorycomboid
    ) AS combo_cats
  ) AS max_categories_in_a_catcombo,

  -- 3. Maximum number of Category Option Combos (COCs) in any single Category Combo
  (
    SELECT COALESCE(MAX(coc_count), 0)
    FROM (
      SELECT categorycomboid, COUNT(categoryoptioncomboid) AS coc_count
      FROM categorycombos_optioncombos
      GROUP BY categorycomboid
    ) AS combo_cocs
  ) AS max_catoptioncombos_in_a_catcombo;

I hope this is helpful to other DHIS2 users.

Regards
Elmarie

2

Thank you @Elmarie_Claasen for sharing this with the community! It’ll definitely be helpful for many.

Thanks for the suggestion. I’ll see if we can create a feature request for this or if there’s a part of the documentation that this can be included in.

Thanks!