Part 5 of a 5 part series
We have put a lot of hard work into this blog series with building a patron analysis so now it’s time to share the wealth of our analysis insights with the rest of the company. Power BI’s web site and mobile approach with the deployment of Office 365 and refreshable data through a Data Management Gateway is the perfect solution. The Data Management Gateway has come a long way since the first version and with version 1.2 it now supports many data source types from Power Query connections.
One of the best parts of the Data Management Gateway is that is makes the connection outside the firewall. That means, no reverse proxies, custom web services or firewalls to deal with.
First, I setup a trial of Power BI at http://powerbi.com. After the provisioning was complete, my Office 365 site has a newly created asset called Power BI.
Creating a Data Management Gateway
Open the admin center and created a new Gateway. A Gateway requires simply a name and description. After giving it a title and description it will generate a new key for you. Save this key as it is the key needed to connect your on premises client to the gateway. If you lose the key you can always regenerate a new key. Also, from here you can download the Data Management Gateway client.
Next, you will use this key to connect to the gateway. Launch your Data Management Gateway client on your on-prem server and select register a new gateway.
Select the endpoint you want the Gateway to use for access
Now we have a connected gateway and we can control the service from this screen.
Creating a Data Source on your Office 365 site
Next, we need to create a data source. Navigate to the Power BI admin center, use the Admin->Power BI link at the top of your Power BI site. Select data source on Power BI admin center site. Click on the “+” sign and select SQL Server as a data source.
Select “Enable Cloud Access” on the data source usage screen.
Select “Next” and then a connection name and connection string.
· The data source name is case sensitive and should match exactly what’s in the Excel workbook.
After you have entered your connection string, select the “Set Credentials” button.
Enter your credentials and test the connection
Next, I clicked on credentials. At this point it will launch a new application and ask for your credentials.
Now my connection is now complete.
Setting up your Microsoft Power BI site for Office 365
From your Office 365 site, click Sites in the upper right corner of the dashboard page.
In the new site page, click the Team Site tile.
In the left navigation pane, click Site Contents.
In the Site Contents page, click the Power BI tile.
In the Power BI page, drag your excel workbook to your site.
To start your own Free Trial of Power BI for Office 365 on this site: http://www.microsoft.com/en-us/powerbi/default.aspx#fbid=v0V8xzuZXJR
As part of the Power BI trial you’ll also receive a free 30 day trial of Office 365 Pro Plus giving you access to the latest version of Excel. Once you have Excel installed, download and install the Power Query and Power Map add-ins for Excel and learn about the new features with the getting started guide. Until my next post have fun sharing the wealth!