Power BI on-premises Gateway unreachable for new reports with APPENDED queries

[Dimitar]

Hi,

There were several similar tickets, but they were several months old or treating more specific cases, so I submit here bellow by issue.

We are using for many months the on-premises Gateway for scheduled refreshes and it was working well … until mid of December 2017. Since then we are not able to connect new reports to the gateway (we have the dot’s turning forever).

What is strange is that ALL old reports continue to update as planned (even if I can’t change anymore the schedules as can’t connect them again to the gateway). We updated the Gateway with the last release, but this didn’t change anything.

When we debugged the problem, we realised that the issue occurs ONLY after we appended some queries within the model (all trials without Append are fine). In our model we use data sources from a MySQL dbase and from Excel files stored on an FTP server. We don’t have the already mentioned case of referencing Folders instead of Excel file names, and we can’t avoid making the Appends. As I mentioned before, Updates via Gateway work fine for all sources if no Appends, or if the reports were already scheduled before December 15th.

We are completely blocked from developing / publishing new reports – please help !!

[v-qiuyu-msft]

Hi @Dimitar,

1. As the report get data from MySQL and FTP server Excel. Please make sure you already create those two data sources under data gateway use the same connection information as in desktop. 

2. Did you use Append Queries or Append Queries as New? As I didn't have the 
FTP server Excel source in my environment, I tested with MySQL and local Excel then appended queries as a new query. on-premise data gateway was available for dataset as long as those two data sources created under data gateway. 

In your scenario, please check if the issue related to FTP server. You can test with MySQL and local Excel. Also you can share the FTP server Excel file for our test if possible. 

3. Please update the on-premise data gateway to the latest version 14.16.6549.2. 

Best Regards,
Qiuyun Yu 

[Dimitar]

Dear Qiuyun,

Thanks for your feedback and Happy New Year!

On your questions & suggestions:

1. All the sources were created in the Gateway and they work properly (the reports can be scheduled well as far as I don’t use Append).
2. I used ‘Append as New’ – it is needed as I have several optional sources, that I want to merge together. I don’t believe there is a problem with the FTP server, as I mentioned before, reports are updating well as far as I don’t use Append. BTW, all my reports which were published and scheduled before early Dec.2017 are updating well. I just can’t schedule NEW reports which have Append.
3. I checked again and we have installed the recommended by you version of the Gateway.

I was able to recreate the issue with some dummy data / and similar environment (only with Excel files, to avoid the access issues to the MySQL). Here it is:

Source data:

       http://dataz.myinfoset.com/xls_import_test1.xlsx

       http://dataz.myinfoset.com/xls_import_test2.xlsx

PBI Files:

      http://dataz.myinfoset.com/2018.01.02 - Test Gateway_2 XLS_APPEND.pbix

      http://dataz.myinfoset.com/2018.01.02 - Test Gateway_2 XLS_NO append.pbix

Looking for your return,

Dimitar

[v-qiuyu-msft]

Hi @Dimitar,

I look into your pbix file, there are two data sources which all point to FTP Excel files. Due to some limitations on our side, I'm not able to access those two files use web data source. I just download and connect to them use Excel data source, then append queries as a new query. After publish the report to Power BI service, the data gateway is available and the refresh for dataset is successful. 

In your scenario, please download Excel files and use Excel data source test again to see if the same issue persists. 

Best Regards,
Qiuyun Yu 

[Dimitar]

Hi Qiuyun,

I sent you a private message with another set of example of the problem.

Please keep in mind that we have exactly the same issue when we use a MySQL database, so I assume it can't be a FTP problem...

In the mean time I was able to find a getaround (not intuitive and boring to implement, but works):

     -1. I publish first in the same Workspace a report with same name as my 'target report', same sources but no Append operations (a dumy simple report).

     - 2. I attach it to the Gateway and schedule it as needed.

     - 3. I overwrite it with my real report, which starts using the previous schedule ... but I can't change the schedule anymore, as I can't confirm again that the Gateway is fine. My real reports is updating well ... until I need to change the schedule, and so I have to restart the process from point 1.

...not very prety but it seems working. Here is a screen copy of the status of my Gateway for the report when I try to attach it directly.

Thanks

[Jorbangr]

I have recently been experiencing the same issue.  My data refresh worked fine with the gateway until I had modified an existing report to include additional appends from other db tables in my sql source.  (Prior I had this table only including an append from one other database which worked fine). Upon adding the additional, the gateway started to give a privacy error warning. 

I have doublechecked to make sure all privacy settings are set to none in my pbix file and on the gateway datasource setting (I have added the new ones to the gateway also). 

I have tried doing an all in one append as well as seperating them with "Append as New".  

Searching forums on the web I have seen a few different reports of this issue but no resolution for it. The workaround provided above did not help my situation sadly. 

[Jorbangr]

I Found a work around for this issue If you are experiencing it with a SQL Query and multiple appends.  Instead of manually appending the 2 seperate sql queries with the query editor.   Join your sql queries as appropriate with the use of UNION or UNION All, and put that code into the advanced editor.  By joining the sql queries through code, your result is 1 table and PBI seems to only care about the first Tables Data source, even if inside the query you are pulling and joining from multiple databases.  After doing so my gateway was able to refresh the data set without the privacy error i was receiving from before.  

Hopefully this helps someone!

[Dimitar]

Thanks for your feedback and ideas Jorbangr !

This looks interesting and I will try it, even if there will be quite substantial modifications of my model. What strikes me is that the model was working perfectly until early December - the only things that could have been changed are the Desktop / Gateway versions !!

Unfortunately your workaround will not work with my multiple XLS files...

@v-qiuyu-msft any ideas on this topic? The WeTransfet example I sent you privately will be active for two more days.

Dimitar

[Jorbangr]

Dimitar, 

Thank you I appreciate that!  I had a thought just now, instead of trying to append the excel files through the query editor. Have you thought about trying to import by combining binaries instead?  Essentially its the same thing and the end result would be the same. 

Instead of importing the excel files and appending them after. Put all of these excel files in one directory, then click on "Get Data > File > Folder. It will then have you select what it calls your sample file, which it uses to mark the column data types as appropriate. After it will append each excel file into one output.  

I assume this still works with no issues, as some one else in my company uses this process for a report. Which i find odd?  

Here is an article on the process. 

https://docs.microsoft.com/en-us/power-bi/desktop-combine-binaries

[v-qiuyu-msft]

Hi @Dimitar,

I can reproduce the issue with your pbix files. Already reported it internally: CRI 57025196. Will update here once I get any information. 

By the way, would you please extend active days for two Excel files? PG team might need to test on their side. Thanks. 

Best Regards,
Qiuyun Yu