Gateway Connections - Power BI

Gateway Connections allow Power BI applications published in our Microsoft M365 tenant the ability to access data that in our data centers (on-premises). This document covers how to create a gateway connection for a Power BI application that has been published to our Power BI Tenant using the Power BI Desktop Application.

Once a Connection has been created, you can also schedule the data set to refresh automatically - up to eight times a day.

Overview

Power BI Applications that are members of a capacity workspace can synchronize data from on-premise data sources using our on-premises gateway.  You create your Power BI application and then publish it to a Workspace.  Now you can update the dataset to use our on-premise gateway to refresh your published application.  The data can be scheduled to refresh automatically up to eight times per day. 

Though there are several dozen datasource types in Power BI, this document covers two types of datasources:  Microsoft SQL server, and File.  Once this connection is created, we can share this dataset to specific individuals so they can create their own Power BI application using this dataset.

Prerequisites

1. SQL datasets are using the Import Data Connectivity Mode
2. Your Power BI application is already published to a workspace
3. You have access to the on-premises gateway (Request access via Ticket to Helpdesk)
4. IT has reviewed where the dataset resides and have provided a service account

Editing the Application Dataset - Creating the initial connection

1. Log into the Power BI portal and browse to the Data hub
2. Find the Dataset you want to update to use the Gateway to access on-premise data and hover over it to see the More Options Menu and click on the three dots

3. Now pick Settings
4. 
5. Scroll down and click on Gateway Connection.  Since your Power BI Application is currently configured to access an on-premise resource (like your H: drive, \\vault, or even a SQL server), it will indicate that the Gateway is Not configured correctly.  Click on the arrow under Actions

File data source (excel spreadsheet, delimited text file)

Please make sure that the filename mentioned in the dataset is using the Univeral Naming Convention (UNC) versus a drive letter.  For example, if you have files on your on vault (your H:), your dataset probably looks like 

H:\CSUSERS\COMMON\HELPDESK

However, to leverage the Gateway to access this dataset from the cloud, it will have to be referenced like this instead:

\\Vault\dept\CSUSERS\COMMON\HELPDESK

Where you replace the H: with \\Vault\Dept.  If you need any assistance with this, please contact the helpdesk.  You can check how your dataset is referenced in the Power BI Desktop by clicking on the Data Icon and clicking on Data Source Settings:

If the file path starts with a letter followed by a colon (H:), then it will have be changed

 

6. You will be presented with the data sources that feed your application.  In this example application, there are four spreadsheets that need to be "defined" on the gateway.   If you are dealing with multiple data sources, make a mental note of which one you picked so you can come up with an appropriate name for the data source.  Click on Add to gateway to start the definition process

7. You will be presented with the following dialogue box

8. Since we just have one gateway cluster, that will auto-populate.  For File based data sources, we can use the file name or even add the application that's leveraging that file.  In this case, the project tasks are for a Team Dynamix report.  When entering the username, be sure to preface it with nic\.  If you do not have a service account, you can use your own just to get the data source created.  We can always change the Data Source Name, Windows username/password, and Privacy level at a later date.
9. Once the data source is created, we can map that data source in the Gateway Connection 

10. Repeat this process for the remaining data sources. Once all the data sources are mapped, the Status should turn green

 

SQL Data Source

6. You will be presented with the data sources that feed your application.  You will need to create a data source for each database you are referencing in your application.  In this example, there are two tables out of the IDM database, and one table out of the 911 database on the same server.  As a result, there are two data sources to define.  What is interesting in this screenshot is that there is already another application that is using the IDM database on OLTP.NIC.EDU; so it already presents the Maps to field.  However, to show how we add a SQL data source, we will click on Add to gateway to start the definition process.  

7. You will be presented with the following dialogue box

8. Since we just have one gateway cluster, that will auto-populate.  For the data source name, at least specify the database and server name.  Set the Authentication Method to Windows.  If you do not have a service account (sa) yet, you can use your own credentials prefaced with "nic\".  Just keep in mind if we have to reset your password, this connector will break.  Scroll down to the bottom of the window and click Create.
9. Once the data source is created, we can map that data source in the Gateway connection.  In this case, I'll go ahead and use the existing data source for the IDM database on OLTP

10. The status for the Gateway Connection for this data set should turn green if it hasn't already

 

Scheduling Automatic Refresh of the dataset

Now that the published application can connect to its data source(s), we can also specify the frequency of when the application refreshes its data.  For this example, I just set it to refresh once a day.  Whether or not I actually update the excel spreadsheets is irrelevant as long as a spreadsheet with the expected columns is in that location.

1. Under that Settings menu that we are creating those Gateway Connections, scroll down to Scheduled refresh

2. Toggle the switch to On and click on Add another time.  If the default time is fine, click on Apply

3. You can add up to eight different times a day for this application to refresh.  

Editing an existing Data Source

1. If we want to change a Gateway Data Source (the name, user account, or privacy level, click on the three dots on the top right of the Power BI portal

2. Hover over Settings and click on Manage gateways

3. You will be presented with a list of our Data Sources, hover over the one you want to edit so that you can click on the More Actions icon (three dots)

4. The Setting dialogue will show up - the same dialogue that was presented when we created this data source.  Note that you cannot change the File path (or the server/database if this is a SQL Server Connection).  We will have to create a new Data Source for that.

5. Once you have made your changes (update any field that is not greyed out), click on Save.

 

Row-level Security (RLS)