A Data Dictionary and Access

In any Monitoring and Evaluation project, a data dictionary is essential. It defines exactly what each column means, so that everyone calculates indicators from the same definitions.

All data in this book is synthetic. It was generated by the R script in Appendix B. No real patient, facility, or programme data is used, and the rates in the data are illustrative only.

A.1 Accessing the Data

Download the five CSV files used in this book:

File Contents Rows
fact_patient_visits.csv One row per patient visit (the line list) 10,030
dim_patient.csv One row per patient 3,500
dim_facility.csv One row per health facility 60
dim_date.csv One row per calendar day in 2024 366
fact_targets.csv Quarterly targets per facility and indicator 960

Save all five files in the same folder on your computer, for example Documents/PowerBI-Book/data.


A.2 How the Tables Fit Together

Table Type Grain (one row = …) Key Connects to
fact_patient_visits Fact one patient visit visit_id dim_patient, dim_facility, dim_date
fact_targets Fact one indicator target for one facility in one quarter none (combination of facility_id, date_id, indicator) dim_facility, dim_date
dim_patient Dimension one patient patient_id fact_patient_visits
dim_facility Dimension one facility facility_id both fact tables
dim_date Dimension one calendar day date_id both fact tables

The two fact tables have different grains: visits are recorded per day, while targets are set per quarter. How to model this is covered in Chapter 6.


A.3 Fact Table: Patient Visits

File: fact_patient_visits.csv
Grain: one row per patient visit to a facility between 1 January and 31 December 2024.
Rows: 10,030 (10,000 visits plus 30 duplicated rows; see Known Data-Quality Issues).

Missing values are written as the text NA. Power Query does not treat this text as a missing value: it stays as the text NA, and converting the column to a number or date type turns each NA into an error.

Column Type Description Values
visit_id Whole number Unique visit identifier 1–10,000
patient_id Whole number Links to dim_patient 3,329 distinct patients
facility_id Whole number Links to dim_facility 1–60
anc_visit Text Antenatal care visit Yes, No (plus inconsistent variants)
malaria_tested Text Malaria test done at this visit Yes, No
tb_screened Text TB symptom screening done Yes, No
hiv_tested Text HIV test done at this visit Yes, No
ncd_screened Text Screened for hypertension and diabetes (adults 18+ only) Yes, No
malaria_positive Text Malaria test result was positive Yes, No (No also when not tested)
tb_test_done Text TB diagnostic test done (only if screened) Yes, No
tb_test_date Text Date the TB sample was collected: the visit day or up to 3 days later YYYY-MM-DD, or NA if no test
tb_positive Text TB test result was positive Yes, No (No also when not tested)
hiv_positive Text HIV test result at this visit was positive Yes, No (No also when not tested)
art_started Text Patient was on antiretroviral therapy (ART) at the time of this visit Yes, No
art_start_date Text Date the patient started ART YYYY-MM-DD, or NA if not on ART
hypertension Text Hypertension identified at screening Yes, No (No also when not screened)
diabetes Text Diabetes identified at screening Yes, No (No also when not screened)
date_id Whole number Visit date as YYYYMMDD; links to dim_date 20240101–20241231
year Whole number Copy of dim_date[year] 2024
quarter Text Copy of dim_date[quarter] Q1–Q4
month Text Copy of dim_date[month] Jan–Dec
iso_week Whole number Copy of dim_date[iso_week] 1–52
art_regimen Text ART regimen TLD, TLE, ABC-3TC-DTG, AZT-3TC-NVP (plus inconsistent variants), or NA
art_line Text ART treatment line First, Second, Third, or NA

Reading the result columns. malaria_positive, tb_positive and hiv_positive contain No both when a test was negative and when no test was done. A positivity rate must therefore always use the number tested as its denominator, never the number of visits.

Each HIV-positive patient is tested only once. After a positive result, later visits by the same patient record hiv_tested = No.

year, quarter, month and iso_week are duplicates of columns in dim_date. They are included on purpose so you can practise removing them. Chapter 6 explains why they belong in the date dimension instead.


A.4 Fact Table: Targets

File: fact_targets.csv
Grain: one row per facility, quarter and indicator.
Rows: 960 (60 facilities × 4 quarters × 4 indicators).

Column Type Description Values
facility_id Whole number Links to dim_facility 1–60
date_id Whole number First day of the quarter as YYYYMMDD; links to dim_date 20240101, 20240401, 20240701, 20241001
year Whole number Target year 2024
quarter Text Target quarter Q1–Q4
indicator Text Indicator the target applies to Malaria tests, HIV tests, TB screenings, NCD screenings
target Whole number Number of services the facility planned to deliver in the quarter 4 or more

Targets were simulated as 90–130% of each facility’s actual quarterly count. They are for practice only.


A.5 Dimension Table: Patients

File: dim_patient.csv
Grain: one row per patient. Rows: 3,500. Of these, 3,329 patients have at least one visit in the fact table.

Column Type Description Values
patient_id Whole number Unique patient identifier (key) 1–3,500
sex Text Sex of the patient Male, Female
age Whole number Age in years 0–80
hiv_status Text HIV status at the end of 2024, based on test results in the fact table Positive (any positive test), Negative (tested, never positive), Unknown (no HIV test in 2024)
age_group Text Age band <5, 5–14, 15–49, 50+
pregnant Text Pregnant during 2024 (only possible for females aged 15–49) Yes, No

age and pregnant are stored once per patient, not per visit. This is a simplification: in real data, both change over time.


A.6 Dimension Table: Facilities

File: dim_facility.csv
Grain: one row per facility. Rows: 60.

Column Type Description Values
facility_id Whole number Unique facility identifier (key) 1–60
facility_name Text Facility name Facility 1–Facility 60
facility_type Text Level of care Primary, Secondary
ownership Text Who runs the facility Public, Private
district Text District 11 districts between District 1 and District 12 (there is no District 6)
state Text State State A, State B, State C

District and state were assigned independently, so the same district name can appear in more than one state. Treat state and district as separate attributes, not as a strict hierarchy, unless you combine them (for example, State A – District 3).


A.7 Dimension Table: Dates

File: dim_date.csv
Grain: one row per calendar day from 1 January to 31 December 2024. Rows: 366 (2024 is a leap year).

Column Type Description Values
date Date Calendar date 2024-01-01 to 2024-12-31
date_id Whole number Date as YYYYMMDD (key) 20240101–20241231
year Whole number Calendar year 2024
quarter Text Calendar quarter Q1–Q4
month_num Whole number Month number, used to sort month 1–12
month Text Month abbreviation Jan–Dec
iso_year Whole number ISO 8601 week-numbering year 2024, 2025
iso_week Whole number ISO 8601 week number 1–52

ISO 8601 weeks start on Monday, and week 1 is the week that contains the year’s first Thursday. As a result, 30 and 31 December 2024 belong to week 1 of ISO year 2025. Always pair iso_week with iso_year, not with year.


A.8 Known Data-Quality Issues

The data was generated with consistent clinical logic, for example ANC visits only for pregnant patients and ART only after a positive HIV test. The problems below were then added on purpose, so you can practise finding and fixing them in Power Query.

Try to find these yourself in Chapter 5 before reading this list.

# Table and column Issue Rows affected
1 fact_patient_visits (all columns) Exact duplicate rows: the same visit entered twice 30
2 All files Missing values stored as the text NA many columns
3 anc_visit " yes " (lowercase, with spaces) 74
4 anc_visit "YES" (uppercase) 58
5 anc_visit + dim_patient[sex] ANC visit recorded for a male patient 10
6 tb_test_date Wrong date format, e.g. 2024/03-15 instead of 2024-03-15 343
7 art_start_date Placeholder date 1900-01-01 6
8 art_regimen "Tld" instead of "TLD" 16
9 art_regimen "TLD " with a trailing space 15
10 art_regimen Missing (NA) although art_started = Yes 4
11 year, quarter, month, iso_week Date attributes duplicated in the fact table all rows

Counts are for the files as downloaded. After removing the 30 duplicate rows, issue 6 affects 342 rows; the other counts are unchanged.


© 2025–2026 Oluwatobi Olatunbosun · CC BY-NC-SA 4.0
Power BI for M&E and Public Health Data Analysts · The Growing Analyst