Scheduled Refresh Failed - no change to dataset, error keeps changing

[vsagan]

Hi,

We have a scheduled refresh through the enterprise gateway.  This refresh worked yesterday.

This morning, the refresh failed at 6am AEST 27 Oct 2017.  Error message:  "The last refresh attempt failed because of an internal service error."

We tried to manually refresh the data at 11:30am AEST 27 Oct 2017, and got this error message:  "The gateway is either offline or could not be reached".

We again tried to refresh at 3:15pm AEST 27 Oct 2017, got this error message:  "There is no available gateway".

When we check the refresh settings for this dataset, it now says "You don't have any gateway installed or configured for the data sources in this dataset.  Please install a new personal gateway or configure the  data source for an existing data gateway."

I will confirm again that this worked the previous day, and the dataset was not changed at any time, and that the gateway is online with the appropriate data sources, and works for other datasets.

[v-haibl-msft]

@vsagan

Please make sure that you have added all the data sources in PBI Service - Manage gateways and the gateway should be online both in on-premises data gateway application and PBI Service.

If issue persists, could you please provide the detail info of the failed refresh?

Best Regards,
Herbert

[vsagan]

@v-haibl-msft

All date sources are in the gateway, and the gateway is online both on-prem and the PBI service.

To be clear, again, this was working the day before for both datasets which confirms that the data sources are in the gateway.

One of the datasets now works correctly.

Here are the two errors:

1st error:

Scheduled

10/27/2017, 6:01:10 AM

10/27/2017, 6:23:59 AM

Failed

The last refresh attempt failed because of an internal service error.

Cluster URI:	WABI-SOUTH-EAST-ASIA-redirect.analysis.windows.net
Activity ID:	7d9728f2-ae8f-426e-9069-b234abaa5d03
Request ID:	159bbfb3-b6f6-4a8a-abff-5b766bc357e9
Time:	2017-10-26 20:23:59Z

2nd error:

On demand	10/27/2017, 11:07:52 AM	10/27/2017, 11:31:11 AM	Failed	The gateway is either offline or could not be reached.
Cluster URI: WABI-SOUTH-EAST-ASIA-redirect.analysis.windows.net Activity ID: 7054eb2e-40ec-4651-bbbf-4be09ba84005 Request ID: c07d5b3d-5a20-6f98-114d-62fdd1ee4e2e Time: 2017-10-27 01:31:11Z

After this, one of the datasets began to refresh again.

The other one got this error:

3rd error:

On demand	10/27/2017, 3:17:18 PM	10/27/2017, 3:17:19 PM	Failed	There is no available gateway.
Processing error: There is no available gateway. Cluster URI: WABI-SOUTH-EAST-ASIA-redirect.analysis.windows.net Activity ID: ee408bdf-41ea-42c3-b2eb-2c047e8d2d99 Request ID: 2e83ae96-5596-44a1-a0db-4bde9c091bd2 Time: 2017-10-27 05:17:19Z

I have been working on this dataset and editing the queries in PBDesktop and will again confirm that all data sources are added to the enterprise gateway and that this used to work.

I have narrowed the error down to a query which refers to two other queries.

If I setup the dataset like this:

Query1:  data source is a folder (from a parameter).

Query2:  data source is a folder (from a parameter).

Everything works.

If I setup the dataset like this:

Query1:  data source is a folder (from a parameter) -- same query as above.

Query2:  data source is a folder (from a parameter) -- same query as above.

Query3:  Append Query1 and Query2 and no other steps.

Then the enterprise gateway says the data source has not been added.

???

[vsagan]

Hi,

I will confirm that appending two queries seems to cause this problem.

I created a brand new PowerBI Desktop file. 

This file contained two queries:

Query1:  One step, Source = Folder.Files("UNC Path")

Query2:  One step, Source = Folder.Files("Other UNC Path")

This desktop file was published and could be refreshed with the enterprise gateway -- the UNC paths were data sources in the gateway.

I then modified this file to add Query3.

Query3:  Source = Table.Combine({Query1, Query2})

This desktop file was published and could NOT be refreshed with the enterprise gateway.

???

I confirm that merge caused this same issue.

[vsagan]

Hi,

We have been able to easily replicate this error with multiple users at our organisation, with the following steps:

Query1:  Source = Folder.Files("some UNC folder path")

Query2:  Source = Folder.Files("Some other UNC folder path")

Publish -- add folders to enterprise gateway -- refresh works with gateway

Then, add (append command):
Query3:  Table.Combine({Query1, Query2})

OR add (merge command):

Query3:  Table.NestedJoin({ relevant stuff})

Publish -- gateway fails with the errors described above

We have also tested this with an EXISTING report as follows:

We have a PBI desktop workbook which appends/merges as described above, and this file was published many weeks ago.   It had one day of failed updates on 27 Oct 2017.  Now it is refreshing with a schedule through the gateway just fine.

However, if I use the PBi Service feature to download the report (pbix) file, then open this file in desktop and publish to a new workspace, scheduled refresh does not work with the errors described above.

!! ????

[v-haibl-msft]

@vsagan

Your issue should be same as the one mentioned in this thread. The product team should be working on a fix for it.

As a workaround, we can declare the files individualy instead of a folder in gateway.

Best Regards,
Herbert

[vsagan]

Hi Herbert

We are doing this with hundreds of files, and new files are added daily.  The described workaround is not feasible.

I have tried another workaround.  I automated the creation of a csv file which contains the file (full path) to read.  I then had PowerBI read this file, and use the Csv.Document(File.Contents()) command to read these files.

This worked fine in desktop -- unfortunately the gateway complains and says the data sources are not supported.  This occurs even with a basic PowerBI desktop file that does nothing but try this method with only one file.

This is a key feature -- any idea on when it will be fixed?

[roytap]

My report is currently encountering the similar problem as well.

I am having this function that will merge all files in a UNC folder into 1 query.

Somehow when i merge this file with any other queries within my Power query, it will indicate the same refresh schedule connection error. However, if this file is a standalone query, the refresh schedule will be working normally.

In my case, I need to merge this file with another exchange query in order to get values based on different currencies, so it is also a dilema for me as well.

I hope the team can resolve this Refresh schedule soon. I have to manually refresh my report everytime there is an update now.