Home > Docs > Features > Composite Models

🔀 Composite Models - Mixed Storage Mode Semantic Models¶

Last Updated: 2026-04-21 | Version: 1.0.0

📑 Table of Contents¶

• 🎯 Overview
• 🏗️ Architecture
• ⚙️ Storage Modes
• 🔗 Relationship Rules
• 📊 Query Delegation and Performance
• 📐 Aggregation Tables
• 🎰 Casino Implementation
• 🏛️ Federal Agency Implementation
• 🔐 Security
• ⚠️ Limitations
• 📚 References
• 🔗 Related Documents

🎯 Overview¶

Composite Models in Power BI allow a single semantic model to contain tables with different storage modes — Import, DirectQuery, and Direct Lake — in the same model. This enables architects to optimize each table's storage independently: import small reference data for maximum performance, use Direct Lake for large fact tables that need freshness, and connect via DirectQuery to external sources that cannot be copied into OneLake.

Why Composite Models Matter¶

Real-world analytics rarely fit a single storage mode. A casino dashboard might need sub-second performance on slot telemetry (Direct Lake from OneLake), always-current player loyalty data (DirectQuery to an operational database), and a static denomination lookup table (Import). Composite models make this possible in a single semantic model without duplicating data or maintaining multiple reports.

Key Capabilities¶

When to Use Composite Models¶

🏗️ Architecture¶

Composite Model Storage Modes¶

flowchart TB
    subgraph Model["📊 Composite Semantic Model"]
        subgraph Import["💾 Import Mode"]
            I1["dim_date<br/>(50K rows)"]
            I2["dim_denomination<br/>(20 rows)"]
            I3["dim_game_type<br/>(200 rows)"]
            I4["agg_daily_revenue<br/>(pre-aggregated)"]
        end

        subgraph DirectLake["⚡ Direct Lake Mode"]
            DL1["fact_slot_performance<br/>(500M rows)"]
            DL2["fact_player_sessions<br/>(200M rows)"]
            DL3["fact_transactions<br/>(1B rows)"]
        end

        subgraph DQ["🔄 DirectQuery Mode"]
            DQ1["live_machine_status<br/>(real-time from Eventhouse)"]
            DQ2["player_loyalty<br/>(operational CRM)"]
        end
    end

    subgraph Storage["💾 Storage Layer"]
        OL["OneLake<br/>Delta Tables"]
        EH["Eventhouse<br/>KQL Database"]
        CRM["External CRM<br/>SQL Database"]
    end

    DL1 --> OL
    DL2 --> OL
    DL3 --> OL
    DQ1 --> EH
    DQ2 --> CRM
    I1 -->|"Imported at refresh"| OL
    I4 -->|"Imported at refresh"| OL

    style Import fill:#27AE60,stroke:#1E8449,color:#fff
    style DirectLake fill:#8E44AD,stroke:#6C3483,color:#fff
    style DQ fill:#E67E22,stroke:#CA6F1E,color:#fff
    style Storage fill:#2E86C1,stroke:#1A5276,color:#fff

Query Execution Flow¶

sequenceDiagram
    participant User
    participant PBI as Power BI Engine
    participant VP as VertiPaq (In-Memory)
    participant DL as Direct Lake (OneLake)
    participant DQ as DirectQuery Source

    User->>PBI: DAX query (revenue by date with live status)
    PBI->>PBI: Analyze query plan
    PBI->>VP: Read dim_date (Import)
    VP-->>PBI: Date dimension rows
    PBI->>DL: Read fact_slot_performance (Direct Lake)
    DL-->>PBI: Fact column segments
    PBI->>DQ: SQL query for live_machine_status
    DQ-->>PBI: Current status rows
    PBI->>PBI: Join results across storage modes
    PBI-->>User: Combined result set

⚙️ Storage Modes¶

Storage Mode Comparison¶

Choosing Storage Mode per Table¶

flowchart TD
    START["New Table"] --> Q1{"In OneLake?"}
    Q1 -->|Yes| Q2{"Large<br/>(>100K rows)?"}
    Q1 -->|No| Q3{"Need real-time?"}
    Q2 -->|Yes| DL["⚡ Direct Lake"]
    Q2 -->|No| IMP["💾 Import"]
    Q3 -->|Yes| DQM["🔄 DirectQuery"]
    Q3 -->|No| Q4{"Can copy to<br/>OneLake?"}
    Q4 -->|Yes| IMP
    Q4 -->|No| DQM

    style DL fill:#8E44AD,stroke:#6C3483,color:#fff
    style IMP fill:#27AE60,stroke:#1E8449,color:#fff
    style DQM fill:#E67E22,stroke:#CA6F1E,color:#fff

Setting Storage Mode in Power BI Desktop¶

⚠️ Warning: Changing a table from Import to DirectQuery is irreversible in that session. Save a backup of your .pbix before changing storage modes.

Dual Storage Mode¶

Tables set to Dual mode are stored both in-memory (Import) and maintain a DirectQuery connection. The engine automatically chooses the optimal path:

• Joins with Import tables → uses the in-memory copy
• Joins with DirectQuery tables → uses the DirectQuery path

This avoids the limited-relationship penalty when a dimension needs to join with both Import and DirectQuery fact tables.

🔗 Relationship Rules¶

Limited Relationships¶

When tables have different storage modes, relationships between them may become limited — meaning the engine cannot guarantee referential integrity and must apply cross-source join strategies.

Impact of Limited Relationships¶

Design Pattern: Minimize Limited Relationships¶

flowchart LR
    subgraph Optimal["✅ Optimal: Dual Dimensions"]
        DD["dim_date<br/>(Dual Mode)"]
        DL_F["fact_performance<br/>(Direct Lake)"]
        DQ_F["live_status<br/>(DirectQuery)"]
        DD -->|Regular| DL_F
        DD -->|Regular| DQ_F
    end

    subgraph Suboptimal["⚠️ Suboptimal: Import Dimensions"]
        ID["dim_date<br/>(Import)"]
        DL_F2["fact_performance<br/>(Direct Lake)"]
        DQ_F2["live_status<br/>(DirectQuery)"]
        ID -->|Limited| DL_F2
        ID -->|Limited| DQ_F2
    end

    style Optimal fill:#27AE60,stroke:#1E8449,color:#fff
    style Suboptimal fill:#E74C3C,stroke:#C0392B,color:#fff

💡 Tip: Set shared dimension tables (dim_date, dim_location) to Dual mode to maintain regular relationships with both Direct Lake fact tables and DirectQuery sources.

📊 Query Delegation and Performance¶

How the Engine Decides¶

When a DAX query spans multiple storage modes, the engine:

1. Decomposes the query into per-source sub-queries
2. Pushes down filters and aggregations to each source
3. Retrieves partial results from each source
4. Joins partial results in-memory
5. Evaluates remaining DAX on the joined result

Performance Optimization Matrix¶

Performance Anti-Patterns¶

📐 Aggregation Tables¶

What Are Aggregation Tables?¶

Aggregation tables are pre-summarized Import-mode tables that the engine automatically uses when a query can be answered at a higher grain. They sit "in front of" a DirectQuery or Direct Lake detail table and intercept compatible queries.

Aggregation Flow¶

flowchart TB
    subgraph Query["📊 User Query"]
        Q["Total Revenue by Month"]
    end

    subgraph Engine["⚙️ Engine Decision"]
        CHECK{"Can agg table<br/>answer this?"}
    end

    subgraph Fast["⚡ Aggregation Path"]
        AGG["agg_monthly_revenue<br/>(Import, 1K rows)"]
    end

    subgraph Slow["🐢 Detail Path"]
        DETAIL["fact_slot_performance<br/>(Direct Lake, 500M rows)"]
    end

    Query --> Engine
    CHECK -->|"Yes — grain matches"| AGG
    CHECK -->|"No — need detail"| DETAIL

    style Fast fill:#27AE60,stroke:#1E8449,color:#fff
    style Slow fill:#E67E22,stroke:#CA6F1E,color:#fff

Aggregation Table Example¶

-- Pre-aggregated monthly revenue (materialized in Gold layer)
CREATE TABLE agg_monthly_revenue AS
SELECT
    date_key_month,
    location_id,
    game_type,
    SUM(coin_in) AS total_coin_in,
    SUM(revenue) AS total_revenue,
    AVG(hold_pct) AS avg_hold_pct,
    COUNT(*) AS record_count
FROM lh_gold.gold_slot_performance
GROUP BY date_key_month, location_id, game_type

🎰 Casino Implementation¶

Real-Time Slot Dashboard with Historical Context¶

The casino composite model combines three storage modes to deliver a unified dashboard:

Model Design¶

erDiagram
    fact_slot_performance ||--o{ dim_machine : "machine_id"
    fact_slot_performance ||--o{ dim_location : "location_id"
    fact_slot_performance ||--o{ dim_date : "date_key"
    live_machine_status ||--o{ dim_machine : "machine_id"
    dim_denomination }o--|| dim_machine : "denomination_id"

    fact_slot_performance {
        int machine_id FK "Direct Lake"
        int location_id FK "Direct Lake"
        int date_key FK "Direct Lake"
        decimal coin_in "Direct Lake"
        decimal revenue "Direct Lake"
        decimal hold_pct "Direct Lake"
    }

    live_machine_status {
        int machine_id FK "DirectQuery (Eventhouse)"
        string current_state "DirectQuery"
        datetime last_event "DirectQuery"
        decimal current_credits "DirectQuery"
        string active_player_id "DirectQuery"
    }

    dim_machine {
        int machine_id PK "Dual"
        string game_title "Dual"
        decimal denomination "Dual"
        string manufacturer "Dual"
    }

    dim_location {
        int location_id PK "Dual"
        string floor_zone "Dual"
        string building "Dual"
    }

    dim_date {
        int date_key PK "Dual"
        date full_date "Dual"
        int month "Dual"
    }

    dim_denomination {
        int denomination_id PK "Import"
        decimal value "Import"
        string label "Import"
    }

Storage Mode Assignments¶

Key DAX Measures¶

// Historical Revenue (from Direct Lake fact table)
Total Revenue =
SUM(fact_slot_performance[revenue])

// Live Machine Count (from DirectQuery Eventhouse)
Active Machines Now =
COUNTROWS(
    FILTER(
        live_machine_status,
        live_machine_status[current_state] = "Active"
    )
)

// Combined: Revenue per Active Machine
Revenue Per Active Machine =
DIVIDE(
    [Total Revenue],
    [Active Machines Now],
    0
)

// Uses aggregation table automatically for monthly grain
Monthly Revenue Trend =
CALCULATE(
    [Total Revenue],
    ALLEXCEPT(dim_date, dim_date[month], dim_date[year])
)

🏛️ Federal Agency Implementation¶

NOAA Real-Time Weather + Historical Analysis¶

A composite model combining real-time weather data from Eventhouse (DirectQuery) with historical climate data from the Lakehouse (Direct Lake).

Model Design¶

erDiagram
    gold_noaa_observations ||--o{ dim_station : "station_id"
    gold_noaa_observations ||--o{ dim_date : "date_key"
    live_weather_readings ||--o{ dim_station : "station_id"
    gold_noaa_storm_events ||--o{ dim_date : "date_key"

    gold_noaa_observations {
        string station_id FK "Direct Lake"
        int date_key FK "Direct Lake"
        decimal temperature_f "Direct Lake"
        decimal precipitation_in "Direct Lake"
        decimal wind_speed_mph "Direct Lake"
    }

    live_weather_readings {
        string station_id FK "DirectQuery (Eventhouse)"
        decimal current_temp "DirectQuery"
        decimal current_wind "DirectQuery"
        string alert_level "DirectQuery"
        datetime reading_time "DirectQuery"
    }

    dim_station {
        string station_id PK "Dual"
        string station_name "Dual"
        string state "Dual"
        decimal latitude "Dual"
        decimal longitude "Dual"
    }

    dim_date {
        int date_key PK "Dual"
        date full_date "Dual"
    }

    gold_noaa_storm_events {
        int event_id PK "Direct Lake"
        int date_key FK "Direct Lake"
        string event_type "Direct Lake"
        decimal damage_property "Direct Lake"
    }

Storage Mode Assignments¶

Key DAX Measures¶

// Historical Average Temperature (Direct Lake)
Avg Historical Temperature =
AVERAGE(gold_noaa_observations[temperature_f])

// Current Temperature (DirectQuery to Eventhouse)
Current Temperature =
AVERAGE(live_weather_readings[current_temp])

// Temperature Deviation from Historical
Temperature Anomaly =
[Current Temperature] - [Avg Historical Temperature]

// Stations with Active Alerts (DirectQuery)
Stations with Alerts =
COUNTROWS(
    FILTER(
        live_weather_readings,
        live_weather_readings[alert_level] <> "None"
    )
)

Multi-Agency Executive Dashboard¶

A chained composite model that connects to individual agency semantic models via DirectQuery and adds cross-agency Import reference tables:

🔐 Security¶

RLS in Composite Models¶

RLS applies independently per storage mode:

Cross-Source Security Considerations¶

Security Best Practices¶

⚠️ Limitations¶

Feature Availability¶

📚 References¶

🔗 Related Documents¶

• Direct Lake -- Direct Lake storage mode details and guardrails
• Real-Time Intelligence -- Eventhouse as a DirectQuery source
• Paginated Reports -- Print-ready reports using composite model data
• Scorecards & Metrics -- KPI tracking with composite model measures
• Fabric IQ -- Natural language queries over composite semantic models
• Materialized Lake Views -- Pre-computed views for aggregation tables
• Architecture -- System architecture overview
• Security -- Security and compliance framework

📝 Document Metadata - Author: Documentation Team - Reviewers: Data Engineering, BI Team, Architecture - Classification: Internal - Next Review: 2026-07-21