Creating a Power BI report when your development environment don’t have access to the data source.

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.

image

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.

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

Next create a Workspace in Power BI and within it create a “Dataset”.

image

Once you click “Dataset” option in the menu, you will get a window like bellow. Select “Databases” option in that window.

image

It open up another menu to select the data sources type. As I mentioned earlier , it contains only 4 data source types.

image

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.

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.

image

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.

image

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.

image

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.

image

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 !!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s