Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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
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.
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?
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.
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.
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?
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.
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
I'll look into that. Thank you!
Hi @Anonymous
It would appear that you need to configure the data source credentials in the Power BI Service for your connection?
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.