Having trouble disconnecting from On-premises data gateway (personal mode)

[KarlinOz]

I created a model which originally used a couple of XL spreadsheets stored on our local network. In order for that to work in the service I had to install a personal gateway which I subsequently upgraded to the On-premises data gateway (personal mode). It all works fine without any issues, except when the data gateway host machine goes down. I have other data sources for the model as well which are all in the cloud, SmartSheet and an Azure service and a couple of tables internal to the model.

Earlier this year I moved data from the XL spreadsheets to a MySQL database hosted on AWS so no longer need to use the data gateway and I would like to remove it due to the afore mentioned fact that when the host machine goes down then my scheduled refreshes fail.

As all my sources are in the cloud or in internal tables I naively assumed that perhaps the data would just go completely via the cloud and the data gateway host could be shut down. However that is not the case, when the host machine is down the refreshes fail. I note on the Settings page (in the service) under Gateway connection there is a switch labelled use data gateway and it is on. I hoped that I could then simply slide that to off. However it is greyed out and performs no action when I click on it. Perhaps on the line below it I just need to delete the personal gateway:

I have spent the entire day trawling through posts in this forum looking for clues as to how to safely disconnect from the gateway and with the chatter of folk having problems after removing a gateway I'm really nervous about deleting it in the hope it will disconnect. I have not been able to find any information anywhere on best practice to acheive what I want to do. Does anyone have a resource they could point me to?

Here are a list of my data source lines extracted from the project's M Code:

Source = Sql.Database("acterys.database.windows.net", "XX_XXxxxx", [MultiSubnetFailover=true]),
dbo_Invoices = Source{[Schema="xero",Item="Invoices"]}[Data]

Source = Sql.Database("acterys.database.windows.net", "XX_XXxxxx", [MultiSubnetFailover=true]),
xero_TrackingCategories = Source{[Schema="xero",Item="TrackingCategories"]}[Data]

Source = TrackingCategories,

Source = Sql.Database("acterys.database.windows.net", "XX_XXxxxx", [MultiSubnetFailover=true]),
dbo_Contacts = Source{[Schema="xero",Item="Contacts"]}[Data]
Source = Sql.Database("acterys.database.windows.net", "XX_XXxxxx", [MultiSubnetFailover=true]),
xero_ChartOfAccounts = Source{[Schema="xero",Item="ChartOfAccounts"]}[Data],
Source = MySQL.Database("xxx.xxxxxxxxxxx.ap-southeast-2.rds.amazonaws.com", "ccul_regstr", [ReturnSingleDatabase=true]),

Source = MySQL.Database("xxx.xxxxxxxxxxx.ap-southeast-2.rds.amazonaws.com", "adm_aa", [ReturnSingleDatabase=true]),

Source = MySQL.Database("xxx.xxxxxxxxxxx.ap-southeast-2.rds.amazonaws.com", "adm_ab", [ReturnSingleDatabase=true]), 

Source = MySQL.Database("xxx.xxxxxxxxxxx.ap-southeast-2.rds.amazonaws.com", "adm_ac", [ReturnSingleDatabase=true]), 

Source = Smartsheet.Tables(),

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8klMyi8tUorViVYKyEnMK1GIKTUwMDJT8E0sSS3KTMwpVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t]),

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipNSU8tUYrViVZyTC4pTcxRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t]),

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8stXcEksSVSITC1R0lEy1AdCS3MDINPICEgYKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlGK1YlWUlWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t]),

Source = let
   Source = #table(type table[LastRefresh=datetime],
    {{DateTime.LocalNow()+10}})
    in
       Source,
       LastRefresh0 = DateTimeZone.ToLocal(DateTime.AddZone(DateTime.LocalNow(),-11))
       in
          LastRefresh0;

[KarlinOz]

Ok so the problem is the connection the the MySQL database in AWS. I remove that from my queries and the connection to the on-premises data gateway is no longer there. I'll do some more looking but the first post I came across is here. Seems others in the past have had this issue.

[Anonymous]

When you are in Power BI Desktop, what is listed within the Data Source Settings?  Do you have any on premise data sources still listed?  This window will give you a concise list of your sources.  Easiest place to find it is "Edit Queries" -> "Data Source Settings"

[KarlinOz]

G'day @Anonymous, just the sources as listed in the code above, all off-premises:

[Anonymous]

Ok great.  If you now turn off the gateway for this dataset, does it let you manually refresh?

The reason i'm suggesting this is that you can use your gateway to refresh cloud resources, so i'm hoping that by disabling it this is the final step you need to take.

[KarlinOz]

Yes that was what I was thinking and hoping I could do. However see in my picture above, the option to turn it off is greyed out and if I click on the button (slider) or try to slide it nothing happens, it's disabled.

[Anonymous]

I had noticed that but felt it was worth a shot.

If you uploaded the same project again, with a slightly different name. Do you get a similar gateway message to my screenshot?

[KarlinOz]

No, same deal sadly, the switch is still greyed out.

[Anonymous]

That would indicate to me that one of your sources can't be connected to directly and needs the gateway.  The best advice i can give you is to create 4 separate project files, that has only 1 of each of the sources contained.  Upload each of those projects and see if you can identify which data source(s) require a gateway.

[KarlinOz]

Hmmm, Ok thanks for that @Anonymous, I guess I'm hoping someone will be able to give me a defninitive answer on whether any of those sources need the gateway. But maybe on Monday I'll do as you suggest. It's Friday evening here now and I have to head home so I'll get back to it on Monday. Thx for your suggestions.