Power BI Dataverse: Store and Manage Data for Report Generation

Microsoft Dataverse provides a secure, scalable, and governed data platform that serves as the backbone for Power BI report generation in enterprise environments. By centralizing business data in Dataverse tables with built-in security, audit logging, and relationship management, organizations can build Power BI reports on a trusted, single source of truth that integrates seamlessly with Power Apps, Power Automate, and Dynamics 365.

What is Microsoft Dataverse?

Microsoft Dataverse (formerly Common Data Service) is a cloud-based data platform that comes with every Power Platform and Dynamics 365 license. It stores data in standardized tables with built-in business logic, security, and integration capabilities that make it ideal for enterprise reporting scenarios.

• Standard tables – Pre-built tables for common business entities (Accounts, Contacts, Leads, Opportunities) aligned with the Common Data Model
• Custom tables – Create organization-specific tables with custom columns, relationships, and business rules
• Security model – Row-level, column-level, and record-level security integrated with Azure Active Directory
• Audit logging – Built-in change tracking that records who modified what data and when, essential for compliance
• API access – RESTful Web API and OData endpoints for programmatic data access from Power BI and custom applications
• 4 GB default storage – Included with Power Apps licensing, with additional capacity available as add-ons

Connecting Power BI to Dataverse

Power BI Desktop includes a native Dataverse connector that supports both Import and DirectQuery modes. The choice between these modes depends on data volume, refresh frequency, and performance requirements.

• Import mode – Loads a snapshot of Dataverse data into the Power BI model at refresh time. Best for datasets under 1 million rows with scheduled refreshes.
• DirectQuery mode – Queries Dataverse in real-time when users interact with reports. Best for always-current data but with higher latency per query.
• Dataverse connector – In Power BI Desktop, select Get Data > Power Platform > Dataverse, then sign in with your organizational account
• TDS endpoint – Enable the Tabular Data Stream (TDS) endpoint in Dataverse settings to connect using the SQL Server connector for faster query performance
• Environment selection – Select the correct Dataverse environment (Production, Sandbox, Development) to connect to the appropriate dataset

-- Power Query M code for Dataverse connection
let
    Source = CommonDataService.Database(
        "org12345.crm.dynamics.com",
        [CreateNavigationProperties=true]
    ),
    accounts = Source{[Schema="dbo", Item="account"]}[Data],
    selectedColumns = Table.SelectColumns(accounts, {
        "name", "revenue", "industrycode",
        "address1_city", "createdon", "modifiedon"
    })
in
    selectedColumns

Designing Dataverse Tables for Optimal Reporting

The table structure in Dataverse directly impacts Power BI report performance and usability. Applying data modeling best practices at the Dataverse layer reduces the need for complex Power Query transformations and DAX calculations.

• Denormalization for reporting – Create flattened reporting views or virtual tables that pre-join related entities to minimize Power BI relationship complexity
• Choice columns – Use Option Sets (Choice columns) for categorical data to ensure consistent values and enable slicer filtering in Power BI
• Calculated columns – Implement business calculations (revenue tiers, age groups, fiscal periods) in Dataverse calculated columns to offload processing from DAX
• Date tables – Create a dedicated Date dimension table in Dataverse with fiscal year, quarter, month, and week attributes for time intelligence
• Relationships – Define proper one-to-many relationships between tables in Dataverse so Power BI can auto-detect and configure model relationships
• Indexing – Enable table indexes on columns frequently used in Power BI filters and slicers to improve DirectQuery performance

Dataverse Security and Power BI Access Control

Dataverse provides enterprise-grade security that integrates with Power BI's row-level security model. Understanding how these security layers interact is critical for compliance-sensitive environments.

• Business units – Organizational units in Dataverse that define data ownership boundaries and default access scopes
• Security roles – Granular CRUD permissions on each table and field, assignable to users or teams
• Column-level security – Restrict access to sensitive columns (SSN, salary, medical records) so they are excluded from Power BI queries for unauthorized users
• Import vs DirectQuery security – Import mode snapshots data with the connection user's permissions; DirectQuery can pass through the report viewer's identity using SSO
• RLS alignment – For Import mode, implement Power BI RLS that mirrors Dataverse security roles to enforce consistent access control after data is loaded

Performance Optimization for Dataverse-Connected Reports

Enterprise Dataverse environments often contain millions of records across dozens of tables. Optimizing the connection and data model ensures Power BI reports remain responsive.

• Enable TDS endpoint – The Tabular Data Stream endpoint provides 2-5x faster query performance compared to the standard OData connector
• Query folding – Ensure Power Query transformations fold back to Dataverse server-side queries by avoiding unsupported operations that force local processing
• Incremental refresh – Configure incremental refresh policies on large Dataverse tables to only import new and modified records, reducing refresh times from hours to minutes
• Dataflows – Use Power BI dataflows as an intermediate layer to stage, transform, and cache Dataverse data before consumption by multiple reports
• Aggregation tables – Pre-aggregate high-volume transaction data into summary tables in Dataverse for dashboard-level visuals while retaining detail tables for drill-through

Why Choose EPC Group for Dataverse and Power BI

EPC Group has implemented Dataverse and Power BI solutions for Fortune 500 organizations for 28+ years as a Microsoft Gold Partner. Our founder, Errin O'Connor, authored 4 bestselling Microsoft Press books, and our team specializes in architecting enterprise data platforms that integrate Dataverse, Dynamics 365, and Power BI into unified analytics ecosystems. We understand the compliance requirements of HIPAA, SOC 2, and FedRAMP environments and design security architectures accordingly.

Frequently Asked Questions