web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Best way to create Pow...
Small and medium business | Business Central, N...
Suggested Answer

Best way to create Power BI Report with BC SaaS Version for Large Amount to Data

(0) ShareShare
ReportReport
Posted on by Nilesh Gajjar 135

Hello Experts,

We wants to create Power BI report with for big E-Commerce Partner customer

They have large amount of date on everyday (2000+ Invoice per day)

Morethan 50 Lakh+ entry in G/L Table, Item Ledger Enrty and other tables

So, basically we very large of data customer have in Business Central

Now, we want know what be best method to connect BC SaaS version with power BI

1) ODATA - this will not work as it is has limit in per page record 

2) Standard API - this API working slow when there is large amount of data (it has take 1 hr to fetch only 10 Lakh record)

I just want to know is there any advance option available to connect Power BI with BC Database like we have in F&O and CRM

1) Azure Data Lake 

2) Azure synapse

3) Azure SQL Access

4) Database Relication

We are not sure that MS release capability for above items or it is plan for future roadmap

If any one can suggest the best way to connect Power BI with large amount to data in BC.

Thank you reading..

I have the same question (0)
  • Suggested answer
    Steven Renders Profile Picture
    Steven Renders 5,672 Moderator on at

    I suggest you create a Query object, with grouping and aggregates. This will reduce the number of records returned.

    Also set DataAccessIntent to ReadOnly.

    Power BI shows aggregated data anyway, so aggregate it at the source (=Query)

    Many try to import all the individual ledger records into Power BI and then show then aggregated in a table/matrix/chart. 

    There's not much difference between ODATA and APIs regarding performance, and to my knowledge no limit on number of records.

  • Suggested answer
    MahGah Profile Picture
    MahGah 15,529 on at

    Hi

    Steven solution is the best idea. We are using similar method as well.

    Also, recently MS released the below test method to move the data from BC to Azure Data Lake github.com/.../bc2adls

    Also you can see and vote for this idea as well   experience.dynamics.com/.../

    Or look into this app appsource.microsoft.com/.../PUBID.efoqus-5058796|AID.replicator|PAPPID.18a4d438-88d1-44ee-a38d-3c0ea0d77338

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 3,143

#2
Jainam M. Kothari Profile Picture

Jainam M. Kothari 1,694 Super User 2025 Season 2

#3
YUN ZHU Profile Picture

YUN ZHU 1,067 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans