Recently, One of my friend called me and asked whether there is a way to select Gateway when publishing a Power BI report. My answer was NO because there is no such option currently.What Power BI does is according to the source connection, Power BI automatically select the available Gateway for you (If you have configured a Gateway with same connection information) . So I asked him to change the data source name ( or IP address) to the respective server before publishing the report. However, in his case, although he has a local copy of the database ( with dummy data). His development environment don’t have access to end user’s Data Server. As a result, although he changed the data source to publish, Power BI doesn’t allow him to publish the report. So what’s the solution? Only solution I can come up with is to use Power BI data sets. Please note that this approach only works with Azure SQL Database, Azure SQL Data Warehouse, SQL Server Analysis Services and Spark on Azure HDInsight so far.The idea here is Power BI Service connects to the client’s server using On-Prem Gateway and Power BI Desktop in your development environment connects Power BI Service using Power BI Datasets. Check the image bellow.
To begin with, you need to configure a Gateway server in Client’s server, which I will not cover in this post because I believe you already know how to configure On-Prem Gateway server in Power BI. If not,please read the following article.
Next create a Workspace in Power BI and within it create a “Dataset”.
Once you click “Dataset” option in the menu, you will get a window like bellow. Select “Databases” option in that window.
It open up another menu to select the data sources type. As I mentioned earlier , it contains only 4 data source types.
In my case, I select “SQL Server Analysis Services” and click “Connect”. It shows all the configured Gate ways which have Microsoft Analysis Service Servers configured. Check bellow image.
Select your Gateway and connect to the configured data source from it, in my case client’s Analysis Services Server. Provide a name to your Dataset; I named it as “Client’s Dataset”.Now if you go to the Datasets section of your Workbooks, it should be visible like this.
Then from your development environment, open Power BI Desktop application from command prompt, using “run as” command. The reason for doing this is we need to pass authentication information from your development environment to client’s data server. Therefore, you have to use “netonly” option to impersonate a client’s domain user from your machine. Check the command bellow.
runas /user: Domain/UserName /netonly “C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe”The highlighted section must be a domain user name who has access to the data source which you intent to connect. Once above command is entered, it asks the password of the respective domain user.
Enter the password and it will open up the Power BI Desktop. Then click “Get Data” button and select “Power BI datasets”. Check the image bellow.
Once selected “Power BI datasets”, it shows all the workspaces under your login and out of those, select the Workspace you created before and then select the dataset.
If you have done all the above steps correctly , you must now have access to the client’s dataset from you Power BI desktop application. Using this method you can connect to client’s server and create and publish Power BI reports. Thanks for reading and Cheers !!