On-premise data gateway is not available for dataset with appended query

[jkloppers]

Hi,

I am new to the community, so I hope this is the right place to ask my question. I have searched the forums but did not find a solution.

I use a on-premise datagateway for refreshing data, this works fine.

For a Power BI report based on data from Microsoft Navision 2017 I use a query multiple times for different Navision companies. So the same query on different Navision companies. In Power BI desktop, I append these queries to one query.

After uploaded of the report I try to refresh it with the on-premise data gateway, but this gateway is not visible.

After changing the Power BI report and remove the appending of queries, the datagateway is visible for refreshing the data.

How can I refresh a datasource with appended queries with the on-premise data gateway ?

[MattAllington]

There is one gateway installation of the software, but every data source needs its own configuration. My best guess is that you don't have a confit set up for the new data source. You need to be an admin of the gateway to add a data source. 

[jkloppers]

If I don't use an appended query in my Power BI report, it is working fine, so the setup for the datasource is correct I guess. The appended queries all use the same Navision server. I use OData for the queries.

[KStrueby]

I think I have a similar challenge.  We have an Enterprise gateway installed which is working for one published report.  But I created a second using the same connection setttings in PBI desktop, and cannot see/access the gateway from that published report.

Here are the two dataset settings.  The difference in the second is that I am connecting to 2 separate GP databases and then combining the queries.  So there are 2 connections defined there, but as far as I can tell I have used the same approach for both.  

The gateway just appeared in my dataset properties for the top one, but everything is greyed out for the second dataset.

Any assistance is greatly appreciated!

Kevin

[nsiddall]

@KStrueby Did you find an answer?

[KStrueby]

@nsiddallApologies for the slow response, some other priorities arose and this PBI work was postponed.

I started looking into this again yesterday, and unfortunately still have not found a resolution.

Oddly, I can create a new report from scratch using the same gateway connections and tables, and combine the tables in the queries, and that new one works.  But the old one still does not.  I have tried removing and republishing the report, double checked all the values, and cannot figure this one out.  It may be tied to the fact that the original file was created before a SQL server upgrade, at which point we updated the gateway to use the new server names.  So the new file is working, but perhaps the old file has some hidden reference to the old server names somewhere that I cannot find.

I'd rather not redo all my visuals and formatting from scratch, so I'll keep digging (unless someone else has cracked this nut in my absence)...

[KStrueby]

I may be on to something with my last post.

1.  If I create a new report using the same data sources/tables with the new gateway name and publish it, I can go into settings and see the gateway, and turn it on and schedule refreshes.

2.  If I use my old report and go to settings, no gateway shows up even though I have editted the data sources in my pbix to match the new server names.

3.  I had our IT group add me to the gateway administrator list.

4.  I look at my old report settings again, and now the gateway shows up but says it is "Not configured correctly"

5.  I open the error details, and it shows the 2 new sql server data sources on the gateway with green checkmarks.  BUT... it also shows the 2 old sql server data sources with red X's, and a link to add them to the gateway (see attached image).  I tried to rename the pbix and publish under a new name in case the service had the old references but that did not help.  So, somewhere in this pbix there is still a reference to the old gateway data source names.  Which is odd because it refreshes in PBI desktop fine using the new names - just not in the service.

Stay tuned...

[KStrueby]

@nsiddall- I appear to have solved the issue, for my circumstances anyway.

I edited my query in the pbix.  I deleted all the "combined" tables I had where I was doing a full outer join to include all records from both sources (I had 3 pairs of combined tables into 3 new queries).  I then recreated the combined tables, with the same names so all my visuals still worked, and republished the report.  Now, when I go into the service and look at my dataset settings, the gateway shows up properly with a green status of running (see image).  This suggests that when you merge queries, it stores a reference to the source someplace that I could not find, and when I recreated the merged queries it then used the new data source names.  Or I just got lucky and through the process triggered something else to update.

If I am right, that's quite the quirk... but at least it's fixable.  Hope this helps someone else in need!

[MattAllington]

This sounds like a bug to me. I suggest you raise it at issues.powerbi.com so the devs can take a look.