I am currently studying the DHIS2 database structure using the Sierra Leone demo database. My goal is to improve my PostgreSQL skills and better understand how DHIS2 manages and stores data.
I spend most of my time exploring tables such as datavalue, dataelement, organisationunit, period, categorycombo, and categoryoptioncombo, and writing SQL queries to understand how the relationships between these tables work.
My main goal is to become very comfortable working with data and managing or validating data directly in PostgreSQL and easier to get a job.
Do you think this is a good way to learn the DHIS2 data model and PostgreSQL?
Or would you recommend a different approach or additional things I should study?
This is a very good approach.
What I am deducing from what you said is that, you have knowledge on databases but wants to understand the Dhis2 database structure.
If that is the case you are on the right track if not you are going to have a tough time.
Thank you.
you might find the “analytics generation” interesting : it turns all the tables you mention into OLAP tables better suited for aggregation or other indicator calculations. The tables are splitted by year, with a lot of indexes to match a lot of the dimensions available. Note that this part is evolving lately to support other databases (ex clickhouse) Analytics database - DHIS2 Documentation
some concerns like translations and sharing settings are json fields attached to each records
a feature also permit to create views/materialized views or simple query in the Maintenance app : see SQLView (you generally don’t grant direct db access to persons, this is a nice tradeoff when volumes are too big for the normal api) Visualizations - DHIS2 Documentation
you won’t do update or insert by hand, you’ll use either the UI or the underlying rest api to create/update things.
using sql would bypass all validations, audit, permissions,… and which tie your work at the db level, where the dhis2 team try to keep as much as possible the api compatible. So using the db directly is clearly a bad idea.
note all screen now use the api to do their things so if you want to automate things it’s a matter of finding with the network view of your browser what are the api used, parametrize the payload.
another thing that you would be interested in is the migration engine from version to version based on flyway (note older version where not that automated and required some sql to be run manually
the documentation also mention a lot of settings to tune the performance, I would recommend you to discover these : PostgreSQL - DHIS2 Documentation
something that administrator might need is also understanding how to delete some data (either soft deleted via the api, closing date, or more radically issuing chirurgical delete statements)
Great tips above! I’d emphasize the point about the DHIS2 API that @Stephan_Mestach mentioned.
I’m also curious, are you trying to understand DHIS2 and taking the approach to study PostgreSQL? If the ultimate purpose is to be good at DHIS2 there are various paths to take depending on your role/focus.
If you’d like to take advantage of the DHIS2 Postgresql databases to learn more about Postgresql then that’d be an interesting approach; however, please keep in mind that the system is designed in a way that the DB is handled by the DHIS2 Core backend or through extensions such as the API.
Are you currently working on a DHIS2 project? It’d be great to learn more about what you want to learn and do.
Thank you for the feedback and the useful suggestions.
My goal is mainly to understand DHIS2 from a data quality and QA perspective.
I’m also planning to explore the DHIS2 API more, since I understand that most operations should be done through the API or UI rather than directly in the database.
Currently I’m not working on a specific DHIS2 project; I’m studying the platform and its data model to build stronger skills in data validation, SQL analysis, and system understanding .
I want to become stronger and more useful for myself and for employers, and to be able to contribute to significant and important work where I study and will work.
Becoming familiar with the API and database are helpful; however, it seems that this is a very technical approach rather than a focus on the subject matter. As you can see in the previous online data quality academy the focus is on specific topics: DHIS2 Academy on Data Quality - DHIS2 such as “DHIS2 Data Quality Toolkit”
and it’s great that you’ve joined the community of practice to connect with other practitioners!
and it’s great that you’ve joined the community of practice to connect with other practitioners! 