Fabric Integration

Microsoft Fabric Integration

Overview

Microsoft Fabric is the unified analytics platform that includes Power BI, Data Factory, Data Engineering, Data Science, Real-Time Intelligence, and Data Warehouse. Power BI is deeply integrated as the visualization and semantic modeling layer of Fabric.

Direct Lake Mode

Direct Lake is a storage mode exclusive to Fabric that reads data directly from delta tables in OneLake without importing or sending DirectQuery requests.

How Direct Lake Works

1. Framing: On refresh, Direct Lake copies only metadata (Parquet file references) from delta tables -- takes seconds 2. On-demand loading: When a query hits the model, data is loaded from Parquet files directly into the VertiPaq engine 3. No data duplication: Unlike Import, no copy of data is stored in the semantic model 4. Near-import performance: Once loaded into memory, queries run at VertiPaq speed

Direct Lake vs Import vs DirectQuery

Direct Lake Variants (2025-2026 GA)

Creating a Direct Lake Semantic Model

In Power BI Desktop (2025+ preview): 1. Get Data > OneLake data hub 2. Select Fabric lakehouse or warehouse 3. Choose tables (loaded as Direct Lake automatically) 4. Build measures and relationships in Desktop 5. Publish to Fabric workspace

Via Fabric Service: 1. Open lakehouse/warehouse in Fabric 2. Click "New semantic model" 3. Select tables to include 4. Open model in web to add measures and relationships

Programmatically via TOM:

var database = new Database() { Name = "DirectLakeModel" };
var model = new Model() { Name = "DirectLakeModel" };
database.Model = model;

// Direct Lake partition source
var table = new Table() { Name = "Sales" };
table.Partitions.Add(new Partition() {
    Name = "Sales-DL",
    Mode = ModeType.DirectLake,
    Source = new EntityPartitionSource() {
        EntityName = "Sales",
        SchemaName = "dbo",
        ExpressionSource = new ExpressionSource() {
            Expression = "DatabaseQuery"
        }
    }
});
model.Tables.Add(table);

Critical distinction: DL/OL does NOT fall back to DirectQuery. If data cannot be served from memory, the query fails. This means DL/OL models must be carefully sized within capacity guardrails.

Direct Lake Guardrails by Capacity

Max Memory is a soft limit for paging -- exceeding it causes performance degradation but not failure.

Max model size on disk/OneLake is a hard guardrail -- exceeding causes DQ fallback (DL/SQL) or query failure (DL/OL).

Direct Lake Fallback Configuration

Monitor fallback in Fabric Capacity Metrics app -- frequent fallback indicates model design issues.

Common fallback triggers (DL/SQL):

• Columns not loaded into memory due to capacity limits
• Calculated columns on Direct Lake tables (may trigger DQ fallback)
• Certain DAX patterns that require full table scan
• Stale framing (delta tables changed but model not re-framed)
• File/row-group count exceeding capacity guardrails

Power BI Embedded with Direct Lake (GA March 2025)

Direct Lake mode is now fully supported for embedded analytics, backed by Microsoft SLA. Generate embed tokens for Direct Lake semantic models using the same embed token API as Import/DirectQuery models.

Framing (Refresh)

# Trigger framing via REST API
POST https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/refreshes
{
  "type": "Automatic"
}

Framing is extremely fast (seconds) compared to Import refresh (minutes/hours). Schedule frequent framing for near-real-time data.

OneLake

OneLake is Fabric's unified data lake -- a single store for all analytics data, built on Azure Data Lake Storage Gen2 with delta format.

OneLake Shortcuts

Connect to external data without copying:

OneLake File API

Access OneLake data programmatically:

# Using Azure Storage SDK (OneLake supports ADLS Gen2 API)
from azure.storage.filedatalake import DataLakeServiceClient

service_client = DataLakeServiceClient(
    account_url="https://onelake.dfs.fabric.microsoft.com",
    credential=token_credential
)

file_system_client = service_client.get_file_system_client(workspace_id)
directory_client = file_system_client.get_directory_client(f"{lakehouse_name}.Lakehouse/Tables")

Fabric Lakehouse

A lakehouse combines data lake flexibility with warehouse SQL capabilities:

Power BI connectivity:

• SQL Analytics Endpoint: Read-only SQL endpoint for DirectQuery or Direct Lake
• Delta tables: Native format for Direct Lake
• Notebooks: Write data from Spark notebooks, read in Power BI

Lakehouse to Power BI Flow

[Data Sources] --> [Fabric Notebooks/Pipelines] --> [Lakehouse Delta Tables]
     |                                                       |
     v                                                       v
[Power Query Dataflows Gen2]                    [Direct Lake Semantic Model]
                                                             |
                                                             v
                                                     [Power BI Reports]

Fabric Warehouse

Fully managed SQL warehouse in Fabric:

• T-SQL support: Full DML (INSERT, UPDATE, DELETE, MERGE)
• Auto-distributed storage: No index tuning needed
• Direct Lake compatible: Tables accessible as Direct Lake sources
• Cross-database queries: Query across warehouses and lakehouses

Dataflow Gen2

Cloud-based ETL in Fabric, evolution of Power BI Dataflows:

Gen1 deprecation: Microsoft has announced Gen1 is legacy. Migrate to Gen2 for all new development. Gen2 supports all Gen1 connectors plus Fabric-native destinations.

Dataflow Gen2 Output Destinations (2025-2026)

Dataflow Gen2 to Direct Lake Pipeline

1. Create Dataflow Gen2 in Fabric workspace 2. Connect to source (any Power Query connector) 3. Set destination to Lakehouse (creates delta tables) 4. Create semantic model on top of lakehouse tables (Direct Lake) 5. Build reports on the semantic model

Real-Time Intelligence

Fabric Real-Time Intelligence items now have GA lifecycle management (2025):

Eventstream to Power BI Pipeline

[Event Hubs/Kafka/IoT Hub] --> [Eventstream] --> [KQL Database] --> [Power BI DirectQuery]
                                     |
                                     +--> [Lakehouse] --> [Power BI Direct Lake]

Notebooks for Data Prep

Fabric notebooks (PySpark/Spark SQL) write data that Power BI consumes:

# Write DataFrame to lakehouse delta table
df.write.format("delta").mode("overwrite").saveAsTable("Sales")

# Optimized write with partitioning
df.write.format("delta") \
    .partitionBy("Year", "Month") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .saveAsTable("Sales")

# V-Order optimization (improves Direct Lake read performance)
spark.conf.set("spark.sql.parquet.vorder.enabled", "true")
df.write.format("delta").mode("overwrite").saveAsTable("Sales")

V-Order: A write-time optimization that orders Parquet data for faster Direct Lake reads. Enable in notebook or pipeline configuration.

Fabric Items in Power BI Context

Semantic Link (sempy)

Python library for Power BI semantic model interaction in Fabric notebooks:

import sempy.fabric as fabric

# List datasets in workspace
datasets = fabric.list_datasets()

# Read data from semantic model using DAX
df = fabric.evaluate_dax(
    dataset="SalesModel",
    dax_string="EVALUATE SUMMARIZECOLUMNS('Date'[Year], 'Product'[Category], \"Sales\", [Total Sales])"
)

# Read model metadata
tables = fabric.list_tables(dataset="SalesModel")
measures = fabric.list_measures(dataset="SalesModel")

# Refresh dataset
fabric.refresh_dataset(dataset="SalesModel")

Composite Models with Direct Lake (2025 Preview)

Mix Direct Lake tables with Import tables in a single semantic model:

Key consideration: Composite model queries involving both DL and Import tables may have different performance characteristics. Test with production data volumes.

Additional Resources

Reference Files

• `references/fabric-architecture-patterns.md` -- Medallion architecture, data mesh patterns, and Fabric workspace design strategies

Fabric Architecture Patterns for Power BI

Medallion Architecture (Bronze/Silver/Gold)

The most common data architecture pattern in Fabric for Power BI:

[Raw Sources] --> [Bronze Lakehouse] --> [Silver Lakehouse] --> [Gold Lakehouse] --> [Semantic Model] --> [Reports]
                  (raw, as-is)          (cleaned, typed)       (business-ready)    (Direct Lake)

Bronze Layer

• Raw data ingested as-is from sources
• Minimal transformation (schema-on-read)
• Full history preserved
• Ingestion via: Data Pipeline, Dataflow Gen2, Notebooks, Eventstream

Silver Layer

• Cleaned, deduplicated, typed data
• Business logic applied (join reference tables, handle nulls)
• Delta format with Z-Order optimization on query columns
• Incremental processing (merge/upsert patterns)

# Silver layer notebook example
from delta.tables import DeltaTable

# Read bronze
bronze_df = spark.read.format("delta").table("bronze.raw_sales")

# Clean and transform
silver_df = bronze_df \
    .dropDuplicates(["OrderID"]) \
    .withColumn("Amount", col("Amount").cast("decimal(18,2)")) \
    .withColumn("OrderDate", to_date(col("OrderDate"), "yyyy-MM-dd")) \
    .filter(col("Amount") > 0)

# Upsert to silver
silver_table = DeltaTable.forName(spark, "silver.sales")
silver_table.alias("target").merge(
    silver_df.alias("source"),
    "target.OrderID = source.OrderID"
).whenMatchedUpdateAll() \
 .whenNotMatchedInsertAll() \
 .execute()

Gold Layer

• Business-ready aggregations and dimensions
• Optimized for Power BI Direct Lake consumption
• Star schema structure (fact + dimension tables)
• V-Order enabled for optimal read performance

# Gold layer - create star schema tables
spark.conf.set("spark.sql.parquet.vorder.enabled", "true")

# Fact table
fact_sales = spark.sql("""
    SELECT
        s.OrderID, s.OrderDate, s.CustomerID, s.ProductID,
        s.Quantity, s.Amount, s.DiscountAmount
    FROM silver.sales s
""")
fact_sales.write.format("delta").mode("overwrite").saveAsTable("gold.fact_sales")

# Dimension tables
dim_product = spark.sql("""
    SELECT DISTINCT ProductID, ProductName, Category, SubCategory
    FROM silver.products
""")
dim_product.write.format("delta").mode("overwrite").saveAsTable("gold.dim_product")

dim_date = spark.sql("""
    SELECT DISTINCT
        OrderDate as Date,
        year(OrderDate) as Year,
        month(OrderDate) as Month,
        quarter(OrderDate) as Quarter,
        dayofweek(OrderDate) as DayOfWeek
    FROM silver.sales
""")
dim_date.write.format("delta").mode("overwrite").saveAsTable("gold.dim_date")

Direct Lake on Gold Layer

The semantic model sits on top of the gold layer lakehouse:

1. Create semantic model from gold lakehouse SQL endpoint 2. All gold tables available as Direct Lake tables 3. Add DAX measures, calculation groups, hierarchies 4. Reports connect to this single semantic model

Data Mesh Pattern

For large organizations with multiple domains:

[Domain: Sales]                    [Domain: Marketing]
  ├── Sales Lakehouse                ├── Marketing Lakehouse
  ├── Sales Semantic Model           ├── Marketing Semantic Model
  └── Sales Reports                  └── Marketing Reports
         │                                    │
         └──── OneLake Shortcuts ────────────┘
                      │
              [Cross-Domain Reports]

Key principles:

• Each domain owns its data products (lakehouse + semantic model + reports)
• Cross-domain access via OneLake shortcuts (no data copying)
• Central governance via Fabric Admin, sensitivity labels, endorsement
• Shared semantic models promoted/certified for cross-domain use

Workspace Design Strategies

Strategy 1: Environment-Based (Small Teams)

Sales-Dev     (workspace, dev capacity)
Sales-Test    (workspace, test capacity)
Sales-Prod    (workspace, prod capacity)

Connected via deployment pipeline: Dev > Test > Prod.

Strategy 2: Domain + Environment (Medium Orgs)

Sales-Dev, Sales-Test, Sales-Prod
Marketing-Dev, Marketing-Test, Marketing-Prod
Finance-Dev, Finance-Test, Finance-Prod
SharedData-Dev, SharedData-Test, SharedData-Prod

Each domain has its own pipeline. SharedData contains cross-domain semantic models.

Strategy 3: Separate Model and Report Workspaces (Enterprise)

Sales-DataModel-Prod     (semantic model only)
Sales-Reports-Prod       (reports using live connection to model)
Sales-Dashboards-Prod    (dashboards, apps)
Sales-DataModel-Dev      (development semantic model)
Sales-Reports-Dev        (development reports)

Benefits: Independent security for data models vs reports. Model team and report team can have different access.

End-to-End Fabric Pipeline for Power BI

1. DATA INGESTION
   ├── Data Pipeline (scheduled, for batch sources)
   ├── Dataflow Gen2 (for sources needing Power Query transforms)
   ├── Eventstream (for real-time streaming sources)
   └── Notebooks (for complex/custom ingestion)
         │
         v
2. DATA STORAGE (Bronze Lakehouse)
   └── Raw delta tables, full history
         │
         v
3. DATA TRANSFORMATION
   ├── Notebooks (PySpark for complex transforms)
   ├── Data Pipeline (orchestration)
   └── Dataflow Gen2 (simple transforms)
         │
         v
4. DATA MODELING (Gold Lakehouse)
   └── Star schema delta tables, V-Ordered
         │
         v
5. SEMANTIC LAYER
   └── Semantic Model (Direct Lake)
       ├── DAX measures
       ├── Calculation groups
       ├── Relationships
       ├── RLS/OLS
       └── Hierarchies
         │
         v
6. PRESENTATION
   ├── Power BI Reports
   ├── Power BI Dashboards
   ├── Paginated Reports
   ├── Power BI Embedded (apps)
   └── Excel (connected via Analyze in Excel)

Capacity Sizing for Fabric + Power BI

Scaling guidance:

• Start with F64 for production (includes viewer access for users without Pro/PPU)
• Use F2-F32 for development/testing (API access only, no interactive viewing)
• Monitor with Capacity Metrics app for CPU, memory, and throttling
• Enable autoscale for burst scenarios
• Consider multiple capacities for isolation (dev vs prod)
• DL/OL models must stay within guardrails -- no DQ fallback available

Data Activator (Alerts and Triggers)

Fabric feature that triggers actions based on data changes:

[Semantic Model] --> [Data Activator Reflex] --> [Action]
                                                    ├── Send email
                                                    ├── Teams notification
                                                    ├── Power Automate flow
                                                    └── Custom webhook

Use cases:

• Alert when KPI drops below threshold
• Notify manager when sales target achieved
• Trigger pipeline when data quality issue detected
• Send daily summary if metrics changed significantly

Migration from Existing Power BI to Fabric

Phase 1: Assessment

• Inventory all workspaces, datasets, reports
• Identify datasets suitable for Direct Lake (large Import datasets)
• Assess gateway dependencies
• Review Premium/PPU licensing

Phase 2: Lakehouse Setup

• Create Fabric workspace and lakehouse
• Migrate data sources to lakehouse tables
• Implement medallion architecture
• Enable V-Order optimization

Phase 3: Semantic Model Migration

• Recreate semantic models with Direct Lake on gold tables
• Migrate DAX measures, relationships, RLS
• Use ALM Toolkit or Tabular Editor for comparison
• Validate measure results match original

Phase 4: Report Migration

• Rebind existing reports to new semantic models
• Test all visuals and interactions
• Update scheduled refreshes (framing instead of full refresh)
• Migrate deployment pipelines

Phase 5: Governance

• Update tenant settings for Fabric
• Configure sensitivity labels
• Set up Capacity Metrics monitoring
• Train admins on Fabric-specific features