NOTE: Information good as of 5/26/2015 and is subject to change!
There seems to be some confusion about where the Data Management Gateway (DMG), or rather any Gateway, for Power BI can be located. The biggest thing I hear is that the gateway has to be on the box that we are trying to connect to.
So, for example, I have a SQL Server that I want to use with Scheduled Data Refresh with Power BI. People would tell me that the gateway has to be on the SQL Server box. That just simply isn’t true. The short answer to this is just plain no!
Does that mean we should just put the gateway anywhere? No! We should be thoughtful with where we locate this. Having the gateway on the machine we are trying to connect to is the shortest distance we can be. This eliminates potential network latency issues and really allows us to get in and get out a little faster. Your milage will vary of course as it really depends on your infrastructure.
That being said, some DBS’s or IT folks may prefer it not to be on the data server as that could impact what the data server is actually trying to do. So, in that respect you may want it on a different machine. You will really need to look at your environment and determine what is the best fit for you. As with most things, it really depends.
Changing the Gateway to a different box
For this example, we have a workbook setup that has a SQL Connection via the Excel Data Tab. We already have a Gateway and Data Source setup for this refresh to succeed.
I have a Gateway called GuyInACube that is sitting on the box that is hosting my SQL Server.
I also have another Gateway available that is just sitting on a different server. This gateway is called ClientGateway.
Unfortunately, the UI doesn’t let you change the gateway of a Data Source. It has a drop down, but only shows the current gateway. So, we need to delete the existing gateway and create a new one. The reason we want to delete the old one, instead of them being side by side is that will cause issues with the Data Source/Connection String Match up.
When we create the new Data Source, we keep all the same info, except I choose the ClientGateway for the Gateway dropdown.
Once that is done, we can go back and test the connection from the Scheduled Data Refresh perspective.
Adam W. Saxton | Microsoft Business Intelligence Support – Escalation Services@GuyInACube | YouTube | Facebook.com\guyinacube