power-bi-performance-troubleshooting

Data Model Analysis:
✓ Model size and complexity
✓ Relationship design and cardinality
✓ Storage mode configuration (Import/DirectQuery/Composite)
✓ Data types and compression efficiency
✓ Calculated columns vs. measures usage
✓ Date table implementation

Common Model Issues:
- Large model size due to unnecessary columns/rows
- Inefficient relationships (many-to-many, bidirectional)
- High-cardinality text columns
- Excessive calculated columns
- Missing or improper date tables
- Poor data type selections

DAX Formula Analysis:
✓ Complex calculations without variables
✓ Inefficient aggregation functions
✓ Context transition overhead
✓ Iterator function optimization
✓ Filter context complexity
✓ Error handling patterns

Performance Anti-Patterns:
- Repeated calculations (missing variables)
- FILTER() used as filter argument
- Complex calculated columns in large tables
- Nested CALCULATE functions
- Inefficient time intelligence patterns

Report Performance Analysis:
✓ Number of visuals per page (max 6-8 recommended)
✓ Visual types and complexity
✓ Cross-filtering configuration
✓ Slicer query efficiency
✓ Custom visual performance impact
✓ Mobile layout optimization

Common Report Issues:
- Too many visuals causing resource competition
- Inefficient cross-filtering patterns
- High-cardinality slicers
- Complex custom visuals
- Poorly optimized visual interactions

Infrastructure Assessment:
✓ Capacity utilization (CPU, memory, query volume)
✓ Network connectivity and bandwidth
✓ Data source performance
✓ Gateway configuration and performance
✓ Concurrent user load patterns
✓ Geographic distribution considerations

Capacity Indicators:
- High CPU utilization (>70% sustained)
- Memory pressure warnings
- Query queuing and timeouts
- Gateway performance bottlenecks
- Network latency issues

Performance Analyzer:
- Enable and record visual refresh times
- Identify slowest visuals and operations
- Compare DAX query vs. visual rendering time
- Export results for detailed analysis

Usage:
1. Open Performance Analyzer pane
2. Start recording
3. Refresh visuals or interact with report
4. Analyze results by duration
5. Focus on highest duration items first

Advanced DAX Analysis:
- Query execution plans
- Storage engine vs. formula engine usage
- Memory consumption patterns
- Query performance metrics
- Server timings analysis

Key Metrics to Monitor:
- Total duration
- Formula engine duration
- Storage engine duration
- Scan count and efficiency
- Memory usage patterns

Fabric Capacity Metrics App:
- CPU and memory utilization trends
- Query volume and patterns  
- Refresh performance tracking
- User activity analysis
- Resource bottleneck identification

Premium Capacity Monitoring:
- Capacity utilization dashboards
- Performance threshold alerts
- Historical trend analysis
- Workload distribution assessment

-- Replace inefficient patterns:
 
❌ Poor Performance:
Sales Growth = 
([Total Sales] - CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))) / 
CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))
 
✅ Optimized Version:
Sales Growth = 
VAR CurrentMonth = [Total Sales]
VAR PreviousMonth = CALCULATE([Total Sales], PREVIOUSMONTH('Date'[Date]))
RETURN
    DIVIDE(CurrentMonth - PreviousMonth, PreviousMonth)

Import Mode Optimization:
- Data reduction techniques
- Pre-aggregation strategies
- Incremental refresh implementation
- Compression optimization

DirectQuery Optimization:
- Database index optimization
- Query folding maximization
- Aggregation table implementation
- Connection pooling configuration

Composite Model Strategy:
- Strategic storage mode selection
- Cross-source relationship optimization
- Dual mode dimension implementation
- Performance monitoring setup

Capacity Scaling Considerations:
- Vertical scaling (more powerful capacity)
- Horizontal scaling (distributed workload)
- Geographic distribution optimization
- Load balancing implementation

Gateway Optimization:
- Dedicated gateway clusters
- Load balancing configuration
- Connection optimization
- Performance monitoring setup

□ Check Performance Analyzer for obvious bottlenecks
□ Reduce number of visuals on slow-loading pages
□ Apply default filters to reduce data volume
□ Disable unnecessary cross-filtering
□ Check for missing relationships causing cross-joins
□ Verify appropriate storage modes
□ Review and optimize top 3 slowest DAX measures

□ Complete model architecture review
□ DAX optimization using variables and efficient patterns
□ Report design optimization and restructuring
□ Data source performance analysis
□ Capacity utilization assessment
□ User access pattern analysis
□ Mobile performance testing
□ Load testing with realistic concurrent users

□ Complete data model redesign if necessary
□ Implementation of aggregation strategies
□ Infrastructure scaling planning
□ Monitoring and alerting setup
□ User training on efficient usage patterns
□ Performance governance implementation
□ Continuous monitoring and optimization process

Key Performance Indicators:
- Average page load time by report
- Query execution time percentiles
- Model refresh duration trends
- Capacity utilization patterns
- User adoption and usage metrics
- Error rates and timeout occurrences

Alerting Thresholds:
- Page load time >15 seconds
- Query execution time >45 seconds
- Capacity CPU >80% for >10 minutes
- Memory utilization >90%
- Refresh failures
- High error rates

Weekly:
□ Review performance dashboards
□ Check capacity utilization trends
□ Monitor slow-running queries
□ Review user feedback and issues

Monthly:
□ Comprehensive performance analysis
□ Model optimization opportunities
□ Capacity planning review
□ User training needs assessment

Quarterly:
□ Strategic performance review
□ Technology updates and optimizations
□ Scaling requirements assessment
□ Performance governance updates

Performance Issue Report:

Issue Description:
- What specific performance problem is occurring?
- When does it happen (always, specific times, certain conditions)?
- Who is affected (all users, specific groups, particular reports)?

Performance Metrics:
- Current performance measurements
- Expected performance targets
- Comparison with previous performance

Environment Details:
- Report/model names affected
- User locations and network conditions
- Browser and device information
- Capacity and infrastructure details

Impact Assessment:
- Business impact and urgency
- Number of users affected
- Critical business processes impacted
- Workarounds currently in use

Solution Summary:
- Root cause analysis results
- Optimization changes implemented
- Performance improvement achieved
- Validation and testing completed

Implementation Details:
- Step-by-step changes made
- Configuration modifications
- Code changes (DAX, model design)
- Infrastructure adjustments

Results and Follow-up:
- Before/after performance metrics
- User feedback and validation
- Monitoring setup for ongoing health
- Recommendations for similar issues