Week 6 · Deliver BI people trust › Day 39

Day 39. Refresh, security and report delivery

1. Why this matters

"Deliver reports" was the last step of the method you saw. Delivery is where pipelines meet people's mornings: the refresh must run after the load, the right people must see only their data, and the 08:00 email must arrive even when one source is late, with an honest note.

2. The Power BI service in one page

• Workspaces hold reports and semantic models; use separate dev/test/prod workspaces (or deployment pipelines) for anything shared.
• Licences (check current Microsoft pricing): Power BI Desktop is free; sharing content generally needs Pro (or Premium Per User) for creators and viewers, unless the workspace is on a Fabric capacity large enough to allow free viewers. Small companies usually buy Pro per user.
• Scheduled refresh: a limited number of refreshes per day on Pro (8), more on Premium/Fabric capacity. Cloud sources like Azure SQL need no gateway; on-premises sources (a file server, a local SQL Server) need the on-premises data gateway.
• Incremental refresh keeps old partitions and refreshes only recent ones (e.g. last 10 days); set up with RangeStart/RangeEnd parameters.

3. Refresh after load, not at a fixed time

If the pipeline finishes at 06:42 on a good day and 07:35 on a late day, a 07:00 refresh is either stale or wasteful. Trigger it from Dagster as the last asset:

@dg.asset(deps=[dg.AssetKey("fct_restaurant_daily")])
def powerbi_refresh(context, pbi: PowerBIResource):
    # 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})

Authenticate with a service principal allowed in the Power BI tenant settings and added to the workspace. Dagster also has a dagster-powerbi integration that represents reports and models as assets; check its status for your version.

4. Row-level security

Define roles in Desktop (Modeling → Manage roles) with DAX filters on dimensions:

-- role "Area manager": a mapping table user_area(email, region) related to dim_restaurant[region]
[email] = USERPRINCIPALNAME ()

Assign users or groups to roles in the service. Test with View as in Desktop and the service. RLS restricts viewers; workspace members with edit rights see everything, so viewers get the app or viewer role, not workspace membership.

5. Scheduled reports by email

Power BI subscriptions email a snapshot of a page on a schedule (and after refresh). Paginated reports (pixel-perfect PDFs/Excel, Report Builder) suit invoices, statements and long tables. Without Power BI, or for full control, render the report in Python:

# reports/daily_email.py
import duckdb, smtplib, ssl
from email.message import EmailMessage
from jinja2 import Template

con = duckdb.connect("data/wh.duckdb", read_only=True)
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"] = f"QSR daily {bdate}"; msg["From"] = SENDER; msg["To"] = ", ".join(TO)
msg.set_content("HTML report attached."); 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(SMTP_HOST, 587) as s:
    s.starttls(context=ssl.create_default_context()); s.login(SMTP_USER, SMTP_PASSWORD); s.send_message(msg)

For Microsoft 365, send through Graph sendMail with an app permission scoped to one sender mailbox instead of SMTP passwords. Render PDF with WeasyPrint or Playwright if a PDF is required.