By: Hristo Hristov | Updated: 2021-08-19 | Comments | Related: > Power Apps
Problem
The Microsoft Power Platform is one of the most popular low-code apps / no-code platforms available. Alongside the core products Microsoft Power Apps, Power BI and Power Automate, it also includes a type of database geared toward citizen developers, called Microsoft Dataverse. For newbies to the Power Platform, it may not be immediately apparent where and why you need Dataverse.
Solution
This tip introduces you to Dataverse and explains its advantages and points to watch out for.
What is Dataverse
Initially called Common Data Service (CDS), Dataverse aims to provide a common data layer between the Microsoft Dynamics 365 CRM offering and the Power Platform. Essentially, Dataverse is the functional implementation of the Common Data Model (CDM). As such, it provides a common backend with a set of standardized and expandable entities (also called tables). The point is that by using the CDM and Dataverse, it is more straightforward to integrate and reuse the data from different business applications into new applications, such as a canvas app. In some of our earlier Power Apps tips we have also mentioned the model-driven type of power app which is built on top of Dataverse entities.
If we must describe Dataverse in one sentence or less, it would probably be Microsoft SQL Server data platform for citizen developers to address business needs. So, a lot of the complexity of the technical set up and data modeling is taken away and put behind the scenes. The maker is provided with a graphical user interface where they can create tables, columns with data types, relationships, keys and add data in various ways in the Dataverse database. There is not a lot in terms of programmability and batch actions (e.g., bulk insert to import data or delete) are in general much slower than in SQL Server.
Glossary
These are some of the terms you may run up against with Dataverse:
- Entity: Refers to a table in Dataverse. Table and entity are often used interchangeably for data access.
- Virtual entity: Aka virtual tables – allows representing an external data set as a Dataverse entity.
- Record: a row in a table storing business data.
Where is it used?
Where will you come across Dataverse?
Here are the main points of contact:
- Dynamics 365 applications
- Dynamics Sales, Marketing, Service, or any other Dynamics offering comes bundled up with Dataverse enabling you to scale (more) quickly and massively.
- Customer Voice
- In 2020 MS Forms Pro was renamed in Customer Voice and was repositioned in the Dynamics 365 set of apps. All data that Customer Voice handles (e.g. surveys, questions, answers, and any of their customizations) are stored into dedicated system entities in Dataverse. Further integration or data extraction can be implemented by leveraging the data in these entities.
- Model-driven apps
- By design, model-driven apps, as their name suggests, are built on top of Dataverse. In some specific occasions, the model-driven app may utilize a data connector and work with a virtual entity.
Data ingestion
Dataverse has two major types of tables:
- Standard tables: created by the platform, e.g. the Account table
- Custom tables: created by the maker and suited for a specific scenario.
You can configure additional relationships and keys for the standard tables, but you cannot delete them. Custom tables can be created and deleted as needed and offer all configuration options that standard ones do. In a production scenario you may have to add a couple of tables, create relevant columns, configure relationships among them according to your data model, and create keys to meet business rules. A key can be created on top of an existing column from the table or combination of columns. Of course, a key must always be unique. You must bear in mind the key will always be created (seemingly) but the internal job that creates it will return an error key if it detects duplicates in the existing data.
To ingest data, you have a couple of options.
- Add records or edit data in Excel. This option is suitable for individual records or low volumes:
- Use a dataflow. This is the same dataflow technology available from Power BI:
Dataflows in Power Apps provide a wide selection of data sources:
Power query is available to apply transformation to the source data if needed. An important caveat is that the mapping is always one-to-one, meaning one data source maps to one entity in Dataverse. If you have, for example, a complex SQL query combining multiple tables, a dataflow may not be the most suitable or efficient automation solution.
- Azure Data Factory
With ADF, all limits of the data flows can be bypassed. The Copy data pipeline activity supports Dataverse as a sink. By default, the behavior is "upsert" which requires a suitable key in the Dataverse entity and allows to quickly build a reliable data ingestion solution.
Integration overview
Dataverse supports integration scenarios that go beyond dataflows or scenarios involving ADF.
SQL Integration
With the Data Export Service, replication from one or multiple Dataverse entities to Azure SQL can be easily configured. This app is an external one, available from the Microsoft App Source for free. This is a useful feature when Dataverse operational data must be available to an outside app, for example.
Azure Synapse Link
Until recently called Export to Data Lake, Azure Synapse Link is offered out of the box. With this functionality, Dataverse entities can be continuously exported to an Azure Data Lake Storage Gen2 storage account allowing for various analytics scenarios on top of the data. When such a link is setup, one or multiple entities are configured for continuous replication (both initial and incremental) to an Azure data lake.
Power BI
There is a dedicated Power BI connector for Dataverse that supports both import and direct query mode. With direct query you can build near real-time reporting solutions based on Dataverse entities.
Points to watch out for
Dataverse in Power Platform has a 4GB limit per entity meaning extra care should be taken forecasting the data volumes. In Dataverse for Teams, the limit is even more restrictive: 2GB or 1,000,000 records.
Finally, we must iterate the specific nature of Dataverse. On one hand, it is MSSQL for citizen developers, on the other hand - an operational data store for Dynamics and the Power Platform. Dataverse is not designed to be a data warehouse, so any mature solution based on it should consider the different data creation or ingestion scenarios. Exporting to a dedicated analytical platform is supported with Azure Synapse (out of the box) or Data Factory (custom developed).
Next Steps
About the author
Hristo Hristov is a Data Scientist and Power Platform engineer with more than 12 years of experience. Between 2009 and 2016 he was a web engineering consultant working on projects for local and international clients. Since 2017, he has been working for Atlas Copco Airpower in Flanders, Belgium where he has tackled successfully multiple end-to-end digital transformation challenges. His focus is delivering advanced solutions in the analytics domain with predominantly Azure cloud technologies and Python. Hristo's real passion is predictive analytics and statistical analysis. He holds a masters degree in Data Science and multiple Microsoft certifications covering SQL Server, Power BI, Azure Data Factory and related technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-08-19
