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 it 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. 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 create 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.
Download components of Power BI integrated into Excel:
1. Power Query
2. Power Pivot
3. Power View
5. Power BI Mobile App (or Mobile BI).
Online Components in Office 365:
6. BI Power for Office 365
7. Power Q&A
8. Data Management and Data Stewardship
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 or contact KTL Solutions if you would like to explore additional Power BI, data mining model, Microsoft GP and CRM solutions.
BARRY CROWELL, MBA | Senior Business Software Consultant and GP Lead
Barry uses his expertise to lead and teach a team of GP consultants to assure our solution scoping, architecting, and delivery meet and exceed the customer’s expectations. He helps clients analyze their current business processes and provides them with process improvements. He has performed implementations as the lead consultant and end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry. He also possesses a bachelor’s degree in accounting and business administration from Black Hills State University and a master’s in business administration from La Salle University.
