Companies spend a significant amount of resources on managing their data in multiple data sources, Cloud storage, and on-premises. With the wide use of modern applications and most services available online, syncing data from on-premises is still a challenge in many scenarios. Data Visualization and Data Analytics are essential for companies to grow using their data and make smarter business decisions.
Power BI is a Business Intelligence tool from Microsoft that helps the organization create reports, manage live dashboards, analyze business data and generate immersive insights. Power BI Data Gateway allows companies to connect to on-premises data sources such as servers, local systems, databases, etc. that contain essential business data and need to be analyzed.
A Power BI Data Gateway makes it easier for users to directly access data from on-premises without moving it to the Cloud. In this article, you will learn about the steps to set up Power BI Data Gateway and connect a data source to Power BI. Also, read about some of the benefits of using Power BI Data Gateway and how it helps companies perform Data Analytics.
Table of Contents
Prerequisites
- An active Power BI account.
- An active on-premise server.
What is Power BI?
Image Source
Power BI is a Data Visualization and Business Intelligence tool developed by Microsoft. It helps organizations analyze their business data using specialized tools for drilling down into data, generating reports, and gaining insights.
Power BI allows its users to connect to multiple data sources to load data into Power BI and analyze it. With the help of Machine Learning, advanced charts, graphs, KPIs, and other tools, Power BI makes it easier for companies to get valuable information from the data.
Power BI can read data even from web pages, XML, JSON, and CSV to transform multiple sources of data into impressive reports and immersive insights. It offers its services mainly in 3 different parts – Power BI Desktop as a Windows application, Power BI Service for online SaaS service, and Power BI Mobile for mobile devices.
Key Features of Power BI
Some of the main features of Power BI are listed below:
- Custom Visualization: Power BI offers custom visualization libraries support that allows users to visualize complex data with ease.
- AI Assistance: Power BI makes it easier for users to perform Data Analytics using Artificial Intelligence. Users prepare data, build Machine Learning models, and gain insights from structured and unstructured data.
- Modeling View: Users can slice and divide complex data into simpler forms to understand better, separate diagrams and multi-select objects.
- Report Sharing: Users can easily share their reports within teams, or organizations without worrying about security.
To know more about Power BI, click here.
Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Power BI, Databases, SaaS applications, Cloud Storage, SDKs, and Streaming Services and simplifies the ETL process. It supports 100+ data sources and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.
Get Started with Hevo for Free
Its completely automated Data Pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.
Check out why Hevo is the Best:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
What is Power BI Data Gateway?
The On-premises Data Gateway acts as a bridge between on-premises data and various Microsoft Cloud services to deliver fast, reliable, and secure data transfer. Power BI is one of the widely used Microsoft Cloud services that can easily connect with on-premises data sources to provide insights on the data stored in these on-premises and visualize it.
With the help of Power BI Data Gateway Integration, companies can easily keep Databases and other data sources on their on-premises networks, yet securely use that on-premises data in cloud services.
Types of Data Gateways
There are 3 different types of Data Gateways and each is used for a different scenario. The different types of Data Gateways are listed below:
- On-premises Data Gateway: This allows multiple users to easily connect to more than one on-premises data source. Once you install the Data Gateway, you can use it to connect to Power BI and other Microsoft Cloud services such as PowerApps, Power Automate, Azure Analysis Services, and Azure Logic Apps.
- On-premises Data Gateway (Personal Mode): This version allows a single user to connect to data sources but cannot be shared with others. An On-premises Data Gateway (personal mode) can be used only with Power BI because it is designed to give access to only one person who creates reports and doesn’t require sharing any data.
- Virtual Network Data Gateway: Using this version, users can connect to multiple data sources through secure virtual networks. As it is managed by Microsoft, users don’t need to install it. It is best suited for handling complex cases where multiple users need to access multiple data sources.
Data Gateway Architecture
Power BI Gateway acts as a mediating channel between your on-premises data sources and your on-cloud Power BI services. This gateway is a Windows application that has to be installed on the same network in which your data sources reside. Only admins can install and configure this gateway.
Image Source: Microsoft Docs
Power BI Gateway processes your data from on-premises data sources to the cloud using the following steps:
- Power BI Cloud Service generates a query and encrypts credentials for on-premise data sources like SQL Server, SQL Server Analysis Services, and other files (bottom of the diagram). This data is subsequently sent to the Gateway Cloud Service for additional processing.
- The Gateway Cloud Service processes the query and sends it to Azure Service Bus Messaging.
- The pending requests are sent to the gateway by Azure Service Bus. Both the gateway and the Power BI service are configured to allow only TLS 1.2 communication.
- The gateway receives the query, decrypts the credentials, and uses those credentials to connect to one or more data sources.
- The gateway forwards the query to the data source for execution.
- The data source sends the findings back to the gateway, which then sends them to the cloud service. The results are then used by the service.
How to Set Up A Power BI Data Gateway?
Now that you have understood Power BI, In this section, you will learn about the steps to set up the Power BI Data Gateway. First, you will install the Data Gateway and then connect it to Power BI. The following steps to set up Power BI Data Gateway are listed below:
Step 1: Installing a Data Gateway
- First, you have to download the Power BI Data Gateway from here.
- While installing the Power BI Data Gateway, keep the installation path as default.
- Then, click on the “Install” button, as shown in the image below.
Image Source
- Now, provide the valid Office 365 organization account details and then click on the “Sign In” button, as shown in the image below.
Image Source
- Once you signed in to your account.
- Next, click on the “Register a new gateway on this computer” and then click on the “Next” button, as shown in the image below.
Image Source
- Now it will ask you to register for the Power BI Data Gateway account.
- Enter the name of the new On-Premise Data Gateway and a strong recovery key, as shown in the image below.
Image Source
- You can also change the region that will connect your Power BI Data Gateway to the cloud services.
- By default, Azure Relays are automatically provisioned, but you can also provide your Azure Relays.
- Then, click on the “Configure” button.
- Now, review the information and then click on the “Close” button, as shown in the image below.
Image Source
Step 2: Adding Another Gateway to Create Cluster
- To ensure data availability all the time, adding a cluster allows Gateway admins to route the data requests to the secondary Gateway if the primary Gateway fails.
- For this, you need another computer to install standard Gateway because you want to create redundancy in the cluster.
- Download and install On-Premises Power BI Data Gateway on a new computer similarly as done in step 1.
- While providing details for the Data Gateway, check the “Add to an existing cluster” box, as shown in the image below.
Image Source
- Now, from the “Add to an existing cluster” list, select the primary Gateway that you have installed in step 1 or the previous computer.
- Enter the valid recovery key and click on the “Configure” button.
Step 3: Connecting Power BI Data Gateway
- Login to your Power BI account in the web browser.
- Here, click on the “Settings” option located at the top right corner of the page header, as shown in the image below.
Image Source
- Select the “Manage gateways” option.
- It will open up the Gateways Cluster and Data Sources settings.
- Here, you can see the cluster name already available that you added in Data Gateway because you are using the same organization ID.
- Select the Gateway and then click on the “ADD DATA SOURCE” option, as shown in the image below.
Image Source
- Provide the name of your data source and then from the “ADD DATA SOURCE” drop-down list, select the type of data source you are adding. For this Power BI Data Gateway, SQL Server is used, as shown in the image below.
Image Source
- Now, enter the “Server” and “Database” details for the data source you want to add to Power BI Data Gateway, as shown in the image below.
Image Source
- Next, select an “Authentication Method” for connecting to the data source and then enter the valid credentials for the data source, as shown in the image below.
Image Source
- Also under the “Advanced settings” option, you could configure Single Sign-On (SSO) for the data source, as shown in the image below.
Image Source
- You can also optionally configure the privacy level for the data source, as shown in the image below.
Image Source
- Click on the “Add” button to add the data source to Power BI Data Gateway. You will see the “Connection Successful” message, as shown in the image below.
Image Source
- Now you can use this data source to load data from the server into your Power BI for creating reports and analyzing.
That’s it! You have successfully set up Power BI Data Gateway.
Benefits of a Power BI Data Gateway
A few benefits of using Power BI Data Gateway are listed below:
- Power BI Data Gateway helps companies keep their dashboards and reports up to date by connecting to on-premises data sources.
- Power BI Data Gateway directly gives access to data without any need to move huge volumes of datasets to online Cloud storage for Data Analysis in Power BI.
- Power BI Data Gateway enables a secure and reliable connection between Power BI and local data sources and keeps the data safe on on-premises systems.
Conclusion
In this article, you learned about Power BI, what is Power BI Data Gateway and why it is used for syncing data from on-premises data sources to Power BI. Also, you went through the steps to install and configure Power BI Data Gateway. Power BI Data Gateway is a secure connection between on-premises and Power BI that help users create reports and keep dashboards up to date with fresh data from on-premises.
Visit our Website to Explore Hevo
Companies need to analyze their business data stored in multiple data sources. The data needs to be loaded to the Data Warehouse to get a holistic view of the data. Hevo Data is a No-code Data Pipeline solution that helps to transfer data from 100+ data sources such as Power BI to desired Data Warehouse. It fully automates the process of transforming and transferring data to a destination without writing a single line of code.
Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.
Share your experience of learning about Power BI Data Gateway in the comments section below!
Aditya has a keen interest in data science and is passionate about data, software architecture, and writing technical content. He has experience writing around 100 articles on data science.