Do you want to learn how to create Power BI reports consuming the standard out-of-the-box API’s in Business Central?
Ever wanted to know what you can use the BC API’s for in Power BI without the need for any AL development?
Then have a look here:
- Create Stunning Power BI Reports Using the Standard BC API’s – Business Central:
- https://youtu.be/cJjcLVSNyfc
In this demo, you will see how easy it is to consume data from different tables in BC, import them into Power BI using the standard, out-of-the-box API’s.
We will create star/snowflake data models and use a little DAX to create powerful measures. And last but not least we will then visualize that data using standard and custom visuals.
Presented by: Steven Renders
View presentation materials here.
Dynamics User Group (DUG) empowers everyday users, administrators, developers, and partners with the tools and information required to excel in their professions. Online discussion forums, video content, training programs, local user groups via Meet-Up, live interactive virtual and in-person events are just some of the resources members enjoy with their free membership.
Visit all the DUG Programs: https://dynamicscon.com/about/learn/
Discover more from think about IT
Subscribe to get the latest posts sent to your email.
Thank you for the insightful video Steven. I have a question: the standard API’s seem to be pages without any filtering applied. Tables such as general ledger entries have millions of rows and take a very long time to load (especially at a rate of 1MB/s). In such cases, is it better to:
Discard the Business Central connector in Power BI and use Web as a source and create custom API calls with filtering parameters provided in the URL
Set up custom API Query’s in ML and load those using the Business Central connector.
Or perhaps even a way to use API filtering on pages using the BC connector in PBI in a way I do not know about yet?
Happy to hear your thoughts.
Martin
I always recommend to create custom APIs based on query objects, not pages. Queries can aggregate data at the level (granularity) required in Power BI, so the dataset will be smaller. Most, if not all, out of the box BC APIs don’t aggregate or filter any data.
Then in the API call, you can also add filters:
https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-connect-apps-filtering
or you can apply filters in Power Query, and via query folding they should happen in BC.
You could also create dataflows, with incremental refresh. Or implement a semi-incremental refresh:
https://thinkaboutit.be/2020/02/how-do-i-implement-an-incremental-refresh-in-power-bi-free-or-pro/
Hi, do you know how to get real-time dashboards from Business Central Cloud?
What do you mean with: real-time dashboards?
Power BI gets data from BC using the APIs, and in the PBI service you set the refresh schedule. With a PBI Pro license, you can refresh 8 times per day and with Premium 48 times per day, which is every 30 minutes. I have almost never encountered a situation where a PBI report required data less old then 30 minutes…
And, here’s an example of how you can script a refresh in PowerShell:
https://www.fourmoo.com/2018/06/05/using-the-power-bi-api-with-powershell-scripts-refreshing-your-dataset/