1.84 Analytics table hooks

Analytics table hooks provides a mechanism for invoking SQL scripts during different phases of the analytics table generation process. This is useful for customizing data in resource and analytics tables, e.g. in order to achieve specific logic for calculations and aggregation. Analytics table hooks can be manipulated at the following API endpoint:

/api/analyticsTableHooks

The analytics table hooks API supports the standard HTTP CRUD operations for creating (POST), updating (PUT), retrieving (GET) and deleting (DELETE) entities.

1.84.1 Hook fields

Analytics table hooks have the following fields:

Analytics table hook fields
Field Options Description
name Text Name of the hook.
phase RESOURCE_TABLE_POPULATED, ANALYTICS_TABLE_POPULATED The phase for when the SQL script should be invoked.
resourceTableType

See “Table type” in “Overview of phases, table types and temporary tables”

The type of resource table for which to invoke the SQL script. Applies only for hooks defined with the RESOURCE_TABLE_POPULATED phase.
analyticsTableType DATA_VALUE, COMPLETENESS, COMPLETENESS_TARGET, ORG_UNIT_TARGET, EVENT, ENROLLMENT, VALIDATION_RESULT The type of analytics table for which to invoke the SQL script. Applies only for hooks defined with the ANALYTICS_TABLE_POPULATED phase.
sql Text The SQL script to invoke.

The ANALYTICS_TABLE_POPULATED phase takes place after the analytics table has been populated, but before indexes have been created and the temp table has been swapped with the main table. As a result, the SQL script should refer to the analytics temp table, e.g. analytics_temp , analytics_completeness_temp , analytics_completenesstarget_temp , analytics_orgunittarget_temp , analytics_event_temp_<program-uid> .

This applies also to the RESOURCE_TABLE_POPULATED phase, which takes place after the resource table has been populated, but before indexes have been created and the temp table has been swapped with the main table. As a result, the SQL script should refer to the resource temp table, e.g. _orgunitstructure_temp , _categorystructure_temp .

You should define only one of the resourceTableType and analyticsTableType fields, depending on which phase is defined.

Only the table type you select will have it’s temporary table available when the hook is run. The following table shows the valid combinations of phase, table types and temporary tables.

Overview of phases, table types and temporary tables
Phase Table type Temporary table
RESOURCE_TABLE_POPULATED ORG_UNIT_STRUCTURE _orgunitstructure_temp
DATA_SET_ORG_UNIT_CATEGORY _datasetorgunitcategory_temp
CATEGORY_OPTION_COMBO_NAME _categoryoptioncomboname_temp
DATA_ELEMENT_GROUP_SET_STRUCTURE _dataelementgroupsetstructure_temp
INDICATOR_GROUP_SET_STRUCTURE _indicatorgroupsetstructure_temp
ORG_UNIT_GROUP_SET_STRUCTURE _organisationunitgroupsetstructure_temp
CATEGORY_STRUCTURE _categorystructure_temp
DATA_ELEMENT_STRUCTURE _dataelementstructure_temp
PERIOD_STRUCTURE _periodstructure_temp
DATE_PERIOD_STRUCTURE _dateperiodstructure_temp
DATA_ELEMENT_CATEGORY_OPTION_COMBO _dataelementcategoryoptioncombo_temp
DATA_APPROVAL_MIN_LEVEL _dataapprovalminlevel_temp
ANALYTICS_TABLE_POPULATED DATA_VALUE, COMPLETENESS analytics_temp
COMPLETENESS analytics_completeness_temp
COMPLETENESS_TARGET analytics_completenesstarget_temp
ORG_UNIT_TARGET analytics_orgunittarget_temp
EVENT analytics_event_temp_<program-uid>
ENROLLMENT analytics_enrollment_temp_<program-uid>
VALIDATION_RESULT analytics_validationresult_temp

1.84.2 Creating hooks

To create a hook which should run after the resource tables have been populated you can do a POST request like this using JSON format:

curl -d @hooks.json "localhost/api/analyticsTableHooks" -H "Content-Type:application/json" -u admin:district -v

{
  "name": "Update 'Area' in org unit group set resource table",
  "phase": "RESOURCE_TABLE_POPULATED",
  "resourceTableType": "ORG_UNIT_GROUP_SET_STRUCTURE",
  "sql": "update _organisationunitgroupsetstructure_temp set \"uIuxlbV1vRT\" = 'b0EsAxm8Nge'"
}

To create a hook which should run after the data value analytics table has been populated you can do a POST request like this using JSON format:

{
  "name": "Update 'Currently on treatment' data in analytics table",
  "phase": "ANALYTICS_TABLE_POPULATED",
  "analyticsTableType": "DATA_VALUE",
  "sql": "update analytics_temp set monthly = '200212' where \"monthly\" in ('200210', '200211')"
}