This article was written by Lance Fisher, Systems Engineer, and Kollin Adams, Business Applications Developer, with ZAG Technical Services.
Microsoft Power Apps is a core part of the Power Platform suite of products, facilitating low-code mobile and browser-friendly solutions to modern business challenges. Additionally, Power Apps is one of the primary extensibility tools for D365, Microsoft’s ERP platform. The technology platform was released to the public in 2016. As a “low-code” development platform, it is designed to be accessible and usable by anyone from the citizen coder to the seasoned professional.
ZAG Technical Services leverages the Power Apps platform for various internal and external use cases. For example, soon after the start of the coronavirus pandemic, ZAG built a custom health check survey application for the employees to use before entering the company’s onsite office spaces. The application verified whether the team member was following coronavirus safety measures or evaluate possible symptoms. If a team member was considered “cleared,” the program would generate a QR code to be scanned before entering the company’s premises. This application use case assisted in mitigating any health risks posed by the pandemic.
While the Power Apps technology is a great tool to use for simple solutions, it is still maturing overall. Like any technology, it has its limitations and drawbacks. Two of the major topics to consider before building a solution with the platform are data source security and the size of data that the Power Apps solution will leverage.
Data source security
Power Apps can connect to a wide range of on- and off-premise data sources including Microsoft Dataverse, D365, Office 365, Power BI, SharePoint, SQL Server, etc. This makes it easy to plug into an existing environment and start using it immediately.
However, there are several key considerations to be made when leveraging shared data source connections through Power Apps applications. For instance, when an application is made using the SQL Server Connector, the connection to the data source is implicitly shared by default. In other words, the end-user accesses the data source with the same credentials as the account that the developer used when creating the application.
The Microsoft Documentation touches on the risks of implicit connection sharing as follows:
“Each time the end user runs the app, they’re using the credentials the author created the app with. […] Since both the app and its connections are deployed to end users, it means that end users can author new applications based on those connections.”
For example, consider you created an application that filtered out the data you do not want users to see. The filtered-out data is present in the database, but you are relying on the filter you configured to ensure the end-users will not see certain data. In a citizen-developer environment, after you deploy this application, end users can use the connection deployed in any new applications they create by extension. In the new applications, users can see the data you filtered out.
Power Apps does offer additional authentication types for the SQL Server data source connector that are explicitly shared. This means that the end-user of the application must authenticate to SQL Server with their own explicit credentials. The explicit authentication types for the SQL Server connector include Azure AD Integrated and Windows Authentication.
Data size considerations
While rapid development is a significant benefit of the Power Apps platform, the performance of the application must also be taken into consideration. To ensure that an application created with Power Apps will be highly performant and function as expected, delegation is an important feature to leverage throughout the application development process.
Power Apps can delegate the processing and shaping of data to the data source. Rather than moving the requested data to the application first then applying the data shaping functions such as Filter or Sort, Power Apps leaves the heavy lifting to the data source and will get the result of the operation for use within the application.
“Delegation is supported for certain tabular data sources only,” according to Microsoft.
The data sources that support delegation are Microsoft Dataverse, SharePoint, SQL Server, and Salesforce. Even though these data sources can have data processing delegated to them, not every Power Apps function can be delegated. Each data source supports its own set of delegable functionalities. Make sure to review what is available to be delegated for each data source and make your decision accordingly.
According to the Microsoft documentation on understanding delegation in Power Apps:
“Formulas that can’t be delegated will be processed locally. This allows for the full breadth of the Power Apps formula language to be used. But at a price: all the data must be brought to the device first, which could involve retrieving a large amount of data over the network. That can take time, giving the impression that your app is slow or possibly crashed.
To avoid this, Power Apps imposes a limit on the amount of data that can be processed locally: 500 records by default. We chose this number so that you would still have complete access to small data sets and you would be able to refine your use of large data sets by seeing partial results.
Obviously, care must be taken when using this facility because it can confuse users. For example, consider a Filter function with a selection formula that can’t be delegated, over a data source that contains a million records. Because the filtering is done locally, only the first 500 records are scanned. If the desired record is record 501 or 500,001, it isn’t considered or returned by Filter.”
This limit of 500 records can be increased to 2,000 records for non-delegable functions. This limit may still be a hurdle if the data source’s identifying columns have non-delegable data types or a requirement necessitates the use of a non-delegable function. In those cases, it is likely that your application may need to retrieve more records than the non-delegation record limit allows. Worse yet, there are no visual cues to the end-user that the record set backing the application is being truncated. If you have already developed Power Apps applications with non-delegable functions or identity columns and the backing data set is growing, you may have a ticking time bomb.
That said, feature updates, bug fixes, optimizations, and new features are continuously being added to the Power Apps platform. As its functionality continues to grow, there will be a wider range of delegation capabilities to allow for more robust and comprehensive data processing in larger datasets. As more options emerge, developers should look at how they can ensure the best possible functionality for the Power Apps they’re developing with considerations around data source security and data size considerations to truly deliver results to clients.