Power BI Core Concepts and Data Modeling

Overview

Core Power BI knowledge covering data modeling best practices, connectivity modes, source types, relationships, and common pitfalls. This skill provides the foundational architecture guidance every Power BI developer needs.

Data Model Design - Star Schema

Always design data models using star schema topology:

Component	Purpose	Example
Fact table	Numeric events/transactions	Sales, Orders, WebVisits
Dimension table	Descriptive attributes	Date, Product, Customer, Geography
Bridge table	Many-to-many resolution	StudentCourse, OrderProduct

Mandatory rules:

• One fact table at the center, dimensions radiating out
• Relationships flow from dimension (one side) to fact (many side)
• Use surrogate integer keys, not natural/business keys
• Keep fact tables narrow (keys + measures only)
• Denormalize dimensions (flatten snowflake into star)
• Create a dedicated Date dimension table (disable auto date/time)
• Never use bidirectional cross-filtering unless absolutely required and contained

Storage Modes

Mode	Data Location	Refresh	Performance	Use When
Import	In-memory VertiPaq	Scheduled/on-demand	Fastest queries	Default choice, data under 1GB compressed
DirectQuery	Source database	Real-time	Depends on source	Real-time needed, data too large for import
Dual	Both	Scheduled + real-time	Best of both	Dimension tables in composite models
Direct Lake	OneLake delta tables	Framing (seconds)	Near-import speed	Fabric lakehouse/warehouse scenarios

Import mode considerations:

• 1GB PBIX file size limit (10GB for Premium/PPU in service)
• Data is a snapshot at refresh time; not real-time
• Scheduled refresh limit: 8/day (Pro), 48/day (Premium/PPU)

DirectQuery limitations:

• No Power Query transformations applied at query time
• Single source per model (unless composite)
• Performance depends entirely on source query speed
• Many DAX functions unavailable or degraded
• No calculated columns on DirectQuery tables
• Row limit of 1 million rows per visual query

Direct Lake key considerations (2025-2026 GA):

• Two variants: Direct Lake on OneLake (DL/OL) and Direct Lake on SQL endpoints (DL/SQL)
• DL/OL does NOT fall back to DirectQuery -- queries fail if data cannot be served
• DL/SQL CAN fall back to DirectQuery via SQL analytics endpoint
• Guardrails vary by capacity: F32 allows up to 1,000 files/row groups per table; F64/P1 allows up to 5,000
• Max Memory is a soft limit for paging, not a hard guardrail -- excess paging hurts performance
• Max model size on disk/OneLake IS a hard guardrail -- exceeding causes DQ fallback (DL/SQL) or failure (DL/OL)
• Full DAX support including calculated columns
• Framing (metadata-only refresh) completes in seconds
• Power BI Embedded with Direct Lake mode is GA since March 2025

Choosing storage mode decision tree:

1. Data in Fabric OneLake delta tables? Use Direct Lake
2. Need real-time data, source is fast? Use DirectQuery
3. Data under 1GB, can tolerate refresh lag? Use Import (best performance)
4. Large data + need fast queries? Use composite model (Import dimensions + DQ facts + aggregation tables)

Relationships

Property	Options	Default
Cardinality	One-to-many, Many-to-one, One-to-one, Many-to-many	One-to-many
Cross-filter direction	Single, Both	Single
Active	Yes/No	Yes (only one active per path)

Relationship rules:

• Only one active relationship between any two tables
• Use USERELATIONSHIP() in DAX to activate inactive relationships
• Avoid bidirectional filtering -- it causes ambiguous filter paths, performance degradation, and unexpected results
• Many-to-many requires a bridge table or composite model many-to-many cardinality
• Referential integrity: set "Assume Referential Integrity" for DirectQuery performance

Data Sources Quick Reference

Category	Sources
Microsoft SQL	SQL Server, Azure SQL, Azure Synapse, SQL Server Analysis Services
Azure	Cosmos DB, Data Explorer (Kusto), Blob Storage, Data Lake, Fabric Lakehouse/Warehouse
Cloud Databases	Snowflake, Databricks, Google BigQuery, Amazon Redshift, Amazon Athena
Files	Excel, CSV/TSV, JSON, XML, Parquet, PDF
Services	SharePoint, Dynamics 365, Salesforce, Google Analytics, Azure DevOps
Protocols	OData, REST API, ODBC, OLEDB
Streaming	Azure Stream Analytics, PubNub, REST API push

Incremental Refresh

Configure incremental refresh for large Import tables to avoid full refresh:

1. Create RangeStart and RangeEnd parameters (type DateTime) in Power Query
2. Apply filter on the date column using these parameters
3. Configure refresh policy: archive period (e.g., 3 years), incremental period (e.g., 30 days)
4. Optionally enable "detect data changes" with a last-modified column
5. Optionally enable real-time data with DirectQuery for the latest partition

Requirements: Premium, PPU, or Fabric capacity for more than basic incremental refresh. Pro workspaces support incremental refresh but with limitations.

2025-2026 improvements:

• Semantic models with incremental refresh can now be edited directly in Power BI Service (change calculated columns, rename tables, adjust hierarchies) without reopening Desktop
• Enhanced refresh API supports selective partition refresh for finer control
• Improved performance for terabyte-scale datasets with faster partition processing

Gateway Configuration

On-premises data gateway bridges on-premises sources to Power BI Service:

Gateway Type	Use Case
Standard (enterprise)	Shared by multiple users, centrally managed
Personal	Single user, development/testing only
Virtual Network (VNet)	Azure VNet-connected sources, no on-prem hardware

VNet data gateway (2025-2026):

• Connects to Azure data sources within a VNet without on-premises hardware
• Managed by Fabric/Power BI Service, no gateway machine maintenance
• Supports Azure SQL, Synapse, Azure Data Explorer, and other VNet-bound services
• Enable in Fabric Admin portal under gateway management

Gateway releases (2025-2026):

• Monthly releases throughout 2025-2026 with enhanced caching and query folding
• Improved query performance through optimized connection pooling
• 64-bit only for Power BI Desktop for Report Server starting September 2025

Common gateway failures:

• Credentials expired -- update in gateway settings
• Source unreachable -- check firewall, VPN, DNS
• Memory exhaustion -- monitor gateway machine resources
• Mashup engine crash -- check Power Query complexity

Data Source Authentication

Method	Use Case	Best For
OAuth2	Cloud sources (Azure SQL, Snowflake, Databricks)	Interactive use, SSO
Service Principal	Automated refresh, CI/CD pipelines	Unattended operations
Workspace Identity	Fabric workspaces (no secret to manage)	Fabric-native models
Managed Identity	Dataflows Gen2 to Azure sources	Zero-secret PaaS access
Username/Password	Legacy on-prem sources	Gateway-bound sources

Workspace Identity (2025-2026):

• Tied to a Fabric workspace, similar to Azure Managed Identity
• No expiration, no secret or password to manage
• Configure in workspace settings, assign to semantic model data sources
• Preferred over service principal for Fabric-native scenarios

OAuth2 token limitation: When set via REST API (not UI), OAuth2 credentials lack a refresh token and expire after 1 hour. Use service principal for long-running automation.

Connection pooling best practices:

• Gateway reuses connections where possible -- minimize distinct credential sets
• Set query timeout in data source settings (default 5 min, increase for complex queries)
• Implement retry logic in Power Query for transient source failures using try/otherwise

Common Gotchas and Anti-Patterns

Pitfall	Impact	Fix
Auto date/time enabled	Hidden date tables bloat model (one per date column)	Disable in Options > Data Load
Implicit measures (drag numeric to visual)	No control over aggregation, no reuse	Create explicit DAX measures
Bidirectional cross-filter	Ambiguity, performance degradation, wrong results	Use single-direction, handle in DAX
Too many columns in fact tables	Bloated model, slow refresh, wasted memory	Keep facts narrow: keys + numeric values
BLANK vs 0 vs null confusion	DAX treats BLANK differently from 0; visuals hide BLANK rows	Use IF/COALESCE to handle explicitly
Circular dependency errors	Usually from calculated columns referencing each other or bidirectional filters	Restructure model, break the cycle
1GB PBIX limit	Cannot save file locally	Remove unused columns, optimize cardinality
Power BI Service vs Desktop gap	Some features only available in one or the other	Check feature matrix before designing
Calculated columns vs measures	Calculated columns consume memory, stored per row	Prefer measures (computed at query time)
String columns in fact tables	High cardinality strings destroy VertiPaq compression	Move to dimension table, use key reference

Additional Resources

Reference Files

• references/data-sources-detail.md -- Detailed connector configuration for all source types
• references/gotchas-deep-dive.md -- Extended pitfall analysis with examples and resolution patterns