fabric-integration

What this skill does

# 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

| Feature | Import | DirectQuery | Direct Lake |
|---------|--------|-------------|-------------|
| Data freshness | Snapshot at refresh | Real-time | Near real-time (after framing) |
| Query performance | Fastest (all in memory) | Depends on source | Near-import (on-demand load) |
| Refresh time | Minutes to hours | N/A | Seconds (framing only) |
| Refresh cost | High (full data copy) | None | Very low (metadata only) |
| Data size limit | 10GB (Premium), 1GB (PBIX) | Source limit | Fabric capacity limit |
| DAX support | Full | Limited | Full |
| Calculated columns | Yes | No | Yes |
| Source requirement | Any | Any | OneLake delta tables only |
| Capacity requirement | Any | Any | Fabric F-SKU |

### Direct Lake Variants (2025-2026 GA)

| Variant | Source | Multi-Source | Fallback | Use Case | GA Status |
|---------|--------|-------------|----------|----------|-----------|
| Direct Lake on OneLake (DL/OL) | OneLake delta files | Yes (multiple Fabric items) | NO fallback | Flexible, multiple lakehouses | GA |
| Direct Lake on SQL (DL/SQL) | Fabric SQL endpoint | No (single Fabric item) | Falls back to DirectQuery | SQL-centric, single source | 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:**
```csharp
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

| Guardrail | F2 | F4 | F8 | F16 | F32 | F64 | F128 |
|-----------|----|----|----|----|-----|-----|------|
| Max model size on disk | 2 GB | 4 GB | 8 GB | 16 GB | 32 GB | 64 GB | 128 GB |
| Max rows per table | 300M | 300M | 300M | 1.5B | 3B | 6B | 6B |
| Max files/row groups per table | 1K | 1K | 1K | 1K | 1K | 5K | 5K |
| Concurrent DL queries | 4 | 8 | 16 | 32 | 64 | 128 | 256 |

**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

| Fallback Behavior | Setting | Impact |
|-------------------|---------|--------|
| Automatic fallback to DirectQuery | Default (DL/SQL only) | Query still works but slower |
| Block fallback | `DirectLakeBehavior = DirectLakeOnly` | Query fails if cannot serve from DL |
| No fallback option | Default (DL/OL) | Queries always fail if data unavailable |

**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)

```bash
# 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:

| Shortcut Type | Source | Use Case |
|---------------|--------|----------|
| OneLake | Another Fabric item | Cross-workspace data sharing |
| ADLS Gen2 | Azure Data Lake | Existing Azure data |
| S3 | Amazon S3 | Multi-cloud data |
| GCS | Google Cloud Storage | Multi-cloud data |
| Dataverse | Dynamics 365 | Business app data |

### OneLake File API

Access OneLake data programmatically:
```python
# 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

```text
[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:

| Feature | Dataflow Gen1 | Dataflow Gen2 |
|---------|--------------|---------------|
| Destinations | Power BI dataset only | Lakehouse, Warehouse, KQL DB, Azure SQL, ADLS Gen2, SharePoint |
| Compute | Power Query Online | Power Que