Access the OData Service via Microsoft Power BI
Using Microsoft Power BI to access the data provided by the OData service is quick and easy. Microsoft Power BI is a great tool for creating reports and statistics.
For more details on how to work with Microsoft Power BI please refer to: https://powerbi.microsoft.com/en-us/support/: new window
To add the OData service to Microsoft Power BI, proceed as follows:
- On the “Home” tab, click the “Get data” button, and then click “OData feed” as data source.
- Enter the URL of the OData service (see chapter “Creating an OData Service”) in the URL field and click “OK”.
Note: The “Basic” connection option is sufficient.
- Since OData usage requires authentication you need to select the “Basic” authentication option. Enter your e-mail address for the cloud log-in as the user name and the generated Password for Applications as the password (see chapter “Creating a Password for Applications”). Click “Connect”.
- After successful authentication, you can select from several display options in the “Navigator”. Select your desired options and click the “Load” button.
Aggregates, Enumerations and Lists
When you create an OData service, it is very likely that some of the required properties have aggregates or lists as data types.
Aggregates are displayed as “Record” in Microsoft Power BI and lists are displayed as “List”. In the “Transform Data” dialog you can see these aggregates and lists.
In order to make them useable, click on the “Expand” tool at the end of the column (see red box on the column in the screenshot) to choose an expanding option. Such an option is e.g. creating a new table; however, this requires additional work as relationships between the tables need to be set.
The easiest way for aggregates (or “Records” in Microsoft Power BI terms) is to simply expand the columns: new columns are inserted for the chosen fields inside the aggregate. You can use it after clicking “Close & Apply”.
As for lists, the approach chosen depends on the expected number of entries. If you can predict having only a couple of entries, expanding the list is an option. In this case, the other values are duplicated. If there are many entries, creating a new table based on the list may be a better solution.