speeding up refresh times with dynamics / Business central datasets

[bco_lde]

Hi  there

accotrding to all guides / tips & tricks (from what I found) , the most reliable way to speed up reports and refresh times is to only pull in data that is really needed. which is easy when using datasources like an sql server. 

but I am building reports on an dynamics Business Central SaaS tenant. 
I have access to the webservices. 

whenever I connect to those services, the amount of clutter (unnecessary columns etc) is ... crazy. 
so here are my question(s)
1. when I delete unnecessary columns in the data transformation, does the service still pull the complete table ? (because I haven´t noticed any imptroevement in neither desktop nor web service refresh of published reports)
2. is the only way to improve those models to actually build my own custom queries and publish webservices based upon those queries ? 
3. does incremental refresh work with business central rest api datasources ? 

thanks in advance
regards

[sgeheeb · Accepted solution]

Hi, I just came across your question.

Regarding (1) query folding / pulling removed columns:

I believe these columns are NOT pulled into Power BI. You cannot check the query folding in the usual way, becuase Power BI is unable to determine if the REST API supports this.

You can do this:

In Power Query, load the desired data from your API (I am using a custom API):

First, you will get all the data:

Then, right-click the last step in your query and select "Diagnose"

You will get four new queries:

The one you want has "Detailed" in it.

Open it, find the column "Dara Source Query" and scroll to the very bottom

Near the end you will find the final Query which was sent to the API

If you click on the cell you will see the full query:

In my case I only selected the columns "no" and "descrption". The auxiliary indexes will always be loaded, there is no way around I have found.

So my deduction is that yes, the BC APIs support query folding.

I have not been able to find a reference that specifies which operations are supported. But if you drop the columns you don't need fairly early in the transformation process, it should work.

You can run the diagnostics part on every step of your query by the way and should be able to make out if the transformation steps are being folded back to the source or not.

Regarding (2):

This is the way I am doing it. I started out by using the api query generator from the bctech git repo, but quickly found that using the generator is more cumbersome then just writing the APIs myself.

The one thing which greatly improved performance for me was doing as much work as possible inside the API Queries themselves. The main thing here is performing the joins in the API and NOT in Power Query and applying filters there as well (For instance filtering out all "Comment" lines in sales invoices).

Regarding (3):

Sadly, no. This is what I am waiting for and what I googled to come across this thread.

Please vote for the idea here:

https://experience.dynamics.com/ideas/idea/?ideaid=cc3146a4-a860-eb11-8fed-0003ff45bb2c

Also, bug everyone you work with to vote as well :). 

For me, this would be a game changer. In many cases I would be able to build great Cloud-BI-Solutions using Power BI Dataflows instead of the full-fledged DWH-Structure with ADF and Azure SQL. Especially for smaller customers, the price tag on the Azure infrastructure is a major hinderance.

[v-yueyunzh-msft]

Hi , @bco_lde 

Here is my understand for your question:
(1)when I delete unnecessary columns in the data transformation, does the service still pull the complete table ? 

For whether to query the entire table, this is related to query folding in Power Query. If your data source supports query folding, and your step in Power Query supports query folding, you won't query the entire table.

For more information, you can refer to :
Query folding guidance in Power BI Desktop - Power BI | Microsoft Learn

How to Configure Incremental Refresh in Power BI? (biconnector.com)

(2)is the only way to improve those models to actually build my own custom queries and publish webservices based upon those queries ? 

To improve the models , you can refer to :
Best practice rules to improve your model’s performance | Microsoft Power BI Blog | Microsoft ...

(3)Does incremental refresh work with business central rest api datasources ? 

Data sources that support query folding can be configured for incremental refresh in Power BI, while for those that do not, a scheduled refresh is the only way to go. 

For more information, you can refer to :
Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[bco_lde]

thanks for the response, but these links are nothign new, yet they do not help to answer my (what I was thinking to be) specific questions. 

I have yet to find a yes / no answer to 

the questions: 
1. do business central rest api data source allow incremental refresh
2. do BC datasources allow query folding

I am reading MS howtos and docs pages for days now. but it seems I am running in circles. 

[v-yueyunzh-msft]

Hi , @bco_lde 

According to your description, you want to kown if the "business central rest api data source" allow the incremental refresh.

For this question, i have not konwn this data source , but you can judge it in Power BI Desktop.

You can go to the "Power Query Editor":

In the power query editor, right click on the “applied steps panel” and check “view native query” button is active. If the button is active, that means the query is folding up to this step and if in-active query folding may not be happening. 

If this "View Native Query" is greyed out , it means that in this step it do not support the query folding.

For this , you can refer to :
Query Folding in PowerBI - Microsoft Power BI Community

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly