What is the best database for PowerApps?

In this article, we will give you all the information you need to choose the best database for PowerApps. We will also introduce you to the various PowerApps data sources, and how they can make your business processes more efficient.

With a functional database that is connected to all of your crucial data sources, everything your businesses need for a seamless operation is at your fingertips. Your final PowerApp solution might integrate all kinds of third-party data sources. This includes Facebook, Twitter, Salesforce and more.

This article will be useful for people looking to start building a canvas PowerApp solution and a place to store the data from scratch. Scroll down for more information.

What are PowerApps data sources?

PowerApps data sources provide your apps with the information that they need to function effectively. These banks of data can be stored in the cloud, allowing you to access them from anywhere in the world.

One example of a PowerApp data source is a table created in Microsoft Excel. The table can be stored in OneDrive for Business, or any other cloud storage, and connected to your PowerApps. You need specific connectors to communicate with the data source. Fortunately, Amnu can connect you to many useful services.

The services that we can provide connectors for include SharePoint, Salesforce, SQL server and all the Office 365 products. Talk to Amnu today to find out how we can enhance your business efficiency by connecting your PowerApps to useful data sources.

What types of PowerApps data sources are there?

Data sources for PowerApps are stored in the cloud, or locally stored in a specific app. The most common form of data sources used for PowerApps are tables. By connecting to cloud and local data sources, you can read, amend, and reformat tables across all of your apps, with total ease and control. Some of the most common PowerApps data sources include:

Microsoft Excel workbooks
Other Office 365 apps
SharePoint
SQL server
Microsoft Dynamics
Other CRM databases

It isn’t just tabulated data that PowerApps can connect to. Amnu can also link your PowerApps to a host of other useful features. This includes calendars, emails and social media accounts. With your notifications and data easily available to you, everything you need is at your fingertips.

PowerApps Storage

When using PowerApps, all of your data will be stored in the cloud. This makes it easy to access your apps and information from anywhere in the world. The volume of data required by businesses has increased drastically in recent years. As a result, Microsoft has introduced the “Dataverse”.

The Dataverse gives PowerApps users access to three different storage areas, one for databases, one for logs and one for files. The amount of storage you will receive will vary depending on the PowerApps subscription that you pay for.

On a PowerApps per-user plan, you will typically receive 10 GB of database capacity, 20 GB of file capacity, and 2 GB of log capacity. Then, each additional user added to your subscription will come with extra Dataverse storage capacity.

However, if you purchased your PowerApps subscription before April 2019, you will still be using their legacy cloud storage model. This gives you a single data capacity entitlement, as opposed to the three storage segments provided by the Dataverse cloud model.

5 Minute Vlog – Database for PowerApps

Unsure which database is right for your PowerApp? Watch the video below on this topic of “What’s the best Database for PowerApps”.

Article by: Dougie Wood

Design your database for PowerApps First

A common mistake that is made when embarking on a PowerApps project is to focus on the user interface design before considering where the data will be stored.

First agree where the data is going to be stored and what columns of data will be required before you start designing your Power App interface.

Questions to ask when choosing a database for PowerApps

There are multiple factors to consider to help you choose the best database for PowerApps. As part of your initial project meeting to discuss PowerApps ask your Team the following questions:

Do we have a tight budget?
One of the key reasons businesses choose Power Apps is that it can be a very cost-effective way of developing software. However, if you rush into the project without considering where your data is going to be stored you could quickly find costs increasing.
If cost is your primary reason for choosing Power Apps, then selecting to store your data in Microsoft Excel spreadsheet or SharePoint Lists could be the best option for you as these options are often included in the majority of Microsoft 365 licenses you likely already have.
How much time do we have?
Another unique selling point of Power Apps is how quickly you can spin up a mobile friendly app. However, often not factored into this time is how long it takes to setup the backend database for storing the relevant data for the Power App.
If you are looking to create proof of concept app to pilot quickly then setting up a new SQL server might take too much time. So, you might choose to set this up in Dataverse as it will be a much quicker setup process.
Choosing Excel Spreadsheets or SharePoint Lists would also provide a very quick option for storing your data. This would be dependent on the amount of data you need to store.
How much data will be stored?
There are a number of issues that can be caused by not correctly estimating how much data your Power App will be generating. This could lead to delegation issues in your Power Apps, issues with SharePoint views, load times opening Excel documents and so much more.
You need to consider how many columns will be in your database and how much data will be submitted over the lifetime of the Power App.
If you are expecting thousands of rows of data, then an Excel Spreadsheet would be sufficient. If you are expecting up to a hundred thousand rows of data, then a SharePoint List would be the chosen option. If you need more than that you would look to use Microsoft Dataverse.
How many users of the Power App?
Knowing how many people will be using the Power App will help scope which database you choose for your solution.
If it will just be a handful of users in a small Team that will be using the Power App, then a Microsoft Excel Spreadsheet can be used. However, this option would not scale well if the Power App was then shared with a wider audience of users.
For small to medium sized organisations that want to build solutions that can scale to the wider group of users then selecting Microsoft Lists (SharePoint Lists) would be the best option.
If you are building an enterprise solution, then choosing Dataverse should be considered.
How will the Power App be Supported?
It is worth considering not only how and who will be supporting the Power App, but also the database that is supporting it in the background. If you do not have the IT resource or PowerApps Developers to support a new SQL server instance, then this might not be the best option.
Using Microsoft Lists (SharePoint Lists) as your database for the Power App will make it much easier for non-technical employees to be able to maintain the data.

Examples of Database for PowerApps

You can watch the 5 minute webinar on this topic by clicking the picture below. You can also see some live examples of powerapps in our other blog resource.
Content by: Dougie Wood

Top 4 Microsoft Databases for PowerApps

There are many different types of databases available on the market. We will not be discussing any of the third-party databases such as MySQL, Oracle, IBM Db2, SAP or AWS (Amazon Relational Database Service) as these are typically not used in combination with PowerApps.

Below we have listed the most commonly used Microsoft products to store data with Canvas PowerApps.

Microsoft Excel Spreadsheet: Database for Power Apps

A Microsoft Excel Spreadsheet hosted in either SharePoint Online or a OneDrive for Business is a viable option for small teams to store data. Using Excel would be the smallest scaled database.

This would be sufficient for personal use Power Apps or if you only plan to store up to a few thousand rows of data. Main benefits of this option include being quick to setup and not requiring an Office 365 Admin to setup an area to store your data.

Microsoft Lists (SharePoint Lists): Database for Power Apps

The most commonly chosen place to store data that we recommend is within Microsoft Lists. These Lists are essentially stored on SharePoint sites and are great for small (SMB) organisations to store up to one hundred thousand rows of data.

Using Microsoft Lists within Office 365 as a DataSource is also a chosen option as SharePoint Online is included within the majority of Microsoft 365 licenses, meaning there is no additional cost.

Microsoft SQL: Database for PowerApps

If you need to store hundreds of thousands or more rows of data to use with your PowerApp, then Microsoft SQL could be considered.

However, it will have a few down sides including additional premium licenses that are required for end users to access SQL data.

Your PowerApp might be being built to interface with an existing SQL database. However, it can be a very costly process to setup and support a new Microsoft SQL environment for your new PowerApp.

So, if you require a database to store large volumes of data then consider Dataverse (Formerly Common Data Service) instead.

Dataverse (Formerly Common Data Service): Database for PowerApps

Dataverse is the best option if you need to store hundreds of thousands of rows of data.

Choose Dataverse if you are looking for a database that will scale to enterprise levels over time.

The key difference of Dataverse is that it is a relational database just like Microsoft SQL compared with the other options. This will allow you to form relationships and lookups to other tables of data.

The downside of this option is that it will require premium licenses which can be quite costly.

Contact our Experts

Next Steps

I hope you found this article useful, if you are interested in getting some assistance with Power Apps, please feel free to contact me with any questions using our contact us form.

If you are interested in PowerApps then please download our free PowerApps white paper.

Author

Dougie Wood

Solutions Architect

Blog