3 Power Apps Best Practices for Accessing Data
Want to better understand how your users are interacting with your environment? Watch our free webinar “Using Power BI & DocAve to Visualize Workspace Usage & Permissions” today!
Looking for more on Power Apps? Read our latest coverage:
- How to Use Power Apps to Support Your Company’s Innovation Processes
- How to Build Microsoft Teams-Aware Power Apps
Power Apps has proven itself as a great low-code solution for building powerful forms to improve business processes. One of the many advantages that Power Apps offers is the ease with which app makers can integrate with Office 365 and other SaaS and on-premises line of business data sources.
There are generally two approaches to performing CRUD (Create, Read, Update, Delete) operations in Power Apps. App makers can either:
- Directly connect an app to the data sources, or
- Leverage Power Automate flows that are called to perform these tasks.
There are implications to which approach an app maker chooses. In this article, we’ll be looking at the data loading scenarios in particular.
Reading Large Data Sets in an App
Today, there’s a limit to the number of items that can be returned to an app from a data source. The default of 500 items can be increased to a maximum of 2000 (under Advanced Settings for the app).
If the app attempts to retrieve more than 2000 items from a data set directly, only the first 2000 are returned. To improve the performance of apps and overcome this limitation, functions such as Filter, Search, and LookUp combined with delegation can be used to reduce the number of items in the app.
The purpose of delegation is to pass the query directly to the data source to process the request and return the smaller data set to the app. This is possible if the data source is Common Data Service (CDS), SharePoint, or SQL Server. However, if the data source is something else, the data source will return the first 2000 items and the app will then filter down the results.
GET OUR NEWSLETTER: Subscribe here for weekly content from AvePoint
Data Set Access
For an app to access data, the app user must have access to the data set. This is fine if the user has permission to see all the data, but there are times when an app user should only have limited access to the data source (if the data source includes personal information, for instance). In such scenarios, the app builder may need to implement “security by obscurity,” a practice where the data source is hidden from the user but the user is still able to get to the data.
Using Power Automate allows the app maker to create flows that will be called during OnStart(), OnVisible(), OnSelect(), or other properties of the app to perform the data reading from sources that the user may not have access to and apply filters to the data source. As shown below, a secondary flow may be needed to access a secured data source that the user does not have access to but requests data from via a service flow that does have access.
Performance Implications
The Power Automate approach extends the possibilities of the Office 365 apps. However, it also adds a level of complexity and reduced performance to the overall solution. The table below shows the results of an experiment I conducted that measures the time it takes an app to access data from a SharePoint list compared to the time it takes the app to retrieve the same data using a Power Automate flow as a conduit.
In the experiment, I used a simple list with just a Title field (a single line of text) and a complex list with the following fields:
- Title (single line of text)
- Number
- Date
- Person
- Notes (multiple lines of text)
The experiment was repeated 10 times for lists containing 10 items, 500 items, and 2000 items. One other experiment was performed where a flow was called by an app and returned nothing.
From the experiment, I concluded the following:
- Calling a flow from an app incurs a performance hit of ~0.75 seconds even if the flow doesn’t do anything.
- Loading data from a SharePoint list using a flow takes nearly 2 seconds more than loading it directly from the app.
- Power Apps direct data loading
- The amount of time it takes an app to load data directly from SharePoint increases linearly with the number of items.
- The number and types of fields in a SharePoint list impact the load time when loading directly from SharePoint.
- Power Apps data loading via flow
- The number of fields and items in a SharePoint list does not appear to have a direct impact on the overall load times.
Conclusion
Based on the experiment, the app maker needs to consider the type, sensitivity, and amount of data that needs to be loaded. If the data lives in CDS, SharePoint, and SQL Server, and there’s no issue for the user accessing the source, then using direct access from the app is the preferred method for performance reasons.
However, if the data source needs to be secured or the amount of data is larger than 2000 records and filtering cannot be delegated, that’s when using Power Automate to retrieve the data is the way to go. A single app may leverage both approaches depending on the needs of the operation.
Learn more about how our Office 365 governance solution now integrates with Power Apps.
