Welcome to the module on advanced app design. In this module, you will learn about app scalability and the practices you can use in AppSheet to improve the performance of your app. We also discussed how to support multiple languages in your app and techniques to collect user feedback about your app. Let's start by discussing how you can improve the performance of your app by using techniques like partitioning and security filters that improve the data scalability of your app. Data scalability refers to the performance of your app when the data it uses grows and becomes large. App creators should consider how their apps perform and design their apps to scale while supporting large datasets. There are also physical limitations on the number of rows or cells that can be stored in certain types of data sources. For example, Google Sheets limits a spreadsheet to five million cells. Smartsheet limits a single spreadsheet to 20,000 rows. There are two app usage patterns to consider when working with large datasets. Non-partitionable and partitionable. The non-partitionable app pattern is used when each user needs access to all the data in the large dataset. For example, an inventory management app that manages a large number of products and their related data may need each user to manage all products in the app. The partitionable pattern involves a large number of data rows. But each app user only accesses a smaller subset of the rows. For example, a project management app may be used to manage a large number of projects and their tasks for an organization. Only project team members working on specific projects need to access a subset of projects and their tasks. You can scale partitionable apps easily and effectively by using security filters and data partitioning. Before we discuss these scaling techniques, it's useful to understand how AppSheet makes data available to the app. There are three steps involved when fetching app data. One, from the Cloud data provider to the AppSheet server. When an app starts and fetches data from AppSheet, the AppSheet server must first retrieve that data from the Cloud data providers server. This data can be from a spreadsheet, database or API. Two, from the AppSheet server to the app. Next, the data is sent from the AppSheet server to the app that is running on a user's mobile device or desktop browser. Three, storing the data on the device. Finally, the app stores the data locally on the device. This enables the app to work seamlessly when offline or on a low bandwidth network. Let's review how you can scale your app using security filters. Security filters are yes-no expressions that are associated with each table in the app and use the user's email address and other data values to limit the data shown to the app user. Security filters were discussed in a previous module in this course. When you scale your app with security filters, the data is filtered during or after Step 1, so you can reduce the data that is used in Steps 2 and 3. Please note that when you use a spreadsheet in your app, the entire sheet is fetched in Step 1. When you use a database, the data is filtered during Step 1 at the database layer, which can lead to significant performance improvements in your app. Security filters are primarily meant to be a security feature, but they can also be used to improve an app scalability. The most common filter returns rows from a dataset that are relevant to the app user. For example, where the owner of the rows matches the email of the signed in user. In the example shown, if a user uses the email address e2@company.com to sign into your app, you can use a security filter to return rows from a team project table that only matches the user's member ID. These rows contain data of the projects that the user is currently a member of. When you use database tables in your app, AppSheet attempts to apply security filters to the database as SQL queries, provided the filters can be mapped to efficient queries. AppSheet apply security filters to the database as SQL queries for the following database providers: MySQL, Microsoft SQL Server, MariaDB, Oracle, Postgres, and Redshift. To ensure that a security filter maps to inefficient database query, try to reduce it to a simple comparison of a column to a value using comparison operators and expressions. Compare columns value with a list of values using the IN function, and use an AND condition with simple comparison expressions. Most other conditional expressions will probably not be applied as database queries. But AppSheet does attempt to apply partial expressions where possible. For example, the condition shown using a simple first expression where column 1 equals 5, that will be applied to the database query, but not the second. Let's now discuss how you can use data partitioning to scale your app. In this approach, the data is divided into identical partitions that have the same column structure. With each partition containing a different subset of the rows of data, each user gets data from a specific partition at any given time when using the app. This technique can be used along with security filters and enables the app to scale efficiently. Let's review how to use data partitioning through an example. To illustrate how data partitioning can be implemented in your app, we'll use a simple project management app that allows users to manage tasks in a project. We'll use the following data sources for our sample app, projects, a spreadsheet containing all the projects that are managed by the app and tasks, a spreadsheet that contains the tasks for each project. To use data partitioning for this app, do the following. Partition the task's spreadsheet to split the tasks into separate data sources by project. The tasks can be split into multiple spreadsheets or into multiple worksheets in the same workbook. The sample shows the contents of the project 3 tasks worksheet. Note that the column structure and all sheets must be identical. In our sample app, we use two tables to store employee information and project team membership. Add the teams and employees tables to our sample app. AppSheet automatically determines the relationship between these tables in the projects table and configures the corresponding columns as type ref. Provide a partition expression that enables AppSheet to determine which task worksheet or workbook to use for a given app user based on the project they're currently working on. The partition expression identifies a specific partition for each user based on the user email or user settings values. To use a partition expression, expand the tasks table definition in the data tables tab in the AppSheet editor. Expand the scale section. Because the tasks in our sample app has been partitioned into multiple worksheets, enable that partition across many worksheets option. For the partition expression, use the expression assistant to type an expression that returns the project ID that the signed in app user is a member of based on their email address. Then specify the data partitions by identifying each worksheet using the worksheet name in the tasks workbook and using the project ID value that is returned by the partition expression. Save your changes to the app. Now when an app user signs into the app, the app only fetches the tasks data from the app sheet server that corresponds to the project that this user is a member of. This makes the app highly performant when there are large number of projects and tasks. It also limits the amount of data transferred from the AppSheet server to the user's device. Please note that the partition expression uses the any function to select any one project ID from the teams table. This works as expected when the user is a member of one project or works on one project at a time. But what if you wanted a user who may belong to more than one project to be able to select a specific project to manage its tasks in the app? To implement this capability where the user has the ability to dynamically update the filter in the app, use AppSheet's user settings feature. This feature is discussed in the next video of this lesson. Now that you know how to partition your data to improve your app's performance, let's discuss the pros and cons of this technique. Data partitioning is a powerful mechanism to scale your app while keeping your data in the format of familiar spreadsheets like Google Sheets, Excel or Smartsheet. The amount of data transferred over the network between the app and the AppSheet server is reduced and the amount of storage needed by the app to store this data on the user's mobile device is also reduced. When using data that must be stored separately, for example, for client data, partitioning provides a convenient solution to maintain and distribute a single app, while still allowing each user to view and manage their own data using the same app. The disadvantages of using data partitioning are, because the data is divided into several physical sheets, you might create extra management overhead for data maintenance and reporting. It's important that all the partitions have an identical column structure. Because AppSheet cannot guarantee this behavior, this becomes an additional responsibility for the app creator. To summarize, in order to scale and improve the performance of your app, you can use security filters, implement data partitioning, and use database sources with efficient filters that can be mapped to database queries. Non-partitionable apps can be difficult to scale. Because all the data must be accessible by each user, all data rows must be fetched and moved through all three data transfer steps discussed earlier. This will cause the app's reforms to degrade as the dataset scales.
