Introduction to Power Apps (Using variables, collection, data types, delegation connections and different data sources) - Session 2
The Tech Platform presents Talks Academy with Aroh Shukla with his 2nd session on Power Apps. Watch us on Facebook and Youtube @thetechplatform . Link is given below to see the full video.
Facebook - https://www.facebook.com/videos
Variables
Apps that you create in Power Apps behave very much like Excel. Instead of updating cells, you can add controls wherever you want on a screen and name them for use in formulas.
Read more .....Click here
Types of variables
Power Apps has three types of variables:
Click to read more.
Collection
Read more .... Click Here
Collection Function
Read more .... Click Here
Data Types
Information flows through an app in small, discrete values, very much like the cells of a spreadsheet. For example, data in a Birthday field and an Anniversary field would both flow through as a Date value that includes the year, the month, and the day. The app knows how to format these values, constrain input to what is appropriate for each, and share the values with a database. Birthdays differ from anniversaries to people, but the system handles them in exactly the same manner. In this case, Date is an example of a data type.
Many of these data types are similar and have the same underlying representation, such as a Hyperlink field being treated as Text. The additional data types provide better default experiences in forms and other controls.
Read more ... Click here
What is API
API is Application Programming Interface that allows two applications to talk to each other. A user initiates Power Apps to get a signature leveraging Adobe Sign, this will be the API call from Application (Power Apps) and Adobe Sign as a data source.
Application programming interfaces consist of two components:
Technical specification describing the data exchange options between solutions with the specification done in the form of a request for processing and data delivery protocols
Software interface written to the specification that represents it
Types of APIs
Basically APIs are of three types Public, Private or Partner API
Private
Used within Organization
Mostly build for company employees
Common used cases are the integration of company system/apps or development of new system using existing resources
Public
Available to any third party organization.
Apps with Public APIs are mostly designed for the end customers.
This API release policy allows for increasing brand awareness and fostering external innovation
It can open and Commercial.
Partner
Openly promotes but available for known business partners
End customers or Business users are Potential Targets audience for such apps
Software integration for two organization is the popular use case for these APIs.
APIs Protocols:
Remote Procedure Call (RPC)
Service Object Access Protocol (SOAP)
Representational State Transfer (RST)
GraphQL
Power Platform Connectors Reference
Power Apps has connectors for many popular services and on-premises data sources, including SharePoint, SQL Server, Office 365, Salesforce, and Twitter. To get started adding data to a canvas app, see Add a data connection in Power Apps. A connector may provide tables of data or actions.
Power Platform has 2 types of Connectors
Standard - Free for office 365 plan
Premium - Comes with $ additional Cost
Click to see the full list for connectors - List of connectors
Types of data sources
Data is the core of every app. we make it easy to get your data into your apps with more than 200 connectors for many popular cloud services and even your on-premises data.
When you build a Power Apps canvas app there are many different data sources you can choose to use such as SharePoint, Microsoft Dataverse, SQL (on-premises), Azure SQL (online), Excel, and others like Oracle. Depending on the data source and connectors you choose in your canvas app, there are different performance optimizations you can apply. On this topic, I would like to focus on the common issues observed and make recommendations on how to solve them.
Kind of Data sources
Data Sources can be connected to cloud service or they can be local to an app
Connected data sources
The most common data sources are tables, which you can use to retrieve and store information. You can use connections to data sources to read and write data in Microsoft Excel workbooks, SharePoint lists, SQL tables, and many other formats, which can be stored in cloud services such as OneDrive for Business, DropBox, and SQL Server.
Data sources other than tables include email, calendars, Twitter, and notifications, but this article doesn't discuss these other kinds of data sources.
Local data sources
Using the Gallery, Display form, and Edit form controls, it is easy to create an app that reads and writes data from a data source. To get started, read the article Understand data forms.
When you ask Power Apps to create an app from data, these controls are used. Behind the scenes, the app uses an internal table to store and manipulate the data that comes from the data source.
A special kind of data source is the Collection, which is local to the app and not backed by a connection to a service in the cloud, so the information can not be shared across devices for the same user or between users. Collections can be loaded and saved locally.
Kinds of tables
Tables that are internal to a Power Apps app are fixed values, just as a number or a string is a value. Internal tables aren't stored anywhere, they just exist in your app's memory. You can't directly modify the structure and data of a table. What you can do instead is to create a new table through a formula: you use that formula to make a modified copy of the original table.
External tables are stored in a data source for later retrieval and sharing. Power Apps provides "connections" to read and write stored data. Within a connection, you can access multiple tables of information. You'll select which tables to use in your app, and each will become a separate data source.
To learn more, Working with tables goes into more detail about internal tables, but it is also applicable to external tables residing in a cloud service.
Working with tables
You can use table data sources the same way that you use an internal Power Apps table. Just like an internal table, each data source has records, columns, and properties that you can use in formulas. In addition:
The data source has the same column names and data types as the underlying table in the connection.
The data source is loaded from the service automatically when the app is loaded. You can force the data to refresh by using the Refresh function.
As users run an app, they can create, modify, and delete records and push those changes back to the underlying table in the service.
1. Records can be created with the Patch and Collect functions.
2. Records can be modified with the Patch, Update, and UpdateIf functions.
3. Records can be removed with the Remove and RemoveIf functions.
4. Errors when working with a data source are available through the Errors function.
The DataSourceInfo, Defaults, and Validate functions provide information about the data source that you can use to optimize the user experience.
Issue types per data source
SQL Server (on-premise)
Canvas app can reach out the data out of on-premises SQL via on-premises data gateway. Once on-premises data gateway is configured, Power Apps canvas app can manage data with various on-premises data sources such as SQL, Oracle, SharePoint on on-premises networks.
However, accessing on-premises data sources could suffer from slowness due to the following common causes. Although this topic is focusing on SQL on-premises. They are still valid for other data sources on-premises.
Common causes
Thick client or excessive requests: some canvas app formed formula to do Group By, Filter By, JOIN operations client-side. Although canvas app can do such operations, they would need CPU and memory resources from client devices. Depending on data size, these operations make extra scripting time at the client side on top of increasing JS heap size of the client. Be aware of each lookup data call also travel to data source via data gateway. In this case, the number of data calls is really a matter.
Unhealthy on-premises data gateway: As organizations can define multiple nodes of on-premises data gateway, all configured nodes should be healthy, on-premises data gateway service should be up and running. If one of nodes was unreachable, data requests onto the unhealthy node would not return the result within a decent time but ‘unreachable’ error message after waiting for a while.
The location of on-premises data gateway: data gateway requires some network calls to on-premises data sources to interpret OData requests. For instance, data gateway needs to understand the data entity schema so that she could translate OData requests into SQL DML (data manipulation language) statement. However, when the data gateway configured at the other continent with high network latency between the data gateway and SQL instance, it would spawn extra overhead.
Scalability: In some enterprises, a high volume of data access onto the on-premises data gateway would be expected. In this case, just one node of the on-premises data gateway could be a bottleneck to cover a large volume of requests. A single node of the on-premises data gateway can deal with concurrent connections up to 200. If all these concurrent connections are executing queries actively, other requests would be waiting for an available connection.
Azure SQL Online
Organizations can connect to Azure SQL Online via SQL connector. In this case, slow requests were caused by slow queries in the database and/or the huge volume of data had to be transmitted to the client. There were the main concerns. In some case, Service tier of a SQL server was also attributed to slow response.
Common issues
Data size transfer to client: by default, PowerApps canvas app shows data entities which would be either tables or views from database objects. All columns of entities would be retrieving, which prompts slow response of data requests in case entities have many columns and define many big data types like NVARCHAR(MAX). Simply, total data size of transferring data to client requires transferring time and scripting time to keep that amount data in the JS heap at client side.
Slow queries: depending on filtering conditions of data requests, the SQL statement which was converted to could be executed with a certain execution plan. If the query executed with heavy IO operation by table scan or index scan, it means data entities might not have proper indexes covering the query. Although the execution plan of queries uses indexes, it could be slow too in case Key Lookup costs high. Refer to item#3,5 and 6 from on-premises SQL section above.
Service tier: Three Azure SQL Database service tiers—Basic, Standard, and Premium—are available. Each tier has a bit different CPU, IO Throughput and IO(Input/output) latency. Under heavy data requests, these resources could be throttled once the threshold hits. Then, query performance would be compromised.
SharePoint online
SharePoint connector pipelines to SharePoint list(s). From SharePoint list itself, maker can see Power App menu which wizard would create a canvas app quickly.
Common issues
Data size transmitting back to client is matter, especially when the SharePoint data source is remote. If formula in events at canvas app has nondelegable functions inside, Power Apps platform would retrieve records up to Data Row Limits, default 500 but maker can change it up to 2000. If Data Row Limits were set to 2000 and the SharePoint list has many columns, data size transmitting to client could be huge and it could lead to slowness.
Too many dynamic lookup columns: SharePoint supports various data types including dynamic lookup, Person or Group and Calculated. If a SharePoint list defines too many dynamics columns, it would take time to manipulate these dynamic columns within SharePoint itself before serving asked data requests. This would depend on the volume of data rows on the SharePoint list.
Picture column and Attachment: size of image and attached file will attribute to slow response if they are all retrieving to client unless specific columns specified.
Microsoft Dataverse
As you can check this article ‘What is Microsoft Dataverse’, Microsoft Dataverse provides a handy way to define custom entities with built-in security model where you can securely store your business data in.
Canvas app can access a Microsoft Dataverse data source which directly connect to Microsoft Dataverse instance without through API management layer (Refer to Figure 3.) Microsoft Dataverse has enabled by default so that when you create a new canvas app connecting to your Microsoft Dataverse instance, data requests from your app will execute through Microsoft Dataverse onto your Microsoft Dataverse instance.
Microsoft Dataverse connector performs much faster than the old connector. If you have existing canvas apps using an old connector, we highly recommend migrating the app to the Microsoft Dataverse connector.
Common issues
Too much data transmitted to a client also made requests be slow. For instance, if your app has set Data Row Limits to 2000, instead of default 500, it adds up extra overhead on transferring data and manipulating received data to JS Heap at client side.
The app did run client-heavy scripting such as Filter By/Join at client side instead of doing such operation at server side.
Canvas app had used old commondataservice connectors. Firstly, the old commondatasource connectors got some overheads. Hence, OData requests via the connector were slower than that via Microsoft Dataverse connector.
Figure 4 Security Role privilege editor
Excel
People in the business world use Excel sheets to manage their business data. The Excel connector in PowerApps provides connectivity from a canvas app to the data in Excel data table. By following steps here, you can define a data table(s) within an Excel file and retrieve such data onto a canvas app.
Although a maker knows a little about other data sources, Excel would be enough to store your business data based on your format.
However, please be aware that the Excel connector has limitations compared to other data sources. As it provides little delegable functions, PowerApps loads data from data table up to 2000 records, nothing more than that. If you really want to load more than 2000 records, you should do partition your data onto a different data table and then load both data tables.
Apart from this limitation, there are some cases when slow performance happens. Let us see what common issues are there.
Common issues
Too many data tables are defined, and each data table has an immense size of data over many columns. As Excel is not a relational database nor data source providing some delegable functions, PowerApps should load data from defined data tables and then you can use functions that PowerApps provides such as Filter, Sort, JOIN, Group By and Search. If you have defined too many data tables and each contains many columns and stores many records, obviously launching App would be affected by because each data table should be manipulated within JS heap in Browser and the app would also consume certain amount of memory for the data(refer to a section how to check memory usage of your app using developer tool.)
Heavy transactions from many users get slow down the app too. We know Excel is a product dealing with data in its spread sheets. It is not a system nor a relational database. Which means that any data changes from your app would be managed by Excel in the same way that Excel does for data in spread sheets. If the app mainly reads data from the excel file but rarely triggers transactions like Create/Update/Delete, the app will perform well although hundreds of thousands of users use the app. However, if heavy transactions happen from a small group of users, it would be a big offender of slow performance. There is no simple number saying what is the threshold of transactions because it is also related to data itself and the size of the data table and others like network footprint and user’s devices.
The location and size of the excel file. If all data tables are defined within a single file and the file size is big, then extra overheads for downloading the file and reading data to load are expected. Meanwhile, you can select various storage to store the excel file(s): Azure Blob storage, One Drive for business and so on. Please be aware that the Excel file should be downloaded to the client before loading data out of the data tables defined within the file. You can naturally imagine the downloading time of the file would be adding up on overall performance of your app start.
The Excel connector and Excel file will be a good fit for small transactions and data. However, it might not be good enough on the enterprise scale.
Excel vs SharePoint vs Dataverse
Power Apps – Naming Conventions
When building canvas apps the naming conventions used by the objects can be very confusing once you have added a few screens. It is important to make sure you rename your components while working to allow for better readability and supportability of your application. Below are some common naming conventions which are available within the PowerApps canvas app coding standards and guidelines whitepaper.
PowerApps Canvas App Coding Standards and Guidelines
Creating a simple canvas PowerApp is easy. But as the complexity of your app increases, care must be taken to keep your apps maintainable and performant.
Once you understand how to build a simple canvas PowerApp, this white paper will help you take it to the next level. The standards and guidelines are targeted at the enterprise application maker (developer) who is responsible for designing, building, testing, deploying, and maintaining PowerApps apps in a small business, corporate, or government environment. We feel that adherence to these guidelines will help developers in these areas:
Simplicity
Readability
Supportability
Ease of deployment and administration
Performance
Accessibility
As we mention in the white paper, these coding standards and guidelines are flexible and serve as a starting point for organizations to develop their own standards. This white paper is intended to be a living document. As Microsoft Power platform capabilities and industry standards change, so will this white paper.
Delegation
Delegation, powerful concept when working with PowerApps helps in improving app performance by limiting amount of data movement over network. To be precise, delegation will delegate (move) as much data processing down to data layer as is possible instead of retrieving all data and process locally for better performance. Here by processing data means: sorting, filtering, transforming.
The basic principle is to push (delegate) as much processing down onto the data layer as is possible, and have the data layer do the heavy lifting and return the results. In theory this is a great idea, however not all data sources support delegation and, at the time if writing, only a small subset of functions support delegation.
Understand delegation in a Canvas app
Power Apps includes a powerful set of functions for filtering, sorting, and shaping tables of data in a canvas app: Filter, Sort, and AddColumns functions to name just a few. With these functions, you can provide your users with focused access to the information they need. For those with a database background, using these functions is the equivalent of writing a database query.
Delegation is where the expressiveness of Power Apps formulas meets the need to minimize data moving over the network. In short, Power Apps will delegate the processing of data to the data source, rather than moving the data to the app for processing locally.
Working with large data sets requires using data sources and formulas that can be delegated. It's the only way to keep your app performing well and ensure users can access all the information they need. Take heed of delegation warnings that identify places where delegation isn't possible. If you're working with small data sets (fewer than 500 records), you can use any data source and formula because the app can process data locally if the formula can't be delegated.
Delegable data sources
Delegation is supported for certain tabular data sources only. If a data source supports delegation, its connector documentation outlines that support. For example, these tabular data sources are the most popular, and they support delegation:
Common Data Service
SharePoint
SQL Server
Imported Excel workbooks (using the Add static data to your app data source), collections, and tables stored in context variables don't require delegation. All of this data is already in memory, and the full Power Apps language can be applied.
Delegable functions
The next step is to use only those formulas that can be delegated. Included here are the formula elements that could be delegated. However, every data source is different, and not all of them support all of these elements. Check for delegation warnings in your particular formula.
These lists will change over time. We're working to support more functions and operators with delegation.
Filter functions
Filter, Search, and LookUp can be delegated.
Within the Filter and LookUp functions, you can use these with columns of the table to select the appropriate records:
And (including &&), Or (including ||), Not (including !)
In
=, <>, >=, <=, >, <
+, -
TrimEnds
IsBlank
StartsWith, EndsWith
Constant values that are the same across all records, such as control properties and global and context variables.
You can also use portions of your formula that evaluate to a constant value for all records. For example, Left( Language(), 2 ), Date( 2019, 3, 31 ), and Today() don't depend on any columns of the record and, therefore, return the same value for all records. These values can be sent to the data source as a constant and won't block delegation.
The previous list doesn't include these notable items:
If
*, /, Mod
Concatenate (including &)
ExactIn
String manipulation functions: Lower, Upper, Left, Mid, Len, ...
Signals: Location, Acceleration, Compass, ...
Volatiles: Rand, ...
Collections
Sorting functions
Sort and SortByColumns can be delegated.
In Sort, the formula can only be the name of a single column and can't include other operators or functions.
Aggregate functions
Sum, Average, Min, and Max can be delegated. Only a limited number of data sources support this delegation at this time; check the delegation list for details.
Counting functions such as CountRows, CountA, and Count can't be delegated.
Other aggregate functions such as StdevP and VarP can't be delegated.
Table shaping functions
AddColumns, DropColumns, RenameColumns, and ShowColumns partially support delegation. Formulas in their arguments can be delegated. However, the output of these functions are subject to the non-delegation record limit.
As in this example, makers often use AddColumns and LookUp to merge information from one table into another, commonly referred to as a Join in database parlance:
AddColumns( Products,
"Supplier Name",
LookUp( Suppliers, Suppliers.ID = Product.SupplierID ).Name
)
Even though Products and Suppliers may be delegable data sources and LookUp is a delegable function, the output of the AddColumns function isn't delegable. The result of the entire formula is limited to the first portion of the Products data source. Because the LookUp function and its data source are delegable, a match for Suppliers can be found anywhere in the data source, even if it's large.
If you use AddColumns in this manner, LookUp must make separate calls to the data source for each of those first records in Products, which causes a lot of network chatter. If Suppliers is small enough and doesn't change often, you could call the Collect function in OnStart to cache the data source in your app when it starts. As an alternative, you could restructure your app so that you pull in the related records only when the user asks for them.
Non-delegable functions
All other functions don't support delegation, including these notable functions:
First, FirstN, Last, LastN
Choices
Concat
Collect, ClearCollect
CountIf, RemoveIf, UpdateIf
GroupBy, Ungroup
Non-delegable limits
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.
Aggregate functions can also cause confusion. Take Average over a column of that same million-record data source. Average can't yet be delegated, so only the first 500 records are averaged. If you're not careful, a partial answer could be misconstrued as a complete answer by a user of your app.
Changing the limit
500 is the default number of records, but you can change this number for an entire app:
On the File tab, select App settings.
Under Advanced settings, change the Data row limit for non-delegable queries setting from 1 to 2000.
In some cases, you'll know that 2,000 (or 1,000 or 1,500) will satisfy the needs of your scenario. With care, you can increase this number to fit your scenario. As you increase this number, your app's performance may degrade, especially for wide tables with lots of columns. Still, the best answer is to delegate as much as you can.
To ensure that your app can scale to large data sets, reduce this setting down to 1. Anything that can't be delegated returns a single record, which should be easy to detect when testing your app. This can help avoid surprises when trying to take a proof-of-concept app to production.
Delegation warnings
To make it easier to know what is and isn't being delegated, Power Apps provides warning (yellow triangle) when you create a formula that contains something that can't be delegated.
Delegation warnings appear only on formulas that operate on delegable data sources. If you don't see a warning and you believe your formula isn't being properly delegated, check the type of data source against the list of delegable data sources earlier in this topic.
For more information or if you have any queries related to this article or any other articles, mail us at support@thetechplatform.com or follow us on Social Media @thetechplatform.
You can also subscribe us at www.thetechplatform.com for more articles.
If you want to to be part of the event "TALKS ACANDEMY" mail us or visit us .
Read More:
The Tech Platform