Linux Server & Power BI Gateway: a practical case

Linux Server & Power BI Gateway: a practical case

Linux Server & Power BI Gateway: a practical case

This time we would like to share with you a practical case of one of our clients, concerning automated dashboards refreshing with Power BI Online Service.

Environment

The customer has, among his data sources, a PostgreSQL Database protected with a Firewall, that can’t be reached through the web.

The customer wants to refresh automatically his dashboards using Power BI service Online, but that data source is not displayed in the Data Sources Scheduling. The only way to do it, keeping of course the firewall active, is to use the Power BI Gateway.

We need to install the Gateway in a machine that can stay on all the time, since the refresh will be done very often (every hour). We decided to use a Server, that in this case is a Linux Machine (Ubuntu).

Step by Step install Windows 10 in the VM on Ubuntu

There are different virtual machines available for Linux/Ubuntu,for this case we adopted  Oracle VM VirtualBox.

  1. Add VirtualBox to Ubuntu repository
    Go to Start > Software & Updates > Other Software > Button ‘Add…’

Enter the following APT line in the input box, substituting by your distribution name (i.e. xenial):

deb https://download.virtualbox.org/virtualbox/debian contrib

Substitute by your distribution name (i.e. xenial). Enter password and close dialog.

2. Download Oracle signature
Download Oracle public key for apt-secure: here for Debian 8 (“Jessie”) / Ubuntu 16.04 (“Xenial”) and later.
You may have to right click on link and select “Save as”.

3. Apply Oracle signature

Go to the terminal and enter the command below:

sudo apt-key add oracle_vbox_2016.asc;

In our case, the file was in Downloads, so we used this command below:

sudo apt-key add ~/Downloads/oracle_vbox_2016.asc;

If everything is fine, you will get this output:

OK

4. Install VirtualBox

Open terminal. Go to Start > Terminal.
sudo apt-get update
sudo apt-get install virtualbox-6.0.14Download Windows 10 ISO image

Visit Microsoft Media Creation Tool from this link.

5. Download “Microsoft Media Creation Tool”.

Go here https://www.microsoft.com/en-us/software-download/windows10

Run the tool from a Windows computer and follow these ISO creation steps:

  1. Accept Terms & Condition
  2. Select ‘Create installation media (USB flash drive, DVD, or ISO file) for another PC.
  3. Select language, architecture and edition.
  4. When asking which media to use, select “ISO file and wait until finished.
  5. When it asks to burn ISO on CD, just click on “Finish”.

Find the newly created “Windows.iso” file and copy or send it to your Linux/Ubuntu computer.

6. Configure Windows 10 on VirtualBox

Open Virtual Box from to Start > Oracle VM VirtualBox.

Click on “New” button.

Name your OS and select the correct OS version (i.e. “Windows 10 (64-bit)”.

Select memory size, i.e. the recommend amount.

Create the virtual hard disk and select hard disk file type, i.e. the proposed.

Select “Dynamically allocated” and accept File Location and size.

Select OS and click on Settings.

Go to “Storage” option and remove any empty optical drive that may exist.

Click on button “Adds new storage attachment” > “Adds optical drive”.

Select “Choose disk”  in the dialog.

Select ISO file download in previous step.

7. Run Windows 10

Select Windows 10 OS from the VirtualBox main screen and click on “Start”.

Enter the Windows registration key (you need to buy a new one if you do not have yet one)

It will be displayed the Windows installation screen, choose i.e. the advanced option of just installing Windows and wait until is finished.

Power BI Gateway Implementation

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 our case PostgreSQL Server. Gateway is an application that can be installed on any servers in the local domain and is responsible for creating the connection and passing data through.

Image result for power bi gateway business

There are basically two different types of gateways, each for a different scenario:

  • On-premises data gateway allows multiple users to connect to multiple on-premises data sources. You can use an on-premises data gateway with all supported services, with a single gateway installation. This gateway is well-suited to complex scenarios with multiple people accessing multiple data sources.
  • On-premises data gateway (personal mode) allows just one user to connect to sources, and can’t be shared with others. An on-premises data gateway (personal mode) can be used only with Power BI.

There are four main steps for using a gateway.

  • Download and install the gateway on a computer (our VM on Ubuntu).
  • Configure the gateway based on the firewall and other network requirements.
  • Add gateway admins who can also manage and administer other network requirements.
  • Troubleshoot the gateway in case of errors.

Microsoft has posted online a very helpful guide on how to install the gateway: https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

and also how to configure, manage and monitor it.