Connecting Power BI to Business Central

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!

Hi @Martin,

Welcome to the forum!

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:

  1. 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.
  2. 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.
  3. In Power BI, connect to your Business Central instance: There you can find now your query and just check it for loading.
  4. 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.

There is also an extensive documentation of the data model at https://dynamicsdocs.com/nav/2018/w1

Best regards,
Martin

1 Like

Thanks for the feedback Martin!

And keep it coming :wink:
I’m sure others will benefit from it in the future.

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… :sweat:

1 Like

I finally built my first Dynamics 365 API Query and successfully imported it into Power BI! :smile:

What you can do with Business Central API Queries and web-services:

  • Expose columns of Business Central tables to Power BI
  • Joins: InnerJoin, LeftOuterJoin, RightOuterJoin, FullOuterJoin, CrossJoin (Cartesian product)
  • Aggregations: Sum, average, min, max, count
  • Filter: By exposed and not exposed columns

Everything else needs to be done in Power BI :wink:

Since there are a few pitfalls, here are the steps to get started, in brief:

  1. You need a user with “SUPER” privileges set in the sandbox environment to publish an API query extension that is under development.
  2. Install Visual Studio Code
  3. Install the full AL Extension Pack by waldo from within Visual Studio Code
  4. Create a new AL project with command AL: Go!
  5. Download symbols for the AL project with command AL: Download symbols
  6. Remove the Hello World page extension if there is one in the project
  7. In the .al file type tquery and select the API query code template (2nd from top).
  8. Define the data in your API query or start with the example from the Microsoft documentation: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-api-querytype The example works out of the box except that you have to change the ID from 20000 to something in the range of 50100 to 50149.
  9. Press F5 to deploy the extension to the Dynamics 365 sandbox environment in the cloud.
  10. Dynamics 365 starts in a new web browser tab. Search for “web” and then open the web-services configuration.
  11. Add a line for your service of type Query and in ID column enter the ID that you have used in your code.
  12. 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 :clap:

Before you start, read through the Microsoft Business Central query development documentation: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-overview

If you struggle, post your launch.json, app.json and *.al files here and describe at which point you struggle.

2 Likes

@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:

https://www.youtube.com/results?search_query=build+business+central+query+object+in+visual+studio+code

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.

BR
Martin

Thanks very much or your response @Martin I will look into these but i do agree BC to a SQL warehouse is the best option.