Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have been creating dataset combining data from Teradata, and files stored on SharePoint and OneDrive. For some of these datasets, the scheduled refresh via the On Premises gateway (which is currently running on my PC) works fine, but for others it worked fine for a week or so on scheduled refresh, but then started to always fail with the message;
Last refresh failed: Thu Mar 08 2018 15:52:05 GMT+0000 (GMT Standard Time)
We reached the data gateway, but the gateway can't access the on-premises data source.Hide details
Cluster URI: | WABI-NORTH-EUROPE-redirect.analysis.windows.net |
Activity ID: | ae324436-bb45-78da-f1a0-a9a742ad95b0 |
Request ID: | 88a5239b-51aa-3771-b9b6-9520bc6afc1c |
Time: | 2018-03-08 15:52:05Z |
I'm using Power BI Desktop Version: 2.55.5010.641 32-bit and 64 bit (February 2018)
On-Premises gateway is 14.16.6614.5
I've tried re-creating the datasets from scratch, and at first everything works fine, but as soon as I add substantial data from SharePoint or OneDrive, the update fails.
I can refresh the data using the Desktop without problem.
I was getting the same error message when trying to mashup data from azure sql datawarehouse with on prem sql server.
To solve my issue I had to:
Prior to ticking the box it was saying my gateway was not set up properly and was suggesting that I add the azure sql datawarehouse to the gateway (which you can't do).
Same intermittent error for me. Also using Teradata.
I'm aliasing my Teradata server using the HOSTS file, but all gateways in my cluster have the same HOSTS file.
Hi @Anonymous,
Did you update the credentials in the settings of datasets? Can your computer access the outside sources? The error message seems clear.
Best Regards,
Dale
Yes, I always check the credentials, and the computer can access the on premise datasources and the web/cloud ones too. When I treid rebuilding the dataset from scratch and using the gateway to update the dataset periodically as I went, the scheduled update would work properly, updating numerous times using the personal mode gateway, and then will cease to function at all, even if I then remove some of the queries I have added. I have tried deleteing the dataset and re-publishing it.
hi @Anonymous
Have you tried installing the On-Premise Data Gateway, but selecting the Enterprise Gateway Option to see if that works?
There has been a recent update where it now allows sources from On-Premise and Cloud
https://powerbi.microsoft.com/en-us/blog/on-premises-data-gateway-february-update-is-now-available/
I have been using both on-premises gateway, and the personal mode version and both with success until now. I have both running concurrently on my PC as we have not rolled this product out to the live/production environment.
I'm not sure what you mean by the enterprise version. Is this a third flavour?
Just to empahsise, I have many other datasets which are successuly updating/refreshing using either of my two gateways, combining data from Teradata and SharePoint. This latest datset also used to update perfectly well, but as we added more and more queries it suddenly broke, and will not revert to successful updating when we remove queries.
I have been using both, but yesterday reinstalled the on-premises gateway (so latest version 14.16.6614.5). With other datasets, the refresh works fine both with our on premises Teradata database and when combining that with data from the cloud.
I don't think it is a timeout problem, as a different error is shown when that occurs, and also this failure is occuring variably between a few seconds and nearly a minute.
FWIW, same problem here. New report that combines SQL via an Enterprise Gateway cluster and a file on Sharepoint that is mashed up in Power Query.
It works fine on both the Sept 2018 and Oct 2018 desktop, but almost always fails within 2-3 seconds of starting. "We reached the data gateway, but the gateway can't access the on-premises data source."
The gateway cluster is fine. Dozens of other reports are working. I have validated my credentials to the Sharepoint server as well in the refresh properties for the dataset.
I've tried to run it 10-12 times. It has worked 2 times, and failed the rest. Seems to be a bug.
I currently have the Sept gateways installed. I don't think October's has been released yet.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDoes anyone have this working where they can Combine Files (XLSX) from a Sharepoint Online library (Office 365) and mash it up against an on prem SQL Server?
I've tried no privacy settings for both & organizational for both. This refreshes just fine on my PC in the desktop. This is just the service failing.
This fails 100% of the time for me. It fails instantly when I try to refresh. As soon as I change the file to not load that query into the Power BI DAX model, the report runs fine. I have the December 2018 Gateway in Enterprise mode.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes. For grins I tried without it and it woudn't even let me configure the scheduled refresh because it said the gateway cluster didn't have access to that resource - or something to that effect. So yes, it is checked. It just will not work. It isn't even trying to work. It fails within 2-3 seconds.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe completed refresh is when I disabled load for the mashed up (Sharepoint Online + On Prem SQL) query. The "there is available gateway" is when I had the box unchecked about allowing this cluster to refresh cloud data.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingJust sharepoint works. Just On-Prem works. I can have both in the same report, but as soon as I merge/combine them in Power Query, it fails. If I keep them as separate queries, the load them separately, I can then join them in DAX, but that doesn't quite do what I need it to do.
Yes, the machine(s) the Gateway nodes are running on all have access to the Sharepoint site, and I've authenticated into the Sharepoint site. Again:
Right now my horrible horrible workaround is to load these tables in the new DataFlows and them mashup sharepoint and dataflows, but that is just awful.
Any ideas? I am wondering if there is some config deep in our tenant that is causing this. It was first set up in 2015.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans Any luck with this? I am having the same problem. I have lots of reports using data from our SQL server AND Excel files on Sharepoint and OneDrive, and they work just fine, refreshing over an Enterprise Gateway. However, this is the first report where I try to merge queries, looking up values in a SQL table onto the Excel tables, and now it will not refresh no matter what I try. Although oddly, I did have one random refresh on demand work in 6 minutes. It refreshes on my Desktop in 5 minutes. I too have a ticket in with Microsoft.
@GilbertQ thanks for the idea. In the pbix file on my desktop, I went into Data Source Settings and set all sources (SQL, OneDrive, SharePoint) to Privacy of Organizational Account. Is this what you mean? I then published and the refresh again failed with the same error. "We reached the data gateway, but the gateway can't access the on-premises data source."
I then went into options and settings > options > privacy in the pbix file and clicked always ignore privacy level settings. Refresh again failed with the same error above.
Please let me know if I'm missing something in your suggestion. Thanks!
FYI, I also have an open support ticket regarding this error connecting to Teradata.