Microsoft Fabric, an end-to-end Saas cloud-based analytics platform, has many workloads and objects. One of the Objects is called Data Warehouse, or in short, Warehouse. In this article, I’ll explain what the Data Warehouse in Microsoft Fabric is and how it works, and I will show you an example of it in action.
Video
Microsoft Fabric
Microsoft Fabric is an end-to-end Saas cloud-based analytics platform from Microsoft. This platform uses multiple workloads to deliver a complete analytics solution. Workloads in Fabric include data ingestion, data warehousing, data engineering, etc. To learn more about Fabric, I suggest reading the below articles;
- What is Microsoft Fabric
- How to enable Fabric in your organization
- What is Lakehouse, and a sample Dataflow Gen2 created to load data into it.
- Getting started with Data Pipelines in Microsoft Fabric Data Factory
- Getting started with Dataflows in Microsoft Fabric Data Factory
Data Warehouse
Microsoft Fabric Data Warehouse is a database system that stores data in OneLake and provides a medium to interact with the database using SQL commands. The Fabric Data Warehouse, which is also called Data Warehouse, or in short, Warehouse, also provides a powerful computing engine behind the scene to account for large volumes of data and support a fast-performing database system. The term Data Warehouse comes from the fact that this is not usually a place to store transactional data for an operational system (for that, you can use Azure SQL Database). A Data Warehouse, in generic Business Intelligence terminology, is a place where you would store the data that needs to be analyzed.
In short, Microsoft Fabric Data Warehouse is a fast-performing, fully SQL-enabled data analytics engine that stores data in OneLake.
This definition of Data Warehouse would perhaps make you wonder, what is the difference between the Data Warehouse and Lakehouse then? There are a few differences, and the main important ones are; Lakehouse supports both structured and unstructured data, whereas the Data Warehouse only supports structured data. The Lakehouse supports SQL commands only to read data, whereas the Data Warehouse supports it for read and write operations. This would require a full article to explain, but I hope this brief explanation helps you differentiate between these two objects of Microsoft Fabric.
Getting Started with Warehouse
To start creating a Warehouse, first, you need to enable Fabric, which I explained here. Then you can go to the Fabric portal and select Data Warehousing workload.
Once in the Data Warehouse workload, click on Creating a new Warehouse.
You notice that the screenshot has Synapse Data Warehouse in the header. That is because Azure Synapse is the component behind the scene responsible for the Data Warehousing workload in Fabric.
After setting a name for the Warehouse, you will be navigated to the Warehouse Explorer. Here you can choose a method to load data into the Warehouse.
You can load the data into the Warehouse using Data Pipelines or writing SQL code. There is also an option to use a sample database, which I am using here. The sample data will have some tables preloaded and ready for work.
You can also use other methods to load the data. The screenshot below is an example of the copy data activity running from a Data Factory Pipeline.
In the Warehouse Explorer, you can explore the data and create database objects such as Stored Procedures, functions, and views. You can also specify security specifications.
However, unlike Lakehouse, you cannot explore files. Because in Warehouse, you can only store structured data (Structured data means data tables).
How to load data into Warehouse
There are multiple methods to load data into Warehouse; the most common methods are;
- Data Pipelines
- Dataflows (Gen 2)
- SQL Commands
The SQL commands you can use for the Warehouse can be used for read and write operations. Here is another difference between the SQL Endpoint of Lakehouse and the Warehouse; The SQL Endpoint in the Lakehouse only allows read operations, whereas the Warehouse allows read and write operations.
This means you can write SQL code that inserts, updates or deletes data in the Warehouse.
Source of the image: https://learn.microsoft.com/en-us/fabric/data-warehouse/data-warehousing
This means you can have SQL codes like the below to load data into tables;
INSERT INTO [dbo].[table2]
SELECT * FROM [dbo].[table1]
WHERE [updated] > '2023-05-31';Reading Data from Warehouse
There are also multiple methods to read data from the Warehouse. Here are some of those;
- SQL query (run either in the Warehouse Explorer or in other tools such as SQL Server Management Studio)
- Visual query (Power Query Editor online)
- Power BI Get Data (Or Dataflow Get Data etc.)
You can write SQL queries simply inside the Warehouse Explorer like below;
You can also use Visual Query, which will use the Power Query Editor online to build queries.
It is important to note that you won’t have Power Query’s full functionality in this editor. This would support mainly actions that can be folded back to the source. You can even View the SQL code generated by the visual query.
You can also use the SQL connection string (which you can acquire from the settings of the Warehouse Explorer) to connect tools such as SSMS to the Warehouse.
Lastly, you can use Power BI to get data from Warehouse. One thing to notice here is that, from the Power BI point of view, there will be different ways to get data from the Warehouse;
- Power BI report connects live to the dataset associated with the Warehouse (this dataset is automatically generated but can be edited)
- Power BI reports connecting DirectQuery or Import Data to the Warehouse itself.
The Warehouse comes with its own Power BI Dataset. This dataset is automatically generated with the Warehouse.
This Power BI Dataset can be modified using the Warehouse Explorer in the Model tab.
Power BI Desktop can then connect to this dataset using Get Data from Warehouse or Get Data from Dataset.
If you get data from the Warehouse, you will create a live connection to the associated Power BI Dataset. This is similar to the way that Power BI Datamart and Lakehouse work.
However, if you connect to the SQL endpoint, you can choose to have a DirectQuery connection to the Warehouse or import that data into your new Power BI report and dataset.
From the Power BI report’s point of view, it won’t make any difference if you connect to Lakehouse or Warehouse; you would have the same outcome if you are connecting to the auto-generated dataset associated with the Lakehouse or Warehouse.
You can also build the report directly from the Warehouse Explorer online.
Summary
Microsoft Fabric Data Warehouse, or Warehouse, is a high-performant structured database system that stores data in OneLake. Microsoft Fabric Warehouse will enable high performance with high computing power behind the scene. The difficulties of scaling up and down are hidden from your side. All you see is a good-performing model. The Warehouse can be loaded with data using Data Pipeline, Dataflows Gen2, or SQL commands, and the data can be read using SQL endpoint’s query commands and tools such as Power BI. A Power BI dataset will be associated with the Warehouse, which can be edited online.
At this stage, you might ask what the difference is between Warehouse, Lakehouse, and Power BI Datamarts. Of course, there are many similarities between these three, and there are points available in all of them, but there are differences too. I will explain that in another article and video, as this one is already long. Stay tuned for the comparison article.
To learn more about Fabric, I suggest reading the below articles;
- What is Microsoft Fabric
- How to enable Fabric in your organization
- What is Lakehouse, and a sample Dataflow Gen2 created to load data into it.
- Getting started with Data Pipelines in Microsoft Fabric Data Factory
- Getting started with Dataflows in Microsoft Fabric Data Factory
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.