Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Incremental Refresh

Hi, folks,

 

I am trying to set up an incremental refresh on a huge report I have. I have create RangeStart and RangeEnd paramenters, added a filter based on these parameters to my date column of interest in Power Query, and then turned on incremental refresh on the table I want to incrementally refresh.

 

I am connecting to Salesforce using a Salesforce Objects connection. I ran Diagnostics in Power Query and verified that the paramenters I created are being folded into the query to Salesforce by looking at the Data Source Query string in the "Detailed" diagnostic table that the diagnistic output; I'm getting the "Where" clause there that indicates the filters are being folded into the query to my Salesforce Object.

 

When I publish to the Power BI service, I'm getting a really long error that starts with this:

{"error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","pbi.error":

 

When incremental refresh is off, I don't get this error and the report refreshes.

 

Could anyone help me figure out what is going on?

13 REPLIES 13
ReportGuru
Helper IV
Helper IV

Hi, I know this reply is very late, but were you able to find a solution? Like other people mentioned here, it could have been an issue related to the SF API. As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supermetrics (which doesn't have PBI as a destination, so you first have to export to Google Sheets and then export to PBI) and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options

AlexPowers
Employee
Employee

Appears this is an issue on the Salesforce API side, I found another thread from here in the forums where a user was able to produce a solution; curious if you could share the M code (remove any personal connection identifiers) and we can all put our heads together.

 

SalesForce Docs

Suggested Solution

 

Anonymous
Not applicable

Follow up: I jumped into a YouTube live session for the legendary Guys in a Cube last Saturday to see if I could get any ideas on how to tackle this. This is what I posted to those guys:

 

“Hey there! I have a 200 megabyte .pbix. The refresh times out after two hours on PBI service. There are multiple queries in the report being merged and appended into a main query.

 

I need to bring in a year of data whereas now I’m only bringing in 4 months. I have tried paring down the model by eliminating tables and unused columns (which I tried to identify using Dax Studio and Power BI cleaner but I think my model has so many relationships and DAX references that it didn’t work well). It’s hard to identify what is used.

 

I then tried disabling auto date/time as per your videos, but that didn’t have a large impact. Now I’m trying to implement incremental refresh in hopes that I could avoid timeouts, but I’m getting long gateway errors instead:

 

"DM_GWPipeline_Gateway_MashupDataAccessError" . . . {"type":1,"value":"invalid query locator"}}

 

I’m using a Salesforce Object connection, and I verified using one of Patrick’s videos on inc. refresh and native queries that my queries are being folded and sent to server.”

 

They suggested I read this article (https://blog.crossjoin.co.uk/2020/06/03/optimising-the-performance-of-power-query-merges-in-power-bi...) and start thinking about how I could use dataflows to do some of the database querying prior to my main refresh query rather that having all queries run at refresh runtime.

 

I’m looking into that now as I have multiple queries being called and merged and appended into a main table in Power Query. That way I could get what I want without needed incremental refresh I believe.  

 

I will follow up with any inroads I make.

Hi, @Anonymous 

 

Is your gateway version up to date? In the gateway management, have you opened the settings?

2.png

Gateway: https://powerbi.microsoft.com/en-us/gateway/

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

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

Anonymous
Not applicable

I'm working on ensuring we have the latest version of the cluster running.

 

I do have that "Allow user's cloud data sources . . ." checked in the gateway.

 

It's still not working though. I'm still working on it.

 

Thanks for your help.

v-janeyg-msft
Community Support
Community Support

Hi, @Anonymous 

 

Have you tried to restart the data source server? In a similar case, it works normally after restarting.

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

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

Anonymous
Not applicable

Thanks, Janey! I will need to see if that is an option for me.

 

A little context: The report is refreshing when I do not have incremental refresh set up (i.e., RangeStart/RangeEnd parameters, filters on the date column in Power Query, and the incremental refresh slider on in the table). However, when I set that incremental refresh set up and republish, I get this:

 

"DM_GWPipeline_Gateway_MashupDataAccessError" . . . {"type":1,"value":"invalid query locator"}}

 

Would a server restart address this issue even if the report is refreshing from the server without incremental refresh on?

Anonymous
Not applicable

I published this report with incremental refresh to My Workspace and refreshed it. It timed out after two hours.

 

Then I published the same exact report to a Premium workspace, which has a 5 hour timeout instead of two, refreshed it, and got this Mashup error: 

 

{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error"

 

It's certainly confusing me.

GilbertQ
Super User
Super User

Yeah that is really strange not sure why that would happen.

 

Have a look at your data sources in your PBIX file and see if they are different when you do not have the incremental refresh running





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

I'll look into that. Thank you!

GilbertQ
Super User
Super User

Hi @Anonymous 

 

It would appear that you need to configure the data source credentials in the Power BI Service for your connection?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

I'm getting a long string starting with this error now: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error"

Anonymous
Not applicable

Thanks. I'll mess with those. It's just odd that this started happening after the incremental refresh was set up.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors