Monday, April 27, 2026

Power Platform Governance & COE Complete Guide

Power Platform Governance & COE — Complete Guide

Governance Fundamentals · DLP Policies · Environment Strategy · COE Starter Kit · ALM · Pipelines · Scenarios · Cheat Sheet

Governance Fundamentals
Data Loss Prevention (DLP) Policies
Environment Strategy & Lifecycle
COE Starter Kit & Adoption
ALM & Deployment Pipelines
Monitoring, Auditing & Compliance
Scenario-Based Questions
Cheat Sheet — Quick Reference

1. Governance Fundamentals

What is Power Platform governance and why does it matter?

Power Platform governance is the set of policies, processes, and tools that ensure the platform is used securely, efficiently, and in alignment with organisational standards.

Without governance:

Users create apps connecting to sensitive data without security reviews
Connectors expose corporate data to external services (consumer apps, personal accounts)
Hundreds of unmanaged environments consume capacity and storage with no oversight
Critical business processes run on flows owned by individuals who leave the organisation
Compliance violations occur when personal data flows to unsanctioned services

Key insight: The governance challenge is unique to Power Platform — it is a self-service platform by design. The goal is to enable innovation while preventing data leakage, shadow IT, and orphaned resources.

What are the three pillars of Power Platform governance?

Pillar	Focus Areas
Security & Compliance	DLP policies, connector restrictions, Azure AD conditional access, tenant isolation, data residency, sensitivity labels
Environment Management	Environment strategy (dev/test/prod), capacity planning, lifecycle (creation, monitoring, cleanup), access control
Adoption & Enablement	COE governance, training programmes, maker communities, app catalogues, support models, usage analytics

Tip: Governance without adoption = platform stagnation. Adoption without governance = security risk. Balancing the two is the central tension in Power Platform architecture.

What admin roles exist for Power Platform and what can each do?

Role	Scope	Use Case
Global Administrator	Full M365 + Power Platform	Too broad — avoid for regular admin
Power Platform Administrator	All environments, DLP, capacity, connectors, tenant settings	Primary Power Platform admin role
Dynamics 365 Administrator	D365 environments specifically	D365-focused admin
Environment Administrator	One specific environment	Delegated env-level management
System Administrator (Dataverse role)	Full access within one Dataverse environment	Customise, configure, manage data

Warning: Never assign Global Administrator for Power Platform administration. Use Power Platform Administrator — it has the right scope without unnecessary M365 access.

What is the Power Platform Admin Center and what can you manage from it?

The Power Platform Admin Center (admin.powerplatform.microsoft.com) is the central management portal.

Capability	Description
Environments	Create, configure, copy, reset, delete environments. Manage capacity and storage.
DLP policies	Create and manage Data Loss Prevention policies across tenant and environments.
Analytics	Usage reports for Power Apps, Power Automate, Copilot Studio across the tenant.
Capacity	Monitor Dataverse storage, file storage, log storage per environment.
Connectors	View and manage custom connectors across the tenant.
Tenant settings	Control who can create environments, AI features, sharing settings.
Support	Raise and manage Microsoft support tickets.

What are the most important tenant-level settings for governance?

Who can create production/sandbox environments — restrict to admins only. Prevents environment sprawl.
Who can create trial environments — balance exploration vs sprawl.
Power Apps/Power Automate for M365 users — enable/disable for specific Azure AD groups.
AI features (Copilot) — enable/disable generative AI features tenant-wide.
Sharing canvas apps — restrict who users can share apps with (organisation, specific groups, admins only).
Weekly digest emails to makers — notify makers when their resources are flagged.

Tip: Restricting environment creation to admins only is the single most impactful governance setting — it prevents the #1 governance problem: uncontrolled environment sprawl.

2. Data Loss Prevention (DLP) Policies

What are DLP policies in Power Platform and how do they work?

DLP policies control which connectors can be used together in a flow or app. They prevent sensitive business data from being exfiltrated to unsanctioned services by enforcing connector grouping rules.

Three connector groups:

Group	Description	Examples
Business	Approved connectors for business data	SharePoint, Dataverse, Teams, Outlook, SQL Server, Azure
Non-business (Personal)	Consumer/personal connectors	Twitter, Facebook, Gmail, personal OneDrive, Dropbox
Blocked	Cannot be used at all in this environment	High-risk or unvetted connectors

A flow or app can only use connectors from one group — Business OR Non-business, never both together. This prevents a flow from reading corporate SharePoint data and writing it to a personal Gmail.

Critical: An environment with no DLP policy allows any combination of connectors — corporate data can flow freely to consumer services. Always apply DLP before allowing maker access to an environment.

What is the difference between tenant-scoped and environment-scoped DLP policies?

Tenant-scoped DLP policy: applies to ALL environments in the tenant (or all except explicitly excluded ones). Set by Power Platform Administrator. Cannot be overridden by environment admins. Used for baseline security.

Environment-scoped DLP policy: applies to one or more specific environments. Can be set by Power Platform Admins or Environment Admins. Can be more restrictive than the tenant policy but never more permissive — tenant policy always wins.

Tenant policy (baseline):         Environment policy (stricter):
Business: SharePoint, Teams        Business: SharePoint only
Non-business: Gmail, Twitter       Blocked: Teams, Gmail, Twitter

Effective result for that environment:
= most restrictive combination of both policies
→ SharePoint in Business, Teams blocked, Gmail blocked

Tip: Always create a baseline tenant-wide DLP policy first. Then add environment-specific policies for additional restrictions in sensitive or production environments.

What are connector action controls in DLP policies?

Connector action controls allow admins to permit or block specific actions within a connector — rather than blocking the entire connector. This provides fine-grained control.

SharePoint connector action controls:
Allow: Get items, Get item, Create item, Update item
Block: Delete item, Delete attachment

HTTP connector action controls:
Allow: GET requests only
Block: POST, PUT, DELETE requests
→ Allows reading external APIs but prevents writing data out

Dataverse connector action controls:
Allow: List rows, Get row, Create row, Update row
Block: Delete row, Execute action, Perform bulk operations

Tip: Connector action controls are a governance maturity milestone — they allow enabling a connector for legitimate use while preventing its abuse. Much more nuanced than all-or-nothing connector blocking.

What are endpoint filtering rules in DLP policies?

Endpoint filtering rules restrict which specific URLs or hosts the HTTP connector, custom connectors, or certain other connectors can connect to.

HTTP connector endpoint rules:
Allow list:
  *.internal-company-api.com
  https://api.approvedsystem.com
Deny list (default):
  * (all other endpoints blocked)

Use case: allow Power Automate to call only internal
APIs — block calls to external/consumer services
via HTTP action even with custom endpoints

How do DLP policies interact with existing flows when a new policy is applied?

When a DLP policy is created or changed, existing flows are evaluated against the new policy
Flows that violate the new policy are suspended — they stop running immediately
The flow owner receives an email notification that their flow has been suspended
The flow remains suspended until: the policy is changed to permit the connectors, OR the flow is modified to comply
Power Platform Admin Center shows which flows are suspended and which policy caused it

Warning: Applying a new restrictive DLP policy to an existing environment can immediately suspend production flows. Always audit existing flows before applying a new policy — use the COE Starter Kit inventory to identify potentially affected flows first.

3. Environment Strategy & Lifecycle

What is environment strategy in Power Platform and what are the recommended patterns?

Environment strategy defines how environments are structured, purposed, and managed across the organisation.

Recommended environment tiers:

Tier	Purpose	Access	DLP
Default	Personal M365 productivity	All licensed users (restricted)	Strict — M365 connectors only
Developer	Individual maker experimentation	One maker only	Restrictive
Shared sandbox	Project development & testing	Project team	Standard business connectors
Test/UAT	Pre-production validation	QA team + business owners	Production-equivalent
Production	Live business applications	End users (read), admins	Most restrictive
COE/Admin	Governance tools and admin flows	COE team only	Permissive (for admin connectors)

How do you prevent environment sprawl in a large organisation?

Restrict environment creation — set "Who can create production and sandbox environments" to admins only in tenant settings
Environment request process — implement a formal request flow (Power Automate approval). COE Starter Kit includes this out of the box.
Environment lifecycle policies — auto-identify environments with no activity for 90 days via COE analytics → notify owners → delete if no response
Trial environment controls — trial environments auto-expire after 30 days. Ensure cleanup is monitored.
Regular audits — monthly review of all environments via COE Starter Kit inventory

Tip: Environment sprawl is the #1 governance problem in large tenants. Microsoft reports that uncontrolled tenants can have 1000+ environments — most unused. Proactive controls prevent this from the start.

What is the Default environment and what special considerations does it have?

Characteristic	Detail
Existence	Every tenant has exactly one — cannot be deleted
Membership	All licensed users automatically added as Environment Makers
M365 flows	Teams, Outlook, SharePoint-triggered flows run here by default
Risk level	Highest risk — anyone can create apps and flows here

Governance recommendations:

Apply strict DLP — allow only M365/productivity connectors, block all premium and personal connectors
Remove Environment Maker role from all users — grant only to approved individuals
Rename to signal its purpose: "Default — Personal Productivity Only"
Use COE Starter Kit to monitor and clean up unused resources regularly

Critical: The Default environment is the highest-risk environment in any tenant. Without governance it becomes a dumping ground for production apps with no oversight.

How do you handle environment capacity and storage management?

Three Dataverse storage types:

Type	Contains
Database storage	Dataverse table data, relationships, metadata
File storage	Attachments, images, file columns
Log storage	Audit logs, plugin trace logs

Capacity management practices:

Monitor via Admin Center → Capacity → Summary
Set storage alerts — get notified before hitting capacity limits
Regularly purge audit logs older than the retention period
Run Dataverse bulk delete jobs for old/inactive records
Use Azure Blob or SharePoint for file storage instead of Dataverse file columns where possible
Archive inactive environments' Dataverse data before deletion

4. COE Starter Kit & Adoption

What is the COE Starter Kit and what does it provide?

The Centre of Excellence (COE) Starter Kit is a free, open-source collection of Power Platform components published by Microsoft that helps organisations govern, monitor, and nurture their Power Platform adoption.

Key modules:

Module	Description
Core components	Inventory of all apps, flows, connectors, environments, and makers across the tenant. Synced daily via admin APIs.
Governance components	Compliance processes — maker compliance emails, app archival, orphaned resource cleanup, environment request flows.
Nurture components	Adoption tools — maker onboarding, training resources, app showcase/gallery, hackathon management, community tools.
Audit & compliance	Risk assessment of apps/flows, DLP violation reporting, sensitivity analysis.
Power BI dashboard	Tenant-wide analytics — top makers, most used apps, connector usage, risk scores, environment health.

Tip: The COE Starter Kit transforms Power Platform administration from reactive firefighting into proactive governance. It is the expected answer to "how do you govern Power Platform at scale."

What does the COE Core inventory collect and how does it work?

A scheduled cloud flow runs daily using the Power Platform Admin connector and Management APIs:

API calls:
Get Environments           → all environments in tenant
Get Apps as Admin          → all canvas and model-driven apps
Get Flows as Admin         → all cloud flows
Get Connectors as Admin    → all connectors used
Get Makers                 → all users who have created resources

Stored in Dataverse tables:
admin_Environment          → all environments
admin_PowerApp             → all canvas and model-driven apps
admin_Flow                 → all cloud flows
admin_Connector            → all connectors used
admin_Maker                → all users who have created resources
admin_PowerAppConnector    → connector usage per app
admin_FlowConnector        → connector usage per flow

Warning: The COE inventory sync requires a service account with Power Platform Administrator role. Use a dedicated service account — never a personal admin account (single point of failure risk).

What is the compliance process in the COE Governance module?

The governance module automates a compliance conversation with makers about their apps and flows:

Admin emails makers of apps/flows that haven't been reviewed — asking for business justification, data classification, and owner confirmation
Maker fills in a compliance form: app purpose, data sensitivity level, business owner, support contact
If no response within the grace period: app/flow is quarantined (shared access removed)
If still no response: app/flow is deleted (with backup)
Compliant resources are tagged and exempted from future compliance sweeps

Tip: This automated compliance process cleans up years of accumulated shadow IT without manual effort. The grace period approach avoids disrupting legitimate users while enforcing governance.

What does a Power Platform Centre of Excellence team look like?

Three layers of a mature COE:

Layer	Composition	Responsibilities
Central COE team	2–5 people (Architect, Admin Lead, Enablement Lead)	Platform strategy, DLP, environment strategy, security standards, COE Starter Kit
Champions network	Power users/citizen developers in each BU	First-line support, evangelism, quality gatekeeping for department apps
Professional developers	IT/dev team	Complex solutions requiring pro-code extensions (PCF, plugins, custom APIs)

Key principle: The COE's role is to enable — not gatekeep. The goal is a "managed self-service" model where makers can build freely within guardrails, not a bottleneck where everything goes through central IT.

What is the maker onboarding process in a governed environment?

User requests maker access via a Power Automate approval flow
Manager approves — confirms the maker needs to build on the platform
Automated onboarding: assigned Environment Maker role in appropriate dev environment, added to makers Azure AD group, added to maker community Teams channel
Welcome email with training resources, DLP policy overview, coding standards, ALM process documentation
Mandatory Power Platform fundamentals training before first app published to production
COE inventory automatically tracks the new maker and their subsequent resource creation

5. ALM & Deployment Pipelines

What is ALM for Power Platform and what does it involve?

ALM (Application Lifecycle Management) covers the processes and tools for developing, testing, and deploying Power Platform solutions in a controlled, repeatable way.

Core ALM elements:

Element	Description
Source control	Solution files stored in Git (Azure DevOps or GitHub) — tracked, versioned, peer-reviewed
Solution packaging	All components in a managed solution (apps, flows, tables, connection references, env variables)
CI/CD pipelines	Automated export → unpack → commit → build → deploy
Environment promotion	Dev → Test/UAT → Production with gated deployments
Connection references	Environment-agnostic connection pointers
Environment variables	Environment-specific configuration values

What is the Power Platform Build Tools and how is it used in CI/CD?

Power Platform Build Tools is an Azure DevOps extension (and GitHub Actions equivalent) providing pipeline tasks for automating Power Platform ALM.

# Typical CI pipeline (on commit):
- task: PowerPlatformToolInstaller@2
- task: PowerPlatformExportSolution@2
    inputs:
      authenticationType: 'PowerPlatformSPN'
      Environment: '$(DevEnvironmentUrl)'
      SolutionName: 'MyAppSolution'
      SolutionOutputFile: '$(Build.ArtifactStagingDirectory)/solution.zip'
- task: PowerPlatformUnpackSolution@2
    inputs:
      SolutionInputFile: '$(Build.ArtifactStagingDirectory)/solution.zip'
      SolutionTargetFolder: '$(Build.SourcesDirectory)/solutions/MyAppSolution'
- task: PowerPlatformChecker@2
    inputs:
      SolutionInputFile: '$(Build.ArtifactStagingDirectory)/solution.zip'

# Typical CD pipeline (to Production):
- task: PowerPlatformPackSolution@2
- task: PowerPlatformImportSolution@2
    inputs:
      Environment: '$(ProdEnvironmentUrl)'
      SolutionInputFile: '$(Pipeline.Workspace)/solution.zip'

What are Power Platform Pipelines (in-product) vs Azure DevOps pipelines?

	Power Platform Pipelines	Azure DevOps / GitHub Actions
Setup	Native in Admin Center	External DevOps tool required
Audience	Makers, citizen developers	Professional developers/DevOps
Customisation	Limited	Fully customisable
Testing	Manual	Automated test support
Best for	Simple solutions, maker-led deployments	Complex solutions, pro-code components, enterprise DevOps

Tip: Most mature enterprise teams use Azure DevOps for complex solutions and Power Platform Pipelines for simpler maker-built apps. Both can coexist in the same tenant.

What is Solution Checker and why should it be part of every deployment pipeline?

Solution checker analyses a Power Platform solution against best practice rules and returns issues by severity (Critical, High, Medium, Low, Informational).

What it checks:

Plugin and custom workflow code quality (deprecated APIs, missing error handling)
JavaScript web resource issues (deprecated client API usage)
Canvas app formula issues (delegation warnings, performance patterns)
Solution structure issues (missing dependencies, invalid references)

# Run via CLI in pipeline:
pac solution check --path ./solution.zip \
  --outputDirectory ./checker-results \
  --rulesetId 0ad12346-e108-40b8-a956-9a373e549abb

# Gate deployment:
# Critical issues > 0 → fail pipeline, block deployment
# High issues > threshold → require manual approval

Tip: Solution checker as a mandatory CI quality gate is the difference between a governed and ungoverned ALM process. It catches technical debt before it reaches production.

What are connection references and environment variables and why are they critical for ALM?

Connection references: solution components acting as pointers to connections. Flows reference a Connection Reference instead of a hardcoded connection. After solution import, each environment's Connection Reference is updated to point to the appropriate connection.

Environment variables: solution components storing configuration values that differ per environment (API URLs, email addresses, SharePoint site IDs, feature flags).

WITHOUT Connection References/Env Variables (broken ALM):
→ Flow hardcodes connection to dev SharePoint site
→ Import to prod → flow still points to dev site
→ Must manually edit flow in each environment
→ Error-prone, time-consuming, not repeatable

WITH Connection References/Env Variables (proper ALM):
→ Flow references "SharePoint Connection Reference"
→ Import to prod → update Connection Reference to prod SharePoint
→ Set env variable: SiteURL = "https://prod.sharepoint.com/sites/app"
→ No flow edits needed — fully automated, environment-agnostic deployment

Critical: Not using Connection References and Environment Variables is the #1 cause of broken Power Platform deployments. Every solution component with environment-specific configuration MUST use these.

6. Monitoring, Auditing & Compliance

What analytics are available natively in Power Platform Admin Center?

Report	Metrics
Power Apps analytics	Active users, app launches, sessions, errors, service performance
Power Automate analytics	Flow runs, success/failure rates, run durations, top flows
Copilot Studio analytics	Sessions, resolution rates, escalation rates per copilot
Capacity	Dataverse storage per environment, tenant-wide consumption
Connector usage	Which connectors are used, by which environments, top users

For advanced analytics beyond the built-in reports, use the COE Starter Kit Power BI dashboard which aggregates inventory, usage, risk, and compliance data across the entire tenant.

How do you audit Power Platform activity for compliance?

Microsoft Purview (formerly Compliance Center):

Power Platform operations are logged in the Microsoft 365 Unified Audit Log
Auditable events: app creation/deletion, flow creation/deletion, environment creation, DLP policy changes, permission changes
Accessible via Microsoft Purview compliance portal → Audit → Search

Power Platform Admin Center:

Environment-level activity logs
Admin activity logs (who changed DLP policies, who created environments)

Dataverse auditing:

Record-level create/update/delete/access tracking
Field-level change history
Accessible via audit history on records or Dataverse Web API

What is the Power Platform for Admins connector and how is it used for governance automation?

The Power Platform for Admins connector provides actions for programmatic management of Power Platform resources — used extensively in COE Starter Kit flows.

Key actions:
Get Environments                → list all environments
Create Environment              → provision new environments
Delete Environment              → remove environments
Get Apps as Admin               → inventory all canvas apps
Get Flows as Admin              → inventory all flows
Get Connectors as Admin         → inventory all connectors
Suspend Flow as Admin           → disable a flow remotely
Get DLP Policies                → list all DLP policies

Common governance automation patterns:
→ Daily inventory sync (COE core)
→ Auto-suspend flows violating new DLP policy
→ Send weekly digest to makers of their resource usage
→ Auto-delete trial environments after 30 days
→ Alert when storage exceeds 80% of capacity
→ Notify when a new environment is created in the tenant

7. Scenario-Based Questions

Scenario: A new CISO wants to ensure no corporate data leaks to consumer services via Power Automate. What do you implement?

Tenant-wide baseline DLP policy: move all Microsoft business connectors (SharePoint, Dataverse, Teams, Outlook, SQL Server, Azure services) to Business group. Move all consumer connectors (Gmail, Twitter, Facebook, personal OneDrive, Dropbox) to Non-business. Block high-risk connectors.
Production environment DLP policy: additional restriction — only Microsoft-approved connectors in Business group. Block all non-Microsoft connectors.
HTTP connector endpoint filtering: restrict HTTP action to only approved internal API endpoints.
Connector action controls: on sensitive connectors like Dataverse, allow Read operations but restrict bulk Delete or Export actions.
COE inventory monitoring: daily review of new connectors used across the tenant. Alert when a blocked connector is attempted.
DLP violation reports: weekly report from COE to CISO showing suspended flows, violation trends, and remediation status.

Warning: Exclude the COE environment from the tenant DLP policy — it needs broader connector access for admin flows. Use an explicit exclusion list.

Scenario: Your organisation has 800 environments after 3 years of ungoverned Power Platform usage. How do you clean this up?

Deploy COE Starter Kit — get full inventory of all 800 environments: owners, last activity, resource counts, storage consumption
Categorise: Active (resources used < 90 days), Stale (90–180 days inactive), Abandoned (180+ days inactive)
Owner notification campaign: automated email — "Your environment will be deleted in 30 days unless you confirm it is still needed"
Stale environment archival: export all resources to solutions, back up Dataverse data, delete the environment
Abandoned environment deletion: no owner response + no active resources → delete after backup
Restrict new creation: simultaneously change tenant setting to admin-only environment creation — prevent new sprawl while cleaning up old
Implement request process: going forward, all new environments require formal request with business justification approved by COE team

Tip: Tackle in waves — start with clearly abandoned environments (no activity, no resources), then stale. Never delete without backup and owner notification.

Scenario: A business-critical flow in production is owned by an employee who has left. How do you handle it?

Immediate remediation:

Use Power Platform Admin role to reassign flow ownership to a service account via Admin Center → Flows → Edit owner
Check the flow's connections — departed user's personal connections will be broken. Replace with service account connections or shared Connection References.
Check if the flow uses the departed user's credentials (SharePoint, Outlook) — replace with service account or delegated credentials
Test the flow end-to-end after ownership and connection transfer

Preventive measures:

COE governance policy: all production flows must be owned by a service account or team, not an individual
COE compliance process: quarterly audit of flow ownership — flag production flows owned by individuals
IT offboarding checklist: include a Power Platform asset transfer step — reassign all flows/apps before account deletion

Critical: Flow connections tied to personal user accounts are the #1 cause of production outages when staff leave. This is a governance failure that COE ownership policies prevent.

Scenario: How do you set up a complete Dev → Test → Production ALM pipeline for a Power Apps solution?

Setup:

Three environments: Dev (sandbox), Test (sandbox), Production. Separate DLP policies per environment.
Dedicated service principal for pipeline authentication (not a personal admin account).
Unmanaged solution in Dev. All components — app, flows, tables, connection references, environment variables.
Azure DevOps repo with branch strategy: feature branches → main → release branches.

CI pipeline (on commit to feature branch):

Export unmanaged solution from Dev environment
Unpack to source files → commit to repo
Run Solution Checker → fail build on Critical issues

CD pipeline — to Test (on merge to main):

Pack as managed solution
Import to Test environment
Set Connection References and Environment Variables for Test
Run automated smoke test flow
Notify QA team for UAT

CD pipeline — to Production (manual trigger + approval gate):

Same managed solution promoted from Test
Required approval from business owner in Azure DevOps
Import to Production
Set Production Connection References and Environment Variables
Post-deployment Teams notification to stakeholders

Scenario: How do you handle a maker who has built a production app in the Default environment using premium connectors?

Problem: Maker built a business-critical app in the Default environment using Dataverse — violating governance policy. Moving it is risky.

Assess impact: use COE inventory to identify all users of the app, data connections, and dependencies
Create a proper production environment: provision a new Production environment with correct DLP policy and security configuration
Export the app as a solution: package the app, its flows, Dataverse tables, and connection references into a managed solution
Migrate Dataverse data: use Data Export Service or Power Automate to copy data from Default to the new production environment's Dataverse
Redirect users: update the app URL, update any embedded links, communicate the change to users with a transition period
Delete or archive original: quarantine the Default environment copy first, verify all users are on the new app, then delete
Update governance policy: document this scenario in the maker guidelines — new apps requiring Dataverse must request a proper environment from the start

8. Cheat Sheet — Quick Reference

DLP Policy Configuration Reference

Connector groups:
Business     → approved for corporate data
Non-business → consumer/personal services
Blocked      → cannot be used at all

Policy scope:
Tenant-wide  → applies to ALL environments (with optional exclusions)
Environment  → applies to specific environments only
Precedence   → most restrictive policy wins

Key connectors to classify:
Business:    SharePoint, Dataverse, Teams, Outlook, OneDrive for Business,
             SQL Server, Azure Blob, Azure Service Bus, Azure Key Vault,
             Power BI, Dynamics 365, Microsoft Forms
Non-business: Gmail, Twitter/X, Facebook, Dropbox, personal OneDrive,
              Google Sheets, Slack (personal), Trello
Blocked:     HTTP (without endpoint filtering), custom connectors
             to unapproved endpoints

DLP violation outcome:
→ Existing flows: suspended immediately
→ New flows: cannot be saved/published
→ Owner notified by email with policy name and violation details

Environment Strategy Reference

Environment types and purposes:
Default      → personal M365 productivity. Strict DLP. Remove Maker role.
Developer    → individual maker sandbox. Free with Developer Plan.
Sandbox      → team dev/test. Reset-able. Not for production data.
Production   → live business data. Full backup. Strict access control.
COE/Admin    → governance tools only. Permissive DLP for admin connectors.

Creation governance:
→ Restrict to admins only (tenant setting)
→ Request process: maker submits → manager approves → admin provisions
→ Mandatory fields: business owner, purpose, expected lifetime, data classification

Lifecycle:
90 days inactive   → automated owner notification
120 days inactive  → quarantine (access suspended)
150 days inactive  → deletion (with backup)
Trial environments → auto-expire after 30 days

ALM Pipeline Checklist

Solution setup:
☐ Custom publisher prefix (not 'new_')
☐ All components in solution
☐ Connection References for all connectors
☐ Environment Variables for all env-specific config
☐ No hardcoded connection strings or URLs

CI pipeline:
☐ Export solution from Dev
☐ Unpack to source files
☐ Commit unpacked files to Git repo
☐ Run Solution Checker
☐ Fail on Critical issues

CD to Test:
☐ Pack managed solution
☐ Import to Test environment
☐ Configure Connection References
☐ Set Environment Variable values
☐ Run smoke test / automated tests
☐ Notify QA for UAT

CD to Production:
☐ Manual approval gate (business owner sign-off)
☐ Import same managed solution from Test
☐ Configure Production Connection References
☐ Set Production Environment Variable values
☐ Post-deployment notification
☐ Monitor for errors in first 24 hours

COE Starter Kit — Key Components

Core module:
→ Daily inventory sync (flows, apps, envs, makers, connectors)
→ Admin Power BI dashboard
→ Environment overview report

Governance module:
→ Compliance flow (maker emails, grace periods, quarantine, delete)
→ Environment request and approval process
→ Developer environment provisioning automation
→ Orphaned resource identification and cleanup
→ DLP violation reporting

Nurture module:
→ Maker onboarding welcome email automation
→ App gallery / showcase
→ Training resource hub
→ Maker community management
→ Hackathon toolkit

Admin connector actions used:
Get/List Environments, Apps, Flows, Connectors, Makers
Suspend Flow as Admin
Delete Environment
Add/Remove Environment Maker role

Governance Maturity Model

Level 1 — Reactive (no governance):
→ Default environment used for everything
→ No DLP policies
→ No inventory visibility
→ Issues discovered only when things break

Level 2 — Basic (foundational):
→ Tenant-wide DLP policy applied
→ Environment creation restricted to admins
→ COE Starter Kit deployed (inventory only)
→ Maker onboarding process defined

Level 3 — Managed (operational):
→ Environment strategy implemented (dev/test/prod)
→ DLP policies per environment type
→ COE governance module running (compliance emails)
→ ALM pipeline for key solutions
→ Regular environment audits

Level 4 — Optimised (mature):
→ Full COE with champions network
→ Automated environment lifecycle management
→ Solution Checker in CI/CD pipelines
→ Connection References + Env Variables everywhere
→ Monthly governance reviews with metrics
→ Risk scoring and remediation tracking
→ Fusion team model (makers + pro devs + IT)

Top 10 Tips

DLP = connector grouping, not content scanning — DLP in Power Platform prevents connectors from being combined, not scanning message content like M365 DLP. Clarify this distinction confidently.
Tenant policy wins over environment policy — environment policies can only be more restrictive, never more permissive. The tenant baseline always applies.
Restrict environment creation first — this is the single most impactful governance action. Mention it in every environment sprawl question.
Default environment is highest risk — every licensed user is a maker there by default. Always recommend removing the Maker role and applying strict DLP.
Connection References are mandatory for ALM — hardcoded connections are the #1 cause of broken deployments. Mentioning this signals real deployment experience.
COE Starter Kit is the governance answer at scale — know its three modules (Core, Governance, Nurture) and what each does. It is the expected answer to "how do you govern Power Platform."
Service accounts for production flows/apps — never personal user accounts.
Solution Checker in CI pipelines — quality gate that catches technical debt before production. A maturity signal in any ALM discussion.
DLP violations suspend existing flows — a new policy change immediately impacts running flows. Always audit before applying a new policy.
Governance vs adoption balance — Governance that blocks legitimate use is a failure just as much as no governance at all. The goal is "managed self-service."

Sunday, April 26, 2026

Microsoft Power BI Complete Guide

Microsoft Power BI — Complete Guide

Core Concepts · Data Modelling · DAX · Power Query · RLS · Service & ALM · Scenarios · Cheat Sheet

Core Concepts — Basics
Data Modelling
DAX — Data Analysis Expressions
Power Query & M Language
Row-Level Security & Object-Level Security
Power BI Service, Gateway & ALM
Performance Optimisation
Scenario-Based Questions
Cheat Sheet — Quick Reference

1. Core Concepts — Basics

What is Power BI and what are its main components?

Power BI is a business analytics service from Microsoft that enables users to connect to data, transform it, build data models, create visualisations, and share insights.

Component	Description
Power BI Desktop	Free Windows app for building reports and data models
Power BI Service	Cloud SaaS (app.powerbi.com) for publishing, sharing, collaboration
Power BI Mobile	iOS/Android apps for consuming reports on mobile
Power BI Report Server	On-premises report hosting for organisations that cannot use cloud
Power BI Embedded	Embed Power BI visuals/reports in custom applications via REST API
Power BI Gateway	Bridge between on-premises data sources and the Power BI Service

What is the difference between a Dataset, Report, and Dashboard?

Dataset (Semantic model): the data layer — contains tables, relationships, measures, and the connection to the data source. Multiple reports can share a single dataset.

Report: the visualisation layer — one or more pages of charts, tables, and visuals built from a single dataset. Created in Power BI Desktop, published to the Service.

Dashboard: a single-page canvas in the Power BI Service only (not Desktop). Pins individual visuals from multiple reports. Live tiles update automatically. Cannot be filtered interactively like a report page.

Key distinction: Reports are created in Desktop from one dataset. Dashboards are assembled in the Service from multiple reports. Datasets are shared and reused across many reports.

What are the data connectivity modes in Power BI?

Mode	Data Storage	Freshness	Performance	Best For
Import	Copied into VertiPaq in-memory engine	As of last refresh	Fastest	Most scenarios, large models
DirectQuery	Not stored — queries sent live to source	Always current	Slower (source-dependent)	Real-time requirements
Live Connection	Not stored — connects to existing AS model	Always current	Fast (remote model)	Reusing existing SSAS/Azure AS models
Composite model	Mix of Import and DirectQuery tables	Mixed	Variable	Large-scale with some real-time tables

Tip: Import = best performance, periodic refresh. DirectQuery = always current, slower. This is one of the most frequently asked architecture questions.

What is the difference between Power BI Pro, Premium Per User (PPU), and Premium capacity?

Licence	Key Features	Dataset Limit	Refreshes/Day
Free	Personal use, My Workspace only	1GB	8
Pro	Publish, share, collaborate	10GB	8
Premium Per User (PPU)	All Pro + paginated reports, deployment pipelines, AI	100GB	48
Premium capacity (P/EM/F SKUs)	Free viewers, dedicated compute, very large models	400GB+	Unlimited (API)

Warning: Free users can only view reports in Premium capacity workspaces — not Pro workspaces. This is a common licensing trap in enterprise deployments.

What is a Power BI Workspace and what types exist?

A workspace is a collaboration container in the Power BI Service holding datasets, reports, dashboards, and dataflows.

Type	Description
My Workspace	Personal workspace for each user. Not for sharing — private.
Collaborative workspace	Shared team workspace backed by Microsoft 365 group. Roles: Admin, Member, Contributor, Viewer.
Premium workspace	Assigned to Premium capacity — enables advanced features and free viewer access.

Tip: Always publish to collaborative workspaces, not My Workspace. Content in My Workspace is lost if the user leaves the organisation.

What is the VertiPaq engine in Power BI?

VertiPaq is the in-memory columnar storage engine that powers Power BI Import mode datasets. Key characteristics:

Stores data in compressed column segments (not row-based like traditional databases)
High compression ratios — especially on low-cardinality columns (like Country, Status, Category)
Loads entire dataset into RAM for fast query processing
DAX queries are evaluated against the VertiPaq in-memory store

Understanding VertiPaq explains why star schemas with low-cardinality dimension columns compress and query so efficiently in Power BI.

2. Data Modelling

What is a star schema and why is it recommended for Power BI?

A star schema organises data into a central fact table (measurable events/transactions) surrounded by dimension tables (descriptive attributes).

Dimension tables (one side):
DimDate     → DateKey, Year, Month, Quarter, WeekDay, IsHoliday
DimProduct  → ProductKey, Name, Category, SubCategory, Cost, ListPrice
DimCustomer → CustomerKey, Name, City, Country, Segment
DimEmployee → EmployeeKey, Name, Email, ManagerEmail, Region

Fact table (many side):
FactSales → DateKey, ProductKey, CustomerKey, EmployeeKey,
            Quantity, Revenue, Cost, DiscountAmount

Why recommended for Power BI:

Optimised for VertiPaq — column-store compression works best on low-cardinality dimension columns
Simpler DAX — relationships are clear and unambiguous
Faster query performance — fewer joins, fewer cross-filter paths
Easier to understand for report authors

Tip: Never bring in a flat denormalised table from a data warehouse as-is. Always reshape into a star schema in Power Query or the model layer.

What is the difference between a fact table and a dimension table?

Fact table: contains measurable, quantitative data about business events. High row count, fewer columns. Contains numeric measures (Revenue, Quantity, Cost) and foreign keys to dimension tables. Examples: Sales, Orders, Transactions, Web events.

Dimension table: contains descriptive, categorical attributes used to filter, group, and label fact data. Fewer rows, more columns. Contains the "who, what, when, where, why" context. Examples: Date, Product, Customer, Geography, Employee.

Warning: A common mistake is mixing descriptive and transactional data in a single table. Keep fact and dimension tables strictly separated.

What are relationship cardinality types and cross-filter directions?

Cardinality types:

Cardinality	Description	Use Case
*One-to-Many (1:)**	Standard star schema relationship	Dimension → Fact
*Many-to-One (:1)**	Same as 1:* from the other side	Fact → Dimension
One-to-One (1:1)	Rare — often indicates tables should be merged	Splitting wide tables
Many-to-Many (:)	Complex scenarios — use with caution	Bridge tables, role-playing dimensions

Cross-filter direction:

Single: filter flows from one-side (dimension) to many-side (fact). Recommended default.
Both: filter flows in both directions. Can cause ambiguous filter paths and unexpected results.

Critical: Avoid bi-directional relationships in star schemas. Use CROSSFILTER() in DAX to apply bi-directional filtering only where specifically needed.

What is a date table and why is it essential in Power BI?

A date table is a dedicated dimension with one row per date containing columns for Year, Quarter, Month, Week, Day, Weekday, IsWeekend, FiscalYear, etc.

Why essential:

Time intelligence DAX functions (SAMEPERIODLASTYEAR, DATESINPERIOD, etc.) require a properly marked date table
Consistent date filtering across all fact tables sharing the date dimension
Fiscal year/period calculations differing from calendar year
Handling gaps in fact table dates (weekends, holidays)

Requirements for marking a date table:
✓ One row per date, no gaps
✓ Date column must be Date data type (not DateTime)
✓ No blanks in the date column
✓ Covers the full range of all dates in all fact tables

Mark via: Table Tools → Mark as date table

Tip: Always create or import a proper date table. Never rely on Power BI's auto date/time feature in production — it creates hidden tables that bloat the model and cannot be customised. Disable it in Options → Data Load.

What is the difference between calculated columns and measures?

	Calculated Column	Measure
Evaluated	Row by row at refresh time	Dynamically at query time
Storage	Stored in model (consumes memory)	Not stored (computed on fly)
Returns	A value per row	A single aggregate value
Used in	Slicers, filters, rows/columns	Values area of visuals
Context	Row context	Filter context
Best for	Categorisation, bucketing, flags	KPIs, aggregations, time intelligence

Calculated column (stored, row-level):
Profit = FactSales[Revenue] - FactSales[Cost]
AgeGroup = IF(Age < 30, "Young", IF(Age < 50, "Mid", "Senior"))

Measure (dynamic, context-aware):
Total Revenue = SUM(FactSales[Revenue])
YoY Growth % = DIVIDE([Revenue] - [Revenue LY], [Revenue LY])

Critical: Calculated columns are frequently overused. Every calculated column consumes memory and slows refresh. If the computation depends on filters or context — it must be a measure.

3. DAX — Data Analysis Expressions

What is the difference between filter context and row context in DAX?

Filter context: the set of filters applied to the model when a measure is evaluated. Comes from slicers, report filters, visual columns/rows, and CALCULATE() filter arguments. Determines which rows are included in aggregations.

Row context: exists inside calculated columns and iterators (SUMX, AVERAGEX, etc.). Represents the current row being processed. Each row has its own context with all column values accessible.

Filter context:
Total Revenue = SUM(FactSales[Revenue])
-- SUM includes only rows matching the current filter context
-- If slicer filters Year=2024, only 2024 rows are summed

Row context in calculated column:
Profit = FactSales[Revenue] - FactSales[Cost]
-- Revenue and Cost are the values of the CURRENT row

Row context in iterator measure:
Weighted Avg Price =
  DIVIDE(
    SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice]),
    SUM(FactSales[Quantity])
  )
-- SUMX iterates every row; Quantity * UnitPrice uses row context per row

Tip: Filter context = what data is visible. Row context = which row am I on. Context transition = when row context converts to filter context inside CALCULATE().

What does CALCULATE() do and why is it the most important DAX function?

CALCULATE(expression, filter1, filter2, ...) evaluates an expression in a modified filter context. It is the only DAX function that can change the filter context — making it the foundation of almost all advanced DAX.

Filter override:
Revenue 2024 =
  CALCULATE(SUM(FactSales[Revenue]), Year[Year] = 2024)

Remove all filters on a column:
Revenue All Products =
  CALCULATE(SUM(FactSales[Revenue]), ALL(DimProduct))

Market share %:
Market Share =
  DIVIDE(
    SUM(FactSales[Revenue]),
    CALCULATE(SUM(FactSales[Revenue]), ALL(DimProduct))
  )

Year-over-year:
Revenue LY =
  CALCULATE(
    SUM(FactSales[Revenue]),
    SAMEPERIODLASTYEAR(DimDate[Date])
  )

YoY Growth %:
YoY Growth =
  DIVIDE([Revenue] - [Revenue LY], [Revenue LY])

Tip: If a measure needs to "ignore" a filter, "change" a filter, or "look at a different time period" — the answer is CALCULATE(). Almost every complex measure uses it.

What is the difference between ALL(), ALLEXCEPT(), and ALLSELECTED()?

ALL(table or column)
→ Removes ALL filters from the specified table/column
→ Used inside CALCULATE to ignore slicers/filters
% of Total =
  DIVIDE([Revenue], CALCULATE([Revenue], ALL(DimProduct)))

ALLEXCEPT(table, column1, column2, ...)
→ Removes ALL filters EXCEPT those on specified columns
→ Keep some filters, remove the rest
% of Category =
  DIVIDE([Revenue],
    CALCULATE([Revenue], ALLEXCEPT(DimProduct, DimProduct[Category])))
-- Each product shows its % within its own category

ALLSELECTED(table or column)
→ Removes filters from the visual level only
→ Keeps slicer/page/report filter context
% of Selection =
  DIVIDE([Revenue], CALCULATE([Revenue], ALLSELECTED(DimProduct)))
-- % within whatever is currently selected in slicers

What are the key time intelligence functions in DAX?

Year-to-date:
Revenue YTD = TOTALYTD([Revenue], DimDate[Date])
Revenue YTD = CALCULATE([Revenue], DATESYTD(DimDate[Date]))

Fiscal year YTD (fiscal year ends June 30):
Revenue FYTD = TOTALYTD([Revenue], DimDate[Date], "06-30")

Same period last year:
Revenue LY = CALCULATE([Revenue], SAMEPERIODLASTYEAR(DimDate[Date]))

Year-over-year growth:
YoY Growth % = DIVIDE([Revenue] - [Revenue LY], [Revenue LY])

Rolling 3 months:
Revenue Last 3M =
  CALCULATE([Revenue],
    DATESINPERIOD(DimDate[Date], LASTDATE(DimDate[Date]), -3, MONTH))

Month-to-date / Quarter-to-date:
Revenue MTD = TOTALMTD([Revenue], DimDate[Date])
Revenue QTD = TOTALQTD([Revenue], DimDate[Date])

Previous month:
Revenue PM = CALCULATE([Revenue], PREVIOUSMONTH(DimDate[Date]))

Warning: Time intelligence functions require a properly marked date table with no gaps. They return BLANK silently if the date table is not marked correctly.

What is DIVIDE() and why should you use it instead of the / operator?

Unsafe — errors on division by zero:
Margin % = [Revenue] / [Cost]
-- Returns error if Cost = 0 → breaks the visual

Safe — handles zero denominator gracefully:
Margin % = DIVIDE([Revenue], [Cost])
-- Returns BLANK if Cost = 0

With custom fallback:
Margin % = DIVIDE([Revenue], [Cost], 0)
-- Returns 0 instead of BLANK when Cost = 0

Best practice: Always use DIVIDE() for any division in DAX — never the / operator. This is asked in every Power BI .

What is RELATED() vs RELATEDTABLE() and when do you use each?

RELATED(column)
→ Used in calculated column on the MANY side (fact table)
→ Traverses the relationship to get a value from the ONE side (dimension)
→ Like a VLOOKUP — fetches a related table's value for the current row

Example (calculated column on FactSales):
Product Category = RELATED(DimProduct[Category])
-- For each sales row, fetches the product's category from DimProduct

RELATEDTABLE(table)
→ Used in calculated column on the ONE side (dimension table)
→ Returns a TABLE of all related rows from the MANY side
→ Used with COUNTROWS, SUMX, AVERAGEX, etc.

Example (calculated column on DimProduct):
Total Units Sold = COUNTROWS(RELATEDTABLE(FactSales))
-- For each product row, counts all its sales transactions

What are iterator functions in DAX and when should you use them?

Iterator functions (SUMX, AVERAGEX, MAXX, MINX, COUNTX, RANKX, FILTER) evaluate an expression for each row of a table in row context, then aggregate the results.

SUMX — sum of a row-by-row calculation:
Total Margin = SUMX(FactSales, FactSales[Revenue] - FactSales[Cost])
-- Cannot use SUM(Revenue) - SUM(Cost) when you need row-level calc first

AVERAGEX — average of a row-by-row expression:
Avg Order Value = AVERAGEX(FactSales,
  FactSales[Quantity] * FactSales[UnitPrice])

RANKX — rank within a table:
Product Revenue Rank =
  RANKX(ALL(DimProduct), [Revenue], , DESC, Dense)
-- Ranks each product by revenue across all products

FILTER — returns a filtered subset of a table:
Top Customers =
  COUNTROWS(FILTER(DimCustomer, [Revenue] > 100000))

4. Power Query & M Language

What is Power Query and what is it used for in Power BI?

Power Query (M language) is the ETL (Extract, Transform, Load) layer of Power BI. It connects to data sources, shapes and cleans the data, and loads it into the data model. Every transformation step is recorded and executed in order during refresh.

Common transformations:

Remove/rename/reorder columns
Filter rows by condition
Change data types, handle nulls/blanks
Split columns, merge columns, extract substrings
Unpivot columns (wide → tall, normalise denormalised data)
Pivot columns (tall → wide)
Merge queries (JOIN two tables)
Append queries (UNION multiple tables)
Group by (aggregate)
Add custom columns using M expressions

What is the difference between Merge and Append in Power Query?

Merge (JOIN): combines two queries horizontally — adds columns from one query to another based on a matching key. Equivalent to SQL JOIN.

Join Type	Description
Inner	Only matching rows from both tables
Left Outer	All rows from left, matching from right
Right Outer	All rows from right, matching from left
Full Outer	All rows from both tables
Left Anti	Rows in left with NO match in right
Right Anti	Rows in right with NO match in left

Append (UNION): combines two or more queries vertically — stacks rows. Equivalent to SQL UNION ALL. Column names must match.

Tip: Append is the correct way to combine multiple files from a folder (monthly Excel files). Merge is correct for enriching a fact table with dimension attributes.

What is query folding in Power Query and why is it important?

Query folding is the ability of Power Query to translate transformation steps into native queries executed by the data source — rather than downloading all data and transforming locally in Power BI.

With folding (efficient):
Source: SQL Server
Steps: Filter rows → Select columns → Group by
Power BI sends: SELECT col1, col2 FROM table WHERE ... GROUP BY ...
→ Source does the heavy lifting, only result is transferred

Without folding (inefficient):
Steps after a non-foldable operation
→ Power BI downloads ALL rows THEN transforms locally
→ Extremely slow for large tables

Check folding: Right-click a step → "View native query". If greyed out, folding is broken at that step.

Common folding breakers: adding an index column, certain custom column expressions using M functions not translatable to SQL.

Warning: Once folding breaks at a step, all subsequent steps also lose folding. Structure your query so foldable steps come first, non-foldable steps last.

What are parameters in Power Query and how are they used?

Parameters are named, typed values referenceable across multiple queries.

Parameter: ServerName = "prod-sql.database.windows.net"
Parameter: DatabaseName = "SalesDB"
Parameter: DaysToLoad = 90

Used in query:
Source = Sql.Database(ServerName, DatabaseName),
Filtered = Table.SelectRows(Source,
  each [LoadDate] >= Date.AddDays(Date.From(DateTime.LocalNow()), -DaysToLoad))

Common uses:

Environment switching (dev/test/prod server names)
Date range filtering (load last N days)
File path management (move files without breaking reports)
API base URLs (switch between environments)

What is incremental refresh and how does it work?

Incremental refresh loads only new and changed data on each refresh, rather than reloading the entire table. Dramatically reduces refresh time for large tables.

Setup steps:

Create two Power Query parameters: RangeStart (DateTime) and RangeEnd (DateTime)
Filter the date column in Power Query: [Date] >= RangeStart and [Date] < RangeEnd
In Power BI Desktop: right-click the table → Incremental refresh
Define: store data for X years, refresh data for Y days
Publish to Premium workspace (required for incremental refresh)

How it works at refresh time:

Only the most recent Y days are re-queried from the source
Historical partitions (older than Y days) are kept as-is
Source must support query folding for incremental refresh to work

5. Row-Level Security & Object-Level Security

What is Row-Level Security (RLS) in Power BI and how is it implemented?

RLS restricts which rows a user can see in a report, based on their identity.

Static RLS (hardcoded filter):
Role: "APAC Region"
Filter on DimGeography: [Region] = "APAC"
→ Anyone assigned this role sees only APAC data

Dynamic RLS (user identity — scalable):
Role: "SalesRLS"
Filter on DimEmployee: [Email] = USERPRINCIPALNAME()
→ Each user sees only their own rows
→ No separate role per user needed

Hierarchical RLS (manager sees team):
Filter on DimEmployee:
[Email] = USERPRINCIPALNAME()
  || [ManagerEmail] = USERPRINCIPALNAME()
→ Each user sees their own rows + their direct reports' rows

Implementation steps:

Define roles in Power BI Desktop (Modelling → Manage roles)
Publish dataset to Power BI Service
Assign users/groups to roles (Dataset → Security)
Test with "View as role" in both Desktop and Service

Tip: Dynamic RLS with USERPRINCIPALNAME() is the scalable enterprise pattern — one role covers all users. Static RLS requires a separate role per filter value — unmanageable at scale.

What is Object-Level Security (OLS) and how does it differ from RLS?

RLS: restricts which rows a user can see. The table and columns are visible — only rows are filtered.

OLS: restricts access to entire tables or columns. Users without permission cannot see the table/column at all — completely hidden from their model view.

	RLS	OLS
Restricts	Rows	Tables or columns
Visibility	Table/columns visible, rows filtered	Table/column completely hidden
Configured in	Power BI Desktop (Manage roles)	Tabular Editor or REST API
Use case	Region, department, personal data filtering	Hide salary, SSN, margin columns from certain roles

Tip: Use OLS to hide sensitive columns (salary, SSN, cost/margin) from certain roles. Use RLS to show the same columns to all users but filter which rows they see. Both can be combined.

6. Power BI Service, Gateway & ALM

What is a Power BI Gateway and when is it needed?

A Power BI Gateway is a software agent installed on an on-premises machine that bridges the Power BI Service (cloud) and on-premises data sources.

Gateway Type	Users	Use Cases
Standard (enterprise)	Multiple users, multiple data sources	Scheduled refresh, DirectQuery, Live Connection
Personal	Single user only	Scheduled refresh only — personal development

Common on-premises sources needing a gateway: SQL Server (on-prem), Oracle, SharePoint (on-prem), Excel/CSV on file shares, SAP, MySQL on-prem

Warning: The gateway machine must always be running when scheduled refreshes are due. Gateway outages are the #1 cause of refresh failures in enterprise deployments. Use a cluster of gateways for high availability.

What are Deployment Pipelines in Power BI?

Deployment Pipelines (Premium feature) provide a Dev → Test → Production promotion path for Power BI content within the Service.

Setup:

Create pipeline with three stages: Development, Test, Production
Assign a workspace to each stage
Develop in the Development workspace
Deploy to Test with one click — content copied to Test workspace
After testing, deploy to Production

Deployment rules: override data source connections and parameters per stage (dev DB → prod DB) without editing the report.

Tip: Deployment rules are the equivalent of environment variables for Power BI — they let you override connection strings per stage without touching the report file.

What are Dataflows in Power BI and when should you use them?

Dataflows are self-service ETL pipelines defined in the Power BI Service using Power Query Online. They extract, transform, and store data as reusable tables (entities) in Azure Data Lake Storage.

When to use:

Centralise common data preparation logic reused across many reports
Offload heavy transformations from individual datasets to a shared layer
Reduce refresh load — dataflow refreshes once, multiple datasets consume the result
Enable self-service analytics on top of IT-governed prepared data

Tip: Dataflows implement a "prepare once, use many" pattern — the Power BI equivalent of a data warehouse staging layer without needing Azure Synapse or Databricks.

What are sensitivity labels and data protection in Power BI?

Power BI integrates with Microsoft Purview Information Protection (MIP) to apply sensitivity labels to datasets, reports, and dashboards.

Labels flow downstream: if a dataset has a "Confidential" label, reports and exports built from it inherit the label automatically.

Capabilities:

Prevent exporting confidential reports to Excel/PDF/CSV
Encrypt exported files
Audit label usage in the Microsoft Purview compliance portal
Mandatory labelling policies — require users to apply a label before publishing

7. Performance Optimisation

What are the key Power BI performance best practices?

Data model:

Use star schema — avoid snowflake schemas (extra joins slow queries)
Remove unused columns and rows before loading
Avoid high-cardinality columns in dimension tables (prefer grouping)
Use integer surrogate keys for relationships (not text/GUID)
Disable auto date/time (Options → Data Load)

DAX:

Prefer measures over calculated columns for context-dependent logic
Use variables (VAR) to avoid repeating expensive expressions
Use DIVIDE() instead of /
Avoid FILTER(ALL(...), ...) patterns — expensive; use KEEPFILTERS() where possible
Avoid row-by-row operations on large tables without need

Power Query:

Enable query folding — apply filters and column selections early (before non-foldable steps)
Use incremental refresh for large tables
Remove unused columns at the source step, not after transformations

Report:

Limit visuals per page — each visual executes a separate query
Use bookmarks/buttons instead of many pages
Use aggregation tables for summary visuals over large fact tables

What is the Performance Analyzer and how do you use it?

Performance Analyzer (View → Performance Analyzer in Desktop) records the time each visual takes to render, broken down into:

DAX query: time to evaluate the measure/query
Visual display: time to render the chart/visual
Other: overhead

How to use:

Open Performance Analyzer panel
Click "Start recording"
Interact with the report (click visuals, change slicers)
Review query times — slow DAX queries indicate model/DAX issues; slow visual display indicates rendering issues
Copy the DAX query for slow visuals and analyse in DAX Studio

8. Scenario-Based Questions

Scenario: A Power BI report refreshes slowly (45 minutes). How do you optimise it?

Situation: Dataset has 10 tables, 50M+ rows, refresh takes 45 minutes.

Enable incremental refresh — only load new/changed rows each refresh. Reduces time by 80–90% for large tables. Requires Premium workspace.
Check query folding in Power Query — ensure filters and transformations fold to source. Non-folding steps force full data download.
Remove unused columns and rows in Power Query — never import what reports don't need.
Use aggregation tables for summary-level visuals — pre-aggregate so most queries hit the small summary table, not 50M rows.
Consider DirectQuery or Composite model for the largest tables — remove the need to import them.
Parallelise independent queries — split one large query into multiple smaller ones where possible.
Upgrade to Premium capacity for more compute resources and parallel refresh threads.

Scenario: Implement RLS where each salesperson sees only their own region, but their manager sees all their reports' regions.

Role: "SalesRLS"
Filter on DimEmployee:
[Email] = USERPRINCIPALNAME()
  || [ManagerEmail] = USERPRINCIPALNAME()

How it works:

Salesperson (a@company.com): only their own row passes → their region only
Manager (mgr@company.com): all rows where ManagerEmail = mgr@company.com → all direct reports' regions

Steps:

Ensure DimEmployee has Email and ManagerEmail columns from Azure AD or HR system
Create the "SalesRLS" role with the DAX filter above
Publish and assign ALL sales users to "SalesRLS" in Power BI Service
Test with "View as" for a salesperson, then a manager

Tip: This hierarchical pattern works for any manager-subordinate structure. Maintain ManagerEmail accuracy in the source system — it drives the entire access model.

Scenario: Your DAX measure shows the same total value for every row in a matrix. What is wrong?

Cause: The measure is not being filtered by the row dimension. Check in order:

Missing relationship — the field in matrix rows comes from a table not related to the fact table. No relationship = no filter propagation. Fix: add the relationship.
Wrong filter direction — relationship exists but single-direction filter flows away from the dimension, not toward the fact. Fix: verify direction in Model view.
Inactive relationship — relationship exists but is dashed (inactive). Fix: use USERELATIONSHIP() in the measure:
```
Revenue by Ship Date =  CALCULATE([Revenue],    USERELATIONSHIP(DimDate[Date], FactSales[ShipDate]))
```
ALL() in the measure — the measure explicitly removes filters. Review the measure definition.

Scenario: Design a Power BI solution for an executive dashboard with near-real-time KPIs.

Requirements: C-suite dashboard showing Revenue, Orders, NPS, Inventory — updated every 15 minutes, accessible on mobile.

Architecture:

Data layer: Import mode with incremental refresh scheduled every 15 minutes (Premium required for sub-hourly refresh). Or DirectQuery for true real-time.
Data model: star schema — DimDate, DimProduct, DimCustomer, DimRegion against FactSales, FactOrders.
Key measures: Revenue MTD, Revenue vs Target (%), Orders Today, NPS Score, Inventory Days of Cover, YoY Growth %.
Report design: KPI card visuals at top. Trend line charts. Geographic map visual. Mobile layout configured in Desktop → View → Mobile layout.
Dashboard: pin KPI cards to a Power BI Dashboard — enables data alerts (notify when Revenue drops below threshold).
Security: OLS on cost/margin columns for non-finance roles. RLS if execs are region-specific.
Distribution: publish to Premium workspace. Create an App for the dashboard. Enable mobile access.

Scenario: How do you handle multiple date relationships in a single fact table?

Problem: FactSales has three date columns — OrderDate, ShipDate, DeliveryDate. You have one DimDate table. You need to analyse by each date independently.

Setup:
- Create active relationship: DimDate[DateKey] → FactSales[OrderDate]
- Create inactive relationships: DimDate[DateKey] → FactSales[ShipDate]
                                  DimDate[DateKey] → FactSales[DeliveryDate]

Measures using inactive relationships:
Revenue by Ship Date =
  CALCULATE([Revenue],
    USERELATIONSHIP(DimDate[Date], FactSales[ShipDate]))

Revenue by Delivery Date =
  CALCULATE([Revenue],
    USERELATIONSHIP(DimDate[Date], FactSales[DeliveryDate]))

Default measure (no USERELATIONSHIP needed):
Revenue by Order Date = [Revenue]
-- Uses the active relationship automatically

9. Cheat Sheet — Quick Reference

Connectivity Modes

Import       → data copied into VertiPaq. Fastest. Periodic refresh.
DirectQuery  → live queries to source on every interaction. Always current. Slower.
Live Connect → connects to existing AS/SSAS model. No local model.
Composite    → mix of Import + DirectQuery tables in one model.

DAX Functions Quick Reference

Aggregation:
SUM(column)          AVERAGE(column)      COUNT(column)
MIN(column)          MAX(column)          DISTINCTCOUNT(column)

Logical:
IF(condition, true, false)
SWITCH(expression, val1, result1, val2, result2, else)
AND(bool1, bool2)    OR(bool1, bool2)     NOT(bool)
IFERROR(expr, alt)   ISBLANK(expr)        COALESCE(val1, val2)

Filter context:
CALCULATE(expr, filter1, ...)
ALL(table/column)    ALLEXCEPT(tbl, col)  ALLSELECTED(col)
FILTER(table, condition)
KEEPFILTERS(filter)  REMOVEFILTERS(col)

Relationship navigation:
RELATED(column)           -- many side → one side
RELATEDTABLE(table)       -- one side → many side
USERELATIONSHIP(col1, col2) -- activate inactive relationship

Iterators (row context):
SUMX(table, expression)   AVERAGEX(table, expr)
MAXX(table, expr)         MINX(table, expr)
COUNTX(table, expr)       RANKX(table, expr)

Time intelligence:
TOTALYTD([measure], date)     TOTALMTD  TOTALQTD
SAMEPERIODLASTYEAR(date)      PREVIOUSMONTH(date)
DATESINPERIOD(date, last, n, period)
DATESYTD(date, [fiscalYearEnd])

Text:
CONCATENATE(text1, text2)   UPPER(text)    LOWER(text)
LEFT(text, n)   RIGHT(text, n)   MID(text, start, n)
LEN(text)   SUBSTITUTE(text, old, new)   FORMAT(value, format)

Math:
DIVIDE(num, denom, [alt])   ROUND(number, decimals)
ABS(number)   INT(number)   MOD(number, divisor)

Variables:
VAR x = expression
RETURN x + 1

Star Schema Template

DimDate       ← DateKey PK | Date | Year | Month | Quarter | Week | Weekday
DimProduct    ← ProductKey PK | Name | Category | SubCategory | Cost | Price
DimCustomer   ← CustomerKey PK | Name | Email | City | Country | Segment
DimEmployee   ← EmployeeKey PK | Name | Email | ManagerEmail | Region | BU

FactSales     ← DateKey FK | ProductKey FK | CustomerKey FK | EmployeeKey FK
               | Quantity | Revenue | Cost | Discount | OrderID

Relationships: All Dim[Key] → Fact[Key], one-to-many, single filter direction
Date table: marked as date table, no gaps, full date range coverage

RLS Patterns

Static RLS:
[Region] = "APAC"

Dynamic RLS (per user):
[Email] = USERPRINCIPALNAME()

Hierarchical (user + manager):
[Email] = USERPRINCIPALNAME()
  || [ManagerEmail] = USERPRINCIPALNAME()

% of Total (ignores RLS for denominator):
% Share = DIVIDE([Revenue],
  CALCULATE([Revenue], ALL(DimProduct)))

Test RLS: Desktop → Modelling → View as roles
         Service  → Dataset → Security → Test as role

Common Performance Issues & Fixes

Issue	Cause	Fix
Slow refresh	Full table reload every time	Enable incremental refresh
Slow refresh	Non-folding Power Query steps	Restructure query — foldable steps first
Slow visual	Expensive DAX measure	Use VAR, avoid FILTER(ALL()), use aggregations
Same value every row	Missing/wrong relationship	Add/fix relationship or use USERELATIONSHIP()
Blank time intelligence	Date table not marked	Mark table as date table, fix gaps
RLS not working	User not assigned to role in Service	Assign user to role in Dataset → Security
DirectQuery slow	Complex joins at source	Add indexes at source; simplify Power Query steps
Model too large	Too many columns imported	Remove unused columns in Power Query

Top 10 Tips

Filter context vs row context is the most-tested DAX concept. Know the difference, and know what "context transition" means (CALCULATE converts row context to filter context).
CALCULATE() is the answer to almost every advanced DAX question — "ignore filter", "change filter", "compare time periods" all use CALCULATE().
DIVIDE() not / — always use DIVIDE() for safe division. Mentioned in every DAX .
Star schema vs flat table — always recommend star schema. Know why (VertiPaq compression, simpler DAX, faster queries).
Mark date table — time intelligence functions silently fail without a properly marked date table. Know the requirements.
Dynamic RLS with USERPRINCIPALNAME() is the scalable pattern — one role for all users. Static RLS = one role per filter value = unmanageable.
Query folding — know what it is, why it matters, and what breaks it.
Calculated column vs measure — columns are stored and consume memory; measures are dynamic. Overusing calculated columns is the most common performance mistake.
Incremental refresh — the answer to "how do you refresh a 100M row table in minutes." Requires Premium, Power Query parameters, and a foldable date filter.
Deployment pipelines + deployment rules — the ALM answer for Power BI. Know that deployment rules are how you change connection strings per environment stage.

Microsoft Dataverse Complete Guide

Microsoft Dataverse — Complete Guide

Core Concepts · Data Modelling · Security · Business Logic · Plugins · Web API · Scenarios · Cheat Sheet

Top Hashtags: #MicrosoftDataverse, #PowerPlatform, #Dataverse, #PowerApps, #D365Developer, #LowCode, #MicrosoftDeveloper, #DataverseSecurity, #PowerPlatformALM, #BusinessApplications

Core Concepts — Basics
Data Modelling & Relationships
Security Model
Business Logic & Extensibility
Plugins Deep Dive
Web API & Integration
ALM & Solutions
Scenario-Based Questions
Cheat Sheet — Quick Reference

1. Core Concepts — Basics

What is Microsoft Dataverse and how does it relate to the Power Platform?

Dataverse is a cloud-based, low-code data platform built on Azure. It provides a secure, scalable, and structured storage layer for business data. It is the backbone of the Power Platform — Power Apps (Model-Driven), Power Automate, Power Pages, Copilot Studio, and Dynamics 365 all store and retrieve data from Dataverse.

Dataverse combines in a single managed service:

Relational database (Azure SQL under the hood)
Role-based security (table, record, and field level)
Business logic (plugins, business rules, low-code plugins)
Audit and compliance
REST API (OData Web API)
File and image storage
Search (Dataverse Search / Azure Cognitive Search)

Key positioning: Dataverse is not just a database — it includes security, logic, audit, and API layers all built in.

What is the difference between Dataverse and Dataverse for Teams?

	Dataverse	Dataverse for Teams
Hosting	Full Power Platform environment	Embedded in Microsoft Teams
Storage	Up to 4TB+ per environment	2GB per team
Plugins	Supported	Not supported
Business Units	Full hierarchy	Not supported
Security Roles	Full custom roles	Owner/Member/Guest only
API access	Full Web API	Limited
Licence	Power Apps/D365 licence required	Included with Microsoft 365
Best for	Enterprise applications	Simple team-level apps

Warning: Dataverse for Teams cannot be upgraded to full Dataverse in-place — data must be migrated manually if you outgrow it.

What is a Dataverse environment and what types exist?

An environment is a container that holds Dataverse data, apps, flows, and other resources. All resources share the same Dataverse database within an environment.

Environment Type	Description
Production	Live business data. Full backup/restore. Full SLA.
Sandbox	Development and testing. Can be reset/copied. Convertible to production.
Developer	Single-user personal environment. Free with Power Apps Developer Plan.
Default	One per tenant, auto-created. Every user can create apps — often ungoverned.
Trial	Temporary 30-day environment for testing premium features.

What are the standard table types in Dataverse?

Table Type	Description	Example
Standard	Custom tables created by makers	`cr123_order`, `cr123_invoice`
System	Built-in Dataverse tables, cannot be deleted	User, Team, Business Unit
Activity	Interaction tracking tables, inherit from Activity base	Email, Phone Call, Task, Appointment
Virtual	Surface external data without copying it	SQL table, SharePoint list
Elastic	High-volume, high-throughput, Cosmos DB-backed	IoT telemetry, event logs

What column types are available in Dataverse?

Text types:    Text, Multiline Text, Email, URL, Phone, Ticker Symbol
Number types:  Whole Number, Decimal, Float, Currency
Date types:    Date Only, Date and Time
Boolean:       Yes/No (Two Options)
Choice:        Choice (single select), Choices (multi-select)
Lookup:        Lookup (to another table), Customer (Account or Contact), Owner
File/Image:    File, Image
Special:       Unique Identifier (GUID), Formula, Rollup, Calculated, Auto Number

Tip: Know the difference between Choice (option set — values stored in metadata) and Lookup (foreign key relationship to another table).

What is the primary key in Dataverse and can it be changed?

Every Dataverse table has a system-generated GUID as its primary key (e.g., accountid for Account, contactid for Contact). This is auto-generated on record creation and cannot be modified.

For integration scenarios where you need to identify records by an external system's ID, use Alternate Keys instead of relying on the GUID.

2. Data Modelling & Relationships

What relationship types exist in Dataverse and how do they differ?

Relationship	Description	Implementation
One-to-Many (1:N)	One parent record → many child records	Lookup column on child table
Many-to-One (N:1)	Same as 1:N from child's perspective	Lookup column on child table
Many-to-Many (N:N)	Multiple records on both sides relate to each other	Auto-created intersection table

Tip: Dataverse does not support N:N with attributes on the relationship. If you need data on the relationship itself (e.g., role or effective date), create a manual intersection table with two lookup columns.

What is the difference between a Lookup, Customer, and Owner column?

Lookup: references a single specific table. A Case's Account lookup can only point to Account records.

Customer: a polymorphic lookup that can reference either an Account OR a Contact. Used when the related party could be either type. Example: the customerid column on Case.

Owner: a system lookup that can reference either a User OR a Team. Controls who owns the record — used in security/record-level access.

Warning: You cannot create additional Customer columns — only one built-in customerid pattern exists per table. For custom polymorphic lookups, use two separate lookup columns.

What are Calculated and Rollup columns and when do you use each?

Calculated column: computes its value from other columns on the same record using a formula. Recalculated every time the record is retrieved.

Example: FullName = FirstName + " " + LastName
Example: DaysOverdue = DIFFINDAYS(DueDate, NOW())
Example: DiscountedPrice = ListPrice * (1 - DiscountPercent / 100)

Rollup column: aggregates values from related child records. Calculated on a schedule (every hour or on-demand). Supports SUM, COUNT, MIN, MAX, AVG with optional filter conditions.

Example: Total Won Revenue = SUM(Opportunities.ActualRevenue)
         where Status = "Won"
Example: Open Case Count = COUNT(Cases) where Status = "Active"
Example: Last Contact Date = MAX(Activities.ActualEnd)

Rule: Use Calculated for real-time single-record computations. Use Rollup for aggregating across child records — but it is not real-time (up to 1-hour delay).

What are alternate keys in Dataverse and why are they useful?

Alternate keys define one or more columns (or combinations) as a unique identifier for a record — an alternative to the system GUID primary key. Enforced at the database level as a unique constraint.

Key uses:

Upsert via API — use the alternate key value in API calls to create-or-update without knowing the GUID
External system ID matching — store the external system's ID, use it for idempotent syncs
Prevent duplicates — database-level uniqueness enforcement

PATCH /api/data/v9.2/accounts(cr123_externalid='EXT-001')
Content-Type: application/json

{"name": "Contoso Ltd", "revenue": 500000}

This creates the account if EXT-001 doesn't exist, or updates it if it does — without querying for the GUID first.

What are Choice columns (Option Sets) and what are global vs local choices?

Local choice: option set defined for a single specific column on a single table. Values are unique to that column. Cannot be reused elsewhere.

Global choice: option set defined at the environment level and reused across multiple tables/columns. When values change, they update everywhere the global choice is used.

Best practice: Always use Global choices for values that represent the same concept across tables (e.g., a standard "Priority" choice used on Cases, Tasks, and Opportunities). Local choices are acceptable for table-specific options unlikely to be reused.

What is the purpose of Publisher and Solution in Dataverse?

Publisher: defines the customisation prefix (e.g., cr123_) applied to all custom components. Ensures no naming conflicts with other publishers or Microsoft's own components.

Solution: a container for all customisations — tables, columns, flows, apps, security roles, etc. Used for ALM — export from dev, import to prod. Solutions reference components; a component can be in multiple solutions.

Warning: Never use the default publisher prefix new_ in production solutions. Always create a custom publisher with a meaningful prefix for your organisation.

What are Virtual Tables and when should you use them?

Virtual tables surface data from external sources (SQL Server, SharePoint, Azure, REST APIs) inside Dataverse without physically copying the data. They appear and behave like regular Dataverse tables — usable in Power Apps, Power Automate, and Model-Driven apps.

Use when:

Data must remain in its authoritative source (regulatory/compliance requirement)
Real-time external data access is needed in Power Apps
You want to avoid data duplication and sync complexity
The external system already has an API or OData endpoint

Limitations: Virtual tables do not support plugins, business rules, or field-level security. They are read-only in most implementations unless the provider supports write operations.

3. Security Model

Explain the Dataverse security model — layers and how they interact.

Dataverse security has four layers that all must be satisfied:

Layer	Controls
Environment access	User must have a licence and security role in the environment
Security roles	What the user can do (CRUD + Append/AppendTo/Assign/Share) on each table at a defined scope
Record-level access	Which specific records can be accessed — controlled by ownership and sharing
Field-level security	Individual column access — overrides table-level for sensitive fields

Tip: All four layers must be satisfied for a user to access a specific field on a specific record. A user with table-level Read can still be blocked by field security or record ownership scope.

What are the access level scopes in a security role?

Scope	Name	Records Accessible
None	—	No records
User	Basic	Only records the user personally owns
Business Unit	Local	Records owned by anyone in user's BU
Parent/Child BU	Deep	User's BU + all child BUs
Organisation	Global	All records in the entire environment

Warning: Organisation (Global) scope is the most permissive. Never assign it carelessly. Most end-user roles should use User or Business Unit scope.

What are the Dataverse privileges beyond CRUD?

Create      → create new records
Read        → read record data
Write       → update existing records
Delete      → delete records

Append      → attach THIS record to another record
              e.g., adding a Note to a Case — the Note needs Append on Note table
AppendTo    → allow another record to be attached TO this record
              e.g., the Case needs AppendTo on Case table for Notes to attach

Assign      → change the owner of a record
Share       → share a record with another user/team beyond role access

Tip: Append + AppendTo is the most commonly misunderstood privilege pair. When a relationship write fails despite having Create and Write, check Append/AppendTo — it is almost always the missing piece.

What is the difference between Record Sharing and Security Roles?

Security roles: policy-based, apply to all records matching the scope. Broad, consistent, manageable at scale.

Record sharing: grants access to a specific individual record to a specific user or team — beyond what their security role allows. Used for exceptions.

Warning: Over-reliance on record sharing creates an unmanageable access model at scale. Design security roles to cover 95%+ of access requirements — use sharing only for genuine exceptions.

What are Business Units and how do they affect data access?

Business Units (BUs) form a hierarchical organisational structure. Every user and record belongs to exactly one BU. BUs affect which records a user can access based on their security role scope.

Common BU structures:

Geography: North / South / East / West regions
Department: Sales / HR / Finance / Operations
Subsidiary: Company A / Company B / Company C

All records created by a user in a BU are owned by that BU by default. Security role scope determines how far up/down the hierarchy a user can see.

What is Field Security in Dataverse and how is it configured?

Field Security restricts read/write/create access on individual columns, configured via Field Security Profiles.

Configuration steps:

Enable field security on the column (in column definition — toggle on)
Create a Field Security Profile defining allowed/denied access levels for that column
Assign the Field Security Profile to users or teams

Users without the profile see the field as blank and cannot write to it — even if they have table-level Read/Write. The System Administrator role bypasses field security.

Tip: Field security is additive to table security — a user needs both table-level Read AND the appropriate Field Security Profile to see a protected column.

What are Teams in Dataverse and what types exist?

Team Type	Description
Owner team	Can own records. Security roles assigned to the team apply to all members.
Access team	Cannot own records. Used for per-record sharing — members get access to specific records via team templates.
AAD Group team	Synced with an Azure AD security group or Microsoft 365 group. Membership managed in Azure AD.

4. Business Logic & Extensibility

What are the server-side extensibility options in Dataverse?

Mechanism	Language	Runs In Transaction	Best For
Plugin (sync)	C# .NET	Yes	Validation, field auto-population, cross-record updates
Plugin (async)	C# .NET	No	Notifications, external calls, logging
Low-code plugin	Power Fx	Yes	Maker-authored server-side logic
Custom API	C# .NET	Configurable	Reusable business operations as API endpoints
Cloud flow	Power Automate	No	Process automation, non-transactional logic
Business rule	Declarative	Entity scope: Yes	Field show/hide/set/validate
Calculated column	Formula	On retrieve	Derived field values
Rollup column	Aggregation	Hourly schedule	Aggregating child record values

What are Business Rules in Dataverse and what can they do?

Business Rules are declarative no-code rules configured in the form designer. They can execute client-side (form only) or server-side (all saves).

Capabilities:

Show or hide fields
Enable or disable (lock) fields
Set field values
Set field requirement level (required / not required / recommended)
Show validation error messages

Scope:

Form scope: runs in the browser form only — does not fire on API or flow saves
Entity (table) scope: runs on ALL save operations — form, API, Power Automate, plugin — server-side enforcement

Critical: Entity scope business rules run server-side on every save including via API and flows. Form scope only runs in the browser. This distinction separates mid-level from senior candidates.

What are Dataverse low-code plugins (Power Fx plugins)?

Low-code plugins allow makers to write server-side Dataverse logic using Power Fx — without writing C#. They run synchronously inside the database transaction.

Example — auto-set priority on create:
If(Revenue > 100000, Set(Priority, 'High'), Set(Priority, 'Normal'))

Example — validate on save:
If(EndDate < StartDate,
  Error("End date cannot be before start date"))

Example — auto-populate calculated field:
Patch(ThisRecord, {FullName: FirstName & " " & LastName})

Tip: Low-code plugins bridge the gap between Business Rules (limited logic) and traditional C# plugins (requires developer). Ideal for makers who need server-side logic.

What is Dataverse auditing and what can be tracked?

Dataverse auditing tracks create, update, delete, and access operations on records and fields.

Configuration levels:

Environment level: enable auditing globally
Table level: enable for specific tables
Column level: enable for specific columns

What is captured:

Who made the change (user)
When the change was made
Old value and new value for each changed column
Operation type (Create / Update / Delete / Access)

Audit logs are stored in the Audit table in Dataverse and accessible via the Audit History subgrid on records or via the Web API.

5. Plugins Deep Dive

What are Plugins in Dataverse and how do they work?

Plugins are C# .NET class libraries implementing the IPlugin interface that execute when a Dataverse event (message) occurs on a table. Registered on a specific step using the Plugin Registration Tool.

public class AutoSetPriority : IPlugin
{
    public void Execute(IServiceProvider serviceProvider)
    {
        var context = (IPluginExecutionContext)serviceProvider
            .GetService(typeof(IPluginExecutionContext));
        var serviceFactory = (IOrganizationServiceFactory)serviceProvider
            .GetService(typeof(IOrganizationServiceFactory));
        var service = serviceFactory.CreateOrganizationService(context.UserId);
        var tracingService = (ITracingService)serviceProvider
            .GetService(typeof(ITracingService));

        if (context.InputParameters.Contains("Target") &&
            context.InputParameters["Target"] is Entity target)
        {
            if (target.Contains("revenue") &&
                (decimal)target["revenue"] > 100000)
            {
                target["cr123_priority"] = new OptionSetValue(1); // High
            }
            tracingService.Trace("AutoSetPriority executed.");
        }
    }
}

What is the Plugin execution pipeline?

User/API call → Dataverse processes request

Stage 10 — PreValidation
  → OUTSIDE the database transaction
  → Can read current database state (pre-change)
  → Use for: input validation, permission checks, reading current values
  → Exception here: does not roll back DB (no transaction yet)

Stage 20 — PreOperation
  → INSIDE the database transaction
  → Target entity not yet written to DB
  → Use for: modifying input values before save, auto-populating fields
  → Can modify InputParameters["Target"] to change what gets saved
  → Exception here: rolls back the entire transaction

Stage 30 — Main Operation
  → Dataverse writes to the database

Stage 40 — PostOperation
  → INSIDE the database transaction
  → Record already written to DB
  → Use for: creating related records, chaining operations
  → context.OutputParameters["id"] has the new record's GUID
  → Exception here: rolls back the entire transaction including stage 30

Tip: PreOperation is the most commonly used stage — you can still modify the record before it saves. PostOperation is used when you need the new record's GUID.

What is the difference between synchronous and asynchronous plugins?

	Synchronous	Asynchronous
Transaction	Inside DB transaction	Outside transaction
User experience	Blocks UI until complete	Non-blocking
Timeout	2 minutes max	2 minutes max per attempt
Rollback	Throws exception = full rollback	Cannot rollback DB
Best for	Validation, field calculation	Notifications, external calls, logging
Performance impact	Direct — slows the save operation	None on save operation

Warning: Never do long-running operations (external HTTP calls, large queries) in synchronous plugins — they slow every save operation for every user.

What information is available in the Plugin execution context?

context.MessageName          // "Create", "Update", "Delete"
context.Stage                // 10, 20, 40
context.Mode                 // 0 = Sync, 1 = Async
context.PrimaryEntityName    // "account", "contact"
context.PrimaryEntityId      // GUID of record

context.InputParameters["Target"]    // Entity being created/updated
context.InputParameters["id"]        // GUID for Delete
context.OutputParameters["id"]       // GUID of newly created record (PostOp)

context.PreEntityImages["preImage"]  // Record values BEFORE the operation
context.PostEntityImages["postImage"]// Record values AFTER the operation

context.UserId               // ID of user who triggered the operation
context.InitiatingUserId     // Original user (different from UserId in async)
context.Depth                // Call depth (prevent infinite plugin loops — check Depth == 1)
context.OrganizationId       // Environment GUID

What are Pre and Post Entity Images and why are they important?

Entity images are snapshots of the record's data registered as part of the plugin step.

Pre-image: values of the record BEFORE the current operation. Available in Update and Delete steps. Must be explicitly registered in the Plugin Registration Tool.

Post-image: values of the record AFTER the current operation. Available in PostOperation of Create and Update. Contains all column values including those not in the Target.

// Why you need pre-images:
// On Update, context.InputParameters["Target"] only contains
// columns that CHANGED. To know ALL column values, register a pre-image.

if (context.PreEntityImages.Contains("preImage"))
{
    var preImage = context.PreEntityImages["preImage"];
    var oldStatus = preImage.GetAttributeValue<OptionSetValue>("statecode");
    var newStatus = target.GetAttributeValue<OptionSetValue>("statecode");
    if (oldStatus?.Value != newStatus?.Value)
    {
        // status changed — trigger notification
    }
}

6. Web API & Integration

What is the Dataverse Web API and what does it support?

The Dataverse Web API is a RESTful OData v4 API providing full CRUD access, plus actions, functions, and batch operations. Uses OAuth 2.0 authentication.

Base URL: https://{org}.crm.dynamics.com/api/data/v9.2/

CRUD operations:
GET    /accounts                              ← list all accounts
GET    /accounts({guid})                      ← single record
POST   /accounts                              ← create (returns 204 + OData-EntityId header)
PATCH  /accounts({guid})                      ← update
DELETE /accounts({guid})                      ← delete

OData query options:
?$select=name,revenue,createdon              ← specific columns
?$filter=revenue gt 100000 and statecode eq 0 ← filter
?$orderby=createdon desc                      ← sort
?$top=25&$skip=50                            ← pagination
?$expand=primarycontactid($select=fullname)  ← join related table
?$count=true                                  ← include total count

Upsert via alternate key:
PATCH /accounts(cr123_externalid='EXT-001')

Execute action (Custom API):
POST /cr123_CalculateDiscount

What is FetchXML and when would you use it over OData?

FetchXML is an XML-based query language native to Dataverse. Supports advanced querying not available in OData.

<fetch top="10" aggregate="true">
  <entity name="opportunity">
    <attribute name="estimatedvalue"
      aggregate="sum" alias="TotalValue"/>
    <attribute name="ownerid"
      groupby="true" alias="Owner"/>
    <filter>
      <condition attribute="statecode" operator="eq" value="0"/>
    </filter>
    <link-entity name="account"
      from="accountid" to="accountid" alias="acct">
      <attribute name="industrycode" alias="Industry"/>
      <filter>
        <condition attribute="statecode" operator="eq" value="0"/>
      </filter>
    </link-entity>
  </entity>
</fetch>

Use FetchXML when you need:

Aggregate queries (SUM, COUNT, AVG, GROUP BY)
Complex multi-table joins with filters on joined tables
Queries on link-entity attributes
Features not supported in OData

Pass FetchXML via Web API:

GET /accounts?fetchXml=<fetch...>

What is Change Tracking in Dataverse and how is it used for integration?

Change Tracking allows external systems to retrieve only records that have changed since the last sync.

Initial sync — get all records + deltaLink:
GET /accounts?$select=name,revenue
Response: @odata.deltaLink = ...?$deltatoken=XYZ123

Store the deltaToken. On next sync:
GET /accounts?$deltatoken=XYZ123
Returns: only new/modified records + deleted record IDs

Use in integration:

Enable Change Tracking on the table
Store deltaToken after each sync run
Pass deltaToken on next sync to get only changes since last run
Process inserts, updates, and deletes separately

Tip: Change Tracking is far more efficient than querying by ModifiedOn date — it is the recommended pattern for all scheduled data sync integrations.

What are Dataverse webhooks and how do they enable real-time integration?

Webhooks push event notifications to an external HTTPS endpoint when a Dataverse event occurs. Registered via the Plugin Registration Tool alongside plugins.

How it works:

Register a webhook endpoint URL in the Plugin Registration Tool
Register a step (table + message + stage) to trigger the webhook
When the event fires, Dataverse POSTs the execution context payload to the URL
The external system must respond within 60 seconds

Webhooks vs Service Bus:

Webhooks: simple HTTPS endpoints — Azure Functions, Logic Apps, Power Automate HTTP trigger. Fire-and-forget with basic retry.
Azure Service Bus: guaranteed delivery, configurable retry, decoupled async processing. Use for critical integrations where message loss is unacceptable.

What is the $batch endpoint and when should you use it?

The $batch endpoint allows sending multiple API operations in a single HTTP request — up to 1000 operations per batch.

POST /api/data/v9.2/$batch
Content-Type: multipart/mixed; boundary=batch_boundary

--batch_boundary
Content-Type: application/http
POST /api/data/v9.2/accounts HTTP/1.1
{"name": "Company A"}

--batch_boundary
Content-Type: application/http
POST /api/data/v9.2/accounts HTTP/1.1
{"name": "Company B"}
--batch_boundary--

Use $batch when:

Importing large numbers of records (reduces API calls from N to N/1000)
Performing atomic multi-record operations (use changeset inside batch for transaction)
Integration scenarios requiring high throughput

7. ALM & Solutions

How do you manage Dataverse customisations with solutions?

# Export solution
pac solution export --path ./solution.zip --name MyDataverseSolution --managed

# Import solution
pac solution import --path ./solution.zip

# Clone solution for development
pac solution clone --name MyDataverseSolution --outputDirectory ./src

# Publish all customisations
pac solution publish

Solution component types:

Tables (with columns, relationships, forms, views, charts)
Global choices
Security roles
Business rules
Plugins and Custom APIs
Power Apps (Canvas and Model-Driven)
Power Automate flows
Connection references and environment variables

What is the difference between managed and unmanaged solutions?

	Managed	Unmanaged
Editable in target	No — read-only components	Yes — full editing
Used in	Production, UAT	Development
Delete behaviour	Removes all components on solution delete	Components remain
Layering	Stacks on top of other solutions	Single layer
Recommended for	Deployment pipeline output	Active development

What are solution layers and why do they matter?

Dataverse supports layered customisations — multiple solutions can customise the same component, and layers are applied in a defined order (base → extensions). The top layer wins.

Why it matters:

Microsoft updates apply as a base layer — your customisations overlay them
ISV solutions can be extended without modifying the original
Unmanaged customisations always sit on top — they can accidentally override managed solution behaviour
Use pac solution check to detect layer conflicts before deployment

8. Scenario-Based Questions

Scenario: Design a Dataverse data model for a multi-tenant B2B application.

Requirement: Multiple companies use the system. Each company's data must be completely isolated.

Architecture:

Business Units as tenant boundaries — create one BU per tenant company
Assign users to their company's BU — never to the root BU
Security roles with BU scope — users can only read/write records in their own BU
All custom tables use organisation-owned records with BU-partitioned ownership
Alternate keys on each table for the tenant's external system ID — enables upsert
Service accounts for integrations use Organisation scope — strictly controlled
Virtual tables if tenant data must remain in their own systems

Tip: Business Unit per tenant is the standard Dataverse multi-tenancy pattern. Never rely on app-level filtering — enforce isolation at the security layer.

Scenario: A plugin is causing records to take 8 seconds to save. How do you diagnose and fix it?

Situation: Saving an Account record takes 8+ seconds. A synchronous PostOperation plugin is registered on Account Update.

Plugin Profiler in Plugin Registration Tool — capture execution timing, identify slow step
Check for outbound HTTP calls inside the sync plugin — move to async or webhook pattern
Check for RetrieveMultiple without proper filters — full table scan on every save is a common culprit
Move non-critical logic (notifications, external calls, logging) to an asynchronous plugin
Replace outbound HTTP with webhook → Azure Function pattern — keeps plugin thin
Consider replacing entirely with a Power Automate cloud flow for non-transactional logic

Scenario: Design a reliable nightly data sync from external SQL into Dataverse.

Design:

Alternate keys on Dataverse tables using the SQL primary key — enables upsert
Change Tracking on SQL (SQL Server CT or ModifiedOn column) — extract only changed rows
Azure Data Factory or Power Automate — schedule nightly pipeline
Upsert via alternate key — PATCH to Web API using external ID
$batch endpoint — send up to 1000 upserts per HTTP request
Error handling — capture failed records, retry with exponential backoff, log to Dataverse audit table
Idempotency — re-running the sync for the same dataset produces the same result

Scenario: A user can't see a field on a Model-Driven form despite having Read on the table.

Three causes — check in order:

Field Security Profile — the column has field-level security enabled. User's account or team is not assigned the profile granting Read. Fix: assign the Field Security Profile to the user or their team.
Business Rule — a Business Rule is hiding the field based on another column's value. Check all Business Rules with "Form" scope on the table — one may have a hide condition matching the user's data.
Wrong form — the field is not on the form shown to this user's security role. Check form order and role assignment — the user may be seeing a different Main form that doesn't include the field.

Critical: Field Security Profile absence is the most common cause. Always check it first when a field shows blank despite table-level Read access.

Scenario: How do you prevent a plugin from causing an infinite loop?

Problem: A PostOperation plugin on Account Update calls service.Update() to set a flag — which re-triggers the same plugin, causing infinite recursion.

Fix — check execution depth:

public void Execute(IServiceProvider serviceProvider)
{
    var context = (IPluginExecutionContext)serviceProvider
        .GetService(typeof(IPluginExecutionContext));

    // Prevent infinite loop — only execute on direct user calls (depth 1)
    if (context.Depth > 1) return;

    // Rest of plugin logic...
}

Alternative fix: Use a specific column update in the UpdatedAttributes filter on the plugin step — so the plugin only fires when specific columns change, not when your flag column changes.

9. Cheat Sheet — Quick Reference

Dataverse Web API Quick Reference

Authentication: OAuth 2.0 Bearer token
Base URL: https://{org}.crm.dynamics.com/api/data/v9.2/

GET    /tablename                    → list records
GET    /tablename({guid})            → single record
POST   /tablename                    → create (no body ID)
PATCH  /tablename({guid})            → update (partial)
DELETE /tablename({guid})            → delete
PATCH  /tablename(altkey='value')    → upsert via alternate key

OData params:
$select=col1,col2           only these columns
$filter=col eq 'value'      filter conditions
$orderby=col desc           sort
$top=N&$skip=M              page
$expand=lookup($select=x)   join related
$count=true                 include total count
$apply=aggregate(...)       aggregate

Security Privileges Reference

Create, Read, Write, Delete   → standard CRUD
Append     → this record attaches to another
AppendTo   → another record can attach to this one
Assign     → change record owner
Share      → grant access beyond role

Scope levels (least → most permissive):
None → User → Business Unit → Deep → Organisation

Plugin Key Reference

Stages:
10 = PreValidation  (outside transaction, can read DB)
20 = PreOperation   (inside transaction, modify Target)
30 = Main operation (DB write)
40 = PostOperation  (inside transaction, record saved)

Modes:
0 = Synchronous (blocks operation, in transaction)
1 = Asynchronous (non-blocking, outside transaction)

Context properties:
context.MessageName           "Create" / "Update" / "Delete"
context.PrimaryEntityName     table logical name
context.PrimaryEntityId       record GUID
context.InputParameters["Target"]    entity being operated on
context.PreEntityImages["pre"]       values before operation
context.PostEntityImages["post"]     values after operation
context.Depth                        loop guard (check == 1)
context.UserId                       user triggering the operation

Prevent infinite loop:
if (context.Depth > 1) return;

Common Column Types Quick Reference

Single line of text    → Text (max 4000 chars)
Multi-line text        → Multiline Text (max 1M chars)
Whole number           → Int (no decimals)
Decimal number         → Decimal (precision configurable)
Currency               → Currency (links to currency table)
Date only              → Date Only
Date and time          → DateTime (UTC stored, timezone displayed)
Yes/No                 → Boolean
Single select list     → Choice (local or global)
Multi select list      → Choices
Reference to 1 table   → Lookup
Reference to Account/Contact → Customer (polymorphic)
Owner (User or Team)   → Owner
Formula                → Calculated (real-time)
Aggregation            → Rollup (hourly)

Top 10 Tips

Dataverse is not just a database — lead with: security, logic, API, audit all built in. Sets the right framing immediately.
Append + AppendTo is the most misunderstood privilege. Know the Note-to-Case example cold — it comes up in every security question.
Entity scope vs Form scope Business Rules — entity scope runs server-side on ALL saves including API/flow. Form scope = browser only.
PreOperation stage is where you modify the record before save (InputParameters["Target"]). PostOperation is where you use the new record's GUID.
Alternate keys for upsert — this is the answer to "how do you sync data from an external system without managing GUIDs." A must-know for integration questions.
Change Tracking over ModifiedOn queries — Change Tracking with deltaToken is the correct integration pattern. Saying "I filter by ModifiedOn" is a junior answer.
context.Depth > 1 check to prevent infinite plugin loops — ask about this almost universally in plugin questions.
Plugin in transaction = rollback on exception — synchronous Pre/PostOperation plugins that throw InvalidPluginExecutionException roll back the entire operation including the DB write.
Field Security Profile absence is the most common cause of "field shows blank despite Read access." Mention it first in any field visibility question.
Virtual tables vs data sync — know when to recommend Virtual Tables (data stays in source, real-time) vs Change Tracking sync (data copied, offline-capable). This is an architect-level question.

Monday, April 27, 2026