Skip to main content

Power BI — NHS Quickstart

📊 SQL Server · Power Query · DAX · Row‑Level Security · Gateway
Why Power BI in the NHS

When your Trust uses SQL Server and needs governed, interactive reports for managers or clinics, Power BI is often the fastest path. This guide focuses on safe patterns (parameterised SQL, RLS, Gateway) so you can publish quickly and securely.

⚙️ 10‑minute setup​

  1. Install Power BI Desktop (current version).
  2. Ensure you have read access to your reporting database (or a dev/schema).
  3. Confirm ODBC Driver 18 for SQL Server (for encrypted connections) is installed.

🚀 “Hello NHS” report (SQL Server → visual)​

1) Create a parameter for your server/database​

In Power Query → Manage Parameters:

  • pServer = YOURSERVER
  • pDatabase = NHS_Analytics

2) Create a safe, foldable query​

Power Query (Advanced Editor) — parameterised and encrypted connection:

Power Query (M) — parameterised native query
let
  Source = Sql.Database(pServer, pDatabase, [CreateNavigationProperties=false, Encrypt=true]),
  Query  = Value.NativeQuery(
            Source,
            "SELECT practice_id, start_time, attended
             FROM dbo.appointments
             WHERE start_time >= DATEADD(DAY, -30, SYSUTCDATETIME());",
            null, [EnableFolding=true])
in
  Query

Tip: Use Value.NativeQuery only when you need custom SQL; otherwise build queries with the GUI to preserve folding.

3) Add basic measures (DAX)​

DAX — measures
Total Appointments := COUNTROWS('appointments')

Attendance Rate :=
DIVIDE(
  CALCULATE(COUNTROWS('appointments'), 'appointments'[attended] = TRUE() ),
  [Total Appointments]
)

Median Wait (mins) :=
MEDIANX(
  'appointments',
  DATEDIFF('appointments'[start_time], 'appointments'[start_time], MINUTE)
)

Keep measures in a Measures table for clarity (Home → Enter Data → name it Measures, store measures there).

4) Visualise​

  • Add a bar chart with practice_id on Axis and Total Appointments as Value.
  • Add a card for Attendance Rate.
  • Save as NHS_KPI.pbix.

🔐 Row‑Level Security (RLS)​

  1. Model view → Manage roles.
  2. Add role PracticeViewer with filter on table that holds practice visibility, e.g.:
    Practice[practice_id] = USERPRINCIPALNAME() (or match on a mapping table).
  3. View as → test the role in Desktop.
  4. After publishing, assign users or AAD groups to the role in the Service workspace.

Prefer AAD groups over individuals; document role logic in the repo README.

☁️ Publish + Gateway + Refresh​

  1. Home → Publish to a Workspace (not My workspace).
  2. Open the dataset in the Service → Settings.
  3. Configure Credentials (OAuth/Windows as per Trust policy).

⚡ Performance & modelling tips​

  • Import mode for small/medium tables; DirectQuery when live, but watch latency.
  • Use a star schema (Fact Appointments → Dim Practice/Date).
  • Create a proper Date table and mark it as Date table.
  • Push heavy filters/calcs to SQL (folding) or pre‑aggregate with a view.
  • For large facts: use Aggregations or Incremental Refresh.

Incremental Refresh (example policy):

  • Parameterise RangeStart / RangeEnd in Power Query.
  • Filter start_time with >= RangeStart and < RangeEnd.
  • In Desktop: Table → Incremental refresh → Store data for 24 months, refresh last 3 days.

🧰 NHS‑ready add‑ons​

  • Shared datasets: centralise conformed models, build thin reports on top.
  • Dataflows Gen2: move repeated Power Query logic out of Desktop.
  • Deployment Pipelines: Dev → Test → Prod with rules for connections/parameters.
  • Power BI Projects (.pbip): store model/report as text files for Git versioning.
  • RLS + “View as” embedded in training packs for analysts.

🛡 IG & safety checklist​

  • Encrypt connections (Encrypt=true / ODBC Driver 18).
  • Filter at source; exclude free‑text PHI; apply small‑number suppression.
  • Secure with RLS + AAD groups; avoid sharing to Everyone.
  • Keep credentials in the Gateway or Service data source — not in queries.
  • Document data sources, owners, refresh cadence, and KPI definitions.

📏 Measuring impact​

  • Refresh latency (source load → dashboard updated).
  • Reliability (% successful refreshes).
  • Adoption (report views, active users).
  • Model quality (validation checks vs source, measure review sign‑off).

🔗 See also​

What’s next?

You’ve completed the Learn — Power BI stage. Keep momentum: