Using Business Central OData in Power BI

December 28, 2023
|In Business Central, Power BI
|By Bond Consulting Services

An open book. Polygonal construction of lines and points. Blue background.

By John Ellis, Pre-Sales Technical Consultant

Although Microsoft Power BI can pull Business Central data from Excel files or Power BI’s “Dynamics 365 Business Central” data source, Microsoft recommends using OData queries.

OData is Open Data Protocol and, according to Microsoft, is an ISO/IEC approved and OASIS standard that defines best practices for building and consuming REST APIs.

A REST API is an interface that business applications use to transfer data over the internet securely.

By pulling data through an OData query, the Power BI report will be forever refreshable.

Many OData queries come “pre-aggregated”, meaning that there is less of a need to create formulas or conduct any other development within Power BI.  In other words, much of the work is already done for you.

Not only does this method of data extraction lend credence to the “work smarter not harder” principle, but there is little doubt of the data that is displayed in Power BI.

Finding OData queries in Business Central is extremely easy.  Begin by clicking Business Central’s search button in the upper right of the home screen and typing “web services”:

A snapshot of the Business Central window search bar with highlighted text reading "web services". below that search bar is a heading "Go to Pages and Tasks" followed by a dropdown list of Web Services

Since profit represents an excellent example of business reporting, type “profit” in the Web Services search field in the upper left.  Highlight the query for Item Sales and Profit and click the OData URL found on the right:

search results of "profit" appear with Object type, object ID, object name, service name fields and two entries titled "Page" and "Query"

*Simply cancel out of any additional browser tabs that may pop up during this step.

Next, click on and copy the OData URL appearing in the browser:

an internet browser page is opened with highlighted text in the URL searchbar. This is the URL Odata that needs to be copied and pasted.

Now we will paste the URL into Power BI. First, navigate to Get Data, then OData feed.

A screenshot of the Power BI app. On the Homepage the dropdown for "Get Data" is down and displays numerous sources of data with one highlighted by a red box reading "OData feed"

Paste the URL into the “OData feed” window, and click OK:

Screenshot of OData feed window within Power BI app. Two options are displayed "basic" and "advanced" with the circle next to the Basic option selected. A URL text box displays the earlier copied URL with two options at the bottom right, "OK" and "cancel".

As shown in previously published Power BI blogs, you can add Visualizations to the Power BI canvas such as the Table and Slicer. And immediately, valuable intelligence is displayed.

OData provides many functionalities, without having to perform major development efforts in Power BI!

If you’d like to learn more about the different functionality and how it may be right for your business, you can reach out to sales@bondconsultingservices.com, watch detailed walkthroughs on our YouTube, or schedule a consultation.