Adatis BI Blogs

Why Delegation has a role even in PowerApps: Delegation Explained

In the first blog of this series: we looked at some tips to keep in mind when doing application development with particular focus on PowerApps. This blog will focus on an important point, more specific to PowerApps, which is worth keeping in mind during the development stage of a PowerApps application.What is Delegation for?Delegation permits the data processing to be done on the datasource’s end, and get only the required data back to the application, in order to avoid shifting all the data in the application itself and process it within. However, some of the functions in PowerApps do not qualify for delegation. Delegation is only supported for certain tabular datasources only – including SQL. If the application being developed is foreseen to work with small datasets that do not exceed 2000 rows (now or in the future), this will not be a concern. In these instances, any datasource or function can be utilized without delegation risks, as if the function cannot be delegated there will still be the ability of the data being processed locally within the application.Difference between a delegatable and non-delegatableIn short, what will be the implications if a function cannot be delegated? The data brought from the datasource will be much less (maximum 2000 rows) than what actually exist in the database, hence producing incomplete results (especially for lists or galleries) or inaccurate results (especially for calculations). Let’s go through an example of this, which architecturally might not be a feasible option to be implemented in a realistic application, but will help us understand better this concept. Let’s say a developer is doing a Form in a PowerApps that will take the details of the end user and submit it to a SQL database. In order to decrease chances of typos and allow better analysis on the gathered data, most of the dropdowns or lists within the Form are being loaded from another SQL database containing master data. And one of these lists is to be populated from the City table, allowing the user to choose from a pre-defined list of cities. The city table in the master database has thousands of records. Without going into the argument of whether this is a good approach or not, let’s say the developer populates a collection first directly from the master data table to be able to hook the list to it with improved performance. The SELECT query passed to the datasource as part of the Collect() function will include a TOP 2000 (beyond the developer’s control), and will only get back 2000 records of the full table. Likewise if a filter was being done or search directly on the database, it will only be considering the initial 2000 records out of the entire table in the datasource. Data row limit for non-delegatable functionsIt is important to note that by default the data row limit for non-delegatable queries is by default 500 (on creation of a new application). However, this can be increased to a maximum of 2000 (at the point of writing this blog) from the App Settings:Delegation WarningsWhilst developing the application, you might encounter warnings that will highlight that that particular action or behaviour might not work properly due to delegation. And this applies were that command will turn into a non-delegatable function. It is normally underlined with a light blue line, similar to the below. Here we are trying to get a dynamic list of all the products that have been inserted within this month and year:The same will happen if we try to get the products inserted in the last 7 days:The relevant control will also have a warning triangle displayed in Edit mode:Proceeding with publishing this application will mean that when the data row limit is exceeded in the source, not all the dataset will be considered for this command (and hence the result will not be exact).Possible workarounds?In certain cases like the above delegation warnings, a different approach for the same command can do the trick. So if upon starting the action we are getting the Start Date once and putting that in a variable, whilst filtering against the range of that and today, then no delegation warnings will be given:A similar solution would be to refer to a used Label on the canvas that is calling the DateAdd(Today(),-7) and referring to it in the Filter instead of the variable. One other option to go around delegation is loading a collection at the start of the application from a large table (as long as it is controllably large, and not millions of rows), and looping through it a number of times (the maximum key in the datasource divided by the default data row limit of 500) to populate the collection with the entire dataset. This can be done either directly in PowerApps or even using a SQL function similar to the following. In this table-valued function we are aiming to output a table with 3 columns. The first column will be the PointerID holding the list of times we are going to re-loop over a database to get up to the maximum row. For each PointerID we will be setting the RangeTo and Range From, when considering the data row limit in PowerApps (which is accepted as a parameter).Following is the SQL code for the function:CREATE FUNCTION [dbo].[GenerateReRunTable] (@Limit INT)RETURNS @ReRunTable TABLE (PointerID INT,RangeFrom INT,RangeTo INT)ASBEGIN--Replace [dbo].[LargeTable] with your table name and the [Id] column with your key--Getting the minimum and maximum from the table to set the entire rangeDECLARE @RangeFrom INT = (SELECT MIN(L.Id)FROM [dbo].[LargeTable] L)DECLARE @RangeTo INT = (SELECT MAX(L.Id)FROM [dbo].[LargeTable] L);--Calculating the number of reruns that need to be done by dividing the range of records with the data row limit in PowerAppsWITH MaxReRunsAS (SELECT ((@RangeTo - @RangeFrom) / @Limit) + 1 AS [MaxReRuns]),ReRunsAS (--Generating the runs including the unique PointerID for each and their start and end range to then be picked by PowerApps for filteringSELECT 1 AS PointerID,@RangeFrom AS [RangeFrom],CASEWHEN @Limit < @RangeFromTHEN @RangeFromELSE @LimitEND AS [RangeTo]UNION ALLSELECT PointerID + 1,[RangeFrom] + @Limit,[RangeTo] + @LimitFROM ReRunsWHERE PointerID + 1 <= (SELECT [MaxReRuns]FROM MaxReRuns))INSERT INTO @ReRunTableSELECT *FROM ReRunsOPTION (MAXRECURSION 10000)RETURNENDGOA SQL View can then be created to get the results from the function. That way whenever we are loading the view, we will be getting the latest result based on the updated data, like for example:CREATE VIEW [dbo].[ReRunsView]ASSELECT * FROM [dbo].[GenerateReRunTable] (500)In this case, the [dbo].[LargeTable] had about 5203 rows. Hence the view would show the following output:In PowerApps, we are then making use of this code (either in the OnVisible() section of the screen or in the OnSelect() of a button):ClearCollect(ReRunsColl,'[dbo].[ReRunsView]');ForAll(ReRunsColl, Collect (ProductListColl, Filter ('[dbo].[Ori Product List]', ProductID >= RangeFrom && ProductID <= RangeTo)))So, for each line in the SQL view (that has been loaded to the collection), we will be posting data to a PowerApps collection named ProductListColl all the contents from the SQL table named [dbo].[Ori Product List] in batches filtering on the specific range for that run (so that we bypass the delegation row limit, which will else disregard the rows above the row limit). Else, a normal Collect(ProductListColl, ‘[dbo].[Ori Product List]’) outside the loop would have only populated the collection with the maximum data row limit allowance of 500 – providing an incomplete dataset. This fully populated collection will also enable us to refer to the ProductListColl locally (as a replica of the entire [dbo].[Ori Product List] SQL table), increasing also the performance of data retrieval from this particular dataset. We need to keep in mind though that eventually in PowerApps, we will still are using a ForAll() loop to read and hence doing multiple calls to the database when dealing with large datasets, increasing the chances of encountering issues which I will be explaining in later releases of this blog series. This will also have an effect on the performance (till the loop is being done and collection populated).I also intend to finish a more practical example related to this to highlight better how delegation works in PowerApps in the upcoming blog post within this series – so please stay tuned for next release! In the meantime feel free to exchange any ideas or comments on this topic! 

Adatis Coding Dojo – Session No.2

The Challenge Write a program that generates all two-word anagrams of the string "documenting", in SQL Server. e.g. “Documenting” = “Document Gin”. Introduction This weeks’ challenge was….well, a challenge. In our first session everyone attempted it via paired programming, for this dojo we decided to attempt the problem with one larger group, consisting of 5 people. This technique encourages an open forum for developers to discuss ideas, with one person at the computer coding. The assigned coder must swap with another participant every 5 – 10 minutes. Preparation Before beginning the Dojo, I asked for one of the Senior Consultants to attempt to complete the problem and provide the code and concepts behind his thinking. The idea is that one challenge can be solved in a number of ways, with 5 people working collaboratively thinking in a completely different way to one individual coder. We provided a file containing 10,000 words (which would become the master list for the anagram solution). If you would like to try this yourself, the text file can be downloaded from here. Senior Developer’s Solution Most importantly, the senior developer DID NOT use the TDD development methodology. It took around 2 hours for the developer to implement a fully functioning stored procedure. Here is how it was achieved: 1. Stored procedure, encapsulating a long T-SQL script. The solution was focused around string manipulation and recursive querying. 2. While loop turns word into a pivoted array of individual letters, which can then be compared against the words in the word list table. 3. Recursive CTE returns words consisting only of letters in that array 4. XML PATH used to create CHECKSUM string of letters and counts. 5. All word combinations of correct length returned and checked against checksum to validate letter counts. The solution was built to be flexible from the start – it returned anagrams with ‘n’ number of words rather than the proposed 2. It would also work with any provided starting word and special characters. Code Dojo Solution The first task was to brainstorm ideas on how to conquer the challenge, starting with whether it could even be achieved in SQL Server! The coding team weighed up string manipulation, recursive CTE’s, a cursor or mathematical calculations, amongst other SQL Server functions. The general consensus was to avoid recursion (where possible) to ensure faster query results and mathematical algorithms to compare against the data in the imported wordlist table. The development team used TDD to implement the mathematical approach to solving the problem. Each code enhancement contained a new automated test script that would capture any failures and enable a simple rollback to the last working codebase. This proved to be a successful technique, as ideas were changing constantly throughout development. Actual Dojo Solution After around 90 minutes of SQL coding and TDD, the team managed to come up with a working solution. This is how they achieved it: 1. A SQL Function to work out a unique value for a character (letter). a. Each letter of the word ‘Documenting’ has a case sensitive ASCII value e.g. ‘D’ is 68 and ‘d’ is 100. b. All letters were converted to uppercase, to ensure a non-case sensitive approach. c. Apply the POWER SQL function within a given letter, which gives it a unique number and cannot be duplicated by another letter. 2. Simple stored procedure that looks up the existing words in the full word list table, which references the function whilst comparing letter values. a. Find all word combinations and their total POWER value. b. Compare the total number against the hardcoded word ‘Documenting’ c. Return and records that have two words adding up to the total POWER of ‘Documenting’. d. Nest the hard coded word into the procedure, which means any word can be compared at run time. 3. The TDD approach helped facilitate the iterative, code review process. Conclusion Although the Senior Developer and Dojo Team’s solutions met the minimum expectations for the challenge, there were a number of pro’s and con’s to both solutions. The Senior Developer thought ahead and made improvements to the requirements, such as dynamically handling more than 2 word anagrams. He also demonstrated some of the under used and more powerful functionality within SQL. However, it takes around 2 minutes to execute one anagram and the code itself is not the most efficient. By not using TDD in his approach, he over complicated the solution and did not encounter performance bottlenecks until the end of the build. On the other hand, the Dojo team fully practiced TDD. This was reflected in the much smaller codebase and, most importantly, the speed in which the anagram procedure executed was much quicker (23 Seconds). Their solution is limited in that it is very rigid and cannot handle more than two word anagrams. It also cannot handle special characters, whereas the Senior Developer solution can. However, these were not requirements of the solution – would a client accept a slower, more complicated product that handles requirements they do not need? Overall, both solutions work adequately but it is clear that when confronting a technical challenge/problem, running a Dojo and practising TDD can bring more efficient results. Had we added further requirements to the challenge, maybe the dojo team would have found another, even more efficient solution. References General Good Dojo Practices - TDD - SQL Functions POWER – ASCII – XML PATH – CHECKSUM - Dojo Code For access to the Senior Developer’s and the Dojo team’s SQL solutions, please leave a comment and I will get back to you directly.  It would be interesting to see if anyone else has a better technique (both in terms of code length and performance) and welcome any suggestions.