This document provides a reference for every chart available across the nine OLMIS dashboards.

For each chart, the following information is provided:

• Purpose – what the chart represents.

• Example output – illustrative data presented as a SQL-style result table.

• Source (PostgreSQL) – the originating PostgreSQL table(s) and column(s) used to derive the chart.

• Notes – implementation details, assumptions, or known limitations.

Each report includes an example output generated using test data for reference.

Contents

Data Flow

All dashboard data follows the same processing pipeline:

Throughout this document, Source (PostgreSQL) refers to the original PostgreSQL tables and columns from which each metric is ultimately derived.

1. OLMIS Summary

Purpose: Reporting rate dashboard.

Data marts:

• mart_reporting_status

• mart_non_reporting_facilities

Example output of the generated report:

Open

1.1 Reporting Rate (Pie Chart)

Purpose

Shows the share of reporting obligations that were Reported versus Did not report.

Example Output

Source (PostgreSQL)

reporting_status is derived as follows:

• Reported if a requisition.status_changes record with status = 'SUBMITTED' exists for the corresponding (facility, program, reporting period).

• Expected reporting obligations are derived from:

  • referencedata.supported_programs

  • referencedata.requisition_group_members

  • referencedata.requisition_group_program_schedules

  • referencedata.processing_periods

Notes

• Requisition submission is the authoritative source.

• No dashboard time filter is applied (the mart contains a rolling 3-year window).

1.2 Timeliness of Reports (Pie Chart)

Purpose

Shows when submitted reports were received during the reporting month.

Example Output

Source (PostgreSQL)

Calculated from the day of month of:

MIN(requisition.status_changes.created_date)

where:

status = 'SUBMITTED'

Only requisitions with reporting_status = 'Reported' are included.

Notes

• Uses reporting buckets: Before 15th, 16th–20th, After 20th.

• These thresholds differ from the Order Timeliness chart (10th/20th).

1.3 Reporting Rate by Program (100% Stacked Bar Chart)

Purpose

Shows the proportion of Reported versus Did not report obligations for each program over the last 12 months.

Example Output

Source (PostgreSQL)

• Program: referencedata.programs.name

• Reporting status: derived using the same logic as Reporting Rate

Window: last 12 reporting months.

1.4 National Reporting Rate – Last 12 Months (Line Chart)

Purpose

Shows the national reporting rate for each reporting period during the last 12 months.

Example Output

Source (PostgreSQL)

• Reporting rate: AVG(reporting_status = 'Reported')

• Reporting period: referencedata.processing_periods.enddate

1.5 Indicator Summary: Reporting Rate by Program (Last 12 Months) (Pivot Table)

Purpose

Shows reporting rate by reporting period and program.

Example Output

Source (PostgreSQL)

Uses the same reporting-rate calculation as above.

• Period: referencedata.processing_periods.name

1.6 Annual Reporting Rate by Program (Bar Chart)

Purpose

Shows the average reporting rate per program for each calendar year.

Example Output

Source (PostgreSQL)

• Year: YEAR(referencedata.processing_periods.enddate)

• Reporting rate: AVG(reporting_status = 'Reported')

Notes

The dashboard requests the last five years, while the mart stores only a rolling three-year history. As a result, the oldest two years are always empty.

1.7 Non Reporting Facilities (Table)

Also used by OLMIS Reporting Rate, OLMIS Stock Status, OLMIS Stockouts, and OLMIS Orders dashboards.

Purpose

Lists active facilities that did not submit a requisition for a given reporting period and program.

Example Output

Source (PostgreSQL)

Derived as:

Expected reporting obligations

(from supported programs, requisition groups and processing periods)

minus

Submitted requisitions

(requisition.status_changes where status = 'SUBMITTED')

Lookup tables:

• referencedata.facilities.name

• referencedata.programs.name

• referencedata.geographic_zones.name

• referencedata.processing_periods.name

2. OLMIS Reporting Rate

Purpose: Reporting rate monitoring dashboard.

Data marts:

• mart_reporting_status

• mart_non_reporting_facilities

Shared charts:

The following charts are reused from OLMIS Summary:

• Reporting Rate

• Non Reporting Facilities

Example output of the generated report:

Open

2.1 Reporting Rate Trend (Line Chart)

Purpose

Shows the monthly reporting rate trend over time, with a separate line for each program.

Example Output

Source (PostgreSQL)

• Reporting rate: AVG(reporting_status = 'Reported')

• Aggregated by: referencedata.programs.name

• Reporting period: referencedata.processing_periods.enddate

2.2 Reporting Timeliness By Week (Stacked Bar Chart)

Purpose

Shows submitted reports grouped by the week of the month in which they were received.

Weeks are calculated as:

• Week 1: days 1–7

• Week 2: days 8–14

• Week 3: days 15–21

• Week 4: days 22–28

• Week 5: days 29–31

Example Output

Source (PostgreSQL)

Calculated from submitted status changes:

FLOOR((DAY_OF_MONTH(status_changes.created_date) - 1) / 7) + 1

Source records:

requisition.status_changes

Filter:

status = 'SUBMITTED'

Notes

• Week 5 contains only days 29–31, resulting in a partial 3-day bucket.

2.3 Expected Facilities to Report (Table)

Purpose

Shows the number of distinct facilities expected to submit reports for each district, reporting period, and program.

Example Output

Source (PostgreSQL)

Expected facilities are calculated from the combination of:

• referencedata.supported_programs

• referencedata.requisition_group_members

• referencedata.requisition_group_program_schedules

• referencedata.processing_periods

The metric counts distinct:

referencedata.facilities.id

District information is taken from:

referencedata.geographic_zones.name

3. OLMIS Stock Status

Purpose: Stock availability monitoring dashboard.

Data mart:

• mart_stock_status

Data grain:

• One row per requisition line item.

Important note:

The authoritative stock data source is stockmanagement, which is not currently ingested into the analytics pipeline. The charts in this dashboard are based on stock values reported through requisitions.

Example output of the generated report:

Open

3.1 Stockout Rate Over Time (Line Chart)

Purpose

Shows the average stockout rate across requisition line items for each reporting period.

Example Output

Source (PostgreSQL)

combined_stockout is derived from:

requisition.requisition_line_items

A line item is considered stocked out when any of the following conditions are true:

• stockonhand = 0

• totalstockoutdays > 0

• beginningbalance = 0

• maxperiodsofstock = 0

Reporting period:

referencedata.processing_periods.enddate

3.2 Months of Stock Over Time (Line Chart)

Purpose

Shows the average months of stock available by district over time.

Example Output

Source (PostgreSQL)

Calculated as:

ROUND(stockonhand / averageconsumption, 1)

Source fields:

• requisition.requisition_line_items.stockonhand

• requisition.requisition_line_items.averageconsumption

District:

referencedata.geographic_zones.name

Notes

• When averageconsumption = 0, the calculation returns 0.

• This may appear as a stockout condition even when stock is physically available.

• Approximately 8% of stocked items are affected by this behavior.

3.3 Stock Status Distribution Over Time (Stacked Bar Chart)

Purpose

Shows the number of distinct facilities in each stock-status category for every reporting period.

Example Output