Business Central / NAV UG
Experts:
Crippling Slow Data Sources with Power BI
-
Crippling Slow Data Sources with Power BI
We are expanding our use of Power BI for reporting & keep running into walls when trying to work with ~medium-sized datasources; example being General Ledger Entries.
Just doing the initial connection to the General Ledger Entries table, before we can add any filtering or de-select any columns (from the canned web service), it can take an hour for the intial data to load.
I feel I must just be missing something in general when connecting Power BI to BC.
How can I filter my actual web service / columns inside of BC itself, versus applying filters inside of Power BI. Ex.) ~Only select 10 columns & only ever be able to report on the last 3 years of G/L data.
Could totally be missing it, but inside of BC, I don’t see any sort of page builder, web service builder, query builder, etc. where I can apply these types of filters/customizations right at the source. It feels like doing a custom extension in AL is the only route.
Any $.02 would be appreciated. Reporting continues to be our largest struggle w/ BC.
-
2 Replies
-
I can’t tell from your post if you’re on-prem, or online, but here are a couple of pennies for you ;). If you’re on-prem, I’d look in to writing some SQL views that you can do a lot of pre-filtering with. If you’re online, I think you will have to go the extension route by writing a custom API. It sounds like your data sets are pretty huge, so it would make sense to build a custom API library that can do a lot of your pre-filtering and joining using the cloud horsepower rather than transmitting gobs of data that may not be used. Custom APIs in BC are really not all that difficult once you get started. You can get Visual Studio Code for free with its AL tools, and there are lots of MS learning modules out there you can get started with. If you don’t want to get that technical, you can take a look at Erik Hougaard’s Simple Object Designer that has some cost associated with it but will let you take a less code-oriented approach. He has an extensive YouTube channel that is a wealth of BC information from building your own API, to using his tool you can see here: Create your own data Queries for APIs and on-screen reports in Business Central.
Log in to reply.
