Is anyone here familiar with connecting Power BI to Microsoft Dynamics Business Central? When using the Power BI Connector for BC I see, among some other tables, a view with lots of Power_BI_xxx tables.
Is there a data model available or a documentation what is Microsoft’s idea behind these tables?
What I need to get, with transformation in PowerQuery as needed, are fact tables e.g. revenue from sales or invoices, and dimensions, e.g. by product, customer, sales department, region,… What’s the smartest way to get these from BC?
I’ve attached a model that gets some basic data from BC to see what I’m looking for. But my impression is, that the BC Connector does not expose all data that I need or I simply don’t know where to find them. So I generated some of the dimension tables by summarizing other tables, although they must be in the database on their own somewhere. Pointing me to a good resource, documentation or video to get started with doing it the right way would already be very helpful. Business Central Cloud SANDBOX CRONUS DE Connection English.pbix (325.0 KB)
Thanks a lot in advance!
I’m not exactly familiar with Business Central but I expect these Power_BI_xxx tables are designed for 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.
In the link below you’ll find instructions on how to connect with your data. Step 5 let’s you define custom queries for web services you can publish to gain access to the fact and dimension tables you require.
Thank you Melissa! This helps me already one step further: It seems that you have to define in Business Central what data you want to expose to Power BI, only then you can load it through the BC connector. I’m curious to see whether, when they are talking about “Create Data Set” in BC in step 5. this is technically indeed a data set as known from Power BI or just something like a view.
If I can actually define data sets in Business Central, wouldn’t there be a way to directly add these data sets to my Power BI workspace and then use the Power BI Data Set connector instead of the Business Central connector? Just thinking aloud…
I will try on Monday.
I wouldn’t expect so. Think more in terms of induvidual tables OR queries built on data from several tables. But let us know when you’ve had a change to explore this on monday.
Hi Melissa,
I found out what is the basic concept how to get any data from Business Central into Power BI using the Dynamics 365 Business Central connector:
Create an “API Query” in Business Central: This is a piece of AL code that you have to write in Visual Studio Code and then deploy to the cloud service. Each API Query returns a table. This table can contain fields from a Business Central table, referenced tables, or aggregations, e.g. a sum of revenue. Design consideration: The more values you aggregate the faster the load, and the more values you load without aggregation the more you can drill, filter, and slice in Power BI - keep you requirements in mind.
Set up a web-service endpoint to expose the API to Power BI: With appropriate privileges you can set this up in the Business Central web-UI and it creates an OData endpoint.
In Power BI, connect to your Business Central instance: There you can find now your query and just check it for loading.
Apply PowerQuery transformations as needed: Especially if you want to limit the number of queries you have to build in Business Central and still want to build a nice multi-dimensional star-scheme data-model in Power BI, you can decompose and transform the tables you get from the Business Central endpoints.
Unfortunatelly I couldn’t get the necessary privileges on our Business Central service today to actually try it out. To be continued…
Btw. “data set” in BC is only a name for the link between the query and the web-service and thus exposes only a single table at an endpoint.
Lesson learnt today: If you have a Business Central installation on-premise and want to connect Power BI with the Dynamics 365 Business Central (local) connector then you must set up a Business Central instance that uses NavUserPassword authentication and you must have a valid https certificate. This connector does not support Windows authentication.
OK, at least now I have some data in my Power BI…
Press F5 to deploy the extension to the Dynamics 365 sandbox environment in the cloud.
Dynamics 365 starts in a new web browser tab. Search for “web” and then open the web-services configuration.
Add a line for your service of type Query and in ID column enter the ID that you have used in your code.
As soon as you see the generated URLs in the Business Central configuration, you can go to Power BI Desktop, start the Dynamics 365 Business Central connector and load your new query into Power BI
@Martin do you have any youtube links that you can provide that helped you with your Business Central API? This is something that i am really interested in because my Odata web services are horribly slow and have started to fall over due to size, any info you can give would be appreciated, thanks
Hi @BCS ,
sorry for the late response. You might want to start with this video for setting up OData endpoints in Business Central:
You can select fields and add filters to minimize your query size. But the OData implementation is slow. So you might want to consider incremental load as supported by Power BI or even switching to dataverse for integration of Business Central and Power BI (is faster, but has even stricter size limit as of now).
You only need to build your own custom queries in Visual Studio Code if you need to extend the options as shown in the video above. This YouTube search will help you to find some videos to start with:
If you want to export data from Business Central with a holistic coverage of all available data I’d recommend to incrementally fill a SQL Data Warehause from the OData tables and load from SQL to Power BI as needed for each individual report.
