On-Premises Data Gateway in Power BI: A Complete Guide (2024)

Home > Blog > Data Visualization > Add-ons > Power BI >

Ah, the data gateway in Power BI.

It sounds like something out of a sci-fi movie, doesn’t it?

But, no, it’s not a gate to another dimension. Nor is it a portal to the underworld. It’s much more exciting than that. It’s the magical bridge that connects your Power BI with the promised land of data.

Like a busy bee, it buzzes back and forth between on-premises data and the cloud. It’s that reliable postman who always delivers on time, ensuring your reports and dashboards are always up-to-date. Not all heroes wear capes, you know.

So, why do we use it, you ask?

Picture this: your data is held hostage in your on-premises servers, yearning for the freedom of the cloud.

Who are you going to call?

Data gateway! This data superhero will liberate your data and make it accessible to Power BI in no time.

Brilliant, isn’t it?

So, grab your adventurer’s hat, and let’s delve into the mysterious world of the Power BI data gateway.

Table of Content:

What is an On-Premises Data Gateway in Power BI?
Types of Power BI Gateway
Set Up and Use a Data Gateway in Power BI
A Step-by-Step Guide For Crafting an Effective Dashboard
Benefits of Using an On-Premises Data Gateway in Power BI?
Wrap Up

What is an On-Premises Data Gateway in Power BI?

Definition: An on-premises data gateway in Power BI is like a secure bridge. It connects Power BI to data stored in various places, like on-premises servers or cloud platforms. This allows Power BI to access, analyze, and update the data, ensuring reports and dashboards are always current. The data gateway also ensures data security by encrypting the connection and managing access to the data sources.

Types of Power BI Gateway

In the context of Power BI, there are two main types of gateways: the On-Premises Data Gateway and the Power BI Gateway for Personal Use. Here’s a brief overview of each:

On-Premises Data Gateway in Power BI

Standard Mode: This mode allows Power BI to connect to on-premises data sources, such as databases, without exposing them directly to the internet. It supports scheduled data refreshes, live queries, and high availability.
Personal Mode: This mode is suitable for individual users or scenarios where there is no need for centralized administration. It allows a single user to connect Power BI to on-premises data sources from their machine.

Data Gateway in Power BI Gateway for Personal Use

This gateway is designed for individual users or small-scale scenarios where sharing and collaboration are not the primary focus. It enables a single user to refresh data from on-premises data sources for their use.

How to Set Up and Use an On-Premises Data Gateway in Power BI?

Setting Up the On-Premises Data Gateway in Power BI:

Follow these steps to set up a data gateway in Power BI:

Download and install:Download the Power BI data gateway installer from the official Microsoft website. Once downloaded, open the installer as an administrator.
Installation:During the installation process, select the “On-premises data gateway” option. Follow the prompts to complete the installation on your Windows PC or Server.
Configuration:After installation, the Power BI Gateway Configuration Manager will launch. Sign in with your Power BI account to register the gateway with your Power BI Service.
Sign-in:The gateway configuration process may prompt you to sign in with your Power BI credentials. This connects the gateway to your Power BI account.
Add data source:Choose the “Add data source” option. Provide the desired Gateway cluster name and the connection details for the data source you want to add.
Data source settings: Configure the data source settings to ensure the gateway can access and retrieve data for building your data model from it.
Data source permissions: Specify who can access this data source by setting up permissions. You can grant permissions to individuals or groups in your organization.

Using the On-Premises Data Gateway in Power BI:

Data source connection: Start by creating or opening a report in Power BI Desktop. Then, connect to your desired data source (database, file, cloud service, etc.) and build your data model. Make sure to set up your queries and data transformations as needed.
Data gateway selection:Select the data gateway you’ve previously set up when publishing your report to the Power BI Service. This ensures the data in your report will refresh using the designated gateway.
Query data:Continue to query and manipulate your data as required in Power BI Desktop. Your report will be designed with these data connections in mind.
Report building:Design your reports and dashboards in Power BI using the data you’ve imported. Visualize the data in a way that makes sense for your analysis.
Publish to Power BI Service:Once your report is ready, publish it to the Power BI Service. Select the dataset that uses the data gateway during the publishing process.
Data refresh:The real magic happens here. The data gateway ensures your data remains up-to-date. Schedule regular data refreshes to keep your reports and dashboards current. The gateway will connect to your data source and fetch the latest data.
Collaboration:Share your reports with colleagues, teams, or the entire organization through the Power BI Service. Collaborators can access and interact with the reports, all powered by the data gateway.
Monitoring:Keep an eye on the performance and health of your data gateway. The Power BI Service provides monitoring and logging features to ensure your data connections work smoothly.

A Step-by-Step Guide For Crafting an Effective Dashboard

We shall break down the process into five stages.

Stage 1: Logging in to Power BI

Log in to Power BI.
Enter your email. Click the “Submit” button.

You are redirected to your Microsoft account.
Enter your password and click “Sign in“.

You can choose whether to stay signed in.

- Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Sankey Chart

Go to the left-side menu and click the “Create” button.
Select “Paste or manually enter data“.

We’ll use the following cash flow data for this example.

Total Cost	Company Type	Company Name	Expertise Categories	Expertise	Cost
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Plumbing & Heating	15456
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Mechanical Work	10159
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Plumbing & Heating	18045
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Mechanical Work	12695
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Plumbing & Heating	14589
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Welding	11456
Total Cost	Supplier	Power-up Builders	Raw Material	Cement	20561
Total Cost	Supplier	Power-up Builders	Raw Material	Steel	32456
Total Cost	Supplier	Five-star Construction	Raw Material	Bricks	10253
Total Cost	Supplier	Five-star Construction	Raw Material	Timber	9000

Paste the above data table in the Power Query Window.
Select the “Create a dataset only” option.

On the left-side menu, click “Data Hub“.
Power BI populates the data set list. (If you have not created a data set, refer to the Error! Reference source not found section).

Choose a data set for the Sankey chart.
PBI populates the screen as shown below:

Click on the “Create a report” dropdown.
Select “Start from scratch“.

A Report Canvas screen appears as below:

Stage 3: Adding the Power BI Sankey Diagram Extension by ChartExpo

Creating the Sankey Diagram requires us to use an add-in or Power BI visual from AppSource.
Navigate to the right side of the Power BI dashboard.
Open the Power BI Visualizations panel.
Click the ellipsis symbol (…) as highlighted in the diagram below. This will import the Power BI Sankey Diagram extension by ChartExpo.

Stage 4: Drawing a Sankey Diagram with ChartExpo’s Power BI extension

Select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.
The following window opens in the report section of your dashboard:

You can resize the visual as needed.
Navigate to the right side of your Power BI dashboard.
You should see “Fields” next to “Visualizations“.

You’ll select the fields to use in your Sankey chart here.
The ChartExpo visual needs to be selected, though. Select the field in the following sequence:
- Total Cost
- Company Type
- Company Name
- Expertise Categories
- Expertise
- Cost

You’ll be asked for a ChartExpo license key or email address.

Stage 5: Activate your ChartExpo Trial or Apply a Subscription Key

Select the ChartExpo visual.
You should see three icons below “Build Visual” in the Visualizations panel.

Select the middle icon, “Format visual“.
The visual properties will be populated as shown below.

If you are a new user,
- Type in your email under the section titled “Trial Mode”.
- This should be the email address that you used to subscribe to the ChartExpo add-in. It is where your ChartExpo license key will be sent.
- Ensure that your email address is valid.
- Click “Enable Trial.” You’ll get a 7-day trial.

You should receive a welcome email from ChartExpo.
If you do not find the email in your inbox, kindly check your spam folder.
The Sankey Diagram you create under the 7-day trial contains the ChartExpo watermark.

If you have obtained a license key:
- Enter your license key in the “ChartExpo License Key” textbox in the “License Settings” section (see below).
- Slide the toggle switch next to “Enable License” to “On“.

To add colors, expand the “Level Colors” properties and select a color.
Do this to change the color of each node.
All changes are automatically saved.

Let’s add the Prefix (e.g., $ sign) to the chart’s numeric values.
Expand the “Stats” properties. Add the Prefix value (e.g., $ sign).

Let’s add colors to each node. Expand the “Level Colors” properties and select the colors.

Automatically, all changes will be saved.

Insights

The procurement cost at Level 1 amounts to $155K.
At Level 2, of the total cost of $155K, $72.3K (46.7%) was allocated to the supplier, while $82.4K (53.3%) was spent on subcontractors.
Moving to Level 3, the $72.3K supplier cost was divided between two companies: Power-up Builder and Five-star Construction, charging $53.0K and $19.3K, respectively.
The subcontractor cost of $82.4K was distributed among three companies: Skyline Contractors, Onyx General Contractors, and Living Well Remodeling. They charged $25.6K, $30.7K, and $26.0K, respectively, for their services.
At Level 4, the supplier companies provided raw materials for procurement, totaling $72.3K. The companies specializing in Mechanical Installations accounted for approximately $82.4K.
Among the $72.3K raw material cost, cement, steel, bricks, and timber accounted for $20.6K, $32.5K, $10.3K, and $9K, respectively.
From the $82.4K mechanical installation cost, Plumbing Heating, Mechanical Work, and Welding accounted for $48.1K, $22.9K, and $11.5K, respectively.

What are the Benefits of Using an On-Premises Data Gateway in Power BI?

Using a data gateway in Power BI offers a plethora of benefits:

Data accessibility: With a data gateway for Power BI, you can summon data from anywhere. On-premises, cloud, you name it, and it’s at your fingertips.
Real-time data: No more waiting for yesterday’s news. The Power BI data gateway serves up real-time data faster than a barista whips up a latte.
Data security: Your data gets the royal treatment. It’s wrapped in layers of encryption and access control, ensuring it doesn’t fall into the wrong hands.
Data scalability: Think of the data gateway as the elastic waistband, accommodating your expanding data empire. As your data grows, it grows with it.
Reduced latency: Forget the buffering wheel of frustration. The Power BI gateway slashes waiting times, so your reports load faster than a hot knife through butter.
Scheduled data refresh: Automation is the name of the game. Set schedules and your data gets a spa day, refreshing itself without bothering you.
Hybrid cloud solutions: The data gateway bridges the gap between on-premises and cloud data, supporting a seamless integration of hybrid solutions.
Ease of use: It’s not just for the IT wizards. Anyone can wield the power of the data gateway with its user-friendly setup and management.
Cost-efficiency: Centralized data management means fewer data duplicates and less resource wastage.
Enhanced collaboration: It’s the ultimate team player, allowing multiple folks to work on the same data. As a result, it promotes collaboration and smarter decision-making.

FAQs

What is a data gateway in Power BI?

A data gateway in Power BI is a bridge connecting Power BI to on-premises or cloud-based data sources. It facilitates secure data retrieval, refresh, and sharing, ensuring up-to-date and accessible reports and dashboards.

What are the types of gateways in Power BI?

Power BI offers two types of gateways:

On-Premises Data Gateway: Connects to on-premises data sources securely, enabling data access and refresh from within your organization’s network.
Power BI Gateway (Personal Mode): Designed for individual use, it allows limited data access and refresh within your personal space.

How do I create a gateway in Power BI?

To create a gateway in Power BI:

Download the Power BI Gateway installer.
Run the installer.
Configure the gateway using Power BI credentials.
Add data sources and configure settings.
Define data source permissions for users.
Your gateway is ready for use.

Wrap Up

Using a data gateway in Power BI is not a mere technicality; it’s a strategic imperative. It’s the bridge that connects your data dots, making the entire data landscape accessible and actionable.

The data gateway in Power BI is the ticket to a seamless data-driven journey. It grants you access to data spread across domains, on-premises, or in the cloud. Real-time data becomes more than a dream. It turns into a reality, empowering you with insights that can change the game.

Security, the crown jewel of data, remains intact with the data gateway. The gateway encrypts and guards the data channels. Furthermore, scalability ensures that, as your data universe expands, the gateway grows with it, no sweat.

Reduced latency ensures your data responses are as swift as thought, while scheduled refreshes bring automation into play. Hybrid cloud solutions unite the best of both worlds – a modern-day diplomatic marvel.

What’s even more beautiful? It’s easy to use, making data accessible to the non-tech-savvy. And lastly, enhanced collaboration fosters teamwork and informed decision-making.

Do not hesitate.

Embrace the Power BI gateway and ChartExpo today to unlock the full potential of your data.