Zeros on Pivot tabe and data set report

Good day,

I have recently upgraded from 2.31.9 to 2.36.0. I am now having issues with zero data values showing on data set report and pivot table like it did on previous version. Please assist where I could have done wrong. I have run the analytics as required. I have realized that 0 data values have been long left on version 2.32, if I am not wrong because I am sure in 2.33 there are not there. Other values are populated except the 0 data values.

Please assist

Another user was having a similar issue @tshidi.moroma so I’d like to start by asking this question so you’d review the settings,

Thanks for your response @Gassim

I’m on 2.36.0 an there is no option to Skip zero data values in analytics tables. But still even when unchecked on version 2.33 the zeros are not showing on Pivot table and on data set report.

Please bring back the old reports module. That module works fine. The discovery made on this new one from 2.33 it does not download custom data sets its only downloads basic data sets.

Hi @tshidi.moroma,

I am sorry you are experiencing this issue. I have tested the issue on play.dhis2.org/2.36.3 and I am not seeing the same issue regarding showing zeros. Please upgrade to 2.36.3 and let us know if that fixes the issue. You also need to upgrade to address a security concern in 2.36.0.

We also highly encourage to you begin to use the data visualizer application to make pivot tables. Starting in the 2.37 release the pivot tables app will no longer be supported as we have moved all of the functionality of that app into the data visualizer application.

Please let us know if the issue continues after you have upgraded to 2.36.3.

1 Like

Update: I see in this jira issue that the feature was removed in 2.36. I still wonder if there is a way to force zero values to show in pivot tables in 2.36 and wonder why the feature was removed.

Hi all… Can someone summarize the availability of the Skip zero data values in analytics tables? It looks like it existed in 2.35 but does not exist in 2.36? In 2.36.3 which we are testing, zeros do not appear in pivot tables (is this the default?) either via the “old” Pivot Tables app or the Data Visualizer app. Is there a way to change this setting in 2.36?

@LauraLincks and @tshidi.moroma
It is very unfortunate that sometimes the DHIS2 team does not think how that will affect before removing certain features!
0 and BLANK has 2 completely different meaning. 0 = something was reported as 0, and BLANK = I am not reporting the incident (or in some cases ‘non-reported’). So when the pivot draws - you definitely would like to know who reported 0 and who did not report.
We are in a same situation in 2.35, we have people reporting stock status end of month, some reported 0, which means they are stockout. And when we make the report it ignores those 0’s and make it blank!! Think about the consequence - you miss the most important indicator - stockout sites!!!

@Scott when you said it is behaving OK…did you put 0 as value in the element and came to see in the pivot report that it is showing that 0 instead of BLANK?

@Mahmud @LauraLincks @tshidi.moroma,

The skip zero values in analytics tables was intentionally removed for 2.36.
https://jira.dhis2.org/browse/DHIS2-10075

The reason it was removed its the huge benefit of skipping zero values in analytics. There are almost no situations where the trade-off between having zero values in analytics and the extra database size and generation time favors zeros. Zero values will still be stored and shown in the analytics if the data element is set to store zero values.

image

We certainly appreciate that for many data values there is a meaningful difference between a zero and a blank value. We have extensive support, trainings, documentation, etc, for how to monitor for zero values entered representing a stockout. You can find how to monitor for these here. Day 5: Predictors and Indicators with Scott - YouTube @GeorgeMcGuire could provide additional support if you need.

Regarding the bug, I have tested in DHIS 2 Demo - Sierra Leone both data elements that store zeros and as well as data elements to do not store zeros. I have seen the analytics showing zeros for the data elements that should store zeros and no zero values for data elements that should not be storing zero values.

Please provide us steps to reproduce this bug. I might be missing something.

Also again, you need to upgrade to 2.36.3. The 2.36.0 release has a major security issue that makes your instance vulnerable for a data breach.

All the best,
Scott

1 Like

@Scott
See the following data entry screen having one product reported as 0 balance, another one having value and the third one having no value.

Now see the pivot table:

The 0 is stored in database (no doubt about it), but it is not usable in reports and visualization. As of your earlier mails this is correct behavior, I understand.

This is 2.36.

Thank you everyone for the responses and explanations. On a test instance of the same database the instance have been upgraded to 2.36.3 and the same issue still persists.

We will upgrade the live instance to 2.36.3.

@Scott

Steps to replicate in Play 2.36.3 with Expenditures dataset:

  1. Change data elements to store zero values

  1. Enter data in the data set for several periods and complete them

  1. Run analytics

  1. in data visualizer select data elements, periods and org unit

  1. Update, hoping to see “0s” but it shows no data

We’ve encountered the same problem: stored zeros are not being displayed in dashboards. Has anyone found a workaround? Using DHIS 2.36.6.

1 Like

@Scott We have two clients with planned upgrades to 2.36 from 2.34 and 2.32. This removal affects some of their monitoring and evaluation pivot tables.

It is really important for data quality to know if a reported value was 0 or Empty but unfortunately it is impossible to determine the difference now.

Also trying to set up an indicator as a workaround does not work, the isNull/isNotNull check returns the same for 0s and nulls.

And there’s a bug with indicators not being properly evaluated if the stored value is 0. For example an indicator with formula “#{DE} + 1” shows as a blank instead of outputting 1 if the stored value is a 0.

1 Like

We’ve just opened Log in - DHIS 2 JIRA and we will raise awareness internally of the bug (indicators that rely on values that can be zero cannot be trusted).

Although having an important feature (being able to differentiate a 0 from an empty) removed without notice might push back some of our planned upgrades.

We think the proper solution should check if the data element can store a 0, if that checkbox has been set on maintenance, analytics should use that check to also save 0s where needed.

This topic has been discussed extensively over the years and there are strong opinions on both sides. As @Scott mentions, there are clear advantages related to performance in not storing or processing zeros. In larger systems, we have seen up to 80% of all values stored being equal to zero. This has a very deleterious affect on data base and analytics performance, so implementers should very carefully weigh up the pros and cons when deciding whether to collect zeros.

The usual recommended approach is to make use of the “Complete” feature of data entry screens. When there is a clear shared understanding of what this means, there should be no need to explicitly enter zeros. If users understand that by pressing the “Complete” button, all blanks can safely be assumed to be zero, then there is no need to store them.

Again, I will not try and repeat all of the discussion which we have seen on this topic, but just did want to add that the utilization of data set completeness features of DHIS2 should be considered as an alternative.

Unfortunately, even with completion that’s not the case for our monitoring and evaluation, where a blank and a 0 mean different things (if the data element metadata has that 0 values should be persisted together with the audit trail logs).

We believe that in those cases, the implementers have not properly configured their data elements metadata regarding storing 0 values.

In any case, before 2.36 with the global setting, we could achieve both outcomes. Right now with that setting removed it is impossible to work with the data in the analytics for 2.36. What was the reasoning to remove this setting? In case they were public, could you share with us those discussions?

Like I said, there are strong opinions on both sides of the fence here. :slight_smile:

I think this is the important note here from @Scott :

Zero values will still be stored and shown in the analytics if the data element is set to store zero values.

If that is not working as it should, then it sounds like its a bug to me.

2 Likes

It definitely is not working after the system setting that allowed it to happen was removed starting from 2.36 (fix: Remove system setting for skip zeros in analytics tables [DHIS2-9902] by larshelge · Pull Request #6567 · dhis2/dhis2-core · GitHub)

3 Likes

@SferaDev

Thanks for raising the issue on Jira. I’m going to follow this up with the development team.

Kind regards,
Phil

2 Likes

Thanks @phil, actually there’re two things here.

  • The analytics global setting being removed and the inability to differentiate the 0s from blanks
  • The Jira we opened (related to the previous) that does not even evaluate indicators if the value is a 0

We identified the second bug trying to find a workaround to the system setting removal.

We believe the analytics for indicators use the analytics tables for data elements internally, and since the 0 values are not persisted anymore, they don’t get calculated.

I just want to give an update to anyone interested in this subject.

As has been mentioned above, the feature was originally removed as experience showed that many implementations stored zeros unnecessarily; resulting in a huge performance burden.

However, we realise that some of your implementations have use cases that need this feature, and for which there is no other suitable alternative. We have therefore decided to reintroduce the feature in future patch releases. The feature will skip zeros in analytics by default, but there will be an option to include them via the System Settings.

Again, for performance reasons, we caution against using this unless it is necessary for your use cases.

Thank you all for sharing your cases and following this up.

Kind regards,
Phil

3 Likes