cancel
Showing results for 
Search instead for 
Did you mean: 
EricRegnier

Dataverse is not a database

I’ve received many questions especially by non-Power Platform developers regarding the difference between Common Data Service (CDS) Dataverse and SQL Server and the advantages of Dataverse as opposed to SQL Server. Although Dataverse’s database is Azure SQL and Dataverse is referred to as a “database” in many places even within Microsoft space, it is much more than a database.

Dataverse comes with a rich set of features that are usually common to any type of system/application especially in the enterprise space. If we were to compare to traditional software layer application design, you'll need to develop your database, a data access layer and its security, business logic layer and some form of presentation layer. With Dataverse all that is taken care of out-of-the-box and developers just need to focus on the data modelling, business and presentation layers. Instead of a database, I believe Dataverse should be referred to as a platform or even as SDaaS (Software Dev as a Service). Below are a few examples of these capabilities that you would have to custom build yourself (or use frameworks) which is why Dataverse is a great platform for your business apps.

  • Data Management and Integration capabilities (import and export)
  • Mobile offline capability
  • Reporting and dashboards for end-users
  • Users and Queue management
  • Activities/Interactions management
  • No code business logic client & server side
  • Pre and post-event business logic execution (plugins)
  • SDKs and APIs which automatically scaffolds on change of data model
  • Auditing and Logging
  • Office and Exchange integration
  • Searching and querying
  • SharePoint, OneDrive and OneNote integration
  • Currency management
  • Data Duplication Detection
  • Multi-language support
  • Pre-built UI template with out-of-the-box data binding (model-driven apps)
  • Governance, administration, and DevOps build tools
  • Core data model (Common Data Model), out-of-the-box schema, and prebuild industry accelerators (verticals).

 

To illustrate more the roles and capabilities of Dataverse, let’s zoom in the following Microsoft’s Power Platform diagram with a traditional software layer lens. The highlighted components come straight out-of-the-box and we can see that these are across the stack. The tight integration with the Microsoft/Azure ecosystem is another significant advantage as it’s simply all native.

 

PowerPlatform.png

Links and brief details to the highlighted Dataverse components:

Data Access

Business and Process Automation

Presentation

Platform

Comments

I am from a SQL background. I'm not a Dataverse expert, but have worked with those who are (from a D365 background) but I find Dataverse extremely limiting (and unpleasant) to work with.

The reason is that Dataverse tables are generally normalized (which is good) but there is no equivalent of SQL (querying language) available from Canvas Apps that will allow you to traverse multiple levels of joins (e.g. Account => Invoice Header => Invoice Detail). So, if you want to filter Invoice Header by Account you can do that, but you cannot filter Invoice Detail by Account (because it goes two levels deep).

 

This leads to one of two solutions:

  • Redundant References: Repeating a reference at multiple levels in the model, e.g. also record Account at the Invoice Detail level. This is a terrible idea as it removes the 'single point of truth' that is the goal of normalization. Data ends up inconsistent (changed at one level and not another) leading to loss of trust in the data and effort to try to maintain 'data integrity'.
  • Having to pull large data sets to the client (Power App) and cycle through it client side. This is bad because it is not always practical due to delegation limits, and is bad for performance not only of the client but also for the database due to the increase I/O and processing load.

To double down on this flaw, Dataverse has many 'enhancements' to make it easier for a novice to build a  database, but break good normalisation and data-modelling practice and so just store up problems for later - e.g. Multivalue fields, Many-Many relationships and Ploymorphic relationships. These seem great shortcuts when you are putting a database together, but just cause headaches later because they do not conform to normalisation standards (try pulling all results from a many-many relationship or filtering by multi-value fields) and require learning new commands (e.g. Relate-To instead of just populating a row in the intersect table using the same 'Patch' syntax you use everywhere else when you add a new row).

Normalisation and the facility to query across joins go hand in glove - you need to have both. The inability to query across joins leads to compromised data-models as is clear when you look at the entities in CDM: Account has 150+ fields including three sets of fields for Address (e.g. Address1Town, Address2Town, Address3Town, etc.) - so if I want to find Accounts with an address in a particular Town, I have to know to search for that in 3 different columns.

Now these flaws may not impact everyone, but in my (admittedly limited experience) the lack of ability to query across multiple joins (outside of FetchXML which is not available from PowerApps) has massively increased the complexity and time taken to produce Apps. Indeed, Dataverse is not a database.

@EricRegnier Very well explained, Thank you so much.

Meet Our Blog Authors
  • PowerApps Community Mentor 2018, Practicing surgeon and former Professor of Surgery, University of Illinois, Chicago.
  • Jill of All Trades (Power Apps, SharePoint, PowerShell, Exchange, Power Automate and odds & ends of assorted other things)
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • I'm Tim Leung - a PowerApps builder. In addition to PowerApps, my main interests include building software with SQL Server and Microsoft .NET. I'm the author of 'Beginning PowerApps' - the first published book on PowerApps. You can buy this from Amazon and other leading bookstores.
  • Systems developer
  • My name is Timothy Shaw and I create digital solutions using the Power Platform, Office 365, and Azure SQL and handle the IT for a small company in the energy sector. When not building the future :), I enjoy playing guitar, good (or really bad!) sci-fi, Xbox therapy, and hanging with my wife and son. Twitter: @ShortForTim