DHIS2 Data Pipeline
Overview
Table and column names in the AP are similar, but not identical to DHIS2, and not all tables are ingested. The general structure however remains, with one table for aggregate data, separate tables for each program and separate tables for metadata, such as categories, data elements and org units.
Table naming
Each data pipeline specifies a schema and a data warehouse base table name. The base table name then becomes the prefix to all tables contained within it. For example, for a data pipeline with base name data, the datavalue table will be named data_datavalue.
There are two versions of the aggregate data tables that are created by default.
| Version |
Table name |
Description |
| Narrow |
datavalue |
For most queries. There are fewer columns to manage, however it will require joining with other metadata tables to get all the information you need. |
| Wide |
analytics |
Ad-hoc analysis. Most of the metadata tables are joined in this table, however it requires familiarity with the column structure. |
Table structure
This section describes the tables which are created and loaded by the DHIS2 data pipeline.
Data tables
The DHIS2 data pipeline creates and loads tables for aggregate data, complete data set registrations, program events, program enrollments, tracked entities and relationships.
| Table name |
Description |
| Audit |
Audit table |
| Data |
Wide aggregate data table |
| Data value |
Narrow aggregate data table |
| Complete data set registration |
Completed datasets |
| [Program name] - Event |
Tracker/event data table |
| [Program name] - Event audit |
Tracker/event audit table |
| Enrollment |
Tracker enrollment data table |
| Tracked entity attribute value |
Tracked entity attribute value table |
| Tracked entity instance |
Tracked entity instance data table |
| Relationship item |
Relationship data table |
Metadata tables are split into their component parts. The wide data value table includes most of the key metadata information, however it might be neccessary to build a star schema in joining metadata tables. There are additional metadata tables suffixed with 'structure' which are automatically created by AP to avoid having to make as many table joins.
| Data elements |
| Data element |
| Data element group |
| Data element group members |
| Data element structure |
| Data element group set |
| Data element group set members |
| Data element group set structure |
| Categories |
| Category |
| Category structure |
| Category combination |
| Category combinations and categories |
| Category combinations and option combinations |
| Category option |
| Category option and organisation units |
| Categories and category options |
| Category option combination |
| Category option combination structure |
| Category option combinations and category options |
| Category option group |
| Category option group members |
| Category option group set |
| Category option group set members |
| Organisation Units |
| Organisation unit |
| Organisation unit structure |
| Organisation unit group |
| Organisation unit group members |
| Organisation unit group set |
| Organisation unit group set members |
| Organisation unit group set structure |
| Time periods |
| Period |
| Period structure |
| Relative monthly period |
| Relative quarterly period |
| Relative yearly period |
| Data Sets |
| Data set |
| Data set element |
| Programs |
| Program |
| Program stage |
| Tracked Entities |
| Tracked entity attribute |
| Tracked entity type |
| Relationships |
| Relationship structure |
| Relationship constraint |
| Relationship type |
| Relationship type structure |
| Indicators |
| Indicator |
| Indicator group |
| Indicator group members |
| Indicator group set |
| Indicator group set members |
| Program indicator |
| Dimension |