Does the connector work with API pages?
Yes. Starting in June 2021, the new Power BI connector supports both Business Central web services and API pages. For more information, see Enable Power BI connector to work with Business Central APIs, instead of with web services only.
Can I build a Power BI report using the Sales Invoice Lines or Journal Lines APIs?
The most commonly used line records are available in the Business Central APIs v2.0). So you can use them to build reports in Power BI by selecting them in the Dynamics 365 Business Central connector. However, the Lines APIs are designed to be used only with some very specific filters, and might not work in your scenario. You might get an error similar to "You must specify an Id or a Document Id to get the lines". To fix this problem, do the following steps when getting data from Business Central for the report in Power BI Desktop:
Instead of including the data source for the lines entity, add the parent data source. For example, add Sales Invoice instead of Sales Invoice Lines.
Select Transform Data in the Power BI Desktop action bar.
Select the query you just added, for example Sales Invoices.
Apply any needed filtering on the records to reduce the amount of records loaded in your report.
Scroll to the right until you find a column named as the lines, for example SalesInvoiceLines.
Select the expand button in the header of the column, next to the column name.
Is it possible to choose which Business Central environment to get data from for Power BI, for example, like a sandbox or production environment?
Yes. It can be easily chosen. When you connect to Business Central using the connector, you have to choose the environment and company name.
Can I merge data from several production environments of the same tenant?
Yes. In Power BI, just run the get data operation again and choose the environment you want.
Which pages in Business Central have the Power BI Report part?
Currently, there are a few selected pages that have a FactBox with a Power BI Reports part for displaying a report.
On list pages, the Power BI Reports part is filtered to show reports that pertain to data in the list. Here's the list type pages that include the Power BI Reports part:
| Page ID |
Name |
| 22 |
Customer List |
| 27 |
Vendor List |
| 31 |
Item List |
| 9305 |
Sales Order List |
| 9308 |
Purchase Invoices |
Here are other pages that contain the larger, non-filtered Power BI Reports part:
| Page ID |
Name |
| 1156 |
Company Detail |
| 4013 |
Intelligent Cloud Insights |
| 9006 |
Order Processor Role Center |
| 9008 |
Whse. Basic Role Center |
| 9010 |
Production Planner Role Center |
| 9015 |
Job Project Manager RC |
| 9016 |
Service Dispatcher Role Center |
| 9022 |
Business Manager Role Center |
| 9024 |
Security Admin Role Center |
| 9026 |
Sales & Relationship Mgr. RC |
| 9027 |
Accountant Role Center |
Tip
We don't have plans to add it to all list pages at the moment. However, you can create a simple page extension that adds the Power BI Reports part in a FactBox. For more information, see Adding Power BI Report Parts to Pages in the Developer and IT Pro help.
Is there any way to filter a dataset from Business Central before I pull it into Power BI, instead of applying filters afterwards?
To filter larger datasets, the easiest way is to set a filter on your Power BI report by editing directly the Power Query formula. Most of the filters you set this way will be passed on to Business Central through query folding. See Incremental refresh for datasets.
There's currently no way of setting a filter for the web service data from within Business Central. If your application needs to set a filter from within Business Central, you'll have to create a custom Business Central App for this purpose.
From Power BI, besides using a query, is there another way to get data from Business Central tables that don't have an associated page? For example, like the Item Attributes Value Mapping table.
No. Not at this point.
Are published queries faster to use than published pages?
When it comes to web services, published queries are usually faster than equivalent published pages. The reason is that queries are optimized for reading data and don’t contain expensive triggers like OnAfterGetRecord.
Web services are based on pages or queries that are built for access from the web and usually not optimized for access from external services. Even though the Business Central connector still supports getting data from web services, we encourage you to use API pages instead of web services whenever possible.
Is there a way for an end user to create a web service with a column that's in a Business Central table, but not a page? Or will the developer have to create a custom query?
There is currently no way of adding a new field to a web service. API pages offer full flexibility on the page structure, so a developer can create a new API page to meet this requirement.
Can I connect Power BI to a read-only database server of Business Central online?
This functionality will be available soon. Starting in February 2022, new reports you create based on Business Central online data will automatically try to connect to a read-only database replica. This will cause your reports to refresh faster, and will have less impact on performances if you're using Business Central while a report is refreshing. We still recommend, whenever possible, that you schedule your reports to refresh outside of normal working hours.
If you have old reports based on Business Central data, they won't connect to the read-only database replica.
I've tried the preview of the new connector for the February 2022 update. When I connect to my custom Business Central API page, I get the error "Cannot insert a record. Current connection intent is Read-Only.". How can I fix it?
With the new connector, new reports that use Business Central data will connect to a read-only replica of the Business Central database by default. This change will bring a performance improvement. However, in rare cases, it might cause the error. This error typically happens because your custom API is making modifications to Business Central records while Power BI tries to get the data. In particular, it happens as part of the AL triggers: OnInit, OnOpenPage, OnFindRecord, OnNextRecord, OnAfterGetRecord, and OnAfterGetCurrRecord.
To fix this issue by forcing the Business Central connector to allow this behavior, see Building Power BI Reports to Display Business Central Data - Fixing Problems.
How do I change or clear the user account I'm currently using to connect to Business Central from Power BI Desktop?
In Power BI Desktop, do the following steps:
- In the File menu, select Options and settings > Data source settings.
- Select Dynamics Business Central from the list, then select Clear permissions > Delete.
Then next time you connect to Business Central to get data, you'll be asked to sign in.
Are there plans for the Power BI connector to support the incremental refresh features in the Power BI Service?
Yes. It's on our roadmap.
If a Business Central on-premises solution doesn't have internet access, can I still use Power BI?
Yes. In this case, you use Power BI Desktop locally and connect to the Business Central on-premises. Once connected, can create and view reports, but you just can't publish them to the Power BI Service.
Are there any plans to make it possible to replicate Business Central online databases so they're accessible for read-only SQL queries? This capability would support incremental refresh and be a lot faster than API's or web services.
Yes. We have this feature on our long-term roadmap.
Yes. This advanced scenario will help Business Central stay performant, because the data access would be done via the Azure Data Factory.
Are there any plans to support Power BI deployment pipelines or a way to build deployment pipelines for PBI reports, similar to extensions? Or maybe even a simple API in the Business Admin Center?
We're looking into this feature. Power BI offers rich APIs to control report deployments. For more information, see Introduction to deployment pipelines.
When I get data from Business Central to use in my Power BI reports, I see some values like "x0020". What are these values?
Some API pages, including most API v2.0 pages, have fields based on AL Enum objects. Fields based on AL enum objects must have names that are consistent and always the same, so that filters on the report always work—no matter the language or operating system you're using. For this reason, the fields based on AL enums aren't translated and are encoded to avoid any special character, including the space. In particular, whenever there's an empty option in the AL Enum object, it's encoded to "x0020". You can always apply a transformation to your data on Power BI if you want to display some different value for these fields, for example "Empty".
See also
Power BI Licensing
Business Central and Power BI Introduction
Power BI Integration Overview
Enabling Power BI in Business Central
Work with Power BI Reports in Business Central
Connect to Power BI from Business Central on-premises
Building Power BI Reports to Display Business Central Data
Power BI documentation
Find free e-learning modules for Business Central here