I recently started playing around with Office 365 and PowerBI. Now that you can connect your organizations on premise data with Office 365, Office 365 becomes a much more viable solution for delivering Business Intelligence. I did meet my fair share of roadblocks when trying to figure out how to set up a connection to my on prem data though, so I figured I’d write it all down to save others (or myself) some time in the future. It’s worth mentioning that you have to have PowerBI enabled in order to get all of this to work.
The examples below were created using a dev environment and the AdventureWorks DW2012 db, so you’ll need to alter some of the connection info to suit your particular scenario. At the end of the tutorial we’ll have a PowerPivot workbook hosted in Office 365 that is configured to refresh data from our on premise server. Cool eh? So let’s get started.
Connecting Office 365 to your on prem data can be boiled down to 4 main tasks:
- Creating and configuring a “Management Gateway” in Office 365 and your computer.
- Creating a data source in Office 365.
- Create a PowerPivot workbook that’s connected to your on prem data.
- Scheduling/testing the refresh.
Creating a “Management Gateway” in Office 365
- In office 365 go to the admin dropdown (located in the upper right hand corner) then go to “PowerBI”.
- Click on “gateways”.
- Click the “+” next to “new gateway”
- Fill out the name and description section of the new gateway. In this example we will not be using the “cloud credential store”, so ensure that the enable box for that option is not checked.
- Click the “Create” button.
- If you haven’t done so previously, use the download button to download and install the gateway software.
The gateway software should be installed on a machine that has access to the data you’re planning to import into Office 365. It should also be noted that the machine you install the gateway software on will need to be running/on in order for any scheduled refreshes to actually work.
- Highlight and copy the “Gateway Key” (we’re going to use it in just a minute)
- Open the Gateway software once it’s installed.
- Click the “Start Service” button if it’s not already running.
- Click the “register gateway” button and paste in the gateway key you copied in step 7
- Click finish on the “install & register” screen in your browser.
After completing the steps above your Power Bi admin center, and Microsoft Data management Gateway Configuration Manager should look like the screens below if everything has gone well.
Creating a data source in Office 365
Now that we’ve configured our gateway, we need to create a data source to the database our workbooks will use to refresh data. Follow these steps:
- Once again in office 365 go to the admin dropdown (located in the upper right hand corner) then go to “PowerBI”.
- Click on the “data sources” link on the left hand side.
- Click the “+” next to “new data source” and select “SQL Server” from the resulting menu.
At the time I originally did this, Power Query didn’t actually work for data refresh. You can see my forum question and resolution on it here: http://social.technet.microsoft.com/Forums/en-US/3fe3ac74-5954-475b-8849-6602573522e7/power-querydata-management-gateway-data-refresh-not-working?forum=powerbiforoffice365#bcc8af38-22da-43aa-b378-7757196f2829
- Ensure “Enable Cloud Access” is enabled and then click next.
- Name your data source.
- Select the gateway you wish to use (the one we just created in the previous section).
- Select a Data Source Type of “Sql Server”.
- For “Connect Using” select “Connection String”. (at the time of this writing, I was told this was the only way it currently worked, but that other options should be resolved shortly)
- For Connection provider select “.NET Framework Data Provider for OLE DB”.
- Paste the exact same connection string used in your PowerPivot workbook into the Connection String window. (this needs to be the exact same string, so you should open up your workbook, open up the connection, and copy the string. If you don’t have a workbook yet, you might want to skip to the section about creating a workbook (ie the next section) and then come back to this step)
- Click the Credentials button at the bottom of the screen.
- Select the credentials type of your choosing (I used windows creds)
- Enter the user name and pwd of the user you want to use to connect to your data source when the workbook refreshes.
- Click test connection. Once complete your window should look something similar to the image below
- Click OK on the data source settings window
- After entering your credentials, click next on the connection info screen. (See completed screen below)
- On the “Users and Groups” screen, add any users or groups who you want to have access to use the data source. (add users by using the “+” button)
- Click Finish.
Once finished, your data sources screen should look similar to the screenshot below:
Create a PowerPivot workbook that’s connected to your on prem data.
Now that we’ve got our gateway and data source ready to roll, it’s time to create a workbook that we can upload and schedule to refresh. So…to do that:
- Open up Excel 2013 and create a blank workbook
- Click on the PowerPivot tab in the ribbon
- Click the Manage button on the PowerPivot Ribbon
- In the PowerPivot management window select “From Database”, then “From SQL Server”.
- Enter the “Server name” and “Database name” into the resulting wizard (the same server, db, etc that you normally use to connect to on prem data in your organization).
- Click the “Advanced Button”.
- On the advanced screen, in the section labeled “Connection string”…this is the exact connection string you should have entered when creating your data source in Office 365. If this connection string and the connection string in your data source in Office 365 do not match exactly, your refresh will not work.
- Click OK on the Advanced Screen.
- Select “Select from a list of tables and views to choose the data to import”.
- Click Next on the Table Import Wizard screen.
- Select the tables you want to import by placing a check in their box. (in my case I am only selecting the DimProductCategory table from the AdventureWorksDW2012 example database)
- Click Finish
- Once the data has imported click close on the Table Import Wizard screen.
- While still on the PowerPivot Management window select “PivotTable” and then “PivotTable” from the resulting dropdown men
- Select “Existing Worksheet” and then click OK to finish creating the pivot table.
- Select “EnglishProductCategoryName” from the PivotTable Fields on the right.
- Save the workbook to your desktop as “O365RefreshTest” the completed workbook should look similar to the image below
- Navigate to your Office 365 environment
- Navigate to site contents within the site
- Select the “Power BI” app
- In the Documents section of the Power BI app, click Add, then “upload workbook” from the resulting menu.
Congrats your test workbook should now show up in your Power BI app like so:
Scheduling/testing the refresh
All that’s left to do now is configure our data refresh! Follow these steps:
- Navigate to your Power BI Application.
- Click the ellipsis in the lower right hand corner of your workbook and select “Schedule Data Refresh”.
- Click the toggle switch for “Refresh Schedule” to turn it on.
- Select the data connection that you want to refresh in the workbook (remember, this connection must have the exact same connection string as the data source you configured in Office 365)
- Configure when you want the data refresh to happen using the menus available.
- Enter an email address for your failure notification.
- Click “save and refresh report”.
Once completed your screen should looks similar to the following
To view the progress of the refresh click on the “History” link at the top of the page. As you can see, my refresh is still running (it usually takes a minute or so, depending on the amount of data)
Once completed, you should see a row show up under “refresh history” with a status of success or failure like so:
Now your workbook should continue to refresh on whatever schedule you set. Mighty powerful stuff if you’re looking to do BI with on prem data using O365. Hopefully this was useful, let me know in the comments.