Blog

Excel vs SharePoint vs Dataverse vs SQL Server - updated 2021

When building canvas apps, an important question is - what data source should use? This post covers the pros and cons of the 4 most popular data sources with Power Apps - Excel, SharePoint, Dataverse, and SQL Server.

When we build canvas apps, it's important to find a data source that best fits the requirements of the system that we want to build. There are 4 popular data sources that we can choose, which vary in terms of functionality and cost. This post examines the pros and cons of each of these popular data sources.

Excel Spreadsheet

Starting with the most simple and most basic choice, the key feature of Excel is that it's very simple to use. Users with very basic IT skills can cope with Excel. Other features include:

  • We can easily work with the data outside of Power Apps.
  • It's simple to backup data by copying the spreadsheet.
A severe limitation of Excel is that we can only access the first 2,000 rows of a spreadsheet. It's also impossible to store more than 2MB of data due to a limitation that the OneDrive connector imposes. There are more details about this in my post here.

For these reasons, Excel is suitable only for simple apps where we don't need to store very much data.

SharePoint

SharePoint is a much better data source, compared to Excel. A big benefit of SharePoint is that the majority of Microsoft 365 plans include access to SharePoint, which makes it very cost-effective choice. The features that SharePoint offer beyond Excel include:

  • It's simple to set up lists in SharePoint, and integration with Power Apps is very smooth. For example,. when we add lookup or choice columns in a list, the form designer can generate combolist/dropdown controls for data entry.
  • We can define data validation rules (such as mandatory fields) at the SharePoint level, and Power Apps will enforce these rules during data entry.
  • It's possible to restrict access to list items based on user. For example, we can configure some users to have only read access on a list
  • There is integrated support for storing file attachments. Excel doesn't support file attachments and with SQL Server, we need to carry out more work to use the file attachment control.
  • To support multiple users, SharePoint includes conflict detection. If a user attempts to modify a record that someone else has modified in the interim, Power Apps displays an error message.
  • SharePoint automatically stores the date/time, and user that created and last modified a record.
  • We can setup multi-text columns to store a version history of previous values.
The biggest limitation of SharePoint is that filtering and searching data can be difficult or impossible due to limited support for delegated queries (more details here). This is the primary reason why app builders consider moving to SQL Server or Dataverse

For example, a 'contains' search isn't delegable with SharePoint which means that SharePoint can only carry out a this type of search on the first 2,000 rows of a list.

SQL Server

SQL Server is one of two 'premium' data sources, the other being Dataverse. Both these data sources provide a more robust storage solution, and both require a per app/per user subscription. For users that subscribe to a Microsoft 365 plan, SQL Server and Dataverse incurs an extra $10 or $40 per user/month, on top of the cost of the Microsoft 365 subscription.

The key features of SQL Server include:

  • Rich database engine features - such as data validation, relationships, auto-incrementing fields
  • Excellent delegation support - we can query and return more accurate results, compared to SharePoint
  • We can use the built in/industry standard tools for backing up and restoring data.
  • We can bulk import data with tools like SSIS, import key values with 'identity insert', and more easily import sets of related data.
  • We can optimise data retrieval and join tables with SQL views. We can use view to return aggregate calucations (sums, counts, averages) for statistical reporting.
  • For more complex tasks, we can call stored procedures with Flow. In particular, we can highly optimise the speed of inserting and deleting multiple records with the use of stored procedures.
  • We store our data 'on premise' using an instance of SQL Server on a local network. Some organisations may have a requirement not to store data in the cloud, or in certain geographic locations.
  • SQL Server offers the most 'open' data platform, and provides easy access to data outside of the Power Platform. As an example, an organisation with existing applications or Microsoft Access based solutions can migrate parts of a system to Power Apps 'piecemeal', while still supporting the older systems.
  • SQL is a standard skill that many IT professionals possess. For larger projects, it's easier to recruit employees with SQL skills, compared to employees with Dataverse/Dynamics 365/FetchXML experience. Depending on where you are, salary expectations of professionals with the latter skills can be higher, which should factor into total cost of ownership calculations.
  • Of all the data sources we cover here, SQL Server it is the most mature platform with the longest history.

The disadvantages of SQL Server is that it doesn't integrate as well or smoothly into Power Apps, compared to Dataverse. For example, more manual steps are needed to configure controls that show related data, images, or binary data.

There are also limitations that can cause problems, such as the inability to add or edit tables with triggers. To provide a secure system, it's also very important to understand the security model, particularly around the topic of implicit connections, and AAD authentication. There are more details in my post here:
http://powerappsguide.com/blog/post/sql-security-how-to-hack-implicit-connections

Dataverse (previously Common Data Service)

Dataverse is Microsoft's preferred data storage solution for apps and solutions that we build with the Power Platform. It is simple to use (compared to SQL Server) and provides great delegation support. The key features include:

  • Comparatively simple to use. We design tables through a web based designer. There's no need to install/learn SQL Management Studio
  • Dataverse is the data source that model driven apps, portal apps, and AI builder use. If we want to build these types of apps, keeping the shared data in a Dataverse database is easier.
  • Dataverse is the database that Dynamics 365 uses. Therefore, it is the data source we choose if we want to extend Dynamics 365 capabilities.
  • Dataverse offers a more granular security model. For instance, it's possible to secure records at a field level. We can define permissions such as - if a user has permissions on a parent record, that user should also have the permissions on related child records. There is also the capability for users to share records with other users.
  • There is a set of built-in data models/tables that we can use (called the Common Data Model or CDM). This saves users from having to create common table structures, and helps those who are unfamiliar with table/database design.
  • A Dataverse database is implicitly attached to an environment.Thi means there's no need to configure Azure firewall, or to understand 'database connection strings'
  • There is good support for file and image data, including features such as automatic thumbnails for images.
  • There is a currency data type that can standardise values based on exchange rates. This is ideal for international operations that use multiple currency types.
  • Other features that don't exist in SQL Server include 'rollup columns' for aggregating values, and polymorphic lookups - eg, the ability to relate a lookup field value to a user/account/team.
  • There are .NET and web-based APIs that we can use to access Dataverse programmatically.
  • We can move or to deploy canvas apps and tables between environments or organisations using a single solution file. Other data sources are not solution aware, and moving apps will require separate deployment of the app and data source.
    There are auditing capabilities that show who modified a record, including the previous new data values.
  • Dataverse is slightly more cost-effective. Both Dataverse and SQL require a premium license. Dataverse is included in the license, whereas with SQL Server, we incur the additional cost of provisioning and managing a SQL Server.
Beyond the user canvas apps, data verse opens the ability to build model driven apps and this provides features that are not available with canvas apps. This includes the ability to define business rules in a no code way (eg, setting default values, mandatory fields, and validation rules conditionally), and the ability to use business process flows to support workflow processes with a defined life-cycle (eg, the progression of a customer enquiry to a sale).

Model driven apps also offer better search capabilities (eg, search that takes into account misspellings, common abbreviations, synonyms), and built-in offline mobile support.

The biggest disadvantage of Dataverse is that it's impossible to build complex views that traverse multiple tables. For example, joining three or four related tables with inner/outer join types is not possible, particularly if we want to group or to aggregate data. This makes it very difficult to retrieve exactly the data that we need.

Conclusion

For PowerApps, SQL Server is the data source that I recommend . It offers the most features, provides great support for delegation, and performs very quickly.


SharePoint


C#


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





SQL Server



 I like T-SQL and SQL Server Management Server.  SQL is very flexible.  It allows me to create powerful views.  I started using on-premise SQL DB.  The use of SQL Profiler allow me to really understand how Powerapps works and optimize my app.  I created an Excel addin to create monthly reports.  Using DV is a lot more difficult than SQL.  I was also looking at  doing some custom apps and using SQL was a lot easier.

Dataverse



  • Dataverse is the data source that model driven apps, portal apps, and AI builder use. If you want to build these types of apps, keeping the shared data in a Dataverse database is easier.
  • Dataverse is the database that Dynamics 365 uses. Therefore, it is the data source that you would choose if you want to extend Dynamics 365 capabilities.
  • Dataverse is slightly more cost-effective. Both Dataverse and SQL require a premium license. Dataverse is included in the license, whereas with SQL Server, you would incur the additional cost of provisioning and managing a SQL Server.
  • Dataverse offers a more granular security model. For instance, it's possible to secure records at a field level. We can define permissions such as - if a user has permissions on a parent record, that user should also have the permissions on related child records. There is also the capability for users to share records with other users.
  • There is a set of built-in data models/tables that you can use (called the CDM). This saves users from having to create common table structures, and helps those who are unfamiliar with table/database design.
  • Other features that you don't get in SQL include 'rollup columns' for aggregating values, and polymorphic lookups - eg, the ability to relate a lookup field value to a user/account/team.
  • Finally, the overall experience of working with Dataverse in a canvas app is more rounded. For example, there's no need to add a connection, because the connection is implicit. The file attachment and image controls integrate more smoothly, including support for image capabilities such as automatic thumbnails.



Not mutually exclusive





Conclusion

We've looked at three things