Using Excel to analyze data with OData with Business Central – An Example
We get a lot of customers who ask about reporting on information from within their Business Central software. Those who come from older software packages usually think of a report as something that prints on a page, out of a laser printer, in a formatted way. The modern approach to ERP is getting away from that environmentally unfriendly, and frankly less useful form of reporting.
Today people tend to report more often on a screen, whether it be a tablet, phone, or PC. The typical end-user wants to both see their data, but also do some slicing and dicing. Analysis of that information is very important. The term Business Intelligence originally referred to reporting tools that were online, with the ability to filter and analyze in real-time.
If you want to pull data out of Dynamics 365 Business Central for analysis you should be looking at OData. You may be familiar with the old ODBC tools for pulling data out of databases and analyzing them. OData is the web-friendly version of ODBC – and is supported by most of the same tools you used. We usually rely on Excel or a similar OData-aware program when using OData with Business Central.
The most important of those tools is Microsoft Excel.
Here’s a quick primer on using OData with Business Central and Excel to do a little slicing and dicing.
First : Find your data
When you want a report out of Business Central, you probably know where the data you want can be found. As you are using the software, make a note of any page you want to report on. You can use the Ctrl-Alt-F1 key combination while in Dynamics to see the “Page Inspection.” This will give you a Page name and number and type. I ran this for the Item List and see this:
Second: Make a Web Service
Microsoft Dynamics Web Services are the primary way of accessing OData with Business Central. Within Business Central, search for the Web Services page. There are a lot of pre-existing web services, and it’s best to use those if you can. Often the data you want isn’t found in the Web Services. No Problem! Add the page(s) you need to the web services list. Let’s add the Item List. I’ll add a New Web Service on the Web Services page. Select Object Type of Page. Enter (in my case) ID 31 for the Item List. Then I need to name it and publish it (the check box to the right of the Service Name). It looks like this when I’m done:
When I reload the page, the service my_item_list will now have an ODATA URL. Make sure you’re logged into the right company. Right-click and “copy link” if you use Edge. My experience is to use ODATA not ODATA V4 in Excel.
Third: Import into Excel
Now that we have an ODATA link, let’s import it into Excel.
I’ll open a blank worksheet.
In the Ribbon – go to Data, and under the Get and Transform Data select Get Data | From Other Sources | From OData Feed:
Paste in the URL:
You will be prompted for login information. If your administrators have set up your computer so that your windows user is your Business Central user, then select Windows.
Otherwise, you probably want an Organizational Account.
After you have logged in successfully, you’ll see a “Data List” like the one below:
This list of data is directly out of the system and is the key to using OData with Business Central.
Click Load and it will populate your current Worksheet:
The most important feature of this now is that you can refresh the data. Click anywhere inside the Excel table that’s been downloaded. Click the Query ribbon and you will find a Refresh icon.
Finally: Do Some Reporting
From here you can now run Power Query to join this ODATA to other tables, use Pivot Tables to slice and dice, use pivot charts to visualize your data … the sky is the limit with Excel.
The bigger question is – what are some valuable analysis I should do? For instance, I am a big believer in doing some statistical analysis of inventory transaction history (Item ledger entries) to find out what my average demand per period is or my standard deviation of material usage. These are necessary pieces of data for calculating safety stock or lead times. This kind of data analysis is something I’ll cover in a future post.
Need Some Help?
If you need help using OData with Business Central, give us a call at (519) 585-7524 or contact us at Sabre Limited. We are happy to help!
