Microsoft is continuing to bolster the features of its Power BI suite of business analytics tools. One of the newest features just released, Power BI Enterprise - Gateway (Preview), is particularly interesting to users who have multidimensional cubes deployed in their environment. One of the limitations of the Power BI suite has always been the inability to connect live to multidimensional cubes. With the release of the enterprise gateway, companies can now open a gateway to the web service and allow reports to query live data from the cube.
How to Install Power BI Enterprise Gateway and Connect to the Cube
In this blog, I will run through how to install the gateway, connect to the cube, and run reports through the gateway.
To start, we will need an organization email account that has access to Office 365. Because the gateway passes this user's credentials through the gateway down to the cube, this account will need to be tied to an Active Directory account that has access to the target cube the reports will be querying. Once you have an account ready to go, visit http://powerbi.microsoft.com and click on "Get Started Free."
Next, click on "Sign Up" under Power BI…
Enter you work email address on the "Get Started" page and click "Sign Up." You will then receive a confirmation email from Microsoft. Follow the instructions in the email to confirm your address and you are ready to start installing the gateway.
One thing to note here - in order to create and manage gateways, you will need a Pro subscription. You can sign up for a free 60-day trial through the Power BI service page once you are signed in. Additionally, any user who will need access to a report and utilize the gateway will also need to have a Pro subscription.
Where to Install Your Power BI Gateway
The first thing you need to decide is where you want to install the gateway. Technically, you can install the gateway on any machine that has access to the cube to which you want to connect and report from. That being said, Microsoft suggests installing the gateway on the same server as the Analysis Services instance. They suggest this to avoid the double hop of having to go from the database server to the gateway server and then up to the Power BI web portal. Once you have decided where to install the gateway, go to that server and log into the Power BI web portal at http://powerbi.microsoft.com with the account you created above. Once in, click on the downloads section and choose "Power BI Gateways."
This will bring you to the gateway page for Power BI. Scroll down and click "Download" under the "For Enterprise Deployments" section and save the file to a location on the server.
Go to the location were you saved the file and click on EnterpriseGatewayInstaller.exe. The installer will start. You can then accept the default installation or choose your own. Click install.
Once the gateway is installed, it will ask you to sign in to Power BI. Click on "Sign in to Power BI" and a Microsoft login popup will come up. Sign in using the account you signed up with above.
How to Set Up Power BI Gateway
Once you have signed in, the gateway setup process will begin. Give the gateway a name (this is what will be displayed in the web portal) and provide a recovery key. As the screenshot below states, the recovery key will be needed if the gateway ever needs to be restored. Click Configure and it will create your gateway.
Once the gateway is created, you will get a message telling you it has been setup and configured.
How to Add Sources to Gateway with Power BI
The next step will be to add data sources to the gateway through the Power BI service. Click on "Close & Add Data Sources" and the service website will come up.
Once the page comes up, you can optionally fill in the Department and Description fields and hit "Apply." Once the screen refreshes, click on the link to "Add Data Sources to Use the Gateway."
Fill in the values of the cube that you would like to add to this gateway and then click "Add."
If you would like to grant access for other users to this gateway, click on the "Users" tab and enter the email addresses of those users.
How to Create Reports in Power BI with Gateway
You are now ready to start creating reports that will utilize this gateway. From the Power BI service, click on the "Downloads" section and choose "Power BI Desktop" and "Save" the file to your machine. You can do this on any machine that is in the same domain as your cube, and has access to the cube.
Once the download completes, go to the location and run the PBIDesktop_x64.msi file. "Accept" the license terms and choose the "Install" location. Once the installation completes, Power BI Desktop will launch. But there is one thing we need to run on in order to query live multidimensional cubes and utilize the gateway. Go to "File" > "Options and Settings" > "Options."
Go to "Preview Features" and check the second checkbox to explore data through a live connection. Click "OK." This will require a restart of Power BI Desktop, so go ahead and close and re-open the application.
Once Power BI Desktop restarts, go "Get Data," and select "Analysis Services." Enter the server name and provide the database. Make sure "Explore Live" is checked. A new window will come up, select the cube you wish to connect to and hit "OK."
You will now get a blank canvas. Create a simple report and save the PBIX file. I saved mine as "Dev Gateway.pbix". You are now ready to publish the report to the Power BI service site. Click "Publish" on the toolbar. A popup will display asking you to login to Power BI. Again, use the credentials of the user you used to sign up. You should receive a success message after the report is published.
Now, if you login to the Power BI service site, you should see your report and dataset there for use. At this point, your data source is connecting live to the cube through the gateway. You can now click on the "Ellipses" next to the dataset, and select "Explore." This will bring up a blank canvas for you to start creating reports through the service site.
Hopefully with the help of this article, your team will be on their way to creating rich reports and dashboards that can be shared across your organization, or within smaller content packs created through the Power BI Service.