Forum Discussion
Advocate VData Refresh Errors after Enterprise Gateway upgrade
After upgrading to the October 2024 gateway, I'm getting refresh errors randomly for scheduled Power BI refreshes. The error is: "DM_GWPipeline_Gateway_DataSourceAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError." The error isn't consistent across reports and there's always a table referenced, but the table isn't consistent either. There's are all SQL Server on prem connections that are failing. I can always go click the refresh button in the workspace and the model refreshes without a problem. Had the same problem when updating from June to July 2024 gateway and reverted and the errors went away.
Things I've tried:
1. Recreated the entire gateway cluster and ALL connections from scratch.
2. Recreated individual connections and reset credentials.
3. Tried different credentials.
4. Tried setting up different connections.
5. Currently on my 2nd support ticket with Microsoft and am still stuck on really low-level troubleshooting (frustating).
6. Creating new reports with new data connections.
7. Rebuilding exisitng reports with new connections.
8. Simplifying the Power Query queries in the reports (this one has shown a little promise, but is problematic).
One thing I've noted through all this is that there are a couple things we do that create "Personal Mode" connections. If one of us "Takes Over" a semantic model to update it, "Personal Mode" connections show up--despite the fact that there are shared connections exsiting. Also, publishing a report to a workspace results in "Personal Mode" connections showing up...even after we've updated the settings to use the proper shared connection. These Personal Mode connections, can't be edited in any meaningful way--can't add credentials, can't update users, etc. and they're always "Offline." There seems to be a correlation between having the Personal Mode connections there (or not--our practice now is to delete them at the end of the day) and the number of failure we get.
Has anyone seen anything like this?
41 Replies
- lbendlin
Super User
After upgrading to the October 2024 gatewaywhich one?
Did you notice the "Trusted Server" discussion in the release notes?
motorayI've just upgrade to 242.8. I hadn't seen the "Trusted Sever" discussion. Can you point me to that?
Same error here too of:
Data source error: {"error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError","parameters":{},"details":[],"exceptionCulprit":1}}} Table: Specialists
where "Table" is always listed as something different. Have done everything you listed as well:
configured CPUUtilizationPercentageThreshold to 70 from 0
Enabled query caching on jobs using import mode
Enabled Large Semantic Model Storage Format
Enabled Query Scale out
Error is completely random, no consistent time or reason. Semantic model is nowhere near exceeding the 25GB size limit. MS just keeps requestintg logs, logs, logs but because the refresh failure throws randomly, we are having a hard time capturing it in logs and when we do submit, no reason is provided from Microsoft. Frustrating!- motoray
I hear you on with the frustration. I did finally get to the next level of tech support...and they wanted more logs. I've found two things that brought some relief--but we still see daily refresh failures.
1. For some reason we get Personal mode connections occasionally in our gateway connections list. We do not use Personal gateways. The Personal mode connections don't connect to anything and can't be changed. If we are careful to check and remove these connections, we get fewer failures.
2. I found that one of the gateway cluster servers was very low on hard drive space. I had our server folks increase HDD space on both servers in the cluster and bring them up to a higher spec in line with what Microsoft recommends for gateway cluster servers. There was a noticeable difference in # of refresh failures.
Maybe take a look at those and see whether you can get any relief that way.
- lbendlin
You also want to consider putting the heavy load items (spooler and logging) onto a different spindle.
- kymramosrpo
have been experiencing the exact same issue here for weeks. Error logs are of no help at all. Have you found a solution, or even a way to diagnose the problem?
- motoray
We were given a solution that's worked well enough for us with the promise of Microsoft doing some kind of more permanent fix in a future gateway.
There's a configuration file--Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config. On our servers, it's here "C:\Program Files\On-premises data gateway."
Open the config file in a text editor and scroll down to almost the bottom. Find the "NextResultSkipped" setting and change the value from False to True. After that, restart your gateway and see if the failures stop. They did for us. I never got a satisfactory answer about why they stopped.
AMD0791Advocate IV
You mention "Personal Mode" connections. Are you referring to
A. The connections on the on-premise gateaway "Connections" tab that have "(Personal mode)" for the Gateway Cluster Name?
or
B."On-premise gateway (Personal Mode)" items on the "On -premise Data Gateways" tab?
For A, I can only see the connections for myself. Have you found a way to identify additional connections possibly created by others? I also haven't found any explanation for what they are or why they matter.
Thanks!
- motoray
Am referring to (Personal mode) in the Gateway cluster name column of the Connections tab.
I haven't found a way to see others' (Personal mode) connections either. Luckily there's only two of us who do this work and we just try and go in there at least 1X weekly to clean it up.- AMD0791
Interesting -- I wonder if this happens for everyone that can publish to the service, or only those that have the admin or creator privileges on the gateway.
- motoray
First thing I do is go check the config files when we start having crashes again--it's usually that the files were overwritten and I need to add the trusted servers and the lastresultskipped flag back in.
