Extend Microsoft Fabric with Custom APIs and Power BI Models

TL;DR

• UI-first works… until you need unsupported sources, custom logic, or event-driven automation.

• Use custom APIs to bridge systems; use Power BI models to standardize logic & security.

• Design for app-only auth, least privilege, observability, and governed models.

• Embed insights in Teams/SharePoint/Apps so analytics meets users where they work.

---

When to move beyond the UI (clear triggers)

• Repeated CSV/email/Excel handoffs to keep reports alive.

• Connector blind spots (legacy ERP, bespoke apps, IoT feeds).

• “Why is this number 2 days old?” (stale batch refresh, no event hooks).

• Complex, cross-system business rules that don’t fit Power Query alone.

• Compliance/audit concerns from shadow processes.

---

Reference architectures

1) Near-real-time via API → Lakehouse → Power BI

• Source system → Custom API (app-only) → Ingest function (Azure Functions/Logic Apps) → Delta/Parquet in Lakehouse → Power BI model (Incremental Refresh/Hybrid).

• Use: operational KPIs, hourly refresh, high volume.

2) Event-driven webhooks

• Source webhooks → API receiver (validates token, queues events) → Stream processor (enrich, dedupe) → Delta table & KQL cache → Power BI DirectQuery/Hybrid table.

• Use: alerts, exception dashboards, live ops.

3) Federated queries without landing data

• API gateway (rate-limited) → Fabric Dataflows Gen2 / Power BI DirectQuery → Semantic model (governed measures).

• Use: sensitive systems, small result sets, strict compliance.

---

Security & platform blueprint (do this first)

• Identity: Azure AD app-only (service principal) or managed identity for compute; no shared user creds.

• Permissions: Scope to specific workspaces/sites/APIs; avoid tenant-wide scopes.

• Secrets: Store in Key Vault; prefer cert auth over client secrets; rotate & alert.

• Network: Private endpoints/approved egress; TLS 1.2+ only.

• Rate limits & resilience: Retry with backoff + idempotency keys; circuit breakers for upstream failures.

• Observability: Centralized logs (App Insights/Kusto), correlation IDs, 4 golden signals (latency/traffic/errors/saturation).

• Data governance: PII classification, sensitivity labels, RLS/OLS in the model, lineage & impact analysis enabled.

---

Minimal auth pattern (Python + MSAL)

from msal import ConfidentialClientApplication import requests, json TENANT = "contoso.onmicrosoft.com" CLIENT_ID = "<app-id>" AUTH = f"https://login.microsoftonline.com/{TENANT}" SCOPE = ["https://analysis.windows.net/powerbi/api/.default"] # or Graph: ["https://graph.microsoft.com/.default"] app = ConfidentialClientApplication( CLIENT_ID, authority=AUTH, client_credential={"private_key": open("cert.pem").read(), "thumbprint": "<thumb>"} ) token = app.acquire_token_for_client(scopes=SCOPE) hdr = {"Authorization": f"Bearer {token['access_token']}"} # Example: list Power BI groups r = requests.get("https://api.powerbi.com/v1.0/myorg/groups", headers=hdr, timeout=30) print(r.status_code, r.json())

---

Power BI model patterns that scale

• Import + Incremental Refresh for large fact tables (partitioned by date).

• Hybrid tables (hot streaming + cold historical) for near real-time.

• DirectQuery for regulated sources; cache small dimensions with Dual storage mode.

• Semantic model as contract: Put business logic in measures, not reports; use calculation groups (time intel); centralize KPI definitions.

• RLS/OLS for security; test role combinations; document who sees what.

Refresh orchestration: Use REST API/Service Principals to chain refresh after data landings; alert on failures; stagger heavy models.

---

Embedding where work happens

• Teams: Power BI app tab, adaptive cards for alerts, deeplinks with filters.

• SharePoint/Portal: Secured web parts with row-level security intact.

• Line-of-Business apps: Power BI Embed (App Owns Data) + service principal; cache tokens server-side; enforce RLS.

---

Practical API use cases

• ERP/Legacy integration: Normalize custom fields via API; land to Lakehouse; merge with CRM data for unified pipeline health.

• Finance reconciliations: API thresholds → trigger Fabric jobs → write discrepancy table → Teams alert + dashboard tile.

• IoT/ops telemetry: Webhook → queue → stream-to-Delta; Hybrid table for live tiles and SLA alerts.

---

Common pitfalls & fixes

• Stale credentials → switch to certs/managed identity + automated rotation.

• Over-broad permissions → scope per workspace/site; separate app registrations per domain.

• Throttling → server-side pagination, conditional GETs (ETag), retry/backoff, batch endpoints where offered.

• Silent refresh failures → health pings, failure webhooks/Teams alerts, SLO dashboards on refresh success rate & duration.

• Logic in reports → move to model measures/calculation groups; version and test.

---

7-day starter plan

Day 1–2: Secure app reg (app-only), Key Vault secret/cert, least-privilege scopes.
Day 3: Build a tiny API client (list entities, pull delta since watermark).
Day 4: Land to Lakehouse (Delta), add basic schema & partitioning.
Day 5: Create a semantic model with 3–5 core measures; enable Incremental Refresh.
Day 6: Orchestrate refresh via REST after load; add failure alerts to Teams.
Day 7: Embed in a Teams channel; run a live demo; log/observe end-to-end.

---

Quick FAQ

Do I need Fabric + Power BI Premium for this?
You can start small (Pro/POC), but near-real-time, large models, or enterprise governance usually benefit from Premium/Fabric capacities.

Batch vs. real-time?
Default to batch + incremental; use Hybrid/DirectQuery only where latency matters and source can handle it.

Can I avoid landing data (compliance)?
Use DirectQuery to sanctioned APIs or data gateways; expect trade-offs in performance & DAX complexity.