Power BI Data Gateway : Connecting Different Data Sources

Published:22 September 2022 - 9 min. read

Meet Active Directory and Windows Server auditing, security and compliance needs with ManageEngine ADAudit Plus. Download Free Trial!

If you are stressing over data analysis and manually carrying out query statements or manual imports, why not consider using the Power BI Data Gateway? With Power BI Data Gateway, you can simplify and virtualize data analysis.

In this tutorial, you will learn how to set up data sources seamlessly and efficiently using Azure SQL Server and On-premises data gateway.

Improve your day-to-day data visualization by connecting sources to your Power BI Data Gateway!

Prerequisites

This tutorial will be a hands-on demonstration. To follow along, be sure you have the following:

  • Power BI installed on your local machine, the version in this tutorial is 2.97.

Connecting On-premises to Power Bi Data Gateway

On-premises data gateway bridges the gap between Power-Bi and cloud-related apps or software, creating a path where you can securely move your data from one end to another. Although the gateway is not responsible for transferring data, the gateway creates a server or a medium to transfer your data.

Before connecting the on-premises data gateway to Power BI, you must install the standard gateway on your local machine.

Check the requirements for On-Premises Gateway installation.

1. Download the standard gateway file, and open the installer once downloaded.

Installing On-premises data gateway
Installing On-premises data gateway

2. Next, provide the email address you want to connect to the gateway and click Sign In. Ensure the email address is the same as the one connected to your Power Bi account.

Providing an email address to use with the on-premises data gateway
Providing an email address to use with the on-premises data gateway

3. Once signed in, choose the Register a new gateway on this computer option, and click Next. This option creates a new gateway on the On-premises data gateway.

Choosing to register a new gateway
Choosing to register a new gateway

4. Now, configure the on-premises data gateway with the following:

  • Provide an on-premises data gateway name.

Optionally, tick the Add to an existing gateway cluster box to add the data gateway to an existing cluster.

  • Provide, and confirm a Recovery key that consists of a minimum of eight characters. This key is stored and used to verify your ownership when you lose access to the on-premises gateway.
  • Click Configure to save your settings and set up your created on-premises data gateway.
Configuring the new on-premises data gateway
Configuring the new on-premises data gateway

Below, you can see a page that confirms your gateway has been configured and is ready for use.

Confirming the gateway configuration is successful
Confirming the gateway configuration is successful

5. Open your Power BI app or workspace, click on the ellipsis (three dots) on the top-right → Settings → Manage gateways to access all your connected gateways or data sources.

Connecting to Power BI
Connecting to Power BI

6. Click the ellipsis next to your data gateway’s name and ensure the gateway is enabled from the blade that opens to the right.

The Enabled status, as shown below, indicates the gateway is active and online.

Verifying successful connection of on-premises gateway on Power BI
Verifying successful connection of on-premises gateway on Power BI

7. Next, click on the ellipsis again beside the data gateway and choose Settings to configure your gateway cluster settings.

Configuring the gateway cluster
Configuring the gateway cluster

8. Configure the settings below, but leave the Description and Department fields empty.

Configuring the settings for the On-premises data gateway
Configuring the settings for the On-premises data gateway

9. Lastly, tick all the boxes, and click Save to save the settings, and you have successfully connected the on-premises data gateway source to your Power BI account.

Now, you can easily send data through this source using SQL Server, Azure, or AWS data sources.

Settings Page for Configured On-premises data gateways on Power Bi
Settings Page for Configured On-premises data gateways on Power Bi

Connecting Azure SQL Database to Power BI

By connecting Azure and Power BI, you can perform data processing into analytics and report that provides real-time insight into any business or company. They both can develop complex business and intellectual solutions using built-in connectivity.

Don’t worry. You don’t need to write code to connect Azure tools and software to Power BI.

1. On the Azure Portal, search for Azure SQL and select the Azure SQL service that comes up to access Azure SQL.

Searching for Azure SQL
Searching for Azure SQL

2. On the Azure SQL page, click Create Azure SQL resource, which opens a configuration setting for the Azure SQL resource page.

Creating a resource for Azure SQL
Creating a resource for Azure SQL

3. Click Create under SQL databases to create a single database.

Choosing a database type
Choosing a database type

4. Provide a descriptive Database name, and click on the Create new link to create a new server first.

Creating an SQL database
Creating an SQL database

5. Input a unique server name, and select a location nearest to you.

Creating server for SQL database
Creating server for SQL database

6. Scroll down to the Authentication section, and configure the authentication with the following:

  • Authentication method – Choose the Use SQL authentication.
  • Server admin login, Password, Confirm password – Provide an admin name (server-admin) and password.
  • Click OK to create SQL admin details for our SQL database.
Creating a SQL database admin details
Creating a SQL database admin details

7. Back to the database settings, select the created server as the server, leave other options on default, and click Review + Create.

Your browser redirects to a page where you can review the configured settings for the database (step eight).

Confirming configured database settings
Confirming configured database settings

8. Review the configured settings for your database and click Create to create the SQL database, which takes some time to complete.

Once created, your browser redirects to a page where you can see the deployment status (step nine).

Confirming SQL database details
Confirming SQL database details

9. After successful deployment, click Go to resource to access the newly-created resource.

Accessing the newly-created resource
Accessing the newly-created resource

10. Once the resource’s dashboard, click  Compute + Storage under the Settings section (left panel) to initiate configuring your SQL server storage settings.

Accessing the SQL database settings
Accessing the SQL database settings

11. Now, select the Serverless option to make your database auto-scale and cost less.

Settings the tiers for SQL database
Settings the tiers for SQL database

12. Drag the slider to minimize the data size, as shown below, and click Apply to save the settings. This tutorial uses 15GB for demonstration.

Changing data size
Changing data size

13. Back to the resource’s dashboard, click on Set server firewall to initiate setting up a firewall to access your data from the Power BI desktop and visualize your data.

Accessing firewall on SQL database
Accessing firewall on SQL database

14. Under the Public access tab, click Selected networks to add an IP address.

Adding a network to access the SQL database
Adding a network to access the SQL database

15. Next, scroll down to the Firewall rules section, click on Add your client IPv4 address or set the start IP address and end IP address to 0.0.0.0.

Once you’ve added your client IP address, click Save to save the configuration.

Adding a client IPv4 to access the SQL database
Adding a client IPv4 to access the SQL database

Once done, you will see the confirmation message shown below.

Verifying firewall configuration success
Verifying firewall configuration success

Creating Data in Azure SQL Database to Visualize

You now have an Azure SQL database and a server, and you’re ready to dig into creating data or querying the database to visualize on Power BI. The Azure SQL server offers a query editor that lets you write queries, create tables, and add data to created tables.

To start creating data in Azure SQL database:

1. On your resource’s dashboard, click Query editor to add a query.

Adding data to the SQL database
Adding data to the SQL database

2. When prompted, provide the SQL admin details you set in step six of the “Connecting Azure SQL Database to Power BI” section, and click OK to sign in.

Signing into SQL query editor
Signing into SQL query editor

Once signed in, you will see a query editor like the one below.

Executing the query

3. Copy and paste the following query to the SQL query editor, which creates four tables named Person, Student, Course, and Credit.

-- Create a User Table

CREATE TABLE Person
(
	PersonId INT IDENTITY PRIMARY KEY,
	FirstName NVARCHAR (128) NOT NULL,
	MiddleInitial NVARCHAR(10),
	LastName NVARCHAR(128) NOT NULL,
	DateOfBirth Date NOT NULL,
)

-- Create Student Table

CREATE TABLE Student
(
	StudentId INT IDENTITY PRIMARY KEY,
	PersonId INT REFERENCES Person (PersonId),
	Email NVARCHAR(256),
)

-- Create a Course Table

CREATE TABLE Course
(
	CourseId INT IDENTITY PRIMARY KEY,
	Name NVARCHAR(50) NOT NULL,
	Teacher NVARCHAR (256) NOT NULL,
)

-- Create Credit Table

CREATE TABLE Credit
(
	StudentId INT REFERENCES Student (StudentId),
	CourseId INT REFERENCES Course (CourseId),
	Grade DECIMAL(5,2) CHECK (Grade <= 100),
	Attempt TINYINT,
	CONSTRAINT [UQ_studentgrades] UNIQUE CLUSTERED
		(
		StudentId, CourseId, Grade, Attempt
		)
)

4. Now, click on Run to execute the query.

Executing the query
Executing the query

5. Click OK to close and continue querying when prompted with the certificate associated with your Azure account, as shown below.

Confirming certificate
Confirming certificate

6. Next, click Run again to execute the query in the editor. Once successful, you will see the Query succeeded message at the bottom of the query editor section.

You can see the tables created after running the query on the left panel.

Confirming SQL query
Confirming SQL query

7. Select the dbo.Course table, and run the following query, which returns 100 records.

SELECT TOP (100) * FROM [dbo].[Course];
Accessing the Tables folder on the SQL database
Accessing the Tables folder on the SQL database

8. Now, select the dbo.Course under Tables (left panel), click the ellipsis (three dots) → Edit Data (Preview) to access the table’s data.

Creating a new row
Creating a new row

9. Finally, click Create New Row to add data to the table, and click on the Save button to save the new data.

Adding data to the dbo.Course table
Adding data to the dbo.Course table

Visualizing Data from Azure SQL Database to Power BI

What better ways to achieve data analysis? Visualizing data from Azure SQL database to Power BI will surely pique your interest.

To visualize your data on Power BI:

1. Click Power BI on your Azure Portal’s overview page → Get started, and a .pbids file will be downloaded.

Double-click on the downloaded .pbids file to open it on your Power BI desktop.

Connecting to Power BI
Connecting to Power BI

2. Next, select Database (left panel), provide your SQL admin and password and click Connect to connect to your SQL Azure database.

Once connected, a new page pops up to see the data on your Azure SQL database (step three).

Connecting to SQL database
Connecting to SQL database

3. Tick the box of the Course table, and click Load to load the table’s data on Power BI.

Loading data of the Course table
Loading data of the Course table

You can find your loaded data under the Fields section on the right side of your Power Bi desktop.

4. Finally, select all columns on the Course table under the Fields section, and a visual pop-up showing the selected table’s data, as shown below.

Visualizing selected data
Visualizing selected data

Perhaps you like to change how to visualize your data. If so, select a visual type (like a Pie chart) under the Visualizations panel, as shown below.

Changing data visualization
Changing data visualization

Conclusion

Power BI has more than twenty data sources. But with this tutorial, connecting your Azure data has never been easier. At this point, you can now quickly send your data from SQL Server using the on-premises data gateway to Power BI.

At this point, you never have to worry about visualizing data from Azure or SQL servers. Besides using Power BI Data Gateway for businesses, will you consider this tool beautifully visualizing data for your IT monitoring tools?

Hate ads? Want to support the writer? Get many of our tutorials packaged as an ATA Guidebook.

Explore ATA Guidebooks

Looks like you're offline!