Excel vs SharePoint vs SQL Server vs CDS

 What data source should you use for your app? Here's a summary of the pros and cons of Excel, SharePoint, SQL Server, and the CDS.

Excel Spreadsheet

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 PowerApps.
• It's simple to backup data by copying the spreadsheet.

A severe limitation of Excel is that we can only access the first 2000 rows of a spreadsheet (more details here). Therefore, it is suitable only for simple apps where we don't need to store very much data.

SharePoint 365

SharePoint is a better data source, compared to Excel. For Office365 users, it works very well because a typical subscription includes access to SharePoint and PowerApps. Therefore, there's no need to pay extra for data storage. Features of SharePoint include:

• It's simple to set up data lists in SharePoint. If you inlcude lookup or choice columns in a list, the form designer can generate combolist/dropdown controls for data entry.
• SharePoint can validate the data that a user enters.

A limitation of SharePoint is that filtering and searching data can be difficult or impossible due to limited support for delegated queries (more details here). For example, a 'contains' search isn't delegable with SharePoint which means that SharePoint can only carry out a contains search on the first 2000 rows of a list. Backing up SharePoint data, and moving lists between Office365 tennents is also difficult.

SQL Server

In my opinion, SQL Server is the best data source. Its features include:

• Rich database engine features - such as data validation, relationships, auto-incrementing fields
• The best 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.
  

Using SQL Server doesn't need to be expensive. There is no license needed for an on-premise installation of SQL Server Express. The most basic setup of SQL Azure can start at around $5 month. Despite all the benefits, there are numerous issues to be aware of - not supporting tables with triggers is one of them. The best reference for SQL Server problems is Meneghino's blog.

CDS (Common Data Service)

The CDS is simple to use (compared to SQL Server) and provides great delegation support. The key features include:

• The ability to design tables via a web based designer - no need to install/learn SQL Management Studio
• Management of permissions through a web interface
• Access to pre-built data structures/entities for common tasks like sales, invoicing, and helpdesk.
• The ability to access data through an Excel add-in
• The simplicity of having a database that's attached to an environment - no need to configure Azure firewall, or to understand 'database connection strings'

Access to pre-built entities can be a great benefit for non-developers. On the forums, non-developers often report problems  that arise from using non-normalised data sources. This can help these users avoid these types of problems. However, using the CDS can be expensive. It requires users to have a 'Plan 1' license which costs $7/month. For applications with 100's of users, the cost of using the CDS can be prohibitaive.   

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.

References:

http://powerappsguide.com/blog/post/excel-vs-sharepoint-vs-sql-server-vs-cds