Guide Power BI Data

An expert agent for Power BI data modeling covering star schema design, relationship management, role-level security, performance optimization, and best practices following Microsoft's official recommendations for enterprise Power BI deployments.

When to Use This Agent

Choose Power BI Data Guide when:

• Designing star schema data models for Power BI reports
• Configuring table relationships, cardinality, and cross-filter direction
• Implementing row-level security for multi-tenant reports
• Optimizing model size and query performance for large datasets
• Migrating from flat table designs to proper dimensional models

Consider alternatives when:

• Writing DAX formulas without model design changes (use a DAX agent)
• Building Power Automate flows alongside Power BI (use a Power Platform agent)
• Working with non-Microsoft BI tools like Tableau or Looker

Quick Start

Copy
# .claude/agents/guide-power-bi-data.yml
name: Power BI Data Modeling Guide
model: claude-sonnet-4-20250514
tools:
  - Read
  - Write
  - Bash
  - Glob
  - Grep
prompt: |
  You are a Power BI data modeling expert following Microsoft's
  official recommendations. Design star schemas, optimize
  relationships, implement RLS, and improve query performance.
  Always reference current Microsoft documentation for best practices.

Example invocation:

Copy
claude --agent guide-power-bi-data "Redesign our flat sales table
  into a proper star schema with date, product, customer, and
  geography dimensions. Include surrogate keys and handle slowly
  changing dimensions."

Core Concepts

Star Schema Design

        ┌──────────┐
        │ DimDate  │
        │ DateKey  │
        └────┬─────┘
             │
┌──────────┐ │ ┌───────────┐
│DimProduct├─┼─┤FactSales  │
│ProductKey│ │ │DateKey     │
└──────────┘ │ │ProductKey  │
             │ │CustomerKey │
┌──────────┐ │ │Amount      │
│DimCustomer├─┘ │Quantity    │
│CustomerKey│   └───────────┘
└──────────┘

Relationship Types

Relationship	Use Case	Cross-Filter
One-to-many	Dimension to fact	Single direction
Many-to-many	Bridge tables	Both directions (with care)
One-to-one	Extended dimension attributes	Single direction

Model Optimization Techniques

Technique	Impact	When to Apply
Remove unused columns	Reduce model size 20-50%	Always
Use integer keys instead of strings	Faster joins, smaller model	Always
Disable auto date/time	Remove hidden date tables	When custom date dim exists
Set column data types correctly	Reduce memory usage	During import
Summarize large fact tables	Reduce row count	When detail isn't needed
Use aggregation tables	Speed up high-level queries	Large models (>100M rows)

Configuration

Parameter	Description	Default
storage_mode	Import, DirectQuery, or Composite	Import
auto_datetime	Auto date/time tables	Disabled
cross_filter	Default cross-filter direction	Single
rls_enabled	Row-level security	false
incremental_refresh	Incremental data refresh	Disabled
aggregations	Pre-aggregated tables	None
calculation_groups	Calculation group usage	As needed

Best Practices

1. Build star schemas, not flat tables. A single wide table with denormalized data seems simpler but creates ambiguous relationships, duplicate data, and poor performance at scale. Split into fact tables (events, transactions with measures) and dimension tables (lookup data with attributes). Star schemas compress better, query faster, and produce correct DAX calculations without workarounds.

2. Use surrogate integer keys for all relationships. Natural keys (product codes, email addresses) are longer and slower to join than integer surrogate keys. Create integer key columns in each dimension and reference them in fact tables. This reduces model size, improves join performance, and decouples your data model from source system key formats that may change.

3. Build a dedicated date dimension table. Disable Power BI's auto date/time feature and create an explicit date dimension with all the time intelligence attributes you need: fiscal year, quarter, week number, holiday flags, and custom periods. A shared date dimension ensures consistent time-based filtering across all fact tables and enables DAX time intelligence functions.

4. Set cross-filter direction to single wherever possible. Bidirectional cross-filtering creates ambiguous filter paths when multiple relationships exist between tables. Use single direction (dimension filters fact) as the default. Only enable bidirectional filtering when specifically needed for a visual, and understand the performance implications. Many-to-many relationships with bidirectional filtering are the most common source of incorrect results.

5. Remove columns that visuals and measures don't use. Every column in an imported model consumes memory regardless of whether any report uses it. Audit your model periodically and remove columns that no measure references and no visual displays. A 50-column table where only 12 columns are used wastes memory on 38 unnecessary columns. This optimization often reduces model size by 30-50%.

Common Issues

DAX measures return unexpected results with bidirectional relationships. Bidirectional cross-filtering propagates filters in both directions, which can cause measures to double-count or filter incorrectly when multiple paths exist between tables. Switch to single-direction filtering and use CROSSFILTER or TREATAS in DAX to control filter propagation explicitly. This makes filter behavior predictable and debuggable.

Model refresh takes too long or times out. For large datasets, implement incremental refresh to process only new and changed data. Partition the fact table by date and configure refresh policies that re-process recent partitions while keeping historical data static. Also check Power Query steps for performance-killing operations: unnecessary column expansions, applied to every row rather than folded back to the source.

Row-level security filters don't work correctly across relationships. RLS filters applied to a dimension table must propagate to fact tables through relationships. Verify that the relationship direction allows filter propagation from the RLS-filtered table to the fact table. Test RLS using the "View as role" feature with specific user accounts. Common mistake: applying RLS to the fact table directly instead of the dimension, which requires different DAX expressions.