Introduction

Traditional applications commonly rely on a Relational Database Management System (RDBMS).Low code apps built with Power Apps, Power Automate, and Power pages can instead use a built-in data management system called Dataverse. It is a master data hub that stores data on Azure (the cloud) and combines data from multiple sources. It can then be shared throughout Microsoft’s ecosystem of applications and software.

It is also possible to pull data from any other third-party systems using the APIs or a dedicated Dataverse standard/premium connector. Dataverse uses Azure Active Directory for Identity and Access Management (AD for IAM) mechanisms to ensure that only authorized users can access the environment, data, and reports.

What is the need for a product like Dataverse?

• Dataverse being a SaaS product, doesn’t require infrastructure like any on-prem database
• It is easy to use, manage, compliant, secure, scalable, and globally available SaaS data service
• As part of the Microsoft Power Platform, Dataverse requires no or less code to achieve the functionality so that it can be easily used by everyone from knowledge workers to professional developers
• Integration with Dynamics 365 will lead to the organization’s D365 data being stored in the Dataverse, which will allow users to build apps that benefit from Dynamics data
• It empowers interoperability and may be able to pull data from multiple data sources
• Due to a Common Data Model (CDM), organizations’ data will share a structural and semantic consistency, which will make the work easier

From citizen developers to professional developer anyone can work on Microsoft Dataverse tables with no/little knowledge of coding.

Dataverse Features

Dataverse is a cloud-based storage space to secure the data business applications. Power Apps, Microsoft Dynamics 365 applications, and sales/customer service modules use Dataverse to store their data. Dataverse allows structured and unstructured data to be integrated from multiple sources into a single data store used in Dynamics 365 and other model-driven apps.

1. Data Storage -

   The data stored in Dataverse is organized as a set of tables where each table represents a specific collection of records. This includes a base set of standard tables such as activities or contacts, which are built into applications, and custom tables that reflect entities specific to an organization.

   File attachments can also be stored within Dataverse as unstructured data. The Dataverse storage model provides 3 types of cloud capacity

   • Database Capacity- Transactional database storage for entity definitions and record data
   • File Capacity- Storing attachments on emails and notes in Dynamics 365 apps and Power Apps, including images, PDFs, documents, and spreadsheets
   • Log Capacity- Audit logs tracking record and attribute data changes, including field value updates, security role updates, and date/time of access
2. Dataverse Security
   • Dataverse uses Azure Active Directory (AAD) identity and Role-Based Access Controls (RBAC) to ensure only authorized users will access the environment, data, and reports
   • Platform-level security built-in facilitates administrators, can define security permissions once and will apply to an authenticated user regardless of which app or service they use to access Dataverse
   • Users must be assigned a security role that decides which tables, columns, features, and other components they will interact with to access any data stored in the Dataverse.
3. Business Logic and Rules
   • Business rules and logic are defined in a Dataverse table and can be applied to model-driven apps across Dynamics 365 and Power Apps, ensuring data consistency regardless of how teams and individual users access the data
   • Dataverse provides multiple configurable logic engines which can be applied to application development. For example, rollup and calculation columns, synchronous workflows, and business rules, including actions to show/hide columns and set default values and more

1. Dataverse with Dynamics 365
   • Microsoft Dataverse, which is the underlying data platform for all Dynamics 365 apps, manages security and compliance through multiple layers
   • Microsoft Dataverse provides secure storage to the data used by various Dynamics 365 applications, including Dynamics 365 Marketing and Dynamics 365 Sales. This allows users to access their core organizational data and create advanced applications. As a result, there is no requirement for accessing the data and metadata 
   • Dynamics 365 Dataverse refers to the seamless integration capacity of Dataverse with the data stored within various sources of Dynamics 365. This implies that the user can store information and also work with data from any external data source in the Dataverse itself.
   • Microsoft Flow allows users to create and customize their Dynamics 365 experience without code. Microsoft Flow enables us to create automated workflows across different apps and services
   • There is also PowerApps for Dynamics 365—a drag-and-drop app builder where users can create new custom apps that connect to their systems
   • The ability to import data from Dynamics 365 into the Power BI dashboard ensures that users will have the most important information right in the right place. Connecting these two platforms will allow us to visualize data and products through in-depth reports
   • Data loss prevention policy and the D365 administrator can identify sensitive information across many D365 apps and prevent accidental sharing of sensitive information
2. Dataverse with Power Platform
   • Power Apps – It allows citizen developers to build custom apps with no code or minimum code easily. We can securely store application data in Dataverse and build rich apps in dataverse power apps so users can manage data. Power Apps can connect multiple data sources (like Excel, Azure SQL, etc.) through standard, premium, and custom connector
   • Since Power Platform is built on the Dataverse, users are provided with semantic consistency and built-in connectivity
   • Power Automate- Enables users to automate routine workflows with an intuitive logic-based interface. These workflows will integrate and update data, synchronize files, get notifications, and more. There is also a library of connectors (300+ connectors) and Microsoft Dataverse that can connect workflows to hundreds of data sources
   • Power BI –Using data stored in Dataverse, users can build informative reports and dashboards to display important data about sales, customer service, and other business functions in a user-friendly power bi dataverse platform. 
   • Power Virtual Agent- Power Virtual Agents enable organizations to create powerful chatbots with little or no coding knowledge. Power Virtual Agent can lookup data in the Dataverse and answer questions intelligently

Why Dataverse is a better choice for Power Platform than any other DB?

• Microsoft Dataverse provides the ability for a business to store its data in a secure and manageable platform that is well integrated with the rest of the Power Platform suite as well as other platforms to allow ease of use, administration, and accessibility
• When we purchase any suit of the Power platform, the Dataverse comes as a package. No need to purchase additionally
• From citizen developers to professional developers, anyone can work on Dataverse tables with little/ no knowledge of coding
• Securing items/objects within Dataverse is much better. It allows for greater control and functionality of any organization’s data and apps
• We can pull data from any other data source as a virtual table or consume it in the application. We can obtain data in our apps from on-prem as well as cloud data sources 

Types of Tables and Design

There are four different types of tables in Dataverse

Standard Tables –

• These are OOTB/ standard tables like Account, Business Unit, and Contact and are included when we create a Dataverse enabled environment
• Standard tables, any user with appropriate privileges can customize these tables where the table property has the customizable property set to true

Custom Tables –

• Custom tables are unmanaged tables that are either imported from an unmanaged solution or a new table created directly in the Dataverse environment
• Any user with appropriate privileges can fully customize these tables

Activity Table –

• The activity table is a time duration scope table with start and end dates. Moreover, the activity table integrates with the user calendar and will be marked as due or completed
• Activity tables are a special kind of table that can only owned by a user or a team, but not by an organization

Virtual Table –

• A virtual table is a custom table in Dataverse that has columns containing data from an external data source like Azure SQL or Oracle
• End-users work with the rows created by the virtual table to view data in columns and grids, search results, and fetch XML-based reports and dashboards
• CRUD operation can be done on virtual tables; developers can implement plug-ins to read, update, or delete external data using the Dataverse web services and plug-in registration tool

Design Consideration

• Adding a lookup column to the table helps to create a 1: N and N: N relationship between tables
• 1:1 relationship can be created using 1: N and N:1 relation with the intermediatory table
• There are some limitations on cascading delete. While configuring relationship, we can't have a relationship with any action set toCascade All, Cascade Active, orCascade User-Ownedbetween a primary custom table and a related system table
• Once the table is created, we can’t change the custom table’s ownership (user, team, or organization).

Usage of Dataverse tables

• Standard tables are built-in Dataverse tables used for general purpose or in standard Apps (e.g., Account, User etc.)
• Custom tables - Used for general purpose or in standard Apps
• Activity tables – To record specific activities (e.g., Home Visits, Events etc.)
• Virtual Tables –To store data from an external source and use it in our Canvas app or model-driven app

Business Rules

Business rules can be created to enforce logic and data values when we enter data into a Dataverse entity. Business rules defined for a table can apply to canvas and model-driven apps. We can do column level validation like set column values, clear column values, set column requirement levels, and enable or disable columns. Business rules have set conditions, actions, and scope.

When do Business Rules fire?

When a form is loaded, the business rules will be applied. Additionally, when data changes in a form, the rules are run, making it possible for a form to be dynamic as the user completes the data entry process.

Business Rules can be applied to the following

Design Considerations

• Few business actions are not available in the Canvas app (show or hide columns, enable or disable columns, create business recommendations based on business intelligence)
• Business rules do not fire on an editable grid on a dashboard
• Conditions/actions defined on composite attributes are not supported in apps

Auditing

Dataverse supports an auditing capability where an organization’s table and column data changes are recorded over time for analysis and reporting purposes. Auditing is supported on all custom and most customizable tables and columns.

• We can enable or disable auditing at the organization, table, and column levels. The organization level is the highest, followed by the table level, and the column level. For column auditing to occur, auditing must be enabled at the column, table, and organization levels. For table auditing to take place, auditing must be enabled at the table and organization levels
• If auditing is not enabled at the organization level, auditing tables and columns, even if enabled, does not occur. By default, auditing is enabled on all auditable table columns but is disabled at the table and organization level
• The ability to retrieve and display the audit history is restricted to users with certain security privileges: View Audit History, and View Audit Summary. There are also privileges specific to partitions: View Audit Partitions and Delete Audit Partitions
• Audited data changes are stored in records of the audit table

Limitation

Auditing is not supported on table or column definition changes, retrieve operations, export operations, or during authentication.

Security in Dataverse

Each Dataverse environment has its own database, users, security roles, teams, and business units. Each environment has a root business unit, and we can create N no. of business units under root BU. Every business unit can have multiple teams. Every team can belong to one BU. Users can be added to the teams.

Role-Based Security;

Security roles are not business unit specific; they are created and assigned to users/teams to grant access to data. Team security roles can be inherited by the users who are members of those teams.

Field/Column Based Security

Sometimes, record-level access control is not sufficient for some scenarios. In such cases, field-level access control features allow a more granular security at the field level. We may need to create multiple profiles for the same field where users need different permissions, e.g., some can only view and others can update/create.

Similarly, column/field security profiles aren’t BU specific; users/teams can be added to the column security profiles to grant granular access to data.

Security best practices

Design from a simple security model to a highly complex security model provides users access to their specific entity, record, and field level 

• Restrict CRUD operation on apps (Canvas as well as Model-driven) by setting up record-level security
• Create or configure a custom security role to meet complex security requirements
• Add users to the Dataverse security roles/security groups & associate security groups with the Dataverse environment; ensure appropriate security roles for the respective user or Azure AD group before sharing Canvas and model-driven apps. We can set security roles for forms based on the accessibility of the user
• Ensure proper permissions are given to connections credentials and associated service entitlements which would be used to determine permissions of an app while connecting through those connectors

Import & Export Data

We can import the data from different sources into Dataverse tables, e.g., from an Excel worksheet, a comma-separated value (CSV) file, or through many connectors. 

How to Import Lookup fields in Dataverse tables?

If the lookup field is mandatory, the data import mechanism populates the value in the create step and makes the lookup field optional. The data import mechanism creates the record with an empty value for lookup and populates it with another update step.

Data Import in Dynamics 365 is a very robust feature that we can use to import data from a CSV file for any entity records such as accounts, contacts, cases, and many more. When it is a single entity record without lookup fields, it’s a very straightforward process to import. Still, when it requires importing multiple related entity records with lookup fields, then it’s not that straightforward. For this scenario, we must take care of the import process properly.

Exporting data from one or more Dataverse tables is possible. Exported data will be in a CSV format. Another option is a data export service as an add-on service available as a Microsoft Dataverse solution. The data export service feature replicates Dataverse data to a Microsoft Azure SQL Database store in a customer-owned Microsoft Azure subscription. The supported target destinations are the Microsoft Azure SQL database and Microsoft Azure SQL Server on Microsoft Azure virtual machines.

Dataverse Web API usage

We can access Dataverse from a custom solution through OData API services.

• The Web API is one of two web services we can use to work with data and table and column definitions in Dataverse
• Azure app registration gives the client an Id/secret for the app userto authenticate
• The web API supports pretty much every type of CRUD (Create, Read, Update or Delete) operation, and wherever necessary, requests conform to the established security model that Dataverse provides us
• In addition to that, we will have a Dataverse web API service client library which we can use in C# & JavaScript

Design consideration while consuming the Dataverse Web API Request

• A maximum of 5000 rows will be returned for each request; if more rows match the query filter criteria, a@odata.nextLinkproperty will be returned with the results
• Wherever possible, use a $filter parameter and restrict the number of rows returned via the $top or $skip options to page large result sets. We should also use $select query parameters to bring back only the columns we need and use the$filtersystem query option to set criteria for which rows will be returned
• Managing single quotes in string values, we must use double quotes around the values
• GET[OrganizationURI]/api/data/v9.2/contacts?$select=fullname&$filter=Microsoft.Dynamics.CRM.In(PropertyName=@p1,PropertyValues=@p2)&@p1='lastname'&@p2=["OBrian","OBryan","O'Brian","O'Bryan"], By using$applywe can aggregate and group data dynamically. We can retrieve and execute predefined queries e.g., views using the Web API. We can also compose a query using FetchXml and use that to retrieve data
• We can create and update table definitions using Web API. To create a table definition and POST the JSON representation of the entity definition data to the“EntityDefinitions”entity set path, we must use the HTTP PUT method while updating data model entities
• We can associate and disassociate table rows (references) using web API

Future Roadmap of Dataverse

• Develop the capability to handle sensitive, large-scale, and streaming data
• Expand archival and preservation features
• Improving Azure Synapse Link
• Enhancements to Dataverse data modeling leveraging rich and intelligent data types
• Improvements in big data performance and high scale connectivity with Dataverse - new capabilities will simplify data preparation through a visual authoring environment

References

Please refer to the following URLs for further information

Microsoft Dataverse | Tables or Entities

Microsoft Dataverse | Business Rules

Dataverse Security Model

Microsoft Dataverse Export Service