A question I recently received is: I created a Power BI report with Business Central data from my Business Central tenant and now I want to publish the report to the tenant of the customer, how does that work?
Let’s start with an example report.
In this report I will connect to the Customers API and import the Customers of my Business Central Sandbox on tenant X:
After you select the Dynamics 365 Business Central connector, you need to sign in to it:
The credentials you provide (email/password) are the same ones you use to login to Business Central.
When you then Connect, a popup displays your environments:
And in the environments you find the companies:
And in the companies you find the APIs:
Here I select the customers API and simply import the data:
Now I want to connect this report to another Business Central tenant.
To do that, open: File, Options and Settings, Data source settings:
Then select the Dynamics 365 Business Central data source and edit the permissions:
Now you need to sign in as a different user, and use a login (email/password) of the other Business Central tenant you want to connect to.
Now you click the Refresh button, to refresh the data:
Normally this works fine, and after the refresh you should see the data of the other Business Central tenant.
In my case however, I get an error:
To solve the error, open the Query Editor (Transform data):
The reason for this error is that in my two different Business Central environments, the companies have different names: CRONUS BE and CRONUS USA, Inc.
To solve the error, I could open the Advanced Editor, and update the company name:
Now when I refresh, I get the customers from the other Business Central tenant:
Now, if you don’t want to open the advanced editor for every query in your report to update the company name and potentially also the environment name, you might be better of using a parameter for them, or use my multi-company template:
In that case you only need to change the credentials in the data source settings as described here above and the report will auto update.
Voila, your Power BI report is now connecting to another Business Central tenant, and the explanation here above explains how to to it in Power BI Desktop.
But, at some point you are going to publish the Power BI report to the Power BI Service:
First you will be asked to save your report, if you have not saved it recently.
Then you need to enter an email address:
As an alternative, you could have also done this by selecting Sign in at the right top.
You know need to enter your credentials (again). You already entered your credentials in the connector, and that was the data source, now we are entering the credentials of the Power BI tenant. (which should be the same then the BC tenant)
After authenticating, you then need to select the destination (workspace):
Now your Power BI report is uploaded to the Power BI Service:
In the Power BI Service, you can then schedule the refresh and/or share the report, or add the visuals to a dashboard.
If you need to use a different user for the refresh, you can change it in the dataset:
And, if this is the first time you have published the report, you will have to enter those credentials:
And make sure the credentials you enter have the correct permissions in Business Central.
Every time the report is refreshed in the Power BI Service, these credentials will be used to logon to Business Central and retrieve the data.
It is extremely important that the Power BI tenant and the Business Central tenant are in the same domain, otherwise it will not work.
Here’s a picture of the architecture:
- Power BI Integration Component and Architecture Overview for Business Central – Business Central | Microsoft Docs
- Power BI FAQ – Business Central | Microsoft Docs
- In Power BI Desktop, the credentials to connect to Business Central are stored in the datasource settings: File, Options and Settings, Data source settings. You can simply edit them to connect to another Business Central tenant.
- In the Power BI Service, you can enter the credentials in the dataset settings.
- The Power BI tenant and Business Central tenant need to be in the same AAD tenant (domain). The Power BI Service can not connect, via the Business Central connector, to a Business Central tenant that is in another tenant/domain.
More information is available here:
- Introduction to Business Central and Power BI – Business Central | Microsoft Docs
- Building Reports in Power BI Desktop to Display Business Central Data – Business Central | Microsoft Docs