PowerApps is one of the key elements of the Power Platform. Its low-code environment makes it a great platform for users who are used to developing in VBA. As of May 2020, there are over 260 connectors to data sources that can be used for PowerApps. But, what is the right data source to use for the first app when we are learning?
The Candidates
There are 4 potential sources that will be considered as a data source for a first app in PowerApps.
Microsoft Excel
Excel (Online) data tables can be used as a data source, though data ranges can not be used as a data source.
SharePoint Online
Sharepoint Online lists can be used as a data source.
SQL Server Database
A database, such as SQL Server can provide multiple data tables for a potential first app.
Common Data Service
The Common Data Service (CDS) is a cloud data base service provided by Microsoft which integrates with the Power Apps environment.
The Criteria
All cases assume the following:
- The user has an Office 365 E3 license. This means that there is no additional cost for Excel and SharePoint.
- The company does not have Dynamics 365, or the user does not have access to any Dynamics 365 resources.
- There is no additional cost to create the data source for this app (i.e. setting up a cloud database in Azure).
- The user can receive sufficient credentials in order to create data sources on the requested source.
- The app that is deployed will be a canvas app. Other apps, such as portals and model apps can only be developed on CDS.
Ease to Deploy
How quickly can a data set be created in the recommended data source?Does it require special permissions in order to deploy? Is the data source easy to learn to use as a beginner?
Cost
Are there any additional licensing costs involved to set up the data source? Are there additional costs to connect PowerApps to the data source?
Capability
Are there limitations with the data source? Can the app scale in order to perform more tasks in the future?
Microsoft Excel with Power Apps
Ease to Deploy – High
Setting up an app with an Excel table simply requires that you have an Excel file on a OneDrive with a data table, like the one below.
This data source can be typed by the user and created using the create table command in Excel.
The interface in PowerApps will allow you to easily select a table.
At this point, you are off and running with starting your first app! There is still a lot to customize, but the basic app created will allow for data input and editing.
Cost – Low
Excel is included in the Office 365 E3 software package. In addition, PowerApps is included in the Office 365 E3 license for the purpose of connecting with Excel.
As a result, there is no incremental cost for setting up an app using Excel data.
Capability – Low
There are major restrictions when using Excel as a data source in PowerApps.
The first major restriction is that there is currently no way for an app to store more than 2,000 records in a single collection. While there are workarounds, these involve creating more code. This defeats some of the purpose of a low-code app builder!
Since Excel is not a true database, it does not have the capability to limit or control data types.
SharePoint List with Power Apps
Ease to Deploy – High
Setting up a SharePoint list requires some understanding of how to create a SharePoint list.
Unlike an Excel table, we have more control over the type of data that is stored in the list, including choice boxes, numbers, dates and text.
An app can be created either through the PowerApps studio, or directly from SharePoint!
Cost – Low
Since SharePoint Online is included as part of Office 365, there is no incremental licensing cost.
However; unlike Excel, at a larger organization additional permissions may be required for a user to set up a SharePoint list.
Capability – Moderate
While a SharePoint list has the capability to control the type of data that is stored within fields, it is not a database.
SharePoint lists can get data from other lists by way of a lookup field, but this can become very cumbersome when linking multiple tables together.
As a result, SharePoint lists can be very effective when dealing with a single group of facts. There are additional challenges as well when dealing with lists of over 5,000 items.
On Premises SQL Server with Power Apps
Ease to Deploy – Low
An on-premises SQL server is not something that you would normally deploy just for setting up an environment for learning about Power Apps!!
Normally, you would already have a database set up; this would be a database that is being used already for business purposes.
Power Apps exists in the cloud, and the data resides on-premises. As a result, you will need to access this data via a data gateway. This enables Power Apps to communicate with on-premises data.
As mentioned earlier, using an online database instance of SQL server provisioned through Azure is not being considered. This would increase costs even further due to storage and bandwidth costs.
Once you get in to the app, you will still need to have an understanding of the database schema. If a database has been designed well (like the WorldWideImporters database), the dimensions can be easy to find.
However; many databases may have table names that are much less descriptive.
Cost – High
Unlike Excel and Sharepoint, SQL Server is not included as part of the base Power Apps licensing with Office 365.
At this time, the lowest cost to license PowerApps is the Plan 1 licensing, which allows for 2 apps to be run and shared.
For personal learning of PowerApps, a free individual learning environment is available which includes all premium connectors.
https://powerapps.microsoft.com/en-us/communityplan/
While this mitigates the ongoing licensing cost, apps cannot be shared with other users.
As a result, it would be challenging to create a business case to provision required resources to create the required gateways.
Capability – Moderate
Unlike SharePoint, SQL Server is a database. As a result, it gains the capability of being able to establish clear relationships between data tables.
The challenge with capability on SQL server is that the data is stored on premises. There are limitations for accessing the information on the server based on the device where the gateway is installed.
Limitations of using a gateway are listed at:
Common Data Service with Power Apps
Ease to Deploy – Moderate
With the Common Data Service, many entities are already set up when PowerApps is initially provisioned.
There is no need to set up additional security or gateway devices, as the data is stored in the cloud.
However; the number of automatically generated fields (even within a custom entity) can be intimidating. New terminology, such as Entities can be challenging to learn.
Unlike Power BI, relationships between tables need to be set up using the Add Relationship option, instead of a drag and drop between tables.
Once data is properly set up, the app will connect to a table, similar to the way SQL server connected.
Cost – Moderate
Like SQL server, the Common Data Service requires a premium connector in order to run an app.
However; unlike SQL server, there are no further costs in order to run the Common Data Service database.
Database capacity is limited to 50 or 250 MB per user, but this is pooled at the tenant level.
When worrying about building your first application, this should not be an issue!
For personal learning purposes, you can also deploy an environment with the community plan that includes 200 MB of CDS database capacity.
https://powerapps.microsoft.com/en-us/communityplan/
Capability – High
In order to build certain types of Power App, you require a CDS database.
These include:
- Model driven apps
- Portal apps
- Apps using the AI Builder
While these apps may not be where you will be getting started with on Day 1, these represent incredible capabilities not available with other data sources.
It can also speed up understanding of relationships between data by allowing drill downs without use of complex queries.
What should I use for my first app?
There is no single right reason to use any of the proposed solutions – it depends on your business and the type of user you are!
Reasons to use Excel
- You are not comfortable with databases and want to easily see your data.
- You want to create an app for personal reasons on your OneDrive.
- Scalabilty to a large audience is not important.
- You are just looking to play with the PowerApps interface.
Reasons to use a SharePoint list
- You want to be able to share your app with others in your organization.
- There is no budget for PowerApps and you need to work within Office 365.
- Having some control over the type of data that is being inputted into the data source is important.
- You are not looking to build a large number of relational connections between lists.
Reasons to use a SQL Server Database
- You want to work with your organization’s data warehouse.
- There is no business need for any new relational databases and cost of data migration to CDS would be high.
- Developers want to be able to access data easier.
Reasons to use a Common Data Service Database
- The database will be used primarily with Power Platform.
- You may want to use Model Apps or AI Builder in the future.
- Dynamics 365 is being used at your company, and you want to build with your D365 data.
- You are new to relational databases and want a low-cost option to get started.