IT2EDU Empowering Education Through Technology
First, I would like to thank you for visiting my blog. In this blog post I am going to discuss about Power BI data Gateway Connection and Cloud Data Integration. Before jump into the article lets have look at what is gateway in Power BI. Now in this example using On-Prem database.
Please be aware that this article does not serve as a beginner’s guide to Power BI. It assumes a foundational understanding of using Power BI Desktop and familiarity with the Power BI service.
Power BI Gateway is a bridge that facilitates secure and efficient data transfer between on-premises data sources and the Power BI service in the cloud. It allows Power BI to access and refresh data from sources like databases, files, and other applications that are located within an organization’s network.
There are two main types of Power BI Gateway:Cloud Data Integration
On-premises data gateway: This type of gateway enables Power BI to connect with on-premises data sources. It’s used when the data that Power BI needs to access resides within an organization’s internal network. It ensures secure communication between the on-premises data sources and the Power BI service in the cloud. The live data refresh in power bi is done though Gateways.
Personal data gateway: Formerly known as the Power BI Gateway – Personal, this gateway is designed for individuals or small-scale usage. It allows a single user to connect to on-premises data sources from their own computer. It’s suitable for scenarios where a single user needs to access and refresh data within Power BI.
Both types of gateways facilitate a secure and seamless flow of data between on-premises sources and the Power BI service, enabling users to create reports, dashboards, and visualizations using up-to-date information from various data sources.
Power BI Data Gateway serves as a crucial link between on-premises data sources and the cloud-based Power BI service. This gateway, operating as a Windows application, must be installed within the same network as the relevant data sources. Examining the diagram below illustrates the multi-step process involved in querying through the data gateway, showcasing a clear architectural distinction between cloud services and on-premise data sources.
Let’s delve into the step-by-step procedure of data processing utilizing the data gateway:
- Cloud Data Model: Cloud services such as Power BI Service, Power Automate, or Azure Analysis Services encapsulate the data model and necessary queries for data refresh.
- Encryption by Power BI Cloud Service: The Power BI Cloud Service encrypts queries and credentials for enhanced security.
- Passing to Gateway Cloud Service: Encrypted information is then transmitted to the Gateway Cloud Service.
- Analysis and Queueing: The Gateway Cloud Service analyzes the query and places it into the Azure Service Bus queue for subsequent processing.
- Azure Service Bus Handling: The Azure Service Bus is responsible for forwarding encrypted queries to the locally installed Application Gateway.
- Decryption and Connection: Upon receiving the information, the Application Gateway decrypts source credentials, connects to specified data sources via the connection string, and executes the query.
- Query Execution: The Application Gateway executes the query on the data source, and the results are sent back.
- Result Transmission: As the Application Gateway receives data from the data source, it relays the information back to the Power BI Cloud Service.
- Processing Update: The Power BI Cloud Service then processes the results, updating visuals and datasets, ensuring a live data refresh.
- Update to Visual: Once the data got processed, then it send to Visuals.
This intricate process illustrates the seamless cloud data integration, live data refresh, and data connectivity achieved through the Power BI Data Gateway.
Installation of Gateway: Cloud Data Integration
Dear visitors, in the provided screenshots are may vary from version to version of Power Bi because Microsoft releases updates frequently on Power BI and services.
To install Gateway, download the gateway software from the Portal. Top right corner click down arrow symbol and choose Data Gateway from the dropdown.
Now This page redirects to Download Page in Power BI portal and choose the preferred. Standard or Personal mode.
Minimum requirements for Installing Data Gateway are Microsoft recommend that you start with a machine with at least 8 CPU cores, 8 GB of RAM, and multiple Gigabit network adapters.
I downloaded standard mode data gateway and installed in my machine.
Click Install and add you Email in the sign which connects to Power BI account and click signin.
Now select Register Gateway and click on Next.
Provide a name for Registered Data Gateway and if needed provide Recovery key, the recovery key is for future use if we want to recover the gateway when lost. Now the Gateway is ready.
To see the data gateway in Power BI service go to app.powerbi.com and click on setting and go to Manage connections and Gateway option.
In the section On-Premises Data Gateway our created gate way is appeared like above.
The similar way we will install Personal mode data gateway. To install Personal mode data gateway download data gateway from the portal by choose personal mode and install it.
Sign in by using the Power Bi service account email and click next.
After installing the data gateway, go to Power BI service and check the created data gateways in the list.
To demonstrate or check the Data gateway is working or not. Here I created a simple power bi report which is using AdventureWorksDW2019 database downloaded from Microsoft portal. In the below report it shows 606 total records.
Now i will check count of records in SSMS.
If we observe both are showing same record count. Now we configure installed data gateway to the above power bi report after publish the report in power bi service.
Configure the database to the data gateway and apply.
Once the Gateway is applied, for testing insert a record into the table in SSMS by executing the following command.
insert into [dbo].[DimProduct]
(ProductAlternateKey, EnglishProductName,SpanishProductName,FrenchProductName,FinishedGoodsFlag,Color,Class)
Values ('It2-edu','Ebook','Ebook-Spanish','Ebook-French',1,'Green','L')
Then go to Power bi service, then click on refresh for the respective report.
Now the data got refreshed, and check the log for how many times the data got refreshed in service by using Data Gateway.
The report is showing two time the data got refreshed. Check the report now in the power bi service or online.
I committed these file in my Github, if needed downloaded from there here is the url of my github.
Thank you for visiting my blog. If you are interested to write and publish your article please visit Guest Post section in this blog and post without Plagiarism. Please click here for post your article
If you seek any help on this please use my contact us page.
Read the related article from Microsoft.
