cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User III
Super User III

Seamless integration of Power BI in Business Central

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.

PowerBIInteraction

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.

Web-Services

In Power BI Desktop, choose Get Data. On the Get Data page, choose Online ServicesDynamics 365 Business Central and then click on ConnectSign in with the same account you used to sign in to Business Central. 

Connect

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

DataSource

In the Power Query Editor we choose the relevant columns and load the data via Close & Apply

Columns

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.

PageSize

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.

ReportFilter

For the report we create a measure Invoice Amount to show only the invoice amounts. 

Measure

We also create a running total for the amount using theQuick Measure Assistant to display the Balance Due. 

QuickMeasure

To display the active customer filter, we display the Customer_Name via a Multi-row card. 

MultiRowCard

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.

LineColumnChart

Now we adjust the names and the hierarchy level of the shared axis. 

Adapt

Then we publish the report in the Power BI Service. 

Deploy

Back in Business Central, we activate the Power BI Fact Box Control on the Customer List via More OptionsActions > Display > Show / Hide Power BI Reports. With Select Report we Enable the report just published.

PowerBIFact

Finish! Now you can consume and filter the Power BI report directly in Business Central without leaving the application. 

PowerBIInteraction

How cool is that? #BetterTogether

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether

1 REPLY 1
Administrator
Administrator

Re: Seamless integration of Power BI in Business Central

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Kudoed Authors