You can access OData feeds exposed in the Power BI for Office 365 Admin Center using Power Query for Excel using Microsoft Online Services ID authentication or Windows authentication.
Note The Power Query client must be located in the same corpnet with the machine hosting the Data Management Gateway; otherwise, the Power Query client cannot gain access to the data included in the OData feed.
After an administrator enables the OData feed for an on-premises data source, the administrator can see the URL to the OData feed in the Admin Center. The administrator can provide the URL to an user, who can access the OData feed from Power Query for Excel using Microsoft Online Services ID or Windows authentication (only if this option is enabled separately).
When you enable the feed, the metadata for the feed is automatically indexed in the Data Catalog, a component of Self Service Information Management (SSIM) capability of Power BI for Office 365. Users can search for and discover the feed by using the Online Search feature of Power Query for Excel.
The following sections describe both ways – using the URL and Online Search from Power Query for Excel – to consume the OData feed exposed by the on-premises data source.
Important Download Microsoft Power Query for Excel from here if you do not have it already installed on your computer or you do not see the Organization section on the POWERQUERY ribbon in Excel, which was not available in the earlier version of Power Query.
Using URL to access the OData feed
As an IT Administrator, you may want to test accessing the URL before handing it over to the data steward. The following procedures provide you instructions for obtaining the URL to OData feed and then using the URL to access the feed.
Obtain URL for the OData Feed from Power BI Admin Center
- In the Power BI Admin Center, click data sources on the menu on left.
- Click … (ellipsis) next to the data source to see a popup window.
- Copy the OData Feed URL to the clipboard by selecting entire text and pressing Ctrl + C or right-clicking and clicking Copy.
This URL is the cloud-based URL that uses the HTTPS protocol that will get resolved to a HTTP/HTTPS based on-premises URL. The advantage of this approach is that regardless of how the OData feed is configured to access (HTTP/HTTPS), user uses the same URL.
For example: https://<domain name>.hybridproxy.clouddatahub.net/ODataService/v1.0/TestDB gets resolved to http://<machine name>.<on-prem domain name>.corp.microsoft.com:8000/TestDB or https://<machine name>.<on-prem domain name>.corp.microsoft.com:8000/TestDB.
Access the OData feed from Power Query for Excel
Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery and access. If you do not have Power Query installed on your computer, install it from here .
To access OData feed from Power Query:
- Launch Excel and switch to the POWER QUERY tab.
- Click From Other Sources on the ribbon, and click From OData Feed.
- In the OData Feed dialog box, Paste the URL you copied to the clipboard earlier and click OK.
- Select Microsoft Online Services ID or Windows for authentication. If you select Microsoft Online Services ID and you are not currently signed into Microsoft Online Services, you will need to sign in using the account that has access to the OData feed. You will be access the feed using Windows authentication only if the access using the authentication is enabled by an administrator using the Power BI Admin Center.
Note When an administrator enables an OData feed for a data source, by default, only Microsoft Online Services ID authentication is supported for clients to access the feed. The administrator uses the general page of the settings tab in the Admin Center to enable accessing OData feeds using Windows authentication. See Configuration of General and Notification Settings for more details.
- In the Navigator pane, you should see the feeds exposed by the data source.
- Select a data set from the feed and click Done.
Search for and discover feeds in Power Query for Excel
The following procedure describes how to search for, discover, and consume OData feeds in Power Query for Excel. When the OData feed is enabled for a data source, the metadata for the feed is indexed in the Data Catalog in the cloud. Therefore, user can search for the feed by using the keywords from the following:
- Name of the data source in the Admin Center
- Description of the data source in the Admin Center
- Names of tables and views
- Names of columns in tables
- Publisher of the OData feed
If you switch to the system health tab on the Admin Center and then switch to the logs page, you should see a message indicating the status of metadata indexing operation. Wait until you see a successful message from the metadata indexing operation in the log.
- Launch Excel and switch to the POWER QUERY tab.
- Click Online Search on the ribbon. You should see the Online Search window with a text box to type the search keywords.
- Enter the keyword to search for the feed. Set the scope to Organization using the drop-down list next to the search button. Press ENTER or click Search button. You can use full or partial names of tables or columns of the original table from the on-premises database to search. For example if the table name is SPCustomer and the column names are SPCustomerID and SPCustomerName, you could search with the keywords: SPCustomer, SPCustomerID, SPCustomerName, SP, SPC, SPCu etc…
- Hover the mouse over the result to preview the data in the feed.
- Click the result to start importing the data from the OData feed into Excel.
- In the Access an OData Feed dialog box, select Microsoft Online Services ID authentication. Follow the instructions to login using the organizational ID that has access to the feed.