3 Things to Know Before Starting a Power BI and Microsoft Dynamics 365 Business Central Project

The Microsoft Power Platform (comprised of Power BI, Power Apps and Power Automate) is an innovative and powerful tool for the Microsoft Dynamics and Dynamics 365 communities. One of its hallmarks is how easy it is to use in comparison to other development tools. However, if you haven’t used the tools before, having some insight always helps! Here are some tips for using Power BI (the business intelligence tool in Microsoft Power Platform) and Microsoft Dynamics 365 Business Central.

Using Power BI and Microsoft Dynamics 365 Business Central

You can access data from anywhere with an internet connection

If this is the first time you are using Power BI with Microsoft Dynamics 365 Business Central, it is important to note that the data source in Business Central is different from an on-premises database like SQL server. The primary difference, and it’s a good one, is that you can access the data anywhere you have Internet access! You are not tied to an on-site server requiring direct access. This also means you do not need a gateway installed on a server workspace for the schedule refreshes. See, it’s already easier!

All the data you need may not be available via the web service

Another difference to note is that not all data in Business Central is exposed, and only data that is exposed via the web services can be pulled into Power BI. What does this mean for you? It is a safe bet that you have added custom tables and fields along with using the default tables and fields inside your instance of Business Central (after all, customization is one of the benefits of Business Central). However, the custom tables and fields may not be exposed via the web services.

It’s easy to gain access to all your data in D365 BC with custom queries

So now you know that to make the most of your Power BI project using all the available data, you will need to make changes to the Business Central environment. The question is what is the best way to expose all the data?

One way is to have a developer build out the custom tables and then expose each one. After each one is exposed, you can bring them into Power BI, and then start transforming and merging the newly exposed table. Sounds like a lot of work, right? It is also a lot of work for your computer to process each time you want to refresh those reports locally.

A better option is to build custom queries and then expose those queries to webservices. Building the custom query in Business Central and just “calling” that query pushes all the transformation computation back to the Cloud – not to your local query engine. The save on resources is not the only benefit of going the custom query route. As most Business Central users will tell you, it is much easier to build and edit queries than it is custom tables. So, there is no need to get developers involved because the user can build and maintain the new queries.

One more benefit goes back to the “ease of use” built into Power BI. Once you have written your query in Business Central, you only need to call that one query and then start building your visuals. You do not have to do any transformations or joins in Power BI query editor because you already did it in Business Central. It eliminates redundancy, so you save time and get better results.