Module 6: Deliver BI people trust · Lesson 6.4 · about 40 min
Refresh, security and report delivery
Refresh Power BI when the load ends, keep row-level security mappings in the warehouse, and send a morning email that never goes out twice.
Module 6 · about 40 minutes, plus the lab · read first: 3.5, 6.3. The Power BI service itself is covered in the Power BI course: publishing and sharing, scheduled refresh and gateways and row-level security. Here you’ll wire those to the pipeline: refresh when the load ends, keep the security mapping in the warehouse, and deliver a morning email that is on time, honest about missing data, and never sent twice.
1. Why this matters
“Deliver reports” is the last step of every pipeline, and the one people notice. Three things must be true at 08:00: the refresh ran after the load, each manager sees only their restaurants, and the email arrived exactly once, even when a hotel’s file was late, with a note saying so.
Think of a newspaper. The presses do not start at a fixed time whatever happens in the newsroom; they start when the last page is signed off. Each region’s edition carries only its local pages. And a delivery driver who is unsure whether they already dropped your paper checks the list before leaving a second one.
2. The Power BI service in brief
- Workspaces hold reports and semantic models. Use separate development and production workspaces (or deployment pipelines) for anything shared.
- Licences: Power BI Desktop is free. Sharing generally needs a paid licence for authors and viewers, unless the workspace sits on a capacity that allows free viewers. Plans and prices change, so check Microsoft’s current pricing page.
- Scheduled refresh has a daily limit that depends on the licence. Cloud sources such as Azure SQL need no gateway; sources on your own network (a file server, an office SQL Server) need the on-premises data gateway.
- Incremental refresh keeps old partitions and refreshes only recent ones (say the last 10 days), set up with
RangeStartandRangeEndparameters. It mirrors the look-back in your incremental dbt model.
3. Refresh after the load, not at a fixed time
If the load finishes at 06:42 on a good day and 07:35 on a late one, a 07:00 refresh is either stale or wasted. Make the refresh the last asset in Dagster:
@dg.asset(deps=[dg.AssetKey(["fct_restaurant_daily"]), dg.AssetKey(["fct_stay_nights"])])
def powerbi_refresh(context: dg.AssetExecutionContext, pbi: PowerBIResource) -> dg.MaterializeResult:
# POST https://api.powerbi.com/v1.0/myorg/groups/{workspace}/datasets/{dataset}/refreshes
run = pbi.trigger_refresh(workspace_id=pbi.workspace_id, dataset_id=pbi.dataset_id)
status = pbi.wait_for_refresh(run, timeout_s=1800)
return dg.MaterializeResult(metadata={"refresh_status": status})
PowerBIResource is a small class you write around the REST call. It signs in as a service principal (an app identity in Microsoft Entra ID) that the Power BI tenant settings allow and that is a member of the workspace. Dagster also has a dagster-powerbi integration that shows reports and models as assets; check its status for your version.
Maths and principle
Principle: a deadline is met only when every step before it finishes in time, so you design for the late days, not the average day.
Equation: if the load’s finish time has a 95th percentile , a fixed refresh at is stale on about 5 % of days. A triggered refresh sends at
where is the refresh and send time, with a partial-data path whenever .
Analogy: getting to the airport. You either leave early enough for the worst traffic and often wait, or you take a car that leaves the moment you are ready.
Where it breaks: a hard deadline with no partial-data path just moves the failure from “stale” to “late”.
Try it: the load ends by 06:50 on 90 % of days and by 07:30 on 99 %. Refresh takes 10 minutes; the email is due at 08:00. Is a fixed 07:00 refresh safe? (No: about 10 % of days are stale. Triggered, 99 % of days still make 08:00.)
4. Row-level security from the warehouse
Roles are defined in Power BI Desktop (Modeling → Manage roles) with a DAX filter. The pipeline’s job is the mapping table that says who may see what. Build it in dbt from a source the business owns (an HR export or a small reviewed CSV seed), not by typing emails into the report:
-- models/marts/sec_user_region.sql : one row per user per region they may see
select lower(trim(email)) as email, region
from {{ ref('seed_area_managers') }} -- reviewed by the report owner
where active
-- role "Area manager", filter on sec_user_region (related to dim_restaurant[region])
[email] = USERPRINCIPALNAME ()
Then test it: use View as in Desktop and in the service with a test user for each role, and keep a short table in the repo of who should see what. Remember that RLS restricts viewers only. Anyone who can edit the workspace (Admin, Member, Contributor) sees everything, so managers get the app or the Viewer role, never workspace membership.
5. Scheduled emails that are safe to retry
Power BI subscriptions email a snapshot of a page on a schedule or after a refresh. Paginated reports suit pixel-perfect PDFs and long tables. For full control, or without Power BI, send it from Python as the last Dagster asset:
# reports/daily_email.py
import duckdb, smtplib, ssl
from email.message import EmailMessage
from jinja2 import Template
def send_daily(bdate, missing_sources, as_of, cfg):
con = duckdb.connect("data/wh.duckdb")
if con.execute("select 1 from ops.sent_reports where report = 'qsr_daily' and business_date = ?", [bdate]).fetchone():
return "already sent" # a retry must not email executives twice
df = con.execute("select * from mart.fct_restaurant_daily where business_date = ?", [bdate]).df()
html = Template(open("reports/daily.html.j2").read()).render(
rows=df.to_dict("records"), bdate=bdate, missing=missing_sources, as_of=as_of)
msg = EmailMessage()
msg["Subject"], msg["From"] = f"Restaurant daily {bdate}", cfg.sender
msg["To"] = cfg.test_to if cfg.test_mode else ", ".join(cfg.recipients) # test mode: only you
msg.set_content("Your email app shows the HTML version.")
msg.add_alternative(html, subtype="html")
msg.add_attachment(df.to_csv(index=False).encode(), maintype="text", subtype="csv", filename=f"qsr_{bdate}.csv")
with smtplib.SMTP(cfg.smtp_host, 587) as s:
s.starttls(context=ssl.create_default_context()); s.login(cfg.smtp_user, cfg.smtp_password); s.send_message(msg)
con.execute("insert into ops.sent_reports values ('qsr_daily', ?, now())", [bdate])
return "sent"
For Microsoft 365, sending through the Microsoft Graph sendMail call with an app permission limited to one sender mailbox is safer than storing an SMTP password. Three guards, always: a sent record per report and business date, a test mode that sends only to you, and a partial-data rule agreed with the business. Keep recipient lists in config that the report owner reviews, never in code.
- Event-driven refresh
- The refresh starts when the load finishes, triggered by the orchestrator.
- Service principal
- An app identity that can call the Power BI API without a person's password.
- Row-level security
- Rules that filter rows per viewer; the who-sees-what table comes from the warehouse.
- Idempotent send
- Checking a sent record first, so a retry cannot send the same email twice.
- Physical meaning
- On a late Monday the chief operating officer gets one email at 07:52 with Sunday's numbers and a line saying "Hotel H3 not received". Not two emails, not Saturday's numbers, and not another region's restaurants.
“Retries make a pipeline safer.” For reading and writing tables with replace-only writes, yes. For side effects like emails, payments or API posts, a retry after a partial success repeats the action. Make those steps idempotent first, then allow retries.
6. Try it
- Publish your report to a Power BI workspace (a trial or developer tenant). If you cannot, document each step with Desktop screenshots instead.
- Set up incremental refresh on
fct_order_linesfor the last 10 days. - Build
sec_user_regionin dbt from a seed, add two RLS roles (by region and by brand) and test them with View as. - Write
reports/daily_email.pywith the HTML body, CSV attachment, partial-data banner, sent record and test mode. Send to yourself only. - Add it as the last Dagster asset, after the dbt models. Force a retry and show that no second email arrives.
- Optional: trigger a dataset refresh from Dagster with a service principal.
What to check: the refresh never runs before the marts are done; each test user sees only their rows; a retried run sends nothing; a missing source produces a banner, not a silent gap. Commit as feat(delivery): RLS, incremental refresh, event-driven refresh and idempotent email.
- Trigger the refresh and the email from the orchestrator when the load finishes; agree a partial-data rule for very late days.
- Keep the RLS mapping table in the warehouse, built from a reviewed source, and test every role with View as.
- RLS applies to viewers only; share through an app or the Viewer role.
- Emails need three guards: a sent record per report and date, a test mode, and a partial-data banner.
Check yourself
Sources
- Microsoft Learn: Power BI REST API, Datasets - Refresh Dataset In Group https://learn.microsoft.com/rest/api/power-bi/
- Microsoft Learn: Row-level security (RLS) with Power BI https://learn.microsoft.com/power-bi/enterprise/service-admin-rls
- Kleppmann, M. (2017), Designing Data-Intensive Applications, O'Reilly, Chapter 11 (idempotence and exactly-once side effects)
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.