After you create a gateway in the Admin Center, you can register on-premises data sources with the Admin Center. You must create at least one gateway before creating a data source in the Admin Center. See Install, Configure, and Register Data Management Gateway for the detailed steps for creating a gateway in the Admin Center. If you try to create a data source without a gateway, you will be redirected to the gateway creation process.
In this scenario, as an IT Administrator, you expose data from an on-premises data source as an OData feed so that it can be consumed from Power Query Add-in for Excel. To expose data from an on-premises data source as an OData feed, you need to perform the following steps:
- Register the on-premises data source with the Power BI Admin Center.
- Enable OData feed for the data source.
- Select tables and views to be included in the OData feed.
- Grant users the access to the feed.
Note For a list of supported data sources and data types, see Supported Data Sources and Data Types.
The following procedure provides steps to create a data source in the Admin Center and enable OData feed for the data source.
Important Maximum number of data sources allowed per tenant is 1000.
To create and enable OData feed for a data source
The following procedure provides detailed steps to expose data from an on-premises data source as an OData feed:
- In the Power BI Admin Center, switch to the data sources tab. See Introduction to Power BI Admin Center for different ways to access the Admin Center.
- Click + new data source on the toolbar. If you haven’t created at least one gateway before this step, you will be redirected to the new gateway page. See Install, Configure, and Register Data Management Gateway for instructions on creating, installing, and configuring a gateway.
- On the data source usage page, select Enable OData feed option, click Next.
You can enable both the cloud access and OData feed for a data source. You must choose at least one of these options to create a data source.
- On the connection info page, perform the following:
- Specify a name and description for the data source. Note that you cannot change the name after you save the data source.
- Select the gateway you created earlier and that you want to associate with this data source from the drop-down list for Gateway. This gateway is used to access the on-premises data source you will be specifying later. You cannot change the gateway after the data source is created.
- Select a data source (for example: SQL Server) for Data Source Type. Oracle data source is not supported yet, so ignore that option.
- Select Connection String option, if you want to select a provider and type the connection string in the text box (or) select Connection Properties option if you want to select a provider and specify the server name, database name (for SQL Server only), and authentication type (for SQL Server only).
- Click Credentials button. This will install the ClickOnce application when you click for the first time. You should see the Data Source Management dialog box. If you run into any issues with using this dialog box, see Troubleshooting issues with using the Data Source Manager.
- In the Data Source Manager dialog box, enter user name and password that you want the client gateway to use to access the data source. Important: These credentials are used by the data management gateway running on the on-premises computer to connect to the on-premises data source.
- Click Test Connection to test the connection. If the testing is successful, OK button is enabled.
- Click OK to save the credential settings for the data source.
- Click Next button on the connection info page.
- On the data settings page, do the following:
- Ignore the Index the feed option and associated settings for now. This feature is not supported yet.
- Select tables/views that you want to be included in the OData feed by using the check boxes. You can choose to include all tables and/or all views to be included in the feed. The table/view must have a primary key or at least one non-nullable column. The data types of all the columns of the table/view must be supported by OData. See the supported data types section at the end of this topic. You will not be able to select a table/view that does not meet these requirements to be included in the OData feed.
Note If credential were not set in previous step using the Data Source Manager, tables and views from the data source will not be displayed. Switch back to the connection info page and specify credential for accessing the data source.
- Click Next to switch to the users and groups page.
- On the users and groups page, click + (plus) on the toolbar to add Organization IDs for users or groups who can access the OData feed.
- Click Finish on the users and groups page to get back to the data sources page.
- Confirm the status of the data source is Ready in the data source list. Click … (ellipsis) next to the data source you created to see some details about the data source and to edit/delete the data source. When you click … (ellipsis) next to the data source in the list, you should see that the value of OData Feed is set to Enabled and an URL for the data feed is displayed.
A user can use this URL from Power Query for Excel to connect to the OData feed using Microsoft Online Services ID or Windows authentication. When you enable 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.
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 in Power Query search window 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. See Access OData Feeds from Power Query Add-in for Excel for details about accessing OData feeds from Power Query.