What is data warehousing in Microsoft Fabric?

Posted by

  • Microsoft Fabric is a single platform that covers all aspects of managing data.
  • It offers a complete solution for data management, analytics, and artificial intelligence (AI) in a Software as a Service (SaaS) model.
  • Fabric is focused around lakes, which are central repositories for data.
  • It caters to both beginners and experts, providing tools for databases, analytics, messaging, data integration, and business intelligence.
  • Users can access these tools through a user-friendly SaaS experience, with Microsoft OneLake as the main feature.
  1. Introduction: Microsoft Fabric brings a new kind of data warehouse that’s focused on lakes. It’s powerful and easy to use, without needing much setup or management. It works closely with Power BI for analyzing and reporting data, combining the benefits of data lakes and warehouses to make analytics simpler for organizations.
  2. Features: The Warehouse in Microsoft Fabric is designed for everyone, from beginners to experts. It offers a range of tools and features that help users quickly understand their data and make informed decisions. By connecting directly to Power BI, it ensures that reports always have the latest data for analysis. It also allows for querying across different databases without duplicating data.

Virtual warehouses with cross database querying

  • Stand Up Virtual Warehouses: With Microsoft Fabric, you can easily create virtual warehouses that hold data from different places using shortcuts. These warehouses can include data from OneLake, Azure Data Lake Storage, or other cloud storage, all in one place without copying the data.
  • Unlock Insights Across Data Sources: Fabric lets you access and analyze data from various sources quickly. You can combine data from different places to get insights without duplicating the data. This means you can analyze different data together to find valuable insights faster.
  • Easy Querying with Visual and SQL Editors: Fabric provides tools like the Visual Query editor and SQL Query editor to make querying data across different sources simple. You can use these tools, along with familiar ones like SQL Server Management Studio, to create queries that pull data from multiple places for analysis.

Data Warehousing experiences in Microsoft Fabric

It has two distinct data warehousing experiences in Microsoft Fabric: the SQL analytics endpoint of the Lakehouse and the Warehouse.

SQL analytics endpoint of the Lakehouse

  1. What it is: The SQL analytics endpoint is like a special area within your Lakehouse where you can use SQL commands to look at your data. It’s automatically created from the main Lakehouse in Microsoft Fabric.
  2. What you can do: You can’t change the data directly here, but you can do things like ask questions about your data, create views or procedures to organize your data, and manage who can see or use certain parts of the data.
  3. How it works: In your Microsoft Fabric workspace, you’ll see this endpoint labeled as “SQL analytics endpoint.” You can use common SQL tools like SQL Server Management Studio or Azure Data Studio to interact with it, just like you would with any other SQL database.

Synapse Data Warehouse

In a Microsoft Fabric workspace, a Synapse Data Warehouse or Warehouse is labeled as ‘Warehouse’ under the Type column. A Warehouse supports transactions, DDL, and DML queries.

  • A SQL analytics endpoint only allows reading data and creating views and TVFs (table-valued functions).
  • In contrast, a Warehouse allows full support for transactional operations like creating and modifying tables (DDL) and manipulating data (DML).
  • Customers create their own Warehouse.
  • Data is loaded into a Warehouse using various methods like COPY INTO, Pipelines, Dataflows, or cross-database ingestion options like CTAS, INSERT..SELECT, or SELECT INTO.

Compare the Warehouse and the SQL analytics endpoint of the Lakehouse

  1. SQL Analytics Endpoint:
    • Automatically created when you make a Lakehouse in Microsoft Fabric.
    • Shows Delta tables created with Spark in the Lakehouse as SQL tables.
    • Lets data engineers build a relational layer for analysis using SQL tools.
    • Data analysts can use T-SQL to access and analyze Lakehouse data for reporting.
  2. Synapse Data Warehouse:
    • A ‘traditional’ data warehouse.
    • Offers full T-SQL capabilities.
    • You control table creation, data loading, transformation, and querying.
    • Can be managed through Microsoft Fabric portal or T-SQL commands.

Note for SQL analytics endpoint of the Lakehouse

  • The SQL analytics endpoint handles tables automatically, so users can’t change them. But they can add their own stuff like schemas, views, and procedures.
  • Each Delta table in your Lakehouse gets turned into a table in the SQL analytics endpoint automatically.
  • When you make a new table or change one in your Lakehouse, it takes a little time for the SQL analytics endpoint to catch up. Usually, it takes 5-10 seconds for the changes to show up in the endpoint.

How to Create a Warehouse in Microsoft Fabric

Three distinct experiences available for creating a Warehouse from scratch in the Microsoft Fabric portal: using the Home hub, the Create hub, or the workspace list view.

Create a warehouse using the Home hub

  • The Home hub is the first thing you see on the navigation pane.
  • You can make a warehouse from the Home hub by picking the Warehouse card in the New section.
  • A blank warehouse is made for you to begin adding things to it.
  • You can either use sample data to start quickly or load your own test data if you want.

Create a warehouse using the Create hub

  • You can make your warehouse using the Create hub, found in the navigation pane.
  • Go to the Create hub and choose the Warehouse card in the Data Warehousing section.
  • When you pick the card, a new empty warehouse is made. You can then start making things in the warehouse or use a sample to start off, like I mentioned before.

Create a warehouse from the workspace list view

To create a warehouse, navigate to your workspace, select + New and then select Warehouse to create a warehouse.

Ready for data –

Once initialized, you can load data into your warehouse. For more information about getting data into a warehouse, see Ingesting data.

How to create a warehouse with sample data

  • The Home hub is the first option you see on the side of the screen.
  • To make a new sample warehouse, go to the Home hub and click on the Warehouse sample card in the New section.
  • Type in a name for your new sample warehouse and click Create.
  • This action sets up a new warehouse and begins putting sample data into it.
  • It takes a few minutes for all the sample data to load.
  • Once it’s done loading, you can open the warehouse and start looking at the data in the tables and views.

How to Create tables in the Warehouse in Microsoft Fabric

  • In the ribbon at the top of the warehouse editor, find the button labeled “New SQL query.”
  • Instead of clicking directly on “New SQL query,” click on the arrow next to it to see more options.
  • From the dropdown menu, choose “Templates” to create T-SQL objects.
  • Select “Table,” and a pre-made script for creating a table will appear in your new SQL query window.
  • You can make changes to this script to fit the specifications of your new table.
  • Finally, click on “Run” to execute the script and create the table.
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x