Connecting with Excel through OData


The Data Federation service enables the reading of data from data sources through the OData interface in Excel and Power BI.


This section shows how to connect data sources in Excel through the OData interface.

Prerequisites

  • You have installed MS Excel.

  • You have created a data federation channel and authorized an application to access data through the channel.

Configuration Steps

  1. From the channel list on the Channel Manager page, find the target channel, select … > Get URL, and copy the OData URL of the channel.

    ../../_images/copy_channel_url.png


  2. Run Excel on your workstation, open a white sheet, and select Data > Get Data > From Other Sources > From OData Feed from the menu.

    ../../_images/excel_odata.png


  3. In the pop-up window, select Basic, enter the OData URL that was copied in step 1 in the URL field, and click OK.

    ../../_images/enter_odata_url.png


  4. In the OData feed window, select Basic, and complete the following.

    • User name: Enter the access key of the application that is authorized by the data federation channel.

    • Password: Enter the secret key of the application. For information about getting the application access key and secret key, see Registering and Managing Applications.

    • Select which level to apply these settings to: Select the complete URL level, for example: {domain_name}/data-query-proxy/channels/read/{channel_id}/odata.svc.


    ../../_images/excel_odata_setting.png


  5. Click Connect, and the metadata of the data source will be displayed. You can then choose to load data from the data source to Excel or transform the data as needed.

    ../../_images/excel_odata_result.png