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 main 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

Thanks for the short but amazing information.

This is a great explanation of the Dataverse and how it compares to an SQL Database. Great informational post with links to deeper info. Great work!!

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.

Very informative 👍 Thanks!

Thank you so much for this great insight, Eric. I am currently studying the possibility of building an enterprise solution and using Dataverse as our data source. My client has data in SQL and Excel. I will have to move this data to Dataverse and model the data using the relationship feature Dataverse provides. As I am testing at this time, I found it very hard to import tables with lookup columns!! Some of these lookup columns have more than 3000 records, and I couldn't find a way to convert those columns to lookup columns in Dataverse? Can you provide me with some guidance on how to do this? Thank you, Eric

Thanks @PaulD1 for your measured review of this fanboy posting. I also come from a SQL/C++/COM/C#/Win32 etc etc background and find Model-Driven/Dataverse a restraint to productivity, not an aid. So sick of hearing about this low-code myth - my experience is definitely that low code "developers" rapidly hit the wall when complexity/security/performance issues swamp their abilities and they inevitably call in a grown-up to fix the mess.

I work for a large multinational which has let some very clever (but non comp-science background) staff build some PowerApps and Flows for number of sandboxed business scenarios. Without exception, the Apps that haven't required the intervention of grown-ups have been glorified MS Forms style apps. i.e. a bunch of data collection screens, some pretty geo-location summaries, a suite of dashboards etc. The sort of thing that litters Microsoft's sample galleries.

As soon as anything touches real-life data (SQL, REST integrations with our LOB apps, etc etc) they rapidly fall off a cliff. Complex joins, JSON manipulation, translating external data to what PowerApps can support (e.g. no bitwise operators, so bitfields need to be mapped to string flags etc). 

 

DataVerse: Why the hell would I want to spend days shoe-horning complex normalised data into pre-canned schemas that I then have to fight to make my views work the way they do in SQL without any trouble? I agree, all the "plumbing" effort to get an Azure SQL instance spun up, secured and connected is a pain in the ass, but they should be working to make this easier to do via templatization. And they should also fix their solution support for AAD SQL which doesn't support environment connection variables yet!

Over half the SQL Views in our latest application are solely there to optimise performance in the PowerApp by minimising client-side/callback joins and making drill-down lookups much faster. - Things that wouldn't even occur to a low-code "developer".