cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rfhickey
Helper I
Helper I

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?

12 REPLIES 12
AlexPowers
Microsoft
Microsoft

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

 

rfhickey
Helper I
Helper I

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, @rfhickey 

 

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.

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, @rfhickey 

 

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.

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?

rfhickey
Helper I
Helper I

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

I'll look into that. Thank you!

GilbertQ
Super User
Super User

Hi @rfhickey 

 

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

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

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors