The Best Databases and Data Sources for Power Apps

Microsoft’s PowerApps platform is a powerful tool that lets you create custom business applications that can improve your internal processes and productivity. 

One of the platform’s key features is its ability to connect to a wide range of data sources, allowing you to bring together data from disparate systems into a single, unified application. 

In this post, we’re going to delve into some of the best databases and data sources that you can leverage for your Power Apps.

Best Data Sources for Microsoft PowerApps

1. Microsoft Dataverse

Microsoft Dataverse, formerly known as Common Data Service (CDS), is perhaps the most native data source for Power Apps. It’s an Azure-based service that provides secure and cloud-based storage for your data [1].

• Structure: Dataverse is a relational database with a data model that’s easy to understand, making it accessible for app creators.
• Integration: It integrates seamlessly with Power Apps, Power Automate, and Power BI, creating a coherent development environment.
• Security and Compliance: Dataverse includes built-in security features and compliance certifications, offering strong data protection.
• Rich Metadata: It allows the creation of complex business objects with relationships, hierarchies, and custom business logic.

2. SharePoint

SharePoint Online is another great data source for Power Apps, especially for organisations already using Office 365.

• Ease of Use: SharePoint lists can easily serve as a data source and can be connected with a few clicks.
• Collaboration: SharePoint’s strong collaboration features are beneficial when multiple users need to work on the same data.
• Permission Management: SharePoint offers detailed permission settings, allowing control over who can access the data.

3. SQL Server

SQL Server, a classic relational database, is a solid choice for more complex, enterprise-level applications.

• Powerful and Versatile: SQL Server can handle complex queries, transactions, and stored procedures.
• Scalability: SQL Server can scale to handle large databases and high volumes of transactions.
• On-Premises and Azure SQL: Power Apps can connect to both on-premises SQL Server and Azure-hosted SQL databases.

4. Azure Data Services

Azure offers a plethora of data services, such as Azure SQL Database, Azure Cosmos DB, and Azure Table Storage.

• Variety of Data Models: Azure services support various data models, from relational to NoSQL.
• Scalability: Azure data services can easily scale to accommodate growth in data volume or application usage.
• Global Availability: Azure services are available worldwide, which can be a crucial factor for global organisations.

5. External APIs and Web Services

Power Apps allows you to connect to a variety of external APIs and web services using custom connectors.

• Extendibility: With custom connectors, Power Apps can connect to virtually any RESTful API, extending its reach to a vast number of services.
• Connector Library: Microsoft provides a library of pre-built connectors for popular services such as Salesforce, Dynamics 365, and more.

6. Excel

Excel, a user-friendly and familiar tool, can act as a simple data source for Power Apps. Many people build their first app using Excel as a data source because it is a familiar tool and easy to use.

• Easy to Use: Excel is a common tool, and its data can be easily imported into Power Apps.
• OneDrive and SharePoint: Excel files stored on OneDrive for Business or SharePoint Online can be used as a data source.

However, we strongly recommend against using Excel as a data source. Read our article on Excel as a data source for PowerApps to find out why you shouldn’t use Excel as a data source.

What Are the Data Source Limitations in Power Apps?

Power Apps offers a broad range of connections to various data sources. However, like any platform, it does have its limitations.

On-Premises Data

Power Apps can connect to on-premises data sources, but it requires the installation and configuration of an On-Premises Data Gateway. This adds an extra layer of complexity.

Delegable and Non-Delegable Data Sources

Power Apps has certain functions (like Filter, Sort, and Lookup) that can be delegated to the data source to perform on the server side. 

This is very efficient, especially when dealing with large datasets. However, not all data sources or all functions support delegation. 

In such cases, Power Apps must load all data into memory to perform the function, which is subject to a limit of 2,000 records as default (this can be increased up to a hard limit of 2,000,000 records, but with potential performance issues).

Excel as a Data Source

While Excel can be used as a data source, it’s better suited for smaller, simple apps. It lacks the advanced functionality and security of a true database, and the Excel file must be hosted in either OneDrive for Business or SharePoint Online.

Real-Time Updates

Not all data sources support real-time updates within Power Apps. For some, you may need to manually refresh the data within the app or rely on a timer to perform the refresh.

API Limitations

For connections to external APIs and services, Power Apps is subject to any API limitations imposed by the external service, such as rate limits.

It’s crucial to be aware of these limitations when designing your Power Apps, especially when dealing with large datasets or complex operations. 

Understanding these limitations will help you make more informed decisions about which data sources to use and how to structure your app.

FAQs

What Is the Fastest Data Source for Power Apps?

Microsoft’s Dataverse, given its tight integration and optimisation with Power Apps.

Since Dataverse is specifically designed to work with Power Apps, Power BI, and Power Automate, Microsoft has optimised it for speed and performance in this environment. This means you’re likely to experience faster data retrieval times and overall better performance compared to other data sources.

However, it’s important to note that the “speed” of a data source can be influenced by numerous factors, such as network latency, the complexity of the data model, the size of the data set, and the specific operations you’re performing.

Does Power Apps Have a Database?

Power Apps does not have a built-in database per se, it uses Microsoft Dataverse as its preferred data storage and management system, providing users with a robust, secure, and integrated database solution.

The Bottom Line

The top three data sources for PowerApps are Microsoft Dataverse, SharePoint and SQL Server.

Which of those you should use for your app will depend on your specific requirements, such as the complexity of your data, the scale of your application, and the tools you are already using within your organisation.