In a previous OptimalBI project, we had to access a MySQL database on a remote server from Microsoft Power BI to provide some insights on the key tables. However, the database was configured to not allow direct access from remote servers for security reasons. To access the database we used an SSH tunnel; a secure way to access a computer over an unsecured network. SSH tunnels allow connections made to a local port to be forwarded to a remote machine via a secure channel.

Here are the steps we followed to establish this connection.

• Install PuTTY if you haven’t already. It is pretty straightforward to download and install using the installer. Once you have this set up on your local computer you are ready to set up an SSH tunnel.
• Open PuTTY, in the “Category” pane go to “Session” and enter your domain host name and port.
• Make sure to select the connection type as SSH.
• Then go to the section Connection -> SSH -> AUTH and upload your private key file for authentication.
• To setup the tunnel, go to SSH -> Tunnels and enter your local port to forward to and remote address as destination.
• Make sure that the Local and Auto radio buttons and checked and click add.
• Enter your username and hit Enter. This should show you a terminal indicating a successful connection.

Once a successful connection has been established you can access the database from Power BI desktop by going to localhost:3306 (the local port number mentioned in previous steps) and enter the database name.

How to refresh data from Power BI service.

This section describes the data refresh options available for the on-premise data source. Once you have all the reports ready and published to Power BI service there is no option to refresh the data directly from Power BI service as there is no connection between the reports running online and the database sitting on remote server. For this you will need to install and set up an on premise data gateway which you can install from the below link.

https://powerbi.microsoft.com/en-us/gateway/

Run the installer and select appropriate gateway installation type. There are two options,

• Enterprise mode that can be shared and reused by multiple users
• Personal mode that can only be used by you and Power BI

Once you have the installation completed go to Power BI service and go to Settings-> Manage gateways on the top right corner and create new data source.

Then go to Power BI service -> Settings -> Datasets and go to gateway connection and map the dataset to the right gateway connection.

The data is ready to be refreshed from Power BI service now. To schedule a refresh turn on the “Schedule refresh option and choose daily or weekly refresh as required.