Working with Large Data Sources – Planning for Delegation in Power Apps

Paul Stork

0 comments

We seem to be creating and getting access to more and more data. In this eBook, we will cover how Power Apps takes on the challenge of how to maintain the balance between retrieving too many records and not enough records when working with data sources using a process called “Delegation”.

One of the challenges that face all database application designers is how to maintain the balance between retrieving too many records and not enough records. Retrieving large data sets will inevitably slow down your application. Data sets will take too much time to retrieve and too many local resources to manage. But retrieving too small a data set also has consequences. If you don’t have all the data, you need you will have to make multiple returns trips to the database to get more data, or you may draw invalid conclusions based on an incomplete dataset. Database application designers must create queries that retrieve just the right number of records. They need a dataset that is small enough to provide good performance, but big enough to provide thorough results without requiring numerous round trips to the database.

In this eBook, we will cover how Power Apps takes on that challenge when working with data sources using a process called “Delegation.” We’ll start by providing a thorough definition of delegation. We’ll also discuss why it’s important to retrieve subsets of the records in a large data source. We’ll look at related concepts like “Paging” that fulfil a similar role to delegation. Then we’ll examine how different data sources provide varying levels of support for delegation. Once we have a good understanding of the benefits and limits of delegation, we’ll look at some strategies and workarounds for implementing delegation in Power Apps.

When you finish reading this eBook, you should understand the concept of delegation and how to employ it to make your Power App both fully functional and performant. You’ll learn how to:

  • Choose a data source for your app that has the right amount of delegation support
  • Retrieve the right number of records using delegable functions and operations
  • Troubleshoot and work around issues with delegation

What is Delegation?

One of the keys to producing a highly performant Power App when using a large data source is to minimize the amount of data that must be downloaded to your device. If you need to aggregate records down to a single value or only need to work with a subset of records, then it is inefficient to download all the records from the data source for local processing. It will be a much faster and more efficient process if the remote data source does the processing for you and only returns the aggregate value or subset of records that you really need. Leveraging remote processing in the data source can reduce the processing power, memory, and network bandwidth that your app needs, resulting in better response times for your users. This becomes critical as mobile apps, on phones, connected via a cellular network become the norm.

Whenever possible, your Power App will delegate the selection and processing of data to the remote data source, rather than downloading all the data to the app for local processing. But this kind of processing is dependent on the remote data source involved. Not all data sources are capable of this kind of remote workload, and the ones that do have different levels of support. This can make implementing delegation in Power Apps a challenge because you must be aware of the delegation support available in the specific data source you are using.

This is made even more challenging because queries that can’t be delegated don’t fail, they simply return an incomplete data set. So, you must watch for delegation warnings when building an app or run the risk that your app will not be able to access all the data available. The screenshot below shows how delegation warnings are displayed while editing your app. Controls that have formulas that don’t support delegation will be flagged with a yellow caution icon, and portions of formulas that aren’t delegable will be underlined in blue.

If you’re working with small data sets (less than 500 records), you won’t need to worry about delegation. But if your data source is already large or has the potential to grow, then using delegable f is critical to the success of your app.

What Happens with Delegation?

Let’s look at an example of how delegation works. Imagine we have a data source that contains purchase records that record the first and last name of a buyer, the amount of the purchase, and the month the purchase was made. A table of data like this can quickly grow very large. In our app, we want to look at only the purchases made during the month of June. So, we issue a query to the data source to filter the records where the Month field is equal to June. The diagram below shows what happens if the data source supports delegation for that query.

Since the query used a delegable function only the 2 records that met the criteria of the query were returned to the local device. This significantly reduced the volume of data that needed to be downloaded from the server to the client, which resulted in a much faster-performing app.

What Happens without Delegation?

Now let’s look at the same example if the data source does NOT support delegation. In this case, all the records in the data source must be returned to the local client. The local client is then responsible for filtering out the data that does not meet the criteria of the query. This has two effects that could slow performance in the app. First is the additional workload on the client required to filter the data, and the second is the amount of time required to download all the records. Combined, these two factors can significantly degrade the performance of your app.

Another possible side effect should be considered. The maximum number of records downloaded by a non-delegable query will never exceed the limit set by Power Apps. By default, that limit is 500 rows of data. But if your query exceeds the limit, an error is not returned. The response from the data source will simply limit the number of records returned. The result will be an incomplete data set. Relying on incomplete data would be disastrous for the reliability of your app.

Delegation versus Paging

There is another concept related to the delegation that is also designed to improve the performance of your app. It’s called paging. Paging takes effect only in the context of the Gallery control. When a gallery control is populated by a query it will only retrieve the first 100 rows of data, even if more data is available. But as a user scrolls below the 100 rows displayed in the Gallery, paging will automatically request another 100 rows of data that are then added to the existing rows in the Gallery. It will continue doing this until the upper limit of the records requested by the query is reached. Queries using delegable functions are designed to return all the records that meet the criteria expressed by the query. Non-delegable queries will return records up to the limit set in Power Apps.

The first screenshot below shows a Gallery control after a request has been made for records. The label next to the Gallery reports the number of rows currently being cached in the Gallery using the formula:

CountRows(Gallery1.AllItems)

At this point, only 100 rows of data have been downloaded even though we are using a delegable query, and the data source contains over 5,000 rows.

The next screenshot shows the same gallery after we scroll down to view the 101st record in the data set. You can see that the Gallery is now holding 200 records. Since we are using a delegable query we could continue to scroll down until we had retrieved all 5,000 records. But that is impractical since it would require scrolling past the bottom of the screen 50 times.

The Gallery control uses paging to maximize performance in the user experience by limiting the number of records downloaded at any one time. But once downloaded the records are cached locally. This provides the user with access to all the records as needed but limits the performance hit to a subset of records currently being viewed. Although paging provides better performance by limiting data transfer it is not a substitute for using delegable queries. Using delegation is the only way to guarantee that you will have access to all the appropriate data in your application.

Common Delegable Data Sources

Only data sources that represent tabular data can support delegation. Connectors that access services to return data, for example, Microsoft Translator or Bing Maps, don’t support delegation. But they really don’t need to support delegation since they normally return a more limited set of data. To verify whether a data source supports delegation, you should look in the documentation for its connector. If delegation is supported, the details will be provided in the documentation. The diagram below lists some of the commonly used data connectors in Power Apps. We’ll discuss the delegation support available for each of them in detail.

Excel

Excel provides support for paging but does not support delegation. This means that the retrieval of rows from a table in Excel will always be limited to the Data Row limit for non-delegable functions. The one exception to this is that an Excel table can be imported as a Read-only data set without worrying about the limit. For this kind of import, the only limitation is that the maximum size of the Excel file can’t exceed 2 Megabytes (MB).

SharePoint

SharePoint is one of the most commonly used data sources. It does not require a premium license like the Common Data Service (CDS) or SQL connectors and does support some delegation, unlike Excel. Although SharePoint supports some delegable functions, it has the weakest support of any of the major connectors we will discuss. If licensing is an issue it’s a good choice, but support for large data sets is limited.

Common Data Service (CDS)

The Common Data Service (CDS) is an entity database used by Dynamics 365 applications like Customer Relationship Management (CRM). CDS has many advantages as a data source. Since it is designed around an entity model, it can often be easier for non-IT professionals to work with than a traditional database, like SQL. If you use Dynamics applications, it may already be one of your primary data repositories. It also provides one of the most extensive sets of delegable functions available.

The major disadvantage to CDS is that it requires either a Dynamics 365 license or a premium license. A per-app license covers two related apps for $10/user/month and a per-user license covers an unlimited number of apps for $40/user/month. If you are using the Power Apps license that comes with Office 365 this can be a major expense.

SQL (SQL)

In my opinion, the SQL connector provides the best support for delegation. As we will see in the strategies and workarounds section later, you can use SQL stored procedures to bypass many of the limitations imposed by non-delegable functions. It also provides almost the same delegable function support as CDS and many more delegable functions than SharePoint. Another advantage is that many organizations already have people who are skilled at creating and maintaining SQL databases.

But like CDS its major limitation is that it requires premium licensing. A per-app license covers two related apps for $10/user/month and a per-user license covers an unlimited number of apps for $40/user/month. There is also SQL server licensing involved, although this is often already covered by other projects if your organization uses SQL. But if you are using the Power Apps licensing that comes with Office 365 the additional premium licenses can be a major expense.

Using Filter Search, and Lookup

There are five (5) primary functions that we need to consider when planning delegation support in our apps. These are the primary functions that will be used to query the data source to retrieve records.

  • Filter( Table, Formula1 [, Formula2, … ] ) – Returns records in a data source that satisfy a formula. Each record in the data source is checked against the formula. If it evaluates to true, the record is returned.
  • LookUp( Table, Formula [, ReductionFormula ] ) – Similar to Filter() except it only returns the first record that matches the criteria.
  • Search( Table, SearchString, Column1 [, Column2, … ] ) – Returns records that contain a string in one of the specified columns. The string may occur anywhere within that column.
  • Sort( Table, Formula [, SortOrder ] ) – Sorts a table based on a formula.
  • SortByColumns( Table, ColumnName1 [, SortOrder1, ColumnName2, SortOrder2, … ] ) – Sorts a table based on one or more columns.

There are also functions and operators that are used inside these functions when querying data sources. We’ll look at those embedded functions and operators in the next section. Each of the different data source connection types we’ve mentioned provides different levels of support for these delegable functions and operators. The tables below summarize which query functions are available for each data type in the three most common data sources that support delegation.

SharePoint

SharePoint has good support for delegation of Text, Number, and Boolean data types. But anything other than the sorting of DateTime values is non-delegable. The major limitation is a total lack of delegable support for the Search() function. A common workaround for this shortcoming is to use Filter() with a StartsWith() embedded function.

SharePointNumberTextBooleanDate Time
FilterYESYESYESNO
SearchNONONONO
LookUpYESYESYESNO
SortYESYESYESYES
SortByColumnsYESYESYESYES

Common Data Service (CDS)

CDS provides much better support for delegation than SharePoint. The table below shows that only searching by non-text fields and Globally Unique Identifiers (GUID) are a problem.

CDSNumberTextOption SetDate TimeGUID
FilterYESYESNOYESYES
SearchNOYESNONONO
LookUpYESYESYESYESYES
SortYESYESYESYESNO
SortByColumnsYESYESYESYESNO

Structured Query Language (SQL)

SQL also has excellent support for delegation. The table below details the data types that work with the five primary delegable query functions.

SQLNumberTextBooleanDate TimeGUID
FilterYESYESYESNOYES
SearchNOYESNONONO
LookUpYESYESYESYESYES
SortYESYESYESYESNO
SortByColumnsYESYESYESYESNO

 

Delegable Operators

The five delegable functions listed in the previous section depend on embedded formulas to set the criteria for retrieving records. If the embedded functions or operators used in the formula aren’t delegable, then the entire function becomes non-delegable. This adds another layer of complexity. Because like the functions discussed in the previous section, support for delegation of these operators and functions differs for each data source type. The tables below summarize which embedded functions and operators are available for each data type in the three most common data sources that support delegation. Table cells are filled in with either ‘YES’ to indicate they are delegable, ‘NO’ to indicate they are non-delegable or left blank to indicate they don’t work at all with that data type.

SharePoint

Like the query functions discussed in the previous section, delegation support for embedded functions and operators is also limited for SharePoint. The key limitations to note are lack of support for IsBlank, Not, and In. Support is also very limited for arithmetic comparison operators other than ‘=’. These limitations on embedded functions and operators are one of the primary reasons why delegation support in SharePoint is considered lacking by many people.

SharePointNumberTextBooleanDate Time
–, +YES NONO
<, <=, <>, >, >=YESNONONO
=YESYESYESNO
And (&&), Or (||)  YES 
IsBlank NO  
Not  NO 
StartsWith, EndsWith YES  

Common Data Service

CDS support for embedded functions and operators is much better. The primary roadblocks in CDS are in four areas:

  • Arithmetic calculations on numbers
  • In is not a supported operator
  • String manipulation functions that calculate length or trim white space
  • Some Date comparison and manipulation functions are not supported

A few other specific limitations are also recorded in the footnotes for the table.

CDSNumberTextOption SetDateTimeGUID
–, +NO    
<, <=, >, >=NOYESNOYES 
<>, =YESYESYESYESYES
And (&&)YESYESYESYESYES
Or (||)YESYESYES  
Average, Sum2YES    
EndsWith, Left, Lower, Mid, Replace, Right, StartsWith, Upper YES   
InNONONONONO
IsBlankYESYESYESYESYES
Len, Substitue, Trim, TrimEnds NO   
Max, Min2YES  NO 
NotYESYESYESYESYES
Now(), Today()   NO 

1 Number fields cannot contain arithmetic expressions
2 Sum, Min, Max, Avg are limited to 50 000 records.

SQL

Based on the table below, delegation support appears to be more limited for SQL than it is for CDS. But that is because SQL has more stringent limitations on which data types specific operators will work with, rather than limitations on delegation. The main delegation limitations that do exist fall in two areas:

  • Date/Time values
  • Lack of support for IsBlank

The lack of support for IsBlank() can easily be worked around by using a combination of the ‘=’ operator and a blank string or null value. Date/Time values present a bigger challenge. Microsoft recommends building parallel numeric or text fields that hold values calculated from dates and times for the purpose of sorting and querying.

Again, take note of some specific limitations listed in the footnotes.

SQLNumberTextBooleanDate TimeGUID
–, +, *, /YES NO  
<, <=, >, >=YESNONONO 
=, <>YESYESYESNOYES
And (&&), Not, Or (||)  YES  
Average, Max, Min, SumYES    
In YES1   
EndsWith, StartsWith, YES2   
Left, Len, Substitute, Replace, Right, Trim, TrimEnds, YES   
IsBlankNONONONONO
Lower, Mid, Upper NO   
Now(), Today()   YES3 

1 Only works looking for a string in a SQL column
2 Only works when testing what the SQL column starts or ends with
3 Direct date filters do not work for SQL Server

Non-Delegable Functions

There are also several embedded functions that can never be delegated. These functions are listed below.

  • First(), FirstN(), Last(), LastN()
  • Choices()
  • Concat()
  • CountIf(), CountRows(), RemoveIf(), UpdateIf()
  • GroupBy(), Ungroup()

Strategies and Workarounds for Delegation

Now that we understand the limitations imposed on delegation for each of the data sources, let’s discuss some ways to work within or ways to bypass those limits. In this section, we will discuss six (6) tips that will make working with large data sets easier in Power Apps

Tip #1 – Increasing the Data Row Limit

The first tip is also the simplest. The default data row limit applied to non-delegable query functions is set to 500 records. This data row limit can be raised up to a limit of 2,000. You can never retrieve more than 2,000 records from a data source that doesn’t support delegation.

As we noted earlier this limit does not apply to static records imported from an Excel spreadsheet. Even though the Excel connector does not support delegation.

You can change the data row limit for an individual app by going to Settings > Advanced Settings on the File menu while editing your Power App. You can change the value to any number between 1 and 2,000. The default is set to 500. Make sure you Save your app after making the change to get it to take effect. The actual location of the setting is highlighted on the screenshot below.

Tip #2 – Troubleshooting Delegation

Our second tip deals with how to determine whether delegation is working for you or not and whether delegation is even necessary.

Test to determine if Delegation is Working

To verify that delegation is working as expected in your app you can decrease the data row limit, discussed in Tip #1, to 1. This makes it easy to identify any control in your app that is relying on non-delegable functions and operations. If you see more than one record you can be assured which will highlight any spots in your app where non-delegable functions are being used.

Test to see if Delegation is Needed

The other potential problem you face with delegation is deciding when you need it. Your data source when you first build your app may not be big enough to require delegation. But how will you know when your data source grows to the point where lack of delegation means that your app is working with incomplete results.

The easiest way to accomplish this is to create a warning label somewhere on your app. Then set the visible property of that label to check the value returned by a non-delegable count function against your data row limit. The following is a good example:

If(CountRows(‘People List’)=500,true,false)

If they are equal your data source is or soon will be too large. The screenshot below shows a warning label in an app that has a data source that has grown too large.

Tip #3 Replace Embedded Functions and Calculated Fields

Our next tip is a workaround to handle a nagging problem when working with delegated functions. Sometimes even though a function can be delegated, a function or formula nested inside the delegated function can’t be delegated. In that instance, the entire function is considered non-delegable. For example, Filter is a delegable query in SharePoint, but the function to retrieve the email for the current logged in user is not delegable. So a Filter statement like:

Filter(‘People List’,Email=User().Email)

will raise a delegation warning for the Filter statement.

The easiest fix for this kind of issue is to pre-calculate the non-delegable function and store the result in a variable. In the example above, we can calculate the User() function in the App OnStart event and store it in a variable called something like CurrentUser. Then we can use that value in a Filter statement like this:

Filter(‘People List’,Email=CurrentUser.Email)

Even though we are referencing a Complex object, this is still considered delegable because Complex Objects base delegation on the data type of the final property reference. In this case, the text value of the Email address.

Tip #4 – Nest Delegable Functions inside Non-Delegable Functions

The fourth tip is the opposite of the third tip we just discussed. While embedding non-delegable queries inside delegable queries can invalidate the whole function, the opposite is not true. Sometimes embedding a delegable query inside a non-delegable query can make the whole function work reliably.

Consider this example. We have a SharePoint list with over 5,000 names and addresses. We would like to be able to retrieve a list of people with a specific first or last name who lives in a specific State. To search for a string value within a text field, we need to use Search(). But Search() isn’t a delegable function in SharePoint, so we can’t use it to search the entire data source. But if we can pre-filter the data source to a data set that is smaller than the data row limit, we can use Search on the result without having to worry about delegation. So we use a Filter() based on the State they live in to retrieve a smaller set of the 5,000 total records. 5,000 records divided among 50 states is an average of 100 per state. This is well below even the default data row limit of 500. Once we have retrieved our approximately 100 records we can apply the non-delegable Search statement at the client to obtain our result. The full statement would look like this:

Search(Filter(‘People List’,’State/Province’=”OH”),SearchBox.Text,”FullName”)

The use of Search() will still throw a delegation warning, but we can ignore that since we are confident that the Filter statement will retrieve fewer than 500 records.

Tip #5 – Use SQL Stored Procedures

Our next tip is specific to SQL as a data source. SQL supports the use of scripts that run internally on the database server called “stored procedures.” The advantage of using these stored procedures is that they are not limited to delegable functions. They can run any kind of statement supported by SQL. They can also support the use of parameters that are passed from a local client to the server. These parameters can be used to supply query values that can modify the output of the stored procedure. The result is that a stored procedure can be used to run queries on a SQL server that are normally not delegable. You can read more about creating and using stored procedures here: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15

The one challenge when using stored procedures is that Power Apps has no native ability to call a stored procedure. But Power Automate does. To use a stored procedure, a Power App will invoke a particular Power Automate flow and pass it the parameters required by the stored procedure using code similar to the line below:

SqlGetActive.Run(DatePicker1.SelectedDate)

The flow will, in turn, call the stored procedure, pass in the parameters, and pass the results back to Power Apps as a Javascript Object Notation (JSON) array. The screenshot below shows an example of a simple flow that calls a stored procedure and returns the results to Power Apps.

Power Apps can then store the array as a collection, which can be bound to an object like a Gallery.

The screenshot below shows how to call the flow and store the results returned as a collection.

You can read more about using SQL stored procedures from Power Apps in the following walkthrough BLOG post:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-a-sql-stored-procedure-to-powerapps-split-method/

Tip #6 – Download Data Source to a Local Collection

Our final tip should be used only as a last resort. Since local collections are not bound by the rules of delegation, one solution to a delegation issue is to download the full data set to the local device. But, this approach has its own share of problems. Downloading a large amount of data can cause the following issues:

  • Initial download of data may delay the start of the app
  • Local storage limitations may limit the amount of data that can be cached
  • Syncing local data with changes made by other users is a challenge

To cache all the data from a data source in a local collection, you need to find a way to retrieve portions of the remote data source in batches. For example, using a data source with over 5,000 names and addresses, I could break the data set down into smaller segments by Filtering the name field on the first letter. Filtering on each of the 26 letters in the alphabet would break my large dataset into 26 batches with approximately 200 (5,000/26) records in each batch. In the screenshot below, you can see the 26 statements that will load the data source into a local collection.

Conclusion

That brings our discussion of working with large data sources in Power Apps to a close. Planning for delegation when choosing your data source and building your app is critical if you want your app to have the following characteristics:

  • Reliable access to all your data
  • Good performance
  • Support for mobile platforms

Even if you think your data source will never grow large enough to require delegation support, it’s a good idea to build in some protections. The simple app you build today working with a limited set of data may grow into a mission-critical app tomorrow that requires large volumes of data. Planning delegation from the beginning will help you build an app you can be proud of. I hope this review of delegation support for large data sources will make it easier for you to work with your organization to build reliable, highly performant Power Apps that can transform your business.

0000-00-00 00:00:00


Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}