Configure PowerBI On-Premise Data Gateway

Configure PowerBI Data Gateway

The on-premises data gateway acts as a bridge to provide quick and secure data transfer between on-premises data (data that isn't in the cloud) and several Microsoft cloud services (in this case PowerBI). There are three different types of data gateways. Detailed information about the each of the gateway and the purposes they serve are described in the microsoft documentation. Here, we will be discussing about two different data gateways that are relevant to us.

1. On-premise Data Gateway (Standard)

Allows multiple users to connect to multiple on-premise data sources. Single installation of Data gateway on a server will allow multiple people to access multiple datasources. This gateway facilitates access to dataset from data source, without needing PowerBI desktop to be up and running after publishing it. It is recommended to use On-premise Data Gateway (Standard) as multiple installations on user desktops is not needed.

2. On-premise Data Gateway (Personal)

Allows one user to connect to source and can't be shared with others. The gateway installation should happen on the instance where PowerBI Desktop for a user is installed.

The process of installing both personal and standard on-premise data gateways is similar with exception of personal gateway being installed on user desktop.

Download and install PowerBI Gateway Standard Mode for standard installation or
Download and install PowerBI Gateway Personal Mode for personal mode installation
After the installation of On-Premise data gateway, open the gateway to set it up.
Enter the email address and sign-in to the PowerBI gateway. Once sign-in is completed, you'll be asked to either migrate an existing gateway or regiser a new one. Select the register gateway option and click next.
Give the Gateway a name and enter the recovery key. Select configure, which completes the gateway installation process.
Once the gateway setup is complete, it will show this dialog box
Follow the steps mentioned in 2nd section of Connect to Amorphic Warehouse using ODBC to establish authentication to the On-premise data gateway.

Note

PowerBI Data Gateway and the instance it is installed on should always be up and running to connect to data sources and to schedule refresh

Configuring PowerBI with Query Engine

Open PowerBI Cloud service, and open Manage Connections and Gateways option.
Confirm that the gateway is registered by selecting On-premies data gateway tab.
Edit the data gateway configuration and update it with following details
Create a connection to the gateway as shown in the following screenshot

PowerBI Desktop Instance setup

Follow the steps mentioned in Connect to Amorphic Warehouse using ODBC to establish authentication with PowerBI desktop. Once the authentication is setup, open PowerBI desktop by double clicking on the app.

Select Get Data option as shown in the image below
A dialog box appears with search space, type Amazon Athena and select the option available on the side and press connect.
Add the DSN in the text box provided, for this ODBC drive it is Simba Athena and click ok.
Select Use Data Source Configuration and the click Connect. A navigator opens up which loads all the datasets that the user has access to.
Click on the Load to load data into PowerBI. Once the data is loaded PowerBI gives us options to create reports using visualization features, with an option to select columns belonging to the dataset for visualization.
Use these options to create a report and save it, then select the option to Publish it.
If you haven't signed into PowerBI yet, then you'll be asked to sign in first before publishing it.
Select the workspace and then publish the report. Once the report is published, it will be accessible from PowerBI cloud, under workspace selected at the time of publication.
Select the dataset shown above, and click schedule refresh as shown below
Configure gateway connection in dataset details with connection, if the status is not running, edit the configuration option on gateway and update gateway details.
Next schedule the refresh based on the need using the Refresh option avaialble as shown.