How to Connect Dynamics 365 Business Central to Microsoft Power BI

Microsoft Dynamics 365 Business Central – How to Connect Dynamics 365 Business Central to Microsoft Power BI

Hello and welcome to TMC’s how-to demo series for Dynamics 365 Business Central where we explore the basic features of Business Central for tasks within your organization. If you cannot find a demo for your role or work position, let us know in the comments below and we’ll try to make it happen.

Today, we are going to cover how to connect your Dynamics 365 Business Central to Microsoft Power BI. Power BI is a data visualization tool perfect for dashboards and other analysis. My name is John Hoyt, solutions specialist for Technology Management Concepts, let’s get started!

In this video, we’ll cover how to connect your Power BI to the D365 Business Central data. We’ll talk about connection strings and OData. We’ll look at Power Apps, which might be a better alternative for connecting to Business Central. And finally, we’ll compare Power BI reports, which are pre-formatted to a Power BI workspace where you can create your own reports.

Let’s see how this all works. Now I’m going to start by opening up my Power BI desktop.  The desktop for Power BI is where the developer creates the initial connection to the data, builds the report, and then publishes it out to where the Power BI user community can access it via the web. As part of that process, I of course need to get my data, and the way to do that should be OData feeds. When the wizard opens the first question that it asks is, “What’s the URL? What’s the web address for the data that you want to connect to?” Now where am I going to get that OData address? That should come out of Business Central.

So, let me open my Office 365 (known as Microsoft 365) and then we’ll go into Dynamics 365, and that’ll pop open my Business Central application. So now my business central application opens and the quickest thing to do is to just search for the web. I opened the web services list inside Business Central; you can see I’ve got a whole variety of predefined queries, including those designed specifically for Power BI. Something like my customer list or my item sales list. And for each one of those queries that are available (over on the right-hand side) you can see the URLs. And what I should be able to do for something like that customer list is: just copy that location, take it back to Power BI, paste that in place, and say ‘okay’ and connect.

In my experience, I run into this error message more often than not, and I’ll be the first to admit I’m not highly technical. I don’t really understand all of the bits and the ins and outs of how to do this ,connection and so I have been frustrated trying to connect my Power BI desktop editor to the Business Central data in the background. Now, I usually have to go find someone technical on my team and get some of their time and get it done.  And I was just thinking to myself one day, “there has to be a better way to do this, it can’t be this difficult.” And so what I was doing was actually went back into my Office 365 home page and I pulled up Power BI, and when Power BI came up from Office 365 on the web client, I noticed there was a link for apps. I clicked on the link for apps, and it showed me where I could go to App Source and get apps specifically designed to connect my business central data to Power BI. I’ve got three of them installed already. They’re going to take a look at things like sales information; they’re going to look at finance information; they’re going to look at customer or CRM related information.

For each one of these apps, all I have to do is connect it to my data. I do that simply by telling the system my Office 365 authentication: my email address and my password.  Then all the authentication was done for me, and now I get access to my sales information directly out of my Business Central system. That CRM piece, I can scroll back and forth through it then I can drill down into the reports. Power BI takes over at this point and now for my ‘Opportunity dashboard,’ I can run a variety of filters if they’re available. I can hide that pain, add my comments, bookmark it, etc: all the things that I would expect to be able to do out of Power BI. But I didn’t have to do any of the data string connection in order to make that work. I’m able to minimize or eliminate the use of these OData strings and go to the desktop editor, instead, as I was saying, go directly into Power BI. Now this one’s looking at my ‘Opportunity information,’ I open our BI  again. I can go back to those apps and I have a finance app. Here’s all my finance information with a variety of predefined views or dashboards. I can look at the financial dashboard, net changes even things like mini trial balance are available.

If I look at the sales information, again, item sales dashboard; all the type of information I’d be looking for with an easy connection just by going through AppSource. The sales snapshot here at the bottom of the list: this is kind of a preview of what the application will appear if I’m looking at it from my smartphone or something of that type. And then what I end up with of course on my workspaces where I can go in and actually build reports connected to the different data sources in the background. Here’s my Business Central for Finance; now this is the same app connection, but now it allows me to look at reports that are available the data set itself. Any data flows that might be associated with it.

So I found that that looking inside the AppSource to try to find those and, again, here’s  AppSource. All I’m going to do is search for ‘Business Central’ and you can see the three Microsoft created apps that I’ve installed, as well as some third-party apps that have been created for the exact same purpose. Let’s just minimize or eliminate the challenges in connecting Power BI to the Business Central data; do that instead with something that’s based out of the AppSource where what you’re going to need to provide is simply the email address and password you use as an Office 365 user.

So in this video, we were able to:

1. Connect to our Dynamics 365 Business Central data.
2. We looked at the dashboards that are available from the Power BI apps
3. And we compared the reports in the workspace’s pre-formatted versus the ability to go in and create your own piece.

That wraps up this video if you have any questions or would like to make a suggestion on what we should cover in the next video, please comment down below. I’ll do my best to answer your comments, and if you need any immediate technical support, I invite you to visit our website https://abouttmc.com/. Also don’t forget to like this video and please subscribe to our channel. Thank you for watching and please follow the social media accounts that are in the description below.

Try Dynamics 365 Business Central today