Some clarifications for Program indicators

Support - Assistance technique
1

Hi dhis-devs

We are looking at doing some analysis for the Anonymous events. For which we are trying to set up some program indicators.

Currently the issue that we face is that the events that DHIS2 considers for calculations within a specific reporting period (week, month) in event reports/visualizer, are the ones whose event date is within that reporting period.

Example of the problem

Inpatient mortality rate

Relevant data elements: date of discharge, exit mode [death, discharged …]

Requirement:

  • Get all events where the discharge date was within the reporting period (all exits during period)

  • Get all events where the discharge date was within the reporting period and the exit mode was “death” (deaths during reporting period)

  • Divide deaths/all exits to get the mortality rate (indicator)

As an example, if a patient gets admitted in April 2016 and hence the event date is in the April 2016 reporting period. Now say the patient gets discharged in May 2016. But this exit will be calculated in the April 2016 reporting period due to considering the event date for analysis.

There is no direct way in DHIS where in we can match the reporting period to a date based data elements for filtering out data or also include them in indicators.

Any thoughts about how other users have solved these kind of issues would be much appreciated.

···

–
With Regards

Vanya
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

2

Hi devs and users

Would anyone with relevant experience in this, help us understand this better?

Regards

Vanya

···

On Tue, May 17, 2016 at 4:43 PM, Vanya Seth vanyas@thoughtworks.com wrote:

Hi dhis-devs

We are looking at doing some analysis for the Anonymous events. For which we are trying to set up some program indicators.

Currently the issue that we face is that the events that DHIS2 considers for calculations within a specific reporting period (week, month) in event reports/visualizer, are the ones whose event date is within that reporting period.

Example of the problem

Inpatient mortality rate

Relevant data elements: date of discharge, exit mode [death, discharged …]

Requirement:

  • Get all events where the discharge date was within the reporting period (all exits during period)
  • Get all events where the discharge date was within the reporting period and the exit mode was “death” (deaths during reporting period)
  • Divide deaths/all exits to get the mortality rate (indicator)

As an example, if a patient gets admitted in April 2016 and hence the event date is in the April 2016 reporting period. Now say the patient gets discharged in May 2016. But this exit will be calculated in the April 2016 reporting period due to considering the event date for analysis.

There is no direct way in DHIS where in we can match the reporting period to a date based data elements for filtering out data or also include them in indicators.

Any thoughts about how other users have solved these kind of issues would be much appreciated.

–
With Regards

Vanya
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

–

With Regards
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

3

Hi Vanya,

thanks for raising this issue - it’s a good question. Having program indicators based on a data element / attributes dates instead of event date has come up earlier and something we will have to support.

Short story: Currently this is not possible.

Long story: The current event analytics solution has a compromise between query performance and flexibility around dates. If you have a look at the event analytics tables (e.g. “analytics_event_2015_ebayegv0exc”) you will see two things:

  • The tables are partitioned by year (one analytics table with events per year per program) based on event date. This reduces query time since the indexes and tables become smaller instead of growing longer infinitely as times goes.

  • The tables have columns for all period types based on event date. This makes it possible to aggregate all periods in a query through group-bys on those columns, instead of having to do one query per period, and hence improves performance a lot.

All this is great for performance but limits the analysis to be based on event date. To partition data one needs a partition key and event date is pretty much the only suitable candidate (in addition to program).

So to support aggregation / analytics based on any date from data elements/attributes of the events, one must sacrifice some of this and basically have one analytics table per program, and have one query per aggregation period. This will soon be a very popular requirement so we will have to support it - still pondering how to solve this in a way which offers required performance and flexibility.

best regards,

Lars

···

On Tue, May 17, 2016 at 1:13 PM, Vanya Seth vanyas@thoughtworks.com wrote:

Hi dhis-devs

We are looking at doing some analysis for the Anonymous events. For which we are trying to set up some program indicators.

Currently the issue that we face is that the events that DHIS2 considers for calculations within a specific reporting period (week, month) in event reports/visualizer, are the ones whose event date is within that reporting period.

Example of the problem

Inpatient mortality rate

Relevant data elements: date of discharge, exit mode [death, discharged …]

Requirement:

  • Get all events where the discharge date was within the reporting period (all exits during period)
  • Get all events where the discharge date was within the reporting period and the exit mode was “death” (deaths during reporting period)
  • Divide deaths/all exits to get the mortality rate (indicator)

As an example, if a patient gets admitted in April 2016 and hence the event date is in the April 2016 reporting period. Now say the patient gets discharged in May 2016. But this exit will be calculated in the April 2016 reporting period due to considering the event date for analysis.

There is no direct way in DHIS where in we can match the reporting period to a date based data elements for filtering out data or also include them in indicators.

Any thoughts about how other users have solved these kind of issues would be much appreciated.

–
With Regards

Vanya
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

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

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

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

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

–

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

4

I have created a blueprint for this here:

https://blueprints.launchpad.net/dhis2/+spec/event-analytics-date-flexibility

Lars

···

On Mon, May 23, 2016 at 11:49 AM, Lars Helge Øverland <lars@dhis2.org> wrote:

Hi Vanya,

thanks for raising this issue - it's a good question. Having program
indicators based on a data element / attributes dates instead of event date
has come up earlier and something we will have to support.

Short story: Currently this is not possible.

Long story: The current event analytics solution has a compromise between
query performance and flexibility around dates. If you have a look at the
event analytics tables (e.g. "analytics_event_2015_ebayegv0exc") you will
see two things:

- The tables are partitioned by year (one analytics table with events per
year per program) based on event date. This reduces query time since the
indexes and tables become smaller instead of growing longer infinitely as
times goes.

- The tables have columns for all period types based on event date. This
makes it possible to aggregate all periods in a query through group-bys on
those columns, instead of having to do one query per period, and hence
improves performance a lot.

All this is great for performance but limits the analysis to be based on
event date. To partition data one needs a partition key and event date is
pretty much the only suitable candidate (in addition to program).

So to support aggregation / analytics based on any date from data
elements/attributes of the events, one must sacrifice some of this and
basically have one analytics table per program, and have one query per
aggregation period. This will soon be a very popular requirement so we will
have to support it - still pondering how to solve this in a way which
offers required performance and flexibility.

best regards,

Lars

5

Thanks a lot for the information Lars.

Really appreciate it.

Regards

Vanya

···

On Mon, May 23, 2016 at 3:19 PM, Lars Helge Øverland lars@dhis2.org wrote:

Hi Vanya,

thanks for raising this issue - it’s a good question. Having program indicators based on a data element / attributes dates instead of event date has come up earlier and something we will have to support.

Short story: Currently this is not possible.

Long story: The current event analytics solution has a compromise between query performance and flexibility around dates. If you have a look at the event analytics tables (e.g. “analytics_event_2015_ebayegv0exc”) you will see two things:

  • The tables are partitioned by year (one analytics table with events per year per program) based on event date. This reduces query time since the indexes and tables become smaller instead of growing longer infinitely as times goes.
  • The tables have columns for all period types based on event date. This makes it possible to aggregate all periods in a query through group-bys on those columns, instead of having to do one query per period, and hence improves performance a lot.

All this is great for performance but limits the analysis to be based on event date. To partition data one needs a partition key and event date is pretty much the only suitable candidate (in addition to program).

So to support aggregation / analytics based on any date from data elements/attributes of the events, one must sacrifice some of this and basically have one analytics table per program, and have one query per aggregation period. This will soon be a very popular requirement so we will have to support it - still pondering how to solve this in a way which offers required performance and flexibility.

best regards,

Lars

–

On Tue, May 17, 2016 at 1:13 PM, Vanya Seth vanyas@thoughtworks.com wrote:

Hi dhis-devs

We are looking at doing some analysis for the Anonymous events. For which we are trying to set up some program indicators.

Currently the issue that we face is that the events that DHIS2 considers for calculations within a specific reporting period (week, month) in event reports/visualizer, are the ones whose event date is within that reporting period.

Example of the problem

Inpatient mortality rate

Relevant data elements: date of discharge, exit mode [death, discharged …]

Requirement:

  • Get all events where the discharge date was within the reporting period (all exits during period)
  • Get all events where the discharge date was within the reporting period and the exit mode was “death” (deaths during reporting period)
  • Divide deaths/all exits to get the mortality rate (indicator)

As an example, if a patient gets admitted in April 2016 and hence the event date is in the April 2016 reporting period. Now say the patient gets discharged in May 2016. But this exit will be calculated in the April 2016 reporting period due to considering the event date for analysis.

There is no direct way in DHIS where in we can match the reporting period to a date based data elements for filtering out data or also include them in indicators.

Any thoughts about how other users have solved these kind of issues would be much appreciated.

–
With Regards

Vanya
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

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

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

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

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

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

–

With Regards
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

6

We would also look into some possibilities ourselves and get back if there’s something worth discussing.

Regards

Vanya

···

On Mon, May 23, 2016 at 3:58 PM, Vanya Seth vanyas@thoughtworks.com wrote:

Thanks a lot for the information Lars.

Really appreciate it.

Regards

Vanya

–

On Mon, May 23, 2016 at 3:19 PM, Lars Helge Øverland lars@dhis2.org wrote:

Hi Vanya,

thanks for raising this issue - it’s a good question. Having program indicators based on a data element / attributes dates instead of event date has come up earlier and something we will have to support.

Short story: Currently this is not possible.

Long story: The current event analytics solution has a compromise between query performance and flexibility around dates. If you have a look at the event analytics tables (e.g. “analytics_event_2015_ebayegv0exc”) you will see two things:

  • The tables are partitioned by year (one analytics table with events per year per program) based on event date. This reduces query time since the indexes and tables become smaller instead of growing longer infinitely as times goes.
  • The tables have columns for all period types based on event date. This makes it possible to aggregate all periods in a query through group-bys on those columns, instead of having to do one query per period, and hence improves performance a lot.

All this is great for performance but limits the analysis to be based on event date. To partition data one needs a partition key and event date is pretty much the only suitable candidate (in addition to program).

So to support aggregation / analytics based on any date from data elements/attributes of the events, one must sacrifice some of this and basically have one analytics table per program, and have one query per aggregation period. This will soon be a very popular requirement so we will have to support it - still pondering how to solve this in a way which offers required performance and flexibility.

best regards,

Lars

–
With Regards
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

On Tue, May 17, 2016 at 1:13 PM, Vanya Seth vanyas@thoughtworks.com wrote:

Hi dhis-devs

We are looking at doing some analysis for the Anonymous events. For which we are trying to set up some program indicators.

Currently the issue that we face is that the events that DHIS2 considers for calculations within a specific reporting period (week, month) in event reports/visualizer, are the ones whose event date is within that reporting period.

Example of the problem

Inpatient mortality rate

Relevant data elements: date of discharge, exit mode [death, discharged …]

Requirement:

  • Get all events where the discharge date was within the reporting period (all exits during period)
  • Get all events where the discharge date was within the reporting period and the exit mode was “death” (deaths during reporting period)
  • Divide deaths/all exits to get the mortality rate (indicator)

As an example, if a patient gets admitted in April 2016 and hence the event date is in the April 2016 reporting period. Now say the patient gets discharged in May 2016. But this exit will be calculated in the April 2016 reporting period due to considering the event date for analysis.

There is no direct way in DHIS where in we can match the reporting period to a date based data elements for filtering out data or also include them in indicators.

Any thoughts about how other users have solved these kind of issues would be much appreciated.

–
With Regards

Vanya
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

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

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

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

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

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

–

With Regards
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

7

We would also look into some possibilities ourselves and get back if
there's something worth discussing.

Thanks, that sounds great.

One option could be to ignore time partitioning completely. Another could
be to have an option for number of years to include for analysis. Let me
know if you come up with any bright new ideas :wink:

best regards,

Lars

···

On Mon, May 23, 2016 at 12:31 PM, Vanya Seth <vanyas@thoughtworks.com> wrote:

Regards
Vanya

On Mon, May 23, 2016 at 3:58 PM, Vanya Seth <vanyas@thoughtworks.com> > wrote:

Thanks a lot for the information Lars.

Really appreciate it.

Regards
Vanya

On Mon, May 23, 2016 at 3:19 PM, Lars Helge Øverland <lars@dhis2.org> >> wrote:

Hi Vanya,

thanks for raising this issue - it's a good question. Having program
indicators based on a data element / attributes dates instead of event date
has come up earlier and something we will have to support.

Short story: Currently this is not possible.

Long story: The current event analytics solution has a compromise
between query performance and flexibility around dates. If you have a look
at the event analytics tables (e.g. "analytics_event_2015_ebayegv0exc") you
will see two things:

- The tables are partitioned by year (one analytics table with events
per year per program) based on event date. This reduces query time since
the indexes and tables become smaller instead of growing longer infinitely
as times goes.

- The tables have columns for all period types based on event date. This
makes it possible to aggregate all periods in a query through group-bys on
those columns, instead of having to do one query per period, and hence
improves performance a lot.

All this is great for performance but limits the analysis to be based on
event date. To partition data one needs a partition key and event date is
pretty much the only suitable candidate (in addition to program).

So to support aggregation / analytics based on any date from data
elements/attributes of the events, one must sacrifice some of this and
basically have one analytics table per program, and have one query per
aggregation period. This will soon be a very popular requirement so we will
have to support it - still pondering how to solve this in a way which
offers required performance and flexibility.

best regards,

Lars

On Tue, May 17, 2016 at 1:13 PM, Vanya Seth <vanyas@thoughtworks.com> >>> wrote:

Hi dhis-devs

We are looking at doing some analysis for the Anonymous events. For
which we are trying to set up some program indicators.

Currently the issue that we face is that the events that DHIS2
considers for calculations within a specific reporting period (week, month)
in event reports/visualizer, are the ones whose event date is within that
reporting period.

*Example of the problem*

*Inpatient mortality rate*

Relevant data elements: date of discharge, exit mode [death, discharged
...]

*Requirement:*

- Get all events where the discharge date was within the reporting
period (all exits during period)

- Get all events where the discharge date was within the reporting
period and the exit mode was "death" (deaths during reporting period)

- Divide deaths/all exits to get the mortality rate (indicator)

As an example, if a patient gets admitted in April 2016 and hence the
event date is in the April 2016 reporting period. Now say the patient gets
discharged in May 2016. But this exit will be calculated in the April 2016
reporting period due to considering the event date for analysis.
There is no direct way in DHIS where in we can match the reporting
period to a date based data elements for filtering out data or also include
them in indicators.

Any thoughts about how other users have solved these kind of issues
would be much appreciated.

--
With Regards
Vanya
ThoughtWorks Technologies
Hyderabad

--Stay Hungry Stay Foolish!!

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

--
Lars Helge Øverland
Lead developer, DHIS 2
University of Oslo
Skype: larshelgeoverland
lars@dhis2.org
http://www.dhis2.org <https://www.dhis2.org/&gt;

--
With Regards
ThoughtWorks Technologies
Hyderabad

--Stay Hungry Stay Foolish!!

--
With Regards
ThoughtWorks Technologies
Hyderabad

--Stay Hungry Stay Foolish!!

--
Lars Helge Øverland
Lead developer, DHIS 2
University of Oslo
Skype: larshelgeoverland
lars@dhis2.org
http://www.dhis2.org <https://www.dhis2.org/&gt;

8

Sure. No problem.

Regards

Vanya

···

On Mon, May 23, 2016 at 4:16 PM, Lars Helge Øverland lars@dhis2.org wrote:

–

On Mon, May 23, 2016 at 12:31 PM, Vanya Seth vanyas@thoughtworks.com wrote:

We would also look into some possibilities ourselves and get back if there’s something worth discussing.

Thanks, that sounds great.

One option could be to ignore time partitioning completely. Another could be to have an option for number of years to include for analysis. Let me know if you come up with any bright new ideas :wink:

best regards,

Lars

Regards

Vanya

–
Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

On Mon, May 23, 2016 at 3:58 PM, Vanya Seth vanyas@thoughtworks.com wrote:

Thanks a lot for the information Lars.

Really appreciate it.

Regards

Vanya

–
With Regards
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

On Mon, May 23, 2016 at 3:19 PM, Lars Helge Øverland lars@dhis2.org wrote:

Hi Vanya,

thanks for raising this issue - it’s a good question. Having program indicators based on a data element / attributes dates instead of event date has come up earlier and something we will have to support.

Short story: Currently this is not possible.

Long story: The current event analytics solution has a compromise between query performance and flexibility around dates. If you have a look at the event analytics tables (e.g. “analytics_event_2015_ebayegv0exc”) you will see two things:

  • The tables are partitioned by year (one analytics table with events per year per program) based on event date. This reduces query time since the indexes and tables become smaller instead of growing longer infinitely as times goes.
  • The tables have columns for all period types based on event date. This makes it possible to aggregate all periods in a query through group-bys on those columns, instead of having to do one query per period, and hence improves performance a lot.

All this is great for performance but limits the analysis to be based on event date. To partition data one needs a partition key and event date is pretty much the only suitable candidate (in addition to program).

So to support aggregation / analytics based on any date from data elements/attributes of the events, one must sacrifice some of this and basically have one analytics table per program, and have one query per aggregation period. This will soon be a very popular requirement so we will have to support it - still pondering how to solve this in a way which offers required performance and flexibility.

best regards,

Lars

–
With Regards
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

On Tue, May 17, 2016 at 1:13 PM, Vanya Seth vanyas@thoughtworks.com wrote:

Hi dhis-devs

We are looking at doing some analysis for the Anonymous events. For which we are trying to set up some program indicators.

Currently the issue that we face is that the events that DHIS2 considers for calculations within a specific reporting period (week, month) in event reports/visualizer, are the ones whose event date is within that reporting period.

Example of the problem

Inpatient mortality rate

Relevant data elements: date of discharge, exit mode [death, discharged …]

Requirement:

  • Get all events where the discharge date was within the reporting period (all exits during period)
  • Get all events where the discharge date was within the reporting period and the exit mode was “death” (deaths during reporting period)
  • Divide deaths/all exits to get the mortality rate (indicator)

As an example, if a patient gets admitted in April 2016 and hence the event date is in the April 2016 reporting period. Now say the patient gets discharged in May 2016. But this exit will be calculated in the April 2016 reporting period due to considering the event date for analysis.

There is no direct way in DHIS where in we can match the reporting period to a date based data elements for filtering out data or also include them in indicators.

Any thoughts about how other users have solved these kind of issues would be much appreciated.

–
With Regards

Vanya
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!

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

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

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

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

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

–

With Regards
ThoughtWorks Technologies

Hyderabad

–Stay Hungry Stay Foolish!!