Surge Data Loader

Python data loader for producing sanitized Power BI JSON from RRMS/Molnix surge training event data.

The main loader:

• logs in to RRMS/Molnix at runtime using username/password credentials
• fetches event-organized data from https://rrms.ifrc.org/api/api/events
• keeps one top-level JSON object per event
• nests participant links inside each event
• enriches participants with safe gender, region, subregion, language, National Society, organization, and training/capability tag fields
• blocks forbidden personal fields before writing output
• supports incremental refreshes so routine runs do not reprocess the whole historical dataset
• writes compact, sparse JSON suitable for GitHub and Power BI

Outputs

Primary Power BI outputs:

data/powerbi/powerbi_training_events.json
data/powerbi/powerbi_loader_manifest.json
data/powerbi/trainings_facilitator_model.json

powerbi_training_events.json contains the event-organized dashboard data.

powerbi_loader_manifest.json contains run metadata such as pages fetched, events processed, participant links written, enrichment counts, output path, incremental-refresh counts, role counts, dashboard summary tables, slicer values, and any errors.

trainings_facilitator_model.json contains narrow Power BI model tables for filter-responsive visuals without expanding the large nested event JSON in Power BI.

The production event JSON is intentionally minified and omits empty/null fields. Power BI can load compact JSON normally, and the smaller file is easier to store in GitHub.

Optional human-review samples may also exist:

data/powerbi/powerbi_loader_manifest_pretty.json
data/powerbi/powerbi_training_events_tagged_sample_pretty.json

Training metrics, when fetched successfully, is written separately:

data/powerbi/training_metrics.json
data/powerbi/training_metrics_manifest.json

Event Output

Each top-level record is an event. Participant records remain nested under participants.

Current event-level fields include:

event_id
event_name
event_tag_name
event_tag_name_N
event_type
event_scale_type
event_from
event_to
event_updated_at
event_venue
event_organization
participants

event_tag_name is derived from source event tags and tag descriptions.

event_tag_name_N is derived from the event name itself, for names such as:

MOT - Managing IFRC Operations
Emergency Team Leader (ETL) training 2023

Participant fields include only dashboard-safe values such as person_id, role, participation dates, gender, region, subregion, language names/codes, person training/capability tags, National Society, and matched organization.

Participant enrichment fields include:

person_id
role
requested
participant_start
participant_end
result
waitlist
sex_or_gender
region_tag
region_label
subregion_tag
subregion_label
language_codes
language_names
person_training_tags
national_society_tag
national_society_name
matched_organization_id
matched_organization_name

role is the event-specific value from event.person[].pivot.role. Person tag group ROLES is not used for event participant/facilitator role because those tags describe surge or technical capabilities.

powerbi_loader_manifest.json also includes dashboard_summary, with compact precomputed data for cards, visuals, and slicers. This lets Power BI build the main dashboard from small summary arrays while keeping powerbi_training_events.json available for detail and validation.

For the reference participant visuals:

• participants_by_organisation_type uses National Society, IFRC, and Other. Participants without a matched National Society or IFRC organisation are counted as Other.
• participants_by_person_training_tag is limited to the five overview categories parsed from person tag groups training and Surge Trainings: Induction, CAP, ETL, MOT, and TAF.

Power BI Model Output

Use data/powerbi/trainings_facilitator_model.json for interactive Power BI reports. It contains:

tables.events
tables.participants
tables.participant_languages
tables.participant_training_tags
tables.training_tag_categories
tables.organisation_type_categories

Each table uses a compact shape:

{
  "columns": ["participation_id", "event_id", "person_id"],
  "rows": [[1, 7412, 5603]]
}

In Power Query, convert each table's rows list into a table using the matching columns list as headers. This avoids repeating field names hundreds of thousands of times in JSON.

Recommended relationships:

participants[event_id] many-to-one events[event_id]
participant_languages[participation_id] many-to-one participants[participation_id]
participant_training_tags[participation_id] many-to-one participants[participation_id]

Useful measures:

Participant Links = COUNTROWS(participants)
People Trained = DISTINCTCOUNT(participants[person_id])
Trainings = DISTINCTCOUNT(events[event_id])
Average Participants Per Training = DIVIDE([Participant Links], [Trainings])

Endpoints Used

The main RRMS/Molnix event loader uses:

POST https://rrms.ifrc.org/api/api/login
GET  https://rrms.ifrc.org/api/api/events?page=N
GET  https://rrms.ifrc.org/api/api/system/organizations
GET  https://rrms.ifrc.org/api/api/people/{person_id}/tags
GET  https://rrms.ifrc.org/api/api/people/{person_id}/languages
GET  https://rrms.ifrc.org/api/api/people/{person_id}

The login endpoint is called with RRMS_USERNAME and RRMS_PASSWORD. The returned access token is used in memory for the same run as:

Authorization: Bearer <access_token>

The separate training metrics command uses:

GET https://rrms.staging.molnix.com/api/api/v1/training-metrics
Api-Token: <TRAINING_METRICS_API_TOKEN with hyphens removed>

That command uses TRAINING_METRICS_API_TOKEN directly. Hyphens are stripped from the token before the request because the live endpoint accepts the token without the hyphen.

PII Policy

The loader does not save raw event pages, raw person detail payloads, or raw person tag payloads.

Forbidden fields are blocked before output is written, including:

firstnames
surname
fullname
email
phone
mobile
address
decrypted_ssn
passport
document
user
signatures
photo
avatar

Credentials

Do not commit credentials.

Local runs use a repo-root .env file copied from .env.example:

Copy-Item .env.example .env

Set:

RRMS_USERNAME=your_molnix_username
RRMS_PASSWORD=your_molnix_password

Optional tuning:

RRMS_PERSON_WORKERS=4
RRMS_REQUEST_RETRIES=5

Training metrics:

TRAINING_METRICS_URL=https://rrms.staging.molnix.com/api/api/v1/training-metrics
TRAINING_METRICS_API_TOKEN=your_training_metrics_api_token

The loader calls POST https://rrms.ifrc.org/api/api/login on each run and keeps the access token in memory only.

Running

Run tests:

uv run --extra dev pytest -q

One-page smoke test:

uv run python scripts/build_powerbi_json.py --max-pages 1 --verbose

Routine incremental refresh:

uv run python scripts/build_powerbi_json.py --all-pages --incremental --verbose --person-workers 4

Full rebuild:

uv run python scripts/build_powerbi_json.py --all-pages --verbose --person-workers 4

Use a full rebuild only when intentionally replacing the whole output.

Incremental Refresh

--incremental reads the existing powerbi_training_events.json, indexes events by event_id, and fetches newest RRMS event pages.

It:

• adds new events
• replaces events when both old and new records have event_updated_at and the value changed
• replaces existing events that were generated with an older participant-field schema
• skips unchanged existing events
• stops early when it reaches a page where all events are already known and unchanged

GitHub Actions

The scheduled workflow is:

.github/workflows/update-powerbi-json.yml

It runs daily and can also be started manually from GitHub Actions.

Required repository secrets:

RRMS_USERNAME
RRMS_PASSWORD
TRAINING_METRICS_API_TOKEN

The workflow event-loader command is:

uv run python scripts/build_powerbi_json.py --all-pages --incremental --verbose --person-workers 4

The workflow also runs:

uv run python scripts/fetch_training_metrics.py --incremental --verbose

It validates JSON output sizes and commits changed JSON files under data/powerbi/.

Training Metrics Endpoint

A separate command exists for the staging training metrics endpoint:

uv run python scripts/fetch_training_metrics.py --verbose

Routine incremental metrics fetch:

uv run python scripts/fetch_training_metrics.py --incremental --verbose

It writes separate files:

data/powerbi/training_metrics.json
data/powerbi/training_metrics_manifest.json