28 Configure report functionality

28.1 Data sources for reporting

28.1.1 Types of data and aggregation

In the bigger picture of HIS terminology all data in DHIS2 are usually called aggregated as they are aggregates (e.g. monthly summaries) of medical records or some kind of service registers reported from the health facilities. Aggregation inside DHIS2 however, which is the topic here, is concerned with how the raw data captured in DHIS2 (through data entry or import)are further aggregated over time (e.g. from monthly to quarterly values) or up the organisational hierarchy (e.g. from facility to district values).

28.1.1.1 Terminology

  • Raw data refers to data that is registered into the DHIS2 either through data entry or data import, and has not been manipulated by the DHIS2 aggregation process. All these data are stored in the table (or Java object if you prefer) called DataValue.

  • Aggregated data refers to data that has been aggregated by the DHIS2, meaning it is no longer raw data, but some kind of aggregate of the raw data.

  • Indicator values can also be understood as aggregated data, but these are special in the way that they are calculated based on user defined formulas (factor * numerator/denominator). Indicator values are therefore processed data and not raw data, and are located in the aggregatedindicatorvalue table/object. Indicators are calculated at any level of the organisational hierarchy and these calculations are then based on the aggregated data values available at each level. A level attribute in the aggregateddatavalue table refers to the organisational level of the orgunit the value has been calculated for.

  • Period and Period type are used to specify the time dimension of the raw or aggregated values, and data can be aggregated from one period type to another, e.g. from monthly to quarterly, or daily to monthly. Each data value has one period and that period has one period type. E.g. data values for the periods Jan, Feb, and Mar 2009, all of the monthly period type can be aggregated together to an aggregated data value with the period Q1 2009 and period type Quarterly.

28.1.1.2 Basic rules of aggregation

28.1.1.2.1 What is added together

Data (raw) can be registered at any organisational level, e.g. at national hospital at level 2, a health facility at level 5, or at a bigger PHC at level 4. This varies form country to country, but DHIS2 is flexible in allowing data entry or data import to take place at any level. This means that orgunits that themselves have children can register data, sometimes the same data elements as their children units. The basic rule of aggregation in DHIS2 is that all raw data is aggregated together , meaning data registered at a facility on level 5 is added to the data registered for a PHC at level 4.

It is up to the user/system administrator/designer to make sure that no duplication of data entry is taking place and that e.g. data entered at level 4 are not about the same services/visits that are reported by orgunit children at level 5.

NOTE

In some cases you want to have duplication of data in the system, but in a controlled manner. E.g. when you have two different sources of data for population estimates, both level 5 catchment population data and another population data source for level 4 based on census data (because sum of level 5 catchments is not always the same as level 4 census data). Then you can specify using advanced aggregation settings (see further down) that the system should e.g. not add level 5 population data to the level 4 population data, and that level 3,2,1 population data aggregates are only based on level 4 data and does not include level 5 data.

28.1.1.2.2 How data gets added together

How data is aggregated depends on the dimension of aggregation (see further down).

Along the orgunit level dimension data is always summed up; i.e. simply added together. Note that raw data is never percentages, and therefore can be summed together. Indicator values that can be percentages are treated differently (re-calculated at each level, never summed up).

Along the time dimension there are several possibilities, the two most common ways to aggregate are sum and average. The user can specify for each data element which method to use by setting the aggregation operator (see further down). Monthly service data are normally summed together over time, e.g. the number of vaccines given in a year is the sum of the vaccines given for each month of that year. For population, equipment, staff and other kind of what is often called semi-permanent data the average method is often the one to use, as, e.g. ‘number of nurses’ working at a facility in a year would not be the sum of the two numbers reported in the six-monthly staffing report, but rather the average of the two numbers. More details further down under ‘aggregation operators’.

28.1.1.3 Dimensions of aggregation

28.1.1.3.1 Organisational units and levels

Organisational units are used to represent the ‘where’ dimension associated with data values. In DHIS2, organisational units are arranged in a hierarchy, which typically corresponds to the hierarchical nature of the organisation or country. Organisational unit levels correspond to the distinct levels within the hierarchy. For instance, a country may be organized into provinces, then districts, then facilities, and then sub-centres. This organisational hierarchy would have five levels. Within each level, a number of organisational units would exist. During the aggregation process, data is aggregated from the lower organisational unit levels to higher levels. Depending on the aggregation operator, data may be ‘summed’ or ‘averaged’ within a given organisational unit level, to derive the aggregate total for all the organisational units that are contained within a higher level organisational unit level. For instance, if there are ten districts contained in a province and the aggregation operator for a given data element has been defined as ‘SUM’, the aggregate total for the province would be calculated as the sum of the values of the individual ten districts contained in that province.

28.1.1.3.2 Period

Periods are used to represent the ‘when’ dimension associated with data values. Data can easily be aggregated from weeks to months, from months to quarters, and from quarters to years. DHIS2 uses known rules of how these different intervals are contained within other intervals (for instance Quarter 1 2010 is known to contain January 2010, February 2010 an March 2010) in order to aggregate data from smaller time intervals, e.g. weeks, into longer time intervals, e.g. months.

28.1.1.3.3 Data Elements and Categories

The data element dimension specifies ‘what’ is being recorded by a particular data value. Data element categories are actually degenerated dimensions of the data element dimension, and are used to disaggregate the data element dimension into finer categories. Data element categories, such as ‘Age’ and ‘Gender’, are used to record a particular data element, typically for different population groups. These categories can then be used to calculate the overall total for the category and the total of all categories.

28.1.1.4 Aggregation operators, methods for aggregation

28.1.1.4.1 Sum

The ‘sum’ operator simply calculates the sum of all data values that are contained within a particular aggregation matrix. For instance, if data is recorded on a monthly basis at the district level and is aggregated to provincial quarterly totals, all data contained in all districts for a given province and all weeks for the given quarter will be added together to obtain the aggregate total.

28.1.1.4.2 Average

When the average aggregation operator is selected, the unweighted average of all data values within a given aggregation matrix are calculated.

It is important to understand how DHIS2 treats null values in the context of the average operator. It is fairly common for some organisational units not to submit data for certain data elements. In the context of the average operator, the average results from the number of data elements that are actually present (therefore NOT NULL) within a given aggregation matrix. If there are 12 districts within a given province, but only 10 of these have submitted data, the average aggregate will result from these ten values that are actually present in the database, and will not take into account the missing values.

28.1.1.5 Advanced aggregation settings (aggregation levels)

28.1.1.5.1 Aggregation levels

The normal rule of the system is to aggregate all raw data together when moving up the organisational hierarchy, and the system assumes that data entry is not being duplicated by entering the same services provided to the same clients at both facility level and also entering an ‘aggregated’ (sum of all facilities) number at a higher level. This is to more easily facilitate aggregation when the same services are provided but to different clients/catchment populations at facilities on level 5 and a PHC (the parent of the same facilities) at level 4. In this way a facility at level 5 and a PHC at level 4 can share the same data elements and simply add together their numbers to provide the total of services provided in the geographical area.

Sometimes such an aggregation is not desired, simply because it would mean duplicating data about the same population. This is the case when you have two different sources of data for two different orgunit levels. E.g. catchment population for facilities can come from a different source than district populations and therefore the sum of the facility catchment populations do not match the district population provided by e.g. census data. If this is the case we would actually want duplicated data in the system so that each level can have as accurate numbers as possible, but then we do NOT want to aggregate these data sources together.

In the Data Element section you can edit data elements and for each of them specify how aggregation is done for each level. In the case described above we need to tell the system NOT to include facility data on population in any of the aggregations above that level, as the level above, in this case the districts have registered their population directly as raw data. The district population data should then be used at all levels above and including the district level, while facility level should use its own data.

28.1.1.5.2 How to edit data element aggregation

This is controlled through something called aggregation levels and at the end of the edit data element screen there is a tick-box called Aggregation Levels. If you tick that one you will see a list of aggregation levels, available and selected. Default is to have no aggregation levels defined, then all raw data in the hierarchy will be added together. To specify the rule described above, and given a hierarchy of Country, Province, District, Facility: select Facility and District as your aggregation levels. Basically you select where you have data. Selecting Facility means that Facilities will use data from facilities (given since this is the lowest level). Selecting District means that the District level raw data will be used when aggregating data for District level (hence no aggregation will take place at that level), and the facility data will not be part of the aggregated District values. When aggregating data at Province level the District level raw data will be used since this is the highest available aggregation level selected. Also for Country level aggregates the District raw data will be used. Just to repeat, if we had not specified that District level was an aggregation level, then the facility data and district data would have been added together and caused duplicate (double) population data for districts and all levels above.

28.1.2 Resource tables

Resource tables provide additional information about the dimensions of the data in a format that is well suited for external tools to combine with the data value table. By joining the data value table with these resource tables one can easily aggregate along the data element category dimension or data element/indicator/organisation unit groups dimensions. E.g. by tagging all the data values with the category option male or female and provide this in a separate column ‘gender’ one can get subtotals of male and female based on data values that are collected for category option combinations like (male, <5) and (male,>5). See the Pivot Tables section for more examples of how these can be used. orgunitstructure is another important table in the database that helps to provide the hierarchy of orgunits together with the data. By joining the orgunitstructure table with the data values table you can get rows of data values with the full hierarchy, e.g. on the form: OU1, OU2, OU3, OU4, DataElement, Period, Value (Sierra Leone, Bo, Badija, Ngelehun CHC, BCG <1, Jan-10, 32) This format makes it much easier for e.g. pivot tables or other OLAP tools to aggregate data up the hierarchy.

28.1.3 Report tables

Report tables are defined, cross-tabulated reports which can be used as the basis of further reports, such as Excel Pivot Tables or simply downloaded as an Excel sheet. Report tables are intended to provide a specific view of data which is required, such as “Monthly National ANC Indicators”. This report table might provide all ANC indicators for a country, aggregated by month for the entire country. This data could of course be retrieved from the main datamart, but report tables generally perform faster and present well defined views of data to users.