Dataverse is not a database

EricRegnier · ‎11-26-2020

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.

Links and brief details to the highlighted Dataverse components:

Data Access

Business and Process Automation

Presentation

Platform

Power Platform build tools - for all DevOps automation

Necdet · ‎11-29-2020

Thanks for the short but amazing information.

JDunny · ‎11-29-2020

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

PaulD1 · ‎12-17-2020

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.

Anonymous · ‎01-11-2021

@EricRegnier Very well explained, Thank you so much.

anuverma · ‎10-07-2021

Very informative 👍 Thanks!

AhmedSalih · ‎01-20-2022

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

MarrinerDev · ‎05-05-2022

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".

tomwinans · ‎10-15-2022

It would seem that No-Code/Low-Code is not a myth if you separate front end from back end. What isn't discussed in this article or others like it is that ability to create a separate/custom connector, essentially permitting the back end to be what it needs to be behind an API boundary, and the API fitting into the Power Platform world ... allowing the low-code and no-code benefits.

I don't think anyone would say that no-code/low-code is the hammer for all nails, but it has its place. And separation of concerns allows such. Think JamStack and you're on the right track.

With this said, given use of SQL under the sheets, it would be nice to be able to create a Dataverse table set with a SQL script. Just my humble opinion.

kshepard · ‎10-18-2022

I'm glad to have found this post. I'm trying to help a budding organization move from cloud files that are siloed across departments into a database solution. I have basic SQL experience, but I'm not in the tech department at this organization and have no voice in terms of developing a SQL server or spending any funds on a technical solution. So I've been toying with Dataverse in the hopes of building a database by pulling cloud files (i.e., Excel mostly) into tables that can be called on in Power Apps and Power BI dashboards. Is this the best route, or is there another solution that could work as a "database" in Office 365?

Other considerations:

- Organization is non-profit

- Dashboards and Power Apps must pull from a data source that is not permission-based so that anyone (e.g., Board of Directors) can access the final dashboards, whether or not they have access to the underlying data.

Do I need to go this route?

* Excel (in the cloud)--> Sharepoint List? --> Dataverse --> Power Apps/BI

Or can I go this route:

Excel (in the cloud) --> Dataverse --> Power Apps/BI

Thanks in advance for any advice.

Kate

tomwinans · ‎10-19-2022

@kshepardI'm not an expert so your mileage with my response may be limited...

In my reading, your approach seems reasonable, i.e., take a file from wherever and bring into Dataverse. Clearly the Excel path makes sense, and you could connect to other databases to bring data into Dataverse. I don't know how much automation you need. I'd think either of the paths you lay out, i.e., Sharepoint -> Dataverse, or simply Dataverse would work well. I'd choose the shortest path, myself.

Use “Edit in Excel” in Dataverse for Teams | Microsoft Power Apps

You should be aware that Dataverse doesn't appear to be a place where you could store more and more data over time. Books I am reading suggest there are limits beyond which Power App and workflow performance suffers.

My counsel based on basic understanding is that there are data bits that you could legitimately store in Dataverse over time because it is reference data, or because it isn't a BIG DATA set, or because you refresh it enough to keep it current (implying pruning of unneeded data).

One thing that seems quite appealing is that Connectors to databases, whether in Azure or AWS, or even in your own company walls (via data gateway) can be used to store and manage the larger data sets, and what you need can be brought into Dataverse for just the time you need it. You may need to upgrade your license.

Also note there appears to be a fee for GBs of data in Dataverse. I've read it is 1 GB free, thereafter a $48/month fee per 1 GB additional. Encouragement to keep data pruned, or to get a server somewhere to keep your larger data sets.

Good luck!

EricRegnier · ‎10-20-2022

@kshepard, I beleive you have the right path and approach by trying Dataverse out and doing some prototypes/POCs. It is Microsoft's recommendation with it comes to internal business apps and where there's a lot of investment from their side.
SharePoint list can also be fine but becare as not meant for complex scenarios and harder to scale. See this post: https://crmtipoftheday.com/1402/why-you-should-not-use-microsoft-lists-sharepoint-lists-for-business...

augustineuba · ‎03-22-2023

Thanks for this explanations, I currently have internal solutions running on SharePoint, been questioning if I need to migrate to dataverse instead, I now feel like I have genuine reasons to.

jlindstrom · ‎04-11-2023

@PaulD1 I agree with a lot of your points about Dataverse and that there are limitations in doing certain types of queries. My background is in Dynamics, but I also worked with SQL. Your address example is not the best example, as it is actually set up more like you recommend behind the scenes. The address records are actually stored in the address table and just reflected in the address fields for convenience. So you can actually find accounts by address by searching one place.

If a customer has a SQL DBA available, then I would probably recommend that they use SQL. The problem is many (even large) companies do not, or if they do, they are busy supporting their important enterprise systems, and the average Power Apps/Power Automate person is not a SQL expert.

That is the biggest plus of dataverse--it gives a structured database that is approachable by people who are not DBA's and allows enterprise companies a standard way of controling access to key data in ways that can be managed by less technical people (dataverse security roles, etc).

I would encourage avoiding a "good/bad" outlook on data sources and connections--they are all useful when used correctly. There is a point for using SharePoint, SQL, and Dataverse. I think it is fair to say "if your data model is highly complex with numerous data joins and unions and you have people or partners available to support it, use SQL." Also high data volumes with busy transactions that would exceed the API limits in Dataverse. But there are other cases where Dataverse makes sense. Solutions, complex security scenarios requiring frequently changing security roles for user/teams, etc. And with advances in virtual tables it is less of an issue.

Lukewarm · ‎11-17-2023

I am an old DBA/Developer who has been out of the industry for a number of years. I want to create a view with a simple outer join of two tables. It looks like there is no easy way to do this in DataVerse. When I add a relationship to the tables, it doesn't ask which fields are part of the join. Also, the web interface seems to be slightly different between the DataVerse in Teams and "regular" DataVerse. It is a shame they limited what could have been a big productivity booster.

EricRegnier · ‎11-20-2023

Hi @Lukewarm,

In Dataverse the method to join tables automatically is with lookup columns. Dataverse will automatically create a relationship and maintain the foreign key (guid). In model-driven apps, the table is automatically joined and with canvas app or Power FX, you can use intellisense to navigate to the joining the table. If you're trying to join within code, there are multiple methods with SQL, OData or Fetch XML:

In C# you can also use QueryExpression or Linq:

Hope this helps....

dms117 · ‎12-05-2023

@EricRegnier - I'm having the same issue as @Lukewarm in that I want to create a relationship between multiple tables in which I define the fields on which to join. I'm not trying to create a lookup but instead to build a filter for a lookup based on relational data. But I cannot relate the data. I have no way to tell Dataverse that I want to pick projects from a list and to limit that list based on the currently selected contact by using a table of data that matches the contact to their projects.

I'm finding Dataverse very inflexible and difficult to use. I'm working with multiple systems and multiple databases including Postgresql and SQL Server. How can I meaningfully integrate all of the data from all of the systems so users can access it in a Dataverse form? There are so many connectors for creating these things, yet once you have something in Dataverse it doesn't seem to have the ability to provide for meaningful relationships.