Incremental Refresh using ODBC not giving faster time (PowerBI Pro)

[brunozanoelo]

Hello my dear friends,

I´m working with Incremental refresh for a couple days (all of my customers are using PowerBI Pro), I already configured all my settings but my refresh time are not getting better.
My parameters:
RangeStart: 01/01/2014
RangeEnd: 01/01/2022 Why 2022?
Well, I need all the data from the next few years, I guess this will not be a problem, since PowerBI will store the past data, right? 
Store Data: From the last 5 Years
Refresh Data: From the last 1 Months or 2 Months (Only one of my .PBIX does that, business rules...).

Incremental RefreshRangeStartRangeEND

 

My pbix files with incremental refresh are there since day 20/03 and I´m not getting any results,  as u can see "Análise de Pedidos" is still getting me error because it reachs the limit of time (max refresh time is 2 hrs as far as I know).

Any tips for my problem? All my configuration are right? I will wait until middle of this week to check if I get better results, any info I will post here.

It should work better even with ODBC connections, right?  I´m using ODBC for Firebird Database.

[Syndicate_Admin]

Hello still nothing about this problem?, for me it is important to have an incremental update by ODBC or if there is any similar alternative, it would be of great help to many people.

Best regards.

[v-diye-msft]

Hi @brunozanoelo 

Please kindly refer to this official blog:

https://powerbi.microsoft.com/en-us/blog/incremental-refresh-query-folding/ 

And a similar thread:

https://community.powerbi.com/t5/Desktop/Query-Folding-amp-Incremental-Refresh/td-p/957917 

[brunozanoelo]

Hello Team,

When I select my table, doesn´t appear the option of "Native Query", I tried to put the Incremental step before everything, even before the step of renamed columns, but I´m not sure this gonna work.

What I should do? There´s any other way to make incremental work?

[v-diye-msft]

Hi @brunozanoelo 

Please kindly confirm the PBIX file was authored in a version of Power BI Desktop after the February release.

https://devinknightsql.com/2016/07/03/power-bi-checking-query-folding-with-view-native-query/ 

BTW,take a look at this Guy in a Cube video. There are times the "View Native Query" option is grayed out yet folding is still happening! He shows how to use Power Query diagnostics to get a detailed list of the communications between PQ and your SQL Server.

[brunozanoelo]

Thank you, I had two problems:

1) My Powerbi Desktop is from Dec/2019. I´m already doing the update...

2) I use my own SQL statement in each Query that I made, it´s obvious, since my data is huge and I don´t want all fields... Why Microsoft made this rule? Is ridiculous in my opinion, being a Data Analyst you must know ur database and understand what fields should actually be exported to PowerBI.

Query folding does not take place in this scenario:
You write your own source query. When you establish a connection in Power BI to a database (let’s say SQL Server for this discussion) you’re provided an option, under Advanced Options, to write your own SQL Statement to define how the data will be imported.  Once you choose this option Query Folding is no longer available on this query. That means if you plan on writing a query to import your data you better know ever transform you would like to apply to the dataset and write it inside the SQL Statement because if you add any additional transforms in the Query Editor later they will not be folded.

In this case, what should I do? Use direct the table without any SQL?

Best Regards from Brazil.
Bruno Zanoelo

[brunozanoelo]

1) My Powerbi Desktop is from Dec/2019. I´m already doing the update...
2) I use my own SQL statement in each Query that I made, it´s obvious, since my data is huge and I don´t want all fields... Why Microsoft made this rule? Is ridiculous in my opinion, being a Data Analyst you must know ur database and understand what fields should actually be exported to PowerBI.

Haven´t solve my problem. There´s any reply from Microsoft about this issue? I can´t belive that running my own SQL I´m not able to do incremental refresh, it´s ridiculous, we are not working with 1GB database guys, I have 200GB databases running in my customers.

[FabioToniolo]

Hi, Bruno!

I'm having the same problem here.

To me, it looks like Firebird/ODBC is not fully compatible with PowerBI and incremental updates.

I keep looking for a solution...

[brunozanoelo]

Yes @FabioToniolo , I will make some tests this week and I will post here the results,  maybe I have the solution, disabling Native SQL and using directly tables.

[Anonymous]

hi @brunozanoelo ,

Were you able to find any workaround for incrementalrefresh using ODBC connection. Please let me know if you did.

Thanks

[brunozanoelo]

Actually not, firebird odbc doesn´t support query folding.

Any news, please reply this post.

[Greg_Deckler]

It's likely the warning you received about folding queries. 

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

[brunozanoelo]

Hello,

I´m working with Incremental refresh for a couple days (all of my customers are using PowerBI Pro), I already configured all my settings but my refresh time are not getting better.
My parameters:
RangeStart: 01/01/2014
RangeEnd: 01/01/2022 Why 2022?
Well, I need all the data from the next few years, I guess this will not be a problem, since PowerBI will store the past data, right? 
Store Data: From the last 5 Years
Refresh Data: From the last 1 Months or 2 Months (Only one of my .PBIX does that, business rules...).

All my printscreens are located in this post:
https://community.powerbi.com/t5/Desktop/Incremental-Refresh-using-ODBC-not-giving-faster-time-Power...