Enabling Your Business Data for Power BI
Getting insights into your Business Central data is easy with the Business Central apps for Power BI. Power BI retrieves your data and then builds an out-of-the-box dashboard and reports based on that data.
You must have a valid account with Business Central and with Power BI. Also, you must download Power BI Desktop if you wish to create your own Power BI reports. Power BI apps require permissions to the tables where data is retrieved from. More details on the requirements are described below.
The Power BI Apps that are described in this article are designed to use Azure Active Directory as the authentication mechanism unless otherwise specified. To install a Power BI App, you must also have a Power BI Pro license. Once the Power BI App is installed, it may be shared with users with any license type.
Microsoft has published the following apps for Power BI:
- Dynamics 365 Business Central - CRM
- Dynamics 365 Business Central - Finance
- Dynamics 365 Business Central - Sales
- Dynamics 365 Business Central(on-premises) - CRM
- Dynamics 365 Business Central(on-premises) - Finance
- Dynamics 365 Business Central(on-premises) - Sales
Using the Business Central dashboards in Power BI
Each app provides reports that you can drill into:
- Choose any visual on the dashboard to bring up one of the underlying reports.
- Filter the report or add fields that you want to monitor.
- Pin this customized view to the dashboard to continue tracking.
You can refresh data manually, and you can set up a refresh schedule. For more information, see Configuring scheduled refresh.
The apps are designed to work with data from any company that you have in your Business Central. When you install the Power BI app, you specify one or more parameters to connect to your Business Central.
You can also build your own reports and dashboards in Power BI based on your Business Central data. For more information, see Connecting Your Business Data to Power BI.
To connect your data in Power BI
Open your browser, navigate to https://powerbi.microsoft.com, and sign into your account.
Select Get Data at the bottom of the left navigation pane.
You can also get starting from within Business Central. From your Home page, navigate to Report Selection in the Power BI section. Select either Service or My Organization from the ribbon. When either of these actions are selected, you will be taken to either the Organization gallery in Power BI or to Microsoft AppSource, which will also be filtered to only display apps related to Business Central.
In the Services box, select Get. This will open a page displaying AppSource and Apps for Power BI.
- Select Apps from the Apps for Power BI tab, choose the Microsoft Dynamics 365 Business Central app that you want to use, and then select Get it now.
When prompted, enter the name of the company in your Business Central that you want to connect to. This is not the display name. You can find the company name in the Companies page in your Business Centralinstance.
If you connect to Business Central on-premises, you must specify the Web Service URL parameter. Find this in the Web Services page in Business Central. Your Business Central Server instance must be configured for Basic authentication, and you must specify a user and the Web Access Key of that user as their password. In the following example, replace myserver:7048 with your Business Central Server name and CRONUS%20US with your company name.
Once connected, a dashboard and reports are added to your Power BI workspace. When completed, the tiles show data from your Business Central company.
- Try asking a question in the Q&A box at the top of the dashboard.
- Change the tiles in the dashboard.
- Select a tile to open the underlying report.
- By default, your dataset is not scheduled to refresh. You can change the refresh schedule or try refreshing it on demand using Refresh Now. For more information, see Configuring scheduled refresh.
Power BI in Business Central
Your Home page in Business Central can include a Power BI control element that can be configured to display Power BI reports on your Home page.
You must have a valid account with Business Central and with Power BI. Also, if you want to modify any reports, you must you must download Power BI Desktop. For more information, see Using Business Central as a Power BI Data Source.
On first login
When you first sign into Business Central, you will notice an empty Power BI part on your Home page. In order to view the reports, you must first connect to Power BI by selecting the Get Started with Power BI link.
Business Central then communicates with the Power BI service to determine if you have a valid Power BI account. Once your license is verified, the default Power BI reports display on your Home page.
Selecting Power BI reports
The Power BI control on your Home page can display any Power BI report. To view an existing report, choose the Select Report action from the Power BI drop down command list.
The reports selection page shows a list of all the Power BI reports that you have access to. This list is retrieved from your Power BI workspace. Enable each report that you want to display on the Home page, and then choose OK. You will be returned to your Home page, and the last report you enabled will appear. Using the drop-down command list, use the previous and next command to navigate between reports.
If you do not see any reports on the Select Reports page, or do not see the report you want. You can choose to get reports from My Organization or from Services. Choose My Organization to go to the Power BI services where you can view the reports within your organization that you have been rights to view and add them to your workspace. Choose Services to go to Microsoft AppSource where you can install Power BI apps.
You can also choose to create new Power BI reports. Once those reports are published to your Power BI workspace, they will appear on this page.
In the Power BI section on your Home page, you can choose the Manage Report action from the drop down command list so that you can modify the report that was in focus on the role center.
Modifications can be made to the report and saved. Any changes made to the report will be changed for any user that this report is shared with since you are modifying the report that is stored in the Power BI service.
Once you have completed your changes, select Save. If this is a shared report, you may want to select Save As to avoid making this change for all users. Return to the role center and the updated report will appear. If you did a Save As command, you will need to open the select report page and enable the new report.
You can upload new Power BI reports and share them with all users of your Business Central. The reports are shared within each company in Business Central.
To upload a report, select the Upload Report action from the drop-down command list. You can then upload a .pbix file that defines the reports that you want to share. You can change the default name of the file.
Once the report has been uploaded to your Power BI workspace, it automatically uploads to the Power BI workspaces of all other users in that company upon their next sign in to Business Central.
To import your Business Central data into Power BI, you must have permissions to the web services used to retrieve data. The web services required for each Power BI app include:
Microsoft Dynamics 365 Business Central – CRM
- Sales Opportunities
- Excel Template View Company Information
- Power BI Report Labels
Microsoft Dynamics 365 Business Central – Finance
- Excel Template View Company Information
- Power BI Report Labels
Microsoft Dynamics 365 Business Central - Sales
- Item Sales by Customer
- Sales Dashboard
- Excel Template View Company
- Power BI Report Labels
Business Central on-premises uses the same web service end points as Business Central online.
An easy way to find the web services is to search for web services in Business Central. In the Web Services page, make sure that the Publish field is selected for the web services listed above.
The Power BI dashboard relies on the published web services that are listed above, and it will show data from the demonstration company or your own company if you import data from your current finance solution. However, if something goes wrong, this section provides a workaround for the most typical issues.
You do not have a Power BI account
A Power BI account has not been set up. In order to have a valid Power BI account, you must have a license, and you must have previously signed into Power BI, in order for your Power BI workspace to have been created.
You need a Power BI Pro license to install the Business Central app in Power BI
Power BI apps can only be installed by users that have a Power BI Pro license. Once the Power BI app is installed, you can share it with users that do not have a Power BI Pro license.
"Parameter validation failed, please make sure all parameters are valid"
This error indicates that the one more of the parameters are not valid.
- The specified company parameter does not match any existing Business Central companies. Verify the company name in the Companies page in Business Central.
- If connecting to Business Central on-premises. you have entered a URL that is not valid. You can verify the URL in the Web Services page in Business Central
- A port is not open to allow the request to go through your firewall.
If you get a "login failed" error after using your Business Central user credentials to log in, then you are probably experiencing one of the following issues:
- The account that you are using does not have permissions to retrieve the Business Central data from your account. Verify that you have permissions to the required data in Business Central and try again.
- You have selected an authentication type other than Basic if connecting to Business Centralon-premises .
- You have not entered a valid user name or password.
Incorrect company name
A common mistake is to enter the company display name instead of the company name. To find the company name search for Companies. Then use the Name field when entering your company name.
The key didn't match any rows in the table
If you enter a non-valid company name during the connection process, you may get the error message "The key didn't match any rows in the table". Provide the correct company name and try connecting again.
Historical data appears to be missing
Once the Power BI app is installed and your data shows up in Power BI, you may notice that not all your data displays. The datasets are filtered to only return the previous 365 days of data. This default is in place to help make the reports faster.
I only see data for a single company
The Power BI app will only display data from the Business Central company that was defined when the Power BI app was installed. Data from additional companies can be added to the reports by adding new queries that use different companies as the data source.
Get started with Power BI
Power BI - Basic Concepts
Apps in Power BI
Quickstart: Connect to data in Power BI Desktop
Importing Business Data from Other Finance Systems
Setting Up Business Central
Using Business Central as a Power BI Data Source
Using Business Central as a PowerApps Data Source
Using Business Central in Microsoft Flow
Start a free trial!