With the on-premises gateway, you can directly connect to your SAP BW system. This allows you to refresh the reports that are in the Power Bi cloud solution. The gateway is very simple to set up. However, if you are not from the US, you will run into one of the following errors.
Unable to connect: We encountered an error while trying to connect to . Details: “We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance.
After some digging through the files of the gateway, we found out that the gateway’s ODBC connector was only available in “en-US”. At first glance, this seems to be an easily fixable inconvenience. It was however more complex than we thought.
Step 1: Set your machine to en-US
To begin, the Region and language settings need to be set up in American English.
Now go to additional date, time & regional settings.
Click on “change location”.
Go to Formats and select United States.
Go to the administrative tab and click on “copy settings”.
Make sure to check the two boxes at the end and click “OK”.
Just to be sure, open PowerShell and execute the command “get-culture”.
It should say “en-US”.
Step 2: Change the gateway account
You would assume that a reinstall would force the gateway to check your region settings again. Unfortunately this is not the case, uninstalling and reinstalling the gateway does absolutely nothing at all.
The first time that you install the gateway, a new windows user is made. The gateway program creates a generic user without ever telling you. This new user has a certain region setting at the time of creation. Whenever you reinstall the gateway, it will simply use that same generic user as before. That means that the old region settings, which are attached to the generic user, will be reused.
To get the get any sort of new region settings in the gateway, we need to switch users.
Go to services and look for “On-premises data gateway service”. to Formats and select United States.
If you click on it, a pop-up opens up. Go to the “Log On” tab.
Click on local system account.
The gateway made his own generic user, which has stored our original faulty culture settings. The account was named: “PBIEgwService”
By setting it to local system account, we can change to an account with the right culture settings.
Just to be sure, restart the service!
Open the gateway program and go to service settings -> change account.
Now fill in the credentials of your local windows user.
Fill in your mail address and click sign-in.
To keep it simple, just register a new gateway on this computer.
Fill in the name and recovery key and you should be good to go.
Now that your region settings are adjusted to en-US, you should be able to refresh your SAP BW reports in the cloud through the gateway.