Data-modeling-made-easy

Microsoft Fabric for Data Warehousing

1. What is its Primary Purpose?

The primary purpose of Microsoft Fabric is to provide an all-in-one, end-to-end, SaaS-based analytics platform that unifies data engineering, data warehousing, data integration, real-time analytics, data science, and business intelligence into a single centralized ecosystem. Its core mission is to eliminate data silos by bringing all organizational tools and data storage formats together into a unified SaaS experience.

2. What Way MS Fabric Can Be Used for a Data Warehouse?

In Microsoft Fabric, data warehousing can be implemented in two primary ways depending on architectural needs:

Fabric Warehouse (Serverless SQL Data Warehouse): A traditional, relational enterprise data warehouse experience that utilizes a fully managed MPP (Massively Parallel Processing) SQL engine. It supports full transactional DML/DDL (Insert, Update, Delete), star schemas, and traditional relational modeling.
Fabric Lakehouse: A modern architectural pattern combining the file storage flexibility of a data lake with the ACID-transaction reliability of a data warehouse. It stores data in open Delta Parquet format over OneLake, allowing both Spark code (for data science/engineering) and SQL queries (via a built-in SQL endpoint) to access the exact same tables concurrently.

3. What Features are Different?

OneLake Foundation ("OneDrive for Data"): Unlike traditional warehouses where data must be explicitly copied and moved between systems, Fabric relies on OneLake—a single, hierarchical, tenant-wide data lake where all workloads automatically store data in open formats.
SaaS Billing & Compute Pooling (Capacity Units - CUs): Instead of provisioning distinct infrastructure instances across different tools, you purchase a pool of Compute Units (Capacity) that automatically scales and powers your data factory, pipelines, warehouses, and Power BI reports under one hood.
Shortcuts (Zero-Copy Virtualization): Fabric allows you to virtualize data residing in external storage accounts (such as AWS S3, Google Cloud Storage, or Azure ADLS Gen2) directly into your workspace without moving or copying physical bytes.
Direct Lake Mode in Power BI: Power BI connects directly to Delta Parquet files in OneLake without importing data into memory (Import mode) or querying it live via slow database connections (DirectQuery mode), delivering in-memory performance with live data freshness.

4. What All Purpose It Can Be Used?

Microsoft Fabric serves multiple enterprise data use cases across different analytical verticals:

Enterprise Reporting & Business Intelligence: Power BI integration for self-service analytics and executive dashboards.
Data Integration & ETL/ELT: Data Factory pipelines and dataflows for moving and transforming data from hundreds of connectors.
Big Data Engineering & Spark Processing: PySpark notebooks for large-scale data cleansing, transformations, and pipeline automation.
Real-Time Analytics: KQL databases for streaming telemetry, log analysis, and IoT data.
Data Science & Machine Learning: ML models trained on OneLake data using Python/R within integrated notebooks.

5. What are its Contents? (Core Components / Workloads)

OneLake: The underlying centralized storage repository.
Data Factory: Cloud-scale data integration pipelines and dataflows.
Synapse Data Engineering: Spark notebooks and job definitions.
Synapse Data Warehousing: Dedicated relational SQL warehouses.
Synapse Data Science: Machine learning environments and model management.
Synapse Real-Time Analytics: KQL querysets and streaming data management.
Power BI: Business intelligence visualization and semantic models.

6. Sample Project Implementation: Insurance Dimensional Model in Microsoft Fabric

Project Scenario

"The Insurance Company" wants to build a Fabric data warehouse to track Policy and Claims data utilizing a dimensional star schema (dim_policy, dim_customer, and fact_claims).

Step-by-Step Implementation Guide

Step 1: Create a Workspace & Assign Capacity

Menus / Windows: Log into the Microsoft Fabric portal (app.fabric.microsoft.com), navigate to the left navigation pane, and click on Workspaces.
Actions: Click New workspace, enter the workspace name (Insurance_DWH_Prod), expand the Advanced settings dropdown, and assign your organization's Fabric Capacity (F-SKU). Click Apply.

Step 2: Ingest Raw Data Using Data Factory Pipelines

Menus / Windows: Inside your workspace, click New -> Data pipeline. Name the pipeline pl_ingest_insurance_raw.
Actions:
- Drag a Copy activity onto the canvas.
- In the Source tab, click New connection, select Azure SQL Database (or source file storage), and provide server credentials pointing to the legacy insurance system. Select source tables: dbo.policies and dbo.claims.
- In the Destination tab, select OneLake / Lakehouse as the target, pointing to your landing bronze storage zone. Click Save and click Run to execute the data load.

Step 3: Create the Relational Fabric Warehouse & Schema

Menus / Windows: Click New in your workspace, and select Warehouse. Name it wh_insurance_enterprise.
Actions: Once the serverless warehouse environment opens, click New SQL Query to write and execute DDL scripts establishing your dimensional model schemas:

SQL
-- Create Dimension: Policy
CREATE TABLE dim_policy (
    policy_key INT IDENTITY(1,1) PRIMARY KEY,
    policy_number VARCHAR(50) NOT NULL,
    policy_type VARCHAR(50),
    effective_date DATE,
    expiration_date DATE
);

-- Create Dimension: Customer
CREATE TABLE dim_customer (
    customer_key INT IDENTITY(1,1) PRIMARY KEY,
    customer_id VARCHAR(50) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    state VARCHAR(2)
);

-- Create Fact Table: Claims
CREATE TABLE fact_claims (
    claim_id VARCHAR(50) NOT NULL,
    policy_key INT FOREIGN KEY REFERENCES dim_policy(policy_key),
    customer_key INT FOREIGN KEY REFERENCES dim_customer(customer_key),
    claim_date DATE,
    payout_amount DECIMAL(18,2)
);

Step 4: Populate Data via SQL Transformation Queries

Menus / Windows: Within the Warehouse SQL query editor window, write and execute INSERT/MERGE statements to load dimensions and facts from your staging tables into the dimensional model tables:

SQL
-- Populate Dim Policy from Staging
INSERT INTO dim_policy (policy_number, policy_type, effective_date, expiration_date)
SELECT DISTINCT 
    TRIM(src.pol_num), 
    src.coverage_type, 
    src.eff_date, 
    src.exp_date
FROM stg_policies src;

-- Populate Fact Claims linking dimensions
INSERT INTO fact_claims (claim_id, policy_key, customer_key, claim_date, payout_amount)
SELECT 
    c.claim_id,
    p.policy_key,
    cust.customer_key,
    c.claim_date,
    c.amount
FROM stg_claims c
JOIN dim_policy p ON c.pol_num = p.policy_number
JOIN dim_customer cust ON c.cust_id = cust.customer_id;

Step 5: Build the Power BI Semantic Model & Direct Lake Report

Menus / Windows: Navigate back to your Fabric workspace homepage. Fabric automatically generates a default Semantic Model alongside your warehouse. Click on the semantic model named wh_insurance_enterprise.
Actions:
- Click Open semantic model to launch the web modeling interface.
- Drag relationships between tables (e.g., connect fact_claims[policy_key] to dim_policy[policy_key]).
- Click New Report, select fields like policy_type and payout_amount, build your executive dashboard charts, and click Save to publish your final analytics solution in Direct Lake mode.