# Power BI connection and refresh

## Readable references upgrade

Use contract 2026-10-31.2 and the updated `NgbvdFeed.pq`. Populate one new offline
snapshot, then require `readable_fields_ready=true` in its manifest and update
the shared SnapshotId parameter. The function refuses an upgrade-pending
snapshot, validates the public schema version, and puts business references and
action/service labels first. For this same-PC test only, set its static BaseUrl
to `http://ngbvd.mglsd.go.lc:8084/api/v2/power-bi/`; production stays HTTPS.

Use Survivor Code/CRB and names in visuals. Keep the HMAC keys for the existing
one-to-many relationships and hide those technical columns in the model's report
view. CRB, survivor codes and action labels can be duplicated or missing, so they
must not be assumed to be unique model keys. Raw database IDs are not provided.
Survivor Code/CRB are confidential business references; keep the source Private.

## Desktop model

1. Complete server deployment and confirm a published snapshot. Create an
   approved dedicated Power BI client with manifest, schema and selected feed
   scopes. Restrict it to the fixed egress IPs of the consuming gateway or test
   workstation. Credentials expire after 90 days.
2. In Power BI Desktop, create a blank query named `NgbvdFeed` and paste
   `power-bi/NgbvdFeed.pq` into its Advanced Editor. Keep its base origin static
   and change the hostname only for the approved staging/production environment.
3. Set the data source authentication to **Basic**: client key as username and
   secret as password. Store them in data source credentials, not M parameters.
   The API requires HTTPS. Set the source privacy classification to Private.
4. Retrieve `/api/v2/power-bi/manifest` with the approved client and create a
   **Text parameter named `SnapshotId`** containing its snapshot UUID. Use that
   same parameter in every table's query. The schema URL also requires its own
   exact scope; this is intentional.
5. Create the required table queries from `ExampleQueries.pq`. Start with
   survivors, incidents, actions, services and status history. Enable perpetrator
   and witness feeds only when needed and sized for the current source volume.
6. Define single-direction one-to-many relationships from
   `survivors.record_key` to each child table's `survivor_key`, and from
   `actions.record_key` to `action_services.action_key`. Avoid an ambiguous second
   active relationship path to services; choose the intended survivor or action
   path in the model. Use explicit measures at the correct grain.

The function follows `has_more`, not the returned page length, and sends the same
snapshot on every page. It has a bounded page count and fails rather than
silently truncating a model. It does not embed credentials or use query-string
API keys. No PBIX or tenant connection is fabricated in this pack.

## Service and gateway

Publish to the approved private workspace and configure Basic source credentials
in the Service/gateway separately. Use an approved gateway or controlled network
path with fixed egress; do not open the API to all internet addresses to make
cloud refresh work. Confirm licensing, gateway routing, credential validation
and model refresh in your tenant. Some authentication/capacity behavior differs
between Desktop and Service.

Set workspace roles, viewer RLS, Build/export permissions, device controls and
retention. API extraction scopes do not apply viewer-specific RLS automatically.
Never use Publish to web. A user who can access imported data can retain a copy;
revoking the API client cannot erase an already imported dataset.

Full daily replacement is the supported default. Existing source rows can be
edited or deleted; partitioning only by incident date can silently retain stale
history. This pack does not claim change-data-capture or deletion-aware
incremental refresh. Refresh limits, model size and duration must be tested for
the 14.4 million perpetrator/witness observations before loading every feed.

## Daily snapshot pinning

The server's midnight job publishes a new snapshot UUID after building it.
That alone does **not** update the Power BI model's `SnapshotId` parameter. An
unchanged parameter will eventually refer to an expired snapshot. Do not fetch
a separate unpinned latest manifest inside each table's query: those evaluations
can span a snapshot publication and select different generations.

Use one approved orchestrator for the model:

1. Wait until today's EAT snapshot is published successfully.
2. Read its manifest once, update the model's `SnapshotId` parameter once, then
   request the model refresh. All table queries use that pinned value.
3. Monitor the refresh until Completed and reconcile the resulting records.
   Parameter update or HTTP 202 means submission, not successful data loading.
4. Do not run concurrent orchestrators or an independent overlapping scheduled
   refresh for this model.

The optional `scripts/refresh_powerbi_model.py` implements that sequence. Run it
on the approved gateway/orchestrator host. It uses only Python's standard library
and never disables TLS verification or follows credential-bearing redirects.
It refuses an old EAT business date, a pending model refresh or a missing Text
parameter. Default mode sends metadata GETs only and reports a plan.

Provide these environment variables through the organisation's secret manager:

- `NGBVD_POWERBI_CLIENT_KEY`
- `NGBVD_POWERBI_CLIENT_SECRET`
- `POWERBI_ACCESS_TOKEN`: a current **delegated dataset-owner token** with
  `Dataset.ReadWrite.All`, acquired by an approved identity workflow.

The native Basic client used by this helper must not require HMAC signatures;
use an approved signing connector instead when that client policy is required.
This helper's standard refresh body is intended for a delegated user. Service
principal or enhanced-refresh orchestration requires its corresponding approved
tenant/capacity configuration and request format.

First run the plan, replacing the two IDs with the actual approved workspace and
model UUIDs:

```bash
python3 scripts/refresh_powerbi_model.py --workspace-id WORKSPACE_UUID --model-id MODEL_UUID
```

After confirming that the model uses **enhanced dataset metadata**, run:

```bash
python3 scripts/refresh_powerbi_model.py --workspace-id WORKSPACE_UUID --model-id MODEL_UUID --enhanced-metadata --apply
```

Enhanced metadata is required by this helper because older models need a
different parameter-update delay. The helper submits the refresh and reports
`refresh_submitted`; it does not wait for, or certify, completion. If a write
times out, inspect Power BI refresh history before retrying. Record completion
and failures through the existing monitoring process, without tokens or case
records in logs. Retry polling for today's ready snapshot through the external
scheduler with an agreed deadline; it never launches the server's build itself.

Official references:

- https://learn.microsoft.com/en-us/powerquery-m/web-contents
- https://learn.microsoft.com/en-us/power-query/connectors/web/web
- https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-parameters-in-group
- https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/refresh-dataset-in-group
- https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/get-refresh-history-in-group
