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 SpreadsheetStarting 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.
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:
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 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.
can optimise data retrieval and join tables with SQL views. We can use
view to return aggregate calucations (sums, counts, averages) for
- 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:
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
- 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
- A Dataverse database is implicitly attached to an environment.Thi means there's no
need to configure Azure firewall, or to understand 'database connection
- 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.
PowerApps, SQL Server is the data source that I recommend . It offers
the most features, provides great support for delegation, and performs