NOTE: Information in this post is valid as of 12/18/2014 and is subject to change in the future.
Data Sources within Power BI are used to define an on-premise data source within the Admin Center. Data Sources rely on a defined Gateway. You can have multiple data sources defined for a single gateway. Power BI will match the connection information in the Excel Workbook with the connection information defined in the data source to find a match.
Go to the Power BI Admin Center
To setup your gateway, you will need to access the Power BI Admin Center. In order to do that you’ll need to be setup in the Power BI Admin Role. You may see an Admin drop down on the O365 Bar which will have Power BI in it. If you don’t, that means you aren’t a Global Admin. You still have some options to get to the Power BI Admin Center.
If you are at Power BI Sites, you can click on the COG in the O365 Bar and click on Power BI Admin Center.
Or, you can just browse to https://admin.powerbi.com.
Create the Data Source
Within the Power BI Admin Center, you’ll want to click on the Data Sources section on the left side. This will show you a list of the existing data sources, and if you click on the + icon, it will create a new data source.
Clicking on the + will give you some options to pick from.
The first three are pretty obvious what they are for. The Power Query option could potentially be confusing. If you connected to SQL Server from Power Query, you would pick the Power Query option, not the SQL Server option. This is true for any data connection you make via Power Query.
SQL Server Data Source
When we select SQL Server, we are then presented with a few steps to go through. The first step shows some check boxes. Enable Cloud Access allows this data source to be usable via SharePoint Online for items like data refresh. Searchable in Power Query allows this data source to be discovered via Online Search when searching Organization items. Enable OData Feed allows you to pull data from the data source in an OData format. This is what the HTTP/HTTPS configuration in the Gateway was for.
For the OData feed, this doesn’t actually allow the OData Feed to be used straight from the internet. The URL that gets created will point to your tenant, but that will redirect you to the Gateway. You will need to have network connectivity to the Gateway. This may require you to be VPN’d in if you are remote.
Clicking next will take us to the Connection Info screen. This allows us to provide a name. This name has to be unique. We can add a description. We can defined if we want to assign this Gateway to an Administrative Group. These groups are defined with Power BI Admin and allow you to section off Gateways and Data Sources to certain people. The Global Admins within Power BI Admin can work with everything. We will then select the Gateway that we want to bind this Data Source to. I already had one defined as Gateway101.
We will then select the Data Source Type of SQL Server. We can then either provide the raw connection string, or select from the connection properties. You will have to be sure about how the connection is defined within the Excel workbook as we will use the connection information in the workbook to match with the data source. That is how Power BI determines which Data Source to use.
The biggest part of confusion here is which Connection Provider to use. If you expand that drop down, you will see four options.
The provider, by default, depends on how you pulled data into the Excel Workbook. You have three options: the Excel Data Tab, Power Query or Power Pivot. Here are the providers they will use by default within Office 2013.
- Power Query – .NET Framework Data Provider for SQL Server (SqlClient)
- Power Pivot – SQL Server Native Client 11 (SQLNCLI11 – SNAC)
- Excel Data Tab – Microsoft OLE DB Provider for SQL Server (SQLOLEDB)
We can look within the Excel workbook to see what the connection properties look like there. Go to the Data Tab and click Connections.
We can see 4 connections listed here. We’ll look at these by selecting the connection and clicking Properties.
Excel Data Tab
The first connection is from the Excel Data Tab. Clicking on Properties and going to the Definition tab, we can see the connection string. From here we can see that it is using the SQLOLEDB Provider.
Power Query
Power Query is the second item. All Power Query connections will look this way. We can see that the Provider here is Microsoft.Mashup.OleDb.1.
Power Pivot
The third one is Power Pivot. This we can see that the provider is SQLNCLI11 which is the SQL Server Native Client from SQL 2012.
We want to get our OLEDB connection for the Excel Data tab setup. So, we’ll fill out the rest of the information for the server. Then the Set Credentials button will enable.
This will launch a ClickOnce app for you to set the credentials to connect to the Server from the Gateway. This has to be run on the server that is hosting the Gateway.
Privacy Level is used for Power Query Connections and will preview usage at some level. I’m just picking Organizational. Once that is done, you can hit test connection. Then hit ok. Then hit next to go to step 3.
This takes us to selecting the Tables/Views for the OData Feed that will be exposed for this Data Source. You may encounter some issues with regards to what can be shared with OData based on what is supported for Data Types. There is a link below that goes to the supported Data Types. After you select what you want, click next.
Then you can define the users and groups. This defines who can search for the OData feed in Power Query’s Online Search. After that’s done, click next.
Then you get to the last step which is the management permissions step. This allows other admins to view and modify usage access. Then click finish.
We will then see it added to the Data Source list.
Power Query
A Power Query data source is a little different. You will be presented with a form that asks for the Connection String.
You can get the connection string from the Connections area in the Excel Data Tab like it was pictured above. Just copy the whole connection string and paste it into this box and click next.
Step 2 is to provide information about the data source. You can give it a name, a description, pick a Gateway and set credential like we did for the SQL Data Source. Then hit save, and next.
Users and groups and management are the same as they were with the SQL Data source. You will then go back to the main list where we should see two data sources present and ready to be used for refreshing data.
Diagnostics
If you encounter an issue, there are two spots you can have a look at to gain more information. This would be the System Health within Power BI Admin. You will also see information from errors when you are doing the Interactive Refresh in the Excel Web App, or in the status of a scheduled data refresh in Power BI Sites.
System Health
The System Health area is the first option within the Power BI Admin Center. This will display information related to the Gateway or Data Sources that you have configured. If an error has occurred, you should check here first.
Additional Resources
Power BI for Office 365 Admin Center Help
Create a Data Source and Enable OData Feed in Power BI Admin Center
Adam W. Saxton | Microsoft SQL Server Escalation Serviceshttps://twitter.com/awsaxton