Microsoft Dataverse Explained

Let’s define this into a couple of sentences: Microsoft Dataverse is a cloud-based, low-code data service and platform that allows organizations to store and manage data used by business applications securely. It’s a fundamental component of the Microsoft Power Platform (Power Apps, Power Automate, Power BI, Power Pages) and the foundation for Dynamics 365 applications (like Sales, Customer Service, and Field Service).

Microsoft Dataverse architecture

I always refer to my customers to think that Dataverse is a Table as a Service, but the reality is that this is more than just a database in the cloud. It’s an abstraction layer built on top of various Azure storage services (including Azure SQL Database, Blob Storage, Cosmos DB, Search) that provides:

1. Data storage

It offers structured data storage through Tables (formerly known as Entities). These tables contain Columns (formerly Fields or Attributes) that define the data types (text, number, date, choice, lookup, etc.) and Rows (Records) that hold the actual data.

2. Rich metadata

Dataverse stores metadata alongside the data. This means data about the data itself—like data types, relationships, formatting rules, and validation rules—is stored within the platform, making it easier to build applications that understand and interact with the data structure.

3. Data modeling

It provides powerful tools for defining relationships between tables (one-to-many, many-to-one, many-to-many), enabling the creation of complex, relational data models crucial for business applications.

4. Logic and validation

You can implement server-side logic directly within Dataverse using:

• Business Rules: Configure rules that apply logic and validation across different apps and platforms (e.g., show/hide columns, make columns required, validate data) without writing code.
• Workflows (Classic): Automate business processes in the background.
• Power Automate Flows: Modern automation triggered by Dataverse events (create, update, delete records) or run on demand.
• Calculated Columns: Define columns whose values are automatically computed based on formulas involving other columns in the same table.
• Rollup Columns: Define columns that aggregate data from related rows (e.g., count related records, sum values).

5. Security

Dataverse features a robust, enterprise-grade security model based on roles, permissions, and business units. It allows for granular control over data access, including:

• Role-Based Security: Assign users to predefined or custom security roles that grant privileges (Create, Read, Write, Delete, Append, Append To, Assign, Share) on specific tables.
• Record-Based Security: Control access to individual records through ownership (User/Team owned vs. Organization owned) and sharing.
• Field-Level Security: Restrict access to specific sensitive columns within a table, even if a user can access the record.  
• Hierarchical Security: Model security based on an organization’s management hierarchy or geographical structure (using Business Units).
• Auditing: Track changes made to data, including who made the change and when.

6. Integration

Dataverse is designed for seamless integration:

• Power Platform: Native connector provides the richest experience for Power Apps (Canvas and Model-driven), Power Automate, Power Pages, etc.
• Dynamics 365: These applications are built directly on Dataverse, sharing the same data model.
• Azure: Integration with Azure services like Azure Synapse Link for Dataverse (for analytics), Azure Logic Apps, Azure Functions.
• Other Systems: Connectors, Dataflows, and APIs (Web API) allow integration with numerous other applications and services, both Microsoft and third-party.
• Virtual Tables: Allows data from external sources (like SQL Server, SharePoint) to be represented as tables within Dataverse without physically migrating the data.  

Advantages of using Microsoft Dataverse

You can store your data in the cloud; Dataverse is only one of the options you have. Many people will use SharePoint, Excel, or even data sources – yes, they’re ALL good data sources. You must know that each data source has advantages, drawbacks, costs, and a learning curve. In my humble experience, it’s not easy to impose Dataverse on customers, but here are a few advantages worth mentioning during POCs or testing phases.

• Robust Security: An enterprise-grade, highly configurable security model is a core strength that is essential for business-critical data.
• Built-in Logic: Business rules, calculated/rollup columns, and workflow capabilities reduce the amount of custom logic needed in applications.
• Seamless Power Platform Integration: Offers the best performance, delegation capabilities (for Power Apps Canvas apps), and feature support within the Power Platform ecosystem. It’s the only way to build Model-Driven Power Apps.
• Scalability: Designed to handle large volumes of data and transactions, managed automatically by Microsoft. SharePoint, for example, has many other limitations.
• Centralized Data Management: Provides a single source of truth for business data accessible by multiple applications and services.
• Pro-Developer Extensibility: Offers APIs, SDKs, and Plug-in capabilities (custom server-side code execution) for complex scenarios.
• Auditing: Built-in auditing capabilities provide visibility into data changes.
• Offline Capabilities: Dataverse data can be readily configured for offline use within Power Apps mobile applications.

Comparison with other data sources

I hear you. We’ve mentioned SharePoint, and you’re eager to learn how to compare it. Let’s compare Dataverse to SharePoint Lists, Dataverse for Teams, and SQL Server.

1. Dataverse vs. SharePoint Lists

SharePoint Lists vs Dataverse: What to use?

Choose SharePoint Lists when: You need simple lists for collaboration, document-centric processes, or basic tracking within a team site, cost is a primary concern, and complex relational data or security isn’t required. They are often used when data is closely tied to SharePoint site content.

Choose Dataverse when: You are building business-critical applications, need complex relational data, require robust and granular security, need server-side logic, want to develop Model-Driven apps, require scalability for large datasets, or need robust ALM capabilities.

2. Dataverse vs. Dataverse for Teams

Dataverse for Teams is a lighter version of Dataverse, specifically scoped within the Microsoft Teams environment.

Dataverse for Teams vs Full Dataverse: What to use?

Choose Dataverse for Teams when: You want to build low-code apps and flows directly within Teams for use by members of that specific team, the data requirements and complexity are moderate, and you want to leverage existing Teams licenses. It’s a great starting point.

Choose Full Dataverse when: You need capabilities beyond a single team, require enterprise-grade security, need advanced features like Model-Driven Apps or plug-ins, expect large data volumes, or need robust ALM across multiple environments. You can upgrade a Dataverse for Teams environment to full Dataverse.

3. Dataverse vs. SQL Server (especially Azure SQL)

SQL Server vs Dataverse: What to use?

Choose SQL Server when: You need maximum control over database structure and performance, have existing SQL expertise, require complex T-SQL logic, need very high transaction throughput beyond typical Dataverse limits, or are building applications outside the core Microsoft business application ecosystem.

Choose Dataverse when: You primarily build applications within the Power Platform or Dynamics 365, want to leverage the built-in security, logic, and integration features, prefer a managed service with less infrastructure overhead, and need features like Model-Driven Apps or the Common Data Model.

Drawbacks of Dataverse

• Cost and Licensing: Can be more expensive than alternatives like SharePoint Lists or Azure SQL for specific scenarios, especially concerning storage capacity and API call limits. Understanding the licensing nuances (per-user, per-app, capacity add-ons, API limits) is crucial and can be complex.
• API Request Limits: Daily API limits are tied to user licenses and pooled at the tenant level. High-volume automated processes or integrations can hit these limits, potentially requiring optimization or purchasing additional capacity, which adds cost.
• Performance Tuning: As a managed service, you have less control over underlying database performance tuning (like specific index creation) than SQL Server. Performance relies on Microsoft’s management and platform optimizations.
• Complexity: While user-friendly for basic use, mastering its full capabilities (security model, solutions, environments, API) requires a significant learning curve.
• Storage Costs: While initial storage entitlement comes with licenses, exceeding database, file, or log storage quotas requires purchasing capacity add-ons, which can become costly for very large datasets or file storage needs.  

Why Power Platform People should use Dataverse

For anyone seriously developing solutions on the Power Platform, Dataverse is often the preferred and most powerful data backend for several compelling reasons:

1. Native Integration: It is the native data source.
2. Model-Driven Apps: These powerful applications, which automatically generate a responsive UI based on the data model and business processes, can only be built using Dataverse.
3. Robust Security: Developers can leverage the sophisticated Dataverse security model directly within their apps and flows, ensuring data is protected according to business rules without manually building complex security logic.
4. Rich Data Capabilities: This capability enables creating applications using complex relationships, specific data types (currency, lookups, images, files), and server-side logic (business rules, calculated/rollup columns).
5. Offline Data: This provides a robust and relatively straightforward configuration for offline data access in Power Apps mobile apps.
6. Single Source of Truth: Promotes using a centralized, well-structured data store for business applications, reducing data silos.
7. Future Direction: Dataverse is central to Microsoft’s strategy for business applications. New Power Platform features often debut with or have the best support for Dataverse.

Conclusion on Microsoft Dataverse

In conclusion, while options like SharePoint Lists and Dataverse for Teams have their place for simpler or team-specific scenarios, and SQL Server remains vital for traditional RDBMS needs, Microsoft Dataverse stands out as the premier data platform for building scalable, secure, and feature-rich business applications within the Microsoft Power Platform and Dynamics 365 ecosystems.

Its combination of data storage, logic, security, and deep integration makes it a cornerstone technology for Power Platform developers aiming to create robust enterprise solutions. The trade-offs in cost and complexity need careful consideration, but the benefits often outweigh the drawbacks for significant business application development.  

Sources:

• Identify foundational components of Microsoft Power Platform – Training | Microsoft Learn
• Dataverse capacity-based storage details – Power Platform | Microsoft Learn
• Tables and metadata in Microsoft Dataverse – Power Apps | Microsoft Learn