Power BI Service, Connectivity Modes, Security & Capstone

Connectivity modes

How does Power BI get its data: copy it into the model (Import) or ask the source each time (DirectQuery)?

Mode	Data stored in Power BI?	Refresh	Typical use
Import	Yes (compressed copy)	Scheduled (up to 8/day Pro, 48/day Premium)	Most reports; Excel, CSV, SQL
DirectQuery	No	Live per interaction	Large SQL databases, near-real-time dashboards
Live connection	No: connects to an existing semantic model (Analysis Services or a published Power BI dataset)	Live	One governed model shared by many reports
Composite model	Mix: some tables Import, some DirectQuery, plus other semantic models	Both	Large fact in DirectQuery, small dimensions imported (dual storage mode)
Direct Lake (Fabric)	Reads Delta tables in OneLake directly	Automatic on data change	Import-like speed with no copy; Fabric only

TipA practical rule

Start with Import. Move to DirectQuery or Direct Lake only if data volume or freshness requirements force you to. Reduce the data first: only needed columns, aggregated tables, filtered date range.

Publishing and the Service

Step by stepPublish a report

1. In Desktop: Home → Publish → sign in with a work/school account → choose a workspace (not "My workspace" if you want to share).
2. The Service now has a semantic model (the data and model) and a report (the visuals): the same objects in two items.
3. Open the report in the browser, check it, and Share with people or add it to an app.
4. Configure scheduled refresh (Settings → Semantic model → Scheduled refresh) with credentials; for on-premises sources install a gateway.

Workspaces, apps and sharing

Concept	Meaning
Workspace	A shared container for semantic models, reports, dashboards. Roles: Admin, Member, Contributor, Viewer
App	A packaged, read-only, curated set of reports/dashboards published to a wider audience (best way to distribute)
Share link	Give a person or group access to one report (they need appropriate licence)
Dashboard	One page of pinned tiles from several reports; supports alerts and mobile
Subscriptions & alerts	Email snapshots on a schedule; data alerts on card/gauge thresholds
Export / Analyze in Excel / Embed	PDF/PowerPoint export, live pivot tables on the model, secure embedding in portals
Deployment pipelines	Move content Dev → Test → Prod with controlled changes
Endorsement, sensitivity labels, lineage view	Governance: certified datasets, data classification, impact analysis

Refresh and gateways

• Cloud sources (Azure SQL, SharePoint Online, OneDrive files) refresh directly without a gateway.
• A standard gateway serves many users; a personal gateway is for one person's testing.
• Incremental refresh (right-click a table → Incremental refresh) loads only new or changed partitions (for example the last 7 days) instead of the whole fact table: essential for big tables. It needs RangeStart and RangeEnd parameters (Power Query).
• Refresh failures (expired credentials, changed schema, gateway offline) appear in the refresh history; enable failure notifications.

Row-level security (RLS)

RLS restricts the rows a user can see: a country manager sees only their country.

Static RLS

Step by stepCreate roles in Desktop

1. Modeling → Manage roles → Create. Name the role India.
2. Select the table dim_territory and add a DAX filter: [country] = "India".
3. Create roles UAE and Singapore the same way. Save.
4. View as (Modeling → View as) → choose a role to test what that person sees.
5. Publish; in the Service: Semantic model → Security → add users or groups to each role.

Because filters flow from dim_territory to fact_sales through the relationship, the India role sees only India's sales everywhere:

PythonCopy

import pandas as pd
D = "adventure_cycles/"
f = pd.read_csv(D + "fact_sales.csv"); t = pd.read_csv(D + "dim_territory.csv"); m = f.merge(t, on="territory_key")
role_view = m.groupby("country").agg(orders=("order_number", "count"), sales=("sales_amount", "sum"))
role_view["sales"] = (role_view.sales / 1e6).round(1)
role_view.rename(columns={"sales": "sales (₹ M) visible to that role"}).assign(**{"all-company total (₹ M)": round(f.sales_amount.sum() / 1e6, 1)})

Output

	orders	sales (₹ M) visible to that role	all-company total (₹ M)
country
India	6135	406.9	594.6
Singapore	1487	96.7	594.6
UAE	1378	91.0	594.6

3 rows × 3 columns

Dynamic RLS

One role for everyone, driven by a security table mapping user emails to territories:

-- Table 'UserSecurity'(user_email, territory_key); role filter on dim_territory:
[territory_key]
    IN CALCULATETABLE ( VALUES ( UserSecurity[territory_key] ),
                        UserSecurity[user_email] = USERPRINCIPALNAME () )

USERPRINCIPALNAME() returns the signed-in user's email. Add a person to the table and they get access automatically: no role edits.

Performance tuning

Performance Analyzer (View → Performance analyzer → Start recording → refresh visuals) shows, for each visual, the time spent in DAX query, Visual display and Other. If DAX dominates, optimise the measure or model; if display dominates, simplify the visual.

Practical wins:

Microsoft Fabric, Copilot and AI

Microsoft Fabric unifies data engineering, data factory, data warehouse, data science, real-time analytics and Power BI over one data lake called OneLake (Delta/Parquet files). Power BI's semantic models can use Direct Lake over lakehouse tables. For data professionals: learn SQL (this track), Python (next track), and then Fabric notebooks, pipelines and lakehouses.

Copilot in Power BI (needs a Fabric or Premium capacity): create a report page from a prompt, suggest measures and write DAX, summarise a page or report, and answer questions about the data. Prep data for AI (add descriptions, synonyms, verified answers) improves the answers. Treat it as a fast assistant, not an authority.

Governance and good practice

1. One certified semantic model per subject area, many thin reports on top (live connection).
2. Name items consistently (Sales – Overview, Sales_Model); keep an owner and a description.
3. Version control: PBIP + Git, deployment pipelines, a test workspace.
4. Document measures (description property) and keep a measure dictionary.
5. Limit who can export data; use sensitivity labels.
6. Monitor usage (usage metrics report) and remove unused reports.
7. Train users: the best dashboard is useless if nobody trusts or understands it.

Capstone: Adventure Cycles performance review

Deliverables checklist

The analysis the report must support: verify your page numbers against this independent calculation.

m = f.merge(t, on="territory_key").merge(pd.read_csv(D + "dim_product.csv"), on="product_key"); m["profit"] = m.sales_amount - m.total_cost
m["order_date"] = pd.to_datetime(m.order_date); m["year"] = m.order_date.dt.year
kpi = m.groupby("year").agg(sales=("sales_amount", "sum"), profit=("profit", "sum"), orders=("order_number", "nunique"))
kpi["margin_%"] = (kpi.profit / kpi.sales * 100).round(1); kpi["YoY_sales_%"] = (kpi.sales.pct_change() * 100).round(1)
kpi[["sales", "profit"]] = (kpi[["sales", "profit"]] / 1e6).round(1)
kpi