On-premises data gateway

Power BI MCSA Certification Training Tips – Part 14: On-premises Data Gateway

To obtain your MCSA: BI Reporting you need to pass two exams: 70-778 (Analyzing and Visualizing Data with Power BI) and 70-779 (Analyzing and Visualizing Data with Excel). However, there is a huge overlap between the two exams resulting from the common BI features found in both Power BI and Excel: namely data modelling using the DAX language; and data connection and transformation using the M language.

We, at G Com Solutions, recommend to the clients to whom we supply Power BI Training that they prepare for both exams simultaneously and then sit both exams on the same day. We also run a 5 day Power BI MCSA Certification Prep course in London which includes both exams 70-778 and 70-779.

In this series of blog posts, we will be examining some of the key features of Power BI, Power Pivot and Power Query which you will need to master in order to pass the two exams.

In this topic, which applies only exam 70-778, we will examine the uses of an on-premises data gateway.

1. Gateway Configuration

Key capabilities of a data gateway

Scheduled refreshing of data imported from on-premises sources.

Access data using DirectQuery mode.

Access on-premises Analysis Services data using live connection mode.

Can be used by Power BI; but also, PowerApps, Flow, Azure Analysis Services, and Azure Logic apps.

Multiple on-premises data gateways can be registered for the same user.

Gateways can be configured in high-availability clusters.

This is the recommended gateway installation for enterprise use.

The first step is to download the software. In the Power BI service, click Download > Data Gateway.

This link takes you to Microsoft’s site where you can download the latest version of the software.

 

 

Double-click and run the downloaded file.

Click “Next” to start the on-premises data gateway installer wizard.

 

 

Leave the gateway type set to “on-premises” data gateway and click “Next”.

After the installation completes, you need to sign into your Power BI tenant using your regular credentials.

 

 

Next, enter a name for the gateway and a recovery key (twice), then click “Configure”.

The gateway is now configured and ready to use. (The next step is to add data sources.)

 

2. Adding a Gateway to a Cluster

To add a gateway to an existing cluster, you begin the install as outlined in the previous section.

However, when you come to name the gateway, you activate the option “Add to an existing gateway cluster.”

To identify the cluster, you must then enter the recovery key that you chose when creating the cluster. Then click “Configure”.

Your “backup” gateway is now configured and will be used whenever the first gateway in the cluster is unavailable.

c

We can test our clusters by deliberately disabling the first gateway in the cluster. To do this, we simply stop the on premises data gateway service.

Then in gateway management (Settings > Manage Gateways), when we refresh our browser, note the cluster is still available.

 

3. Adding a SQL Server Data Source

In this section, we will be comparing the role of a data gateway plays when connecting to SQL Server in Import and DirectQuery modes. So, we begin with a report in each mode.

We publish both reports into the Power BI service.

Next, we move to the Power BI service and compare the behaviour of the two reports.

The report which is in Import mode works fine, since all the data required by visuals is present in the data model. (The gateway role, here, is refreshing the imported data.)

However, the report which is in DirectQuery mode cannot be displayed until its data source has been added to a data gateway. (This time, the gateway is essential from the outset.)

To access the gateway in the Power BI service, we click the Settings button (the cog icon) and choose “Manage Gateways”.

Then, in the top left, we click on “Add Data Source”.

Let us name our new data source “Event Sales”.

 

 

And specify the “Data Source Type” as SQL server.

Next enter the server credentials: Server name, Database, Windows Authentication, user name (formatted as DOMAIN\USER) and the password you use to log into your Windows computer. Then, click the “Add” button.

Next, we need to configure our datasets to use the data source we have just added. Choose Settings from the ellipsis menu next to the name of the DirectQuery dataset.

Expand “Gateway Connection” and activate the option to “Use a data gateway”. Then choose the data source which we just created (“Event Sales”) from the “Maps to” dropdown. Then click the “Apply” button.

Now, let us return to our DirectQuery mode report to see the effect of configuring a gateway.

Now that the Power BI service has a mechanism for connecting to the on-premises database, we can see and interact with the report.

Now, let us configure the Import dataset. (And, this time, our focus will be to keep the imported data refreshed.)

First, we choose “Settings” from the ellipsis menu.

Again, we choose the data source which we just created (“Event Sales”) from the “Maps to” dropdown. Then click the “Apply” button.

However, this time we also need to set up a scheduled refresh. So, we activate “Scheduled refresh”, choose a frequency (e.g. “Daily”), specify the time zone and click “Add another time”.

Summary

SQL Server Import Mode

If the data is located on premises, an on premises data gateway must be configured.

The gateway is used to perform a scheduled refresh of the data in the published report.

Both reports and dashboards are refreshed via the schedule.

SQL Server DirectQuery Mode

If the data is located on premises, an on premises data gateway must be configured.

The gateway connects to the server and queries the database in real time; so, reports are constantly refreshed.