Nearly two years ago, Microsoft launched PowerApps as a tool that Citizen Developers could use to build simple mobile solutions for accessing their data. We wrote an introduction to PowerApps in December last year that highlighted some of the benefits. In this post, we are going to look at using the PowerApps On Premises Data Gateway to build a PowerApp using On-Premises Data. There are now an endless number of connections that you can make using PowerApps, that with a few clicks, you can have the data from those connections available in a mobile form on your mobile device, tablet or desktop. Those connections include Twitter, Twilio, SQL Server, Slack, WordPress, Wunderlist, YouTube and of course SharePoint! Open up PowerApps from your Office 365 Tenant and click Add Connection, and you will see long list of possible connections.
In this post, we are going to focus on accessing On Premises data using PowerApps. Many organizations still have, and are likely to have for some time, legacy databases which are used for critical operations within their businesses. The databases that I am referring to may be running on SQL On-Premises, Oracle, DB2 or many other database platforms. It may be that for the time being at least, it is just too costly, or there are policies in place restricting you from hosting such data in the cloud. However, you want to make the data available to the cloud, for business users to benefit from.
PowerBI for some time has provided the On-Premises Data Gateway as a service for accessing On-Premises data, and PowerApps can utilize the same technology. The On-Premises Data Gateway can be used to connect to a number of different data sources including SAP HANA, ODBC, SQL Server, Oracle, DB2 and ODBC.
Download the Microsoft On Premises Data Gateway
Allowing your On-Premises data to be available to the cloud is quite straight forward. First, you need to download and install the On-Premises Data Gateway. The installation file can be downloaded from here: https://www.microsoft.com/en-us/download/details.aspx?id=53127
Installing the Microsoft On Premises Data Gateway
Once you have downloaded the Microsoft On-Premises Data Gateway, run the installer executable and go through the steps below:
1. Click Next to the warning regarding the machine being always on.
2. Accept the Terms and Conditions and set the installation path.
3. Set the email address to be used for the gateway. You will need to sign in to your Azure tenant.
4. In this example, we will register a new gateway on this computer.
5. Provide a name for your gateway along with a recovery key.
6. Your gateway will be configured for Power BI, PowerApps and Microsoft Flow. A Gateway can be configured for Logic Apps and Azure Analysis Services.
7. Now that the Microsoft On Premises Data Gateway is configured and running, you can turn your attention to PowerApps. Login to your Office 365 Tenant and select PowerApps from the Office 365 App Launcher. Once PowerApps has launched, select Apps from the left hand navigation pane and, then click the Create an App link in the top right hand corner of your page.
8. On the New App page, click the Phone Layout link under Common Data Service to connect to a data source via your Microsoft On Premises Data Gateway.
9. You can now create a new connection to your data source. Click New Connection.
10. From the list of Data Sources, choose your desired data source. In our case, we will be selecting Microsoft SQL Server.
11. Select the radio button to choose to Connect using On Premises Data Gateway.
12. Set the SQL Server Name, Database Name, Authentication Type (Windows or Basic), the credentials, and select the name of your On Premises Data Gateway that you set in step 5.
`13. Once the connection is established, you can select a Table from the list of tables returned from the database.
14. PowerApps will be launched allowing you to work with your data.
Working with the App
You’ll notice that you now have a phone layout browse screen allowing you to browse, view and edit the data from your desktop, but also if you install the PowerApps phone app, and sign in to your tenant, you will be able to use the data from your phone as well. Before you let users loose on the App, you may want to consider input validation and consider the integrity of your database. In the left hand navigator, your will see that you can customize the browse screen, detail screen and edit screen. Customizations may be to include layout changes, and also validation. In our next blog post, we will focus on making design changes and add some validation logic to our forms.
<Brett/>
