As a business intelligence developer at KUMAVISION AG, one of the world’s largest implementation partners for Microsoft Dynamics, I am so excited about the seamless integration of Power Bi in Dynamics 365 Business Central.
You can simply use the Power BI Business Central Connector to load your business data into Power BI and create impressive reports from it. The finished reports can then be seamlessly and interactively integrated into Business Central.
In this way, the knowledge gained from reporting can be used directly in the operative business.
In this blog post I will show you how easy it is to implement this. To import your Business Central data into Power BI, you must have permissions to the web services used to retrieve data. An easy way to find the web services is to search for Web Services in Business Central. On the Web Services page you can publish queries or pages for loading data. Business Central already provides a set of web services here.
In Power BI Desktop, choose Get Data. On the Get Data page, choose Online Services, Dynamics 365 Business Central and then click on Connect. Sign in with the same account you used to sign in to Business Central.
The Power BI Navigator shows a list of Microsoft Business Central environments, companies, and data sources. These data sources represent all the web services that you have published from Business Central. In this post we choose the data source Cust_LedgerEntries and click on Transform Data.
In the Power Query Editor we choose the relevant columns and load the data via Close & Apply.
For a proper scaling of the report in the available space by the Power BI Fact Box control, the size of the report must be set to 325 pixels by 310 pixels. To define the size of the report, place focus outside the report layout area, and then choose the paint roller icon. You can change the width and height of the report by choosing Custom in the Type field. For a suitable color representation, we import my Dynamics 365 Business Central inspired theme from the Power BI Theme Gallery.
The report is required to have a basic report filter to filter correctly in the Power BI Fact Box Control. The filter that is passed to the Power BI report from each list page will be based on the primary key. In most cases, the primary key for a list is the No. field. To define a filter for the report, select the primary key from the list of available fields, and then drag and drop that field into the Report Filter section.
For the report we create a measure Invoice Amount to show only the invoice amounts.
We also create a running total for the amount using theQuick Measure Assistant to display the Balance Due.
To display the active customer filter, we display the Customer_Name via a Multi-row card.
The sales invoice activity is displayed on a Line and stacked column chart.
Bring the Posting_Date to the Shared axis, the Measure Invoice Amount in the Column values field and the Measure Amount running total in Posting_Date in the Line values field.
Now we adjust the names and the hierarchy level of the shared axis.
Then we publish the report in the Power BI Service.
Back in Business Central, we activate the Power BI Fact Box Control on the Customer List via More Options: Actions > Display > Show / Hide Power BI Reports. With Select Report we Enable the report just published.
Finish! Now you can consume and filter the Power BI report directly in Business Central without leaving the application.
How cool is that? #BetterTogether
Looks like all this is assuming your Business Central solution is hosted by Microsoft?
We host our customers Business Central solution in our own hosting center. And I have yet to find a way to publish the Odata service in a way that can be used in both the Power BI Desktop app AND app.powerbi.com
In the desktop app, I can only get Windows authentication to work.
In app.powerbi.com you can only choose "basic" authentication - but that I can't get to work in the Desktop app.
Hi @rlposselt !
Yes, if everything is hosted by Microsoft it is the easiest way.
But it also works with self-hosted Business Central solutions.
The first question is which connector you use.
To the best of my knowledge, the Business Central Connector only supports user\password (basic) authentication.
But if you use the standard ODATA connector, Windows authentication should be supported as well.
1. We have to login in Microsoft Dynamics 365 CRM
2. At the main menu, we have to select the “Settings” option.
3. Then, at the “customization” menu, click on the “customizations” option.
4. We will visualize a new tab. Then we click on “Developer Resources”.
5. At Developer Resources, we have to search and select “instance Web API”. In this blank, we must paste the URL that we can find at “Service Root URL”
How to do it from Power BI Desktop
1. First, click the Home button, then, click on the “Get Data ” option.
2. This option opens a new window. There we select “Online Services” and choose the option “Dynamics 365 (Online)”. Finally, click on the “Connect” button.
3. Once we are connected to the service, a new window called “Dynamics 365 (online)” will appear. Then, we have to select “Basic” and paste the Microsoft Dynamics 365 URL (copy the ULR from the “Web API URL”) and click on OK.
4. If we are doing this process for the first time, we will visualize a new window. We have to select the authentication method at the “OData feed” window.
• In this scenario, we will choose the “Organizational account” as a predeterminate authentication method.
• We paste the URL previously supplied and we click on the button “Sign in”
• Then we write the user and password that we use to access Microsoft Dynamics 365. After logging in, Power BI will show us the “OData feed” window. If we don’t have to make any modification there, we click on “Connect”.
5. The connection between Power BI and Microsoft Dynamics 365 will take a few minutes to function correctly. Once the connection is established, we will visualize the “Navigator” window. There, we will find a folder with the name of the URL that we previously entered, it contains all the tables that are integrated into Microsoft Dynamics 365.
6. If we select a table, Power BI shows us a content preview on the right side of the window. Also, we can automatically select all the tables that are related to our selection, just clicking on the “Select related tables” button.
7. As soon as we have selected all the necessary tables, then we click on “load” button to start uploading all the data to Power BI Desktop.
I work alot with BC and PowerBi.
Howerver, one major problem we face is the very slow speed while importing large data sets!
Once it even took a whole day to import one table!
This applies for tables as well as queries.
Do you have any solution for that?
Hi @AdamSof ,
unfortunately this is the biggest problem with the OData interface.
I hope Microsoft will deliver a better performing solution here in the future.
I currently prefer direct access to the SQL database whenever possible.
But let's see what the future holds.
Yes I do the same, I go to the SQL behind it.
However, this can be done only when you have your DB on-prem!
Or is it possible to do that when the DB on cloud? I haven't tried that to be honest, but if you did, please share your expierence.
Hi @AdamSof ,
with a Business Central (Saas) solution hosted by Microsoft, you cannot currently access the database directly.
It should be checked whether the loading time can be reduced by incremental refresh or a data flow.
Fortunately, I haven't had to face this problem yet.
Check out new user group experience and if you are a leader please create your group
Click here to read more about the April 2021 Updates!
May the fourth be with you, join us online!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.