How To Connect To On-Premises Data In Microsoft Fabric

In this training session, Austin Libal, a Pragmatic Works trainer, walks through how to connect on-premises data into a Microsoft Fabric Lakehouse using a Dataflow Gen2 pipeline. The demonstration provides step-by-step guidance for setting up a secure connection, transforming data, and publishing it into a Fabric environment for analytics.

Introduction to Microsoft Fabric

Microsoft Fabric, part of the Power BI service, unifies analytics for data engineers, analysts, and scientists in one collaborative platform. Fabric’s Lakehouse architecture enables organizations to consolidate data from multiple sources, including on-premises systems, and use it for end-to-end analytical solutions.

Austin highlights that if users are unfamiliar with Lakehouses, they should explore his “Learn with the Nerd” session, where a full end-to-end Fabric analytics demo is covered. This video builds on that foundation by focusing specifically on connecting on-premises SQL Server data into Fabric.

Why Connect On-Premises Data?

• Many organizations still run critical workloads on SQL Server or on-premises file systems.
• Business insights require bringing that data into the cloud for broader accessibility and analysis.
• Fabric makes it possible to integrate these sources through secure gateways without moving the entire infrastructure.

Step 1: Access the Data Factory Persona

Inside a Fabric-enabled workspace in Power BI Service, Austin switches to the Data Factory Persona. This persona provides the tools needed to create and manage Dataflow Gen2 pipelines, which allow users to extract, transform, and load (ETL) data into Fabric.

Step 2: Create a Dataflow Gen2

The interface for Dataflow Gen2 closely resembles Power Query in Power BI Desktop. From the left panel, users select the option to get data, then choose SQL Server as the data source.

At this point, connection details such as server name, database name, and credentials must be entered. For this demo, Austin uses the AdventureWorksDW2019 database hosted locally on his laptop.

Step 3: Set Up the On-Premises Data Gateway

The critical piece of the puzzle is the On-Premises Data Gateway. Austin explains:

• The gateway acts as a secure bridge between on-premises sources and the Power BI service.
• It supports SQL Server, file systems, and other local resources.
• The gateway must be installed on the machine hosting the source system.

With the gateway installed and authentication configured, the connection is established successfully.

Step 4: Select and Transform Data

Once connected, all available tables from the SQL Server instance appear. For demonstration, Austin selects the DimCurrency table.

He previews the table and applies simple transformations:

• Retains only key fields such as CurrencyKey, AlternateKey, and Name.
• Removes unnecessary columns to streamline the dataset.

This familiar Power Query editing environment helps shape the dataset before loading it into Fabric.

Step 5: Choose a Data Destination

Fabric offers multiple destinations, including:

• Warehouse
• Lakehouse
• Azure SQL Database
• Azure Data Explorer

Austin selects his Fabric Lakehouse. Within his workspace, he chooses the AdventureWorks Lakehouse used in earlier sessions. He keeps the table name as DimCurrency for consistency.

Step 6: Configure and Publish

The final step involves:

1. Validating column data types.
2. Confirming transformation rules.
3. Saving and publishing the dataflow.

Once published, the dataflow runs to load the SQL Server table into the Lakehouse. Austin refreshes the workspace and verifies the new table appears in his Lakehouse environment.

Key Takeaways

• Microsoft Fabric provides a seamless way to integrate on-premises data into cloud analytics.
• On-Premises Data Gateway is essential for secure connectivity.
• Dataflow Gen2 combines the ease of Power Query with enterprise-scale data pipelines.
• Users can target multiple destinations but Lakehouses are ideal for analytical workloads.

This demonstration underscores how organizations can extend the life of their on-premises data systems while still benefiting from modern cloud analytics in Microsoft Fabric.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Fabric and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.