Using SQL Server Views in PowerApps

 

We are very pleased to announce today that SQL views from the SQL Server connector available at the end of the week in PowerApps. Leveraging SQL Views will allow you to combine data in SQL Server prior to bring that data into PowerApps. This can allow for better performance in PowerApps without having to load sets of data and mashing it up later, explained in Mehdi’s blog post here (https://powerapps.microsoft.com/en-us/blog/performance-considerations-with-powerapps/ ).

A common scenario for makers is to have a single table (Product Category) with lookups to another table (Products) which contain data points like the product number, name and price. In the past you would need to bring in both tables into PowerApps, create a gallery of categories, then lookup the detail information for the product’s information. If this was done incorrectly, you could experience performance issues due to the amount of data calls to your SQL Server.

Another scenario this addresses is the need to aggregate (group and sum) data prior to bringing it into PowerApps. Rather than bring all of the records into PowerApps and using the Sum feature, for example, you can do all of that advanced processing in SQL Server.

Let’s look at the correct way to do this now:

Start in SQL Server by creating a SQL View to mash up your two tables:

1. Log into SQL Server Management Studio (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms ) or your tool of choice to access SQL Server
2. Expand Databases and go to Views
3. Right click on Views to create a new View
4. Use the designer to create your view or write it using T-SQL

    5. Click Save and name your view (I called mine VW_ProdCat_W_ProdDetails)

    6. Run your view to make sure you are getting the results you expect

Next, we move to PowerApps:

1. Open PowerApps in your environment that has a connection to the SQL Database that has the view you just created
2. Open Data Sources and select your SQL Connection
3. Now you will see your new view in the list (or if you have many tables and views it might not show by default, use the search feature to search for your view)

    4. Check the box next to your new view then click the Connect button

    5. Add a Gallery to the canvas and select your view as the data source

Now you will see your mashed-up data in the gallery with out having to make multiple calls to the database to get that data together.

 

There are a few features to keep in mind when using SQL Views:

• At this time, SQL Views are read-only in PowerApps
• You cannot update or create records through a view, but you can perform these operations directly on a SQL Table.
• You will be able to use key columns from the view to update /create rows in other tables.  You will need to update the individual tables that fed into the view.  Be sure to include in the view primary keys for any lookups so you can easily interact with the base tables.
• If you would like to call Stored Procedures, you will need to use Microsoft Flow at this time (https://docs.microsoft.com/en-us/connectors/sql/)