Gateway connectivity issue (gateway only works for 1 dataset)

[Mark598]

Hi,

I am in desperate need of some help. I have searched forums, tutorials and spent several and several hours trying to understand what I am doing wrong. I am quite new to PowerBI gateway set up and cannot find what is wrong.

I have created a report in PowerBI desktop that connects to a database with the following string:

Everything works fine in PowerBI desktop, I can refresh the data and create needed reports. Now when I publish my report to PowerBI portal and try to set up scheduled updates, then I get stuck.

I have created a gateway to PowerBI portal with all the same data:

I have added myself to the users list:

And now when I go to the dataset to schedule an automated refresh then I cannot choose this gateway:

I have another gateway connection to the same database but using the ANSI driver and this works fine:

I do not know what I did differently that I could choose a data gateway right away with the ANSI connection. If I created the Unicode connection then I could not choose a gateway anymore. What I also tried is to add a new dataset which is using the same ANSI connection and this also does not work anymore. So now I have one dataset and one report that works with automated scheduled refresh but all the others that use the same database and string do not work (data gateway button is not active and I cannot choose it). I would really appreciate to help me identify what I am doing wrong with the new datasets. I am probably missing a simple thing but I don't know how the first one just started to work.

[v-yuezhe-msft]

@Mark598,

 I am not able to see the above images you uploaded. In your scenario,  please make sure the following things.

1. You are using the latest version of gateway.
2. You have added all the data sources of the dataset within gateway, and ensure the connection strings you use are same as that you use in Power BI Desktop.

Regards,
Lydia

[Mark598]

Hi,

I will write the same post again with pictures so you can understand better.

I am in desperate need of some help. I have searched forums, tutorials and spent several and several hours trying to understand what I am doing wrong. I am quite new to PowerBI gateway set up and cannot find what is wrong.

I have created a report in PowerBI desktop that connects to a database with the following string:

 

Everything works fine in PowerBI desktop, I can refresh the data and create needed reports. Now when I publish my report to PowerBI portal and try to set up scheduled updates, then I get stuck.

I have created a gateway to PowerBI portal with all the same data:

I have added myself to the users list:

And now when I go to the dataset to schedule an automated refresh then I cannot choose this gateway:

I have another gateway connection to the same database but using the ANSI driver and this works fine:

I do not know what I did differently that I could choose a data gateway right away with the ANSI connection. If I created the Unicode connection then I could not choose a gateway anymore. What I also tried is to add a new dataset which is using the same ANSI connection and this also does not work anymore. So now I have one dataset and one report that works with automated scheduled refresh but all the others that use the same database and string do not work (data gateway button is not active and I cannot choose it). I would really appreciate to help me identify what I am doing wrong with the new datasets. I am probably missing a simple thing but I don't know how the first one just started to work.

[v-yuezhe-msft]

@Mark598,

Are you able to select gateway when adding the ODBC data source following the guide in the thread below?

https://community.powerbi.com/t5/Service/Enterprise-Gateway-with-ODBC/td-p/98959

Regards,
Lydia

[Mark598]

@v-yuezhe-msft,

The ODBC guide did not help me. I still did not manage to select the gateway. However, after hours of messing around and trying different things I yesterday finally found out what was the issue. What I did wrong was that when starting a new report I connected to the database and selected the tables I needed. Then later on I discovered I need a few more tables from the database. I used recent sources to add them and this screwed everything up. It added the extra tables but not to the existing query but created a new query with the exact same string. So now I had 2 identical connections to the same database due to adding extra tables:

And when I copied the report published them I couldn't select gateways anymore.

Any idea how I could add tables to a connection later on, without adding extra duplicated connections?

[v-yuezhe-msft]

@Mark598,

Do you make sure that you are getting data from the same database? I am surprised that Power BI Desktop create two identical connections for you even you choose recent data source and click the former connection.

Regards,
Lydia

[Mark598]

@v-yuezhe-msft

Yes, I am 100% sure these are the same connections.

After messing around with the reports yesterday I found out that this might still not be the issue. I created a new report from scratch. Added the tables I needed. Then published this to PBI portal. I managed to connect the data gateway and make automated refreshes.  Everything worked as it should be. And somewhere along the way of creating the report it disconnected the gateway and cannot find it anymore. So by the time I finished with the report it did not work anymore. I did not add any tables along the way or changed the connection. I only added some columns and created charts.

What I am trying now, is to create the same report and publish it after every step. So I could spot the place where it stops working and disconnects the gateway.

[v-yuezhe-msft]

@Mark598,

Have you checked gateway logs?

Regards,
Lydia

[Mark598 · Accepted solution]

I wrote the logs over by accident and didn't check them. But now I have created 2 new reports and published them after every step. I did the reports exactly as the first one that did not work. With the 2 new ones, I have had no issues at all and everything works as needed. I am able to add gateway and make automatic updates. So I still don't know what causes the gateway issue from time to time. Since everything works at the moment then I will continue creating reports. If I get new information then I will post here. Thanks a lot for thinking with me and trying to help. I really appreciate it!