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.
Power BI for M&E and Public Health Data Analysts · The Growing Analyst