Power BI Data Gateway: Earth To Cloud
The Data Gateway is Power BI's main mode of communication with on-premise data sources
Whether it be Sage X3, Microsoft Dynamics, Excel, or just about any on-premises data source, a data gateway is the go to method of connectivity. With out a gateway connection, the only viable way of "refreshing" is to open up your Power BI .PBIX file, refresh your data, and re-publish. This is unrealistic, and cumbersome. Installing a gateway allows you to push refreshes through in the online service with no need to back track.
A gateway really is the only option when it comes to efficiency, speed, and convenience for refreshing Power BI reports from an on-premise data source. Oh and did I mention you can set a schedule to automatically refresh during certain times of the day? Well now I did! In this blog we will go through all of the steps in setting up and installing a Power BI On-Premises Data Gateway, which you can get here.
What resources do I need to accomplish this?
Data Source
Internet Connection
Power BI Desktop
It really is that simple!
Here are few points highlighting the utility and benefits that data gateways open up to you and your business.
Up-to-date data
An on-premises data gateway is the most streamlined way to have your data keep up-to-date. With an allowance of up to eight refreshes a day, a gateway will almost always suffice.
Dynamic data sources
Each gateway you create can contain as many data sources as you wish. This allows for many reports and data sets to be configured to a single gateway and refreshed simultaneously.
Organization Wide Consistency
When you create a gateway, you can allow your organization access. This creates an environment in which standarized gateway configurations are achieved very easy throughout your organization.
One time set-up
Once your gateway is installed and configured on your computer, data sources are added through the online service. So you only need to configure your gateway once!
Accessibility
As you will see, the entire process of connection to your data and configuring a automated live refresh is relatively simple. This is a huge benefit, and creates leverages Power BI as an extremely accessible tool.
Flexibility
All of the points in this list combine to create a consistent, dynamic and accessible data source. The flexibility that this combination of attributes provides is endless.
What steps are required to set up a gateway connection?
The challenge for most, is that they aren't quite sure where to start. Here we will go through an example, from start to finish, of getting data from a SQL Server sample X3 database and setting up a gateway to automatically refresh our data.
Here are some steps we overcome in this guided tutorial.
How do I get data from SQL Server ?
Where do I get a gateway ?
How do I set my gateway up ?
How do I add data sources to my gateway ?
How do I schedule a refresh pattern ?
All of these are answered in the steps below and in the video at the end of this blog.
I encourage you to follow along if you have never set up a gateway before! Its quick, easy, and a major step in creating a rich analytical environment.
Step-by-Step Instructions
1) Get Data:
First we need to get some data to build our connection with. In a new report click get data and choose SQL Server.
2) Connect to SQL Server:
Enter your SQL Server details. Here you can see I have my SQL Server name in the first box, the data base name in the second and my query under advanced. This is a very simple SQL query for example sake which obtains company information.
3) SQL Server often requires credentials:
It is quite likely that you will be presented with this window. Here you provide your SQL Server credentials to establish the connection.
4) Publish this report for later!:
Lets publish this report to our work space in order to set up our refresh later on.
5) Download the gateway installer:
The next step is to install the On-Premises data Gateway in order to create the pipeline to the Power BI web service. Click here to get to this page, or navigate to https://powerbi.microsoft.com/en-us/gateway/ .
6) Install the gateway:
Once you have downloaded the gateway installer, proceed by opening the installer in your downloads folder. Continue with the installing process.
7) Choose connection type:
Select the option "On-premises data gateway", and click next.
8) Continue with install:
Again, proceed with the install procedure.
9) Proceed to configure a gateway:
Once the gateway is installed, we need to sign in to our power bi account to configure the gateway.
10) Sign in using your app.powerbi.com login:
11) Create your gateway:
Once you are signed in, create your gateway: As a note, we it is sometimes helpful name our gateway with our Recovery Key in the title. This eliminates the need to store more passwords and keys.
12) Navigate to your Workspace:
Now that your gateway is installed and configured, we have just a few more steps to take in the online service. Navigate to your workspace, and under the Datasets tab, select the "Schedule Refresh" Icon next to our reports dataset.
13) Manage Gateways:
Our dataset will not currently be active to schedule with. So select the option "Manage Gateways".
14) Add a data source:
With our newly created gateway selected, click the button "Add data sources to use the gateway".
15) Add a data source continued:
Enter you data source details. In this example, the details I am entering are the same as when I connected to SQL Server in Power BI.
16) Now to schedule a refresh:
Now that our gateway is connected to our data source we want to create a refresh schedule. To do this, again navigate back the the Datasets tab in your workspace and click "Schedule Refresh". Same as before.
17) Activate your gateway:
Under Gateway connection, choose the option, "Use an on-prem data gateway." And click apply.
18) Schedule your refreshes:
Last but not least, under the Scheduled Refresh tab, turn "Keep your data up to date" on. Now add refresh times by clicking "Add another time" and entering your desired times!
And there we have it. That is the entire process for connecting your data, installing the on-prem gateway, configuring datasources, and scheduling a refresh in Power BI. I hope you find this helpful in your buisiness ventures.
How Can This Help My Business?
In addition to the points covered earlier on in this blog, there are a few more benefits that I feel should be mentioned.
TIME: When comparing the hours and labor needed to have someone manually refresh reports when they are needed versus the time spent installing the gateway , it is obvious that setting up a gateway to do all the hard work for is the only way forward.
COST: Along side the cost saved in time. The other options for creating a live data analytical environment are more costly and require more effort for little more advantage.
ANALYTICS AVAILABLE: The best part of Microsofts on-prem data gateway is once you have your gateway set up, every report you build using that same data set will also be configured to use teh same data gateway. This opens up the the doors for creativity and design in a way no other connection can.
If you are interested in more details behind how the gateway works have a look here. Thanks for reading and I look forward to posting with you again.
Our Latest Blogs
About FreshBI
Based in USA and Canada, we have helped hundreds of businesses achieve excellence & success through business intelligence.
Power BI runs in our blood, and we are ready to take your business to next level.