PLEASE READ CAREFULLY
Before getting into the topic of this post, I want to clarify that what I am describing here is an UNSUPPORTED configuration. There are very specific reasons I am setting up my environment in the manner described below, and anyone repeating these steps should understand that they are accepting all responsibility for deviating from standard guidance.
In addition, I want to clarify that these steps apply to the products released in February 2014. As these products evolve rapidly – one of the great benefits of the cloud – these steps may become quickly out of date.
NOTE: Gerhard Brueckl has documented another way to implement a very similar demo environment. Please check out his post here.
I frequently need to setup trial Power BI environments and as part of my demonstration I need to show the OData feed feature of the Power BI Data Gateway in action. In a standard deployment, the Data Gateway would be installed on a server that is part of a domain which has been federated with Office 365, but as I am implementing a demonstration environment that is not part of a formal domain (and may be working with multiple deployments simultaneously), I need to take a non-standard approach to my setup.
For my deployment, I am using a client system that is part of my corporate domain, a trail Power BI site with which I have no Active Directory federation, and an Azure Virtual Machine running Windows Server 2012 R2 with SQL Server 2012 SP1 which also has no Active Directory affiliations. To setup the Power BI Data Gateway, I follow the steps outlined here. I use HTTPS with the default port of 8050 and a self-signed certificate created via makecert per these instructions. When executing makecert, I use the short server name so that if the fully qualified name of my Azure VM was myserver.cloudapp.net, I would use myserver as the server name in that step. I then complete the setup of my data source and OData feeds, again using the standard steps.
At this point, the Data Gateway and the data source are fully configured and should appear to be in good working order from within the Power BI Admin Center. The challenge is now to get my client system to properly communicate with them. To do this, I do the following:
In this last step, the server name is the same short server name as used in earlier steps.
With the HOSTS file modified, I can now locate the server used as my data source. My next step is to install the certificate used to encrypt the HTTPS channel to my client system. To do this, I do the following:
Now I can launch Power Query, perform an Online Search to locate data sources I’ve exposed as OData feeds via my Power BI Data Gateway, and pull data through those feeds into my workbook. Be sure to remember to sign-in to the Power Query add-in using the login (Organizational Account) associated with your Power BI site.