Here’s a comprehensive solution to address all aspects of your Power BI performance degradation:
1. Query Optimization
Refactor your SQL query for cloud efficiency:
SELECT
ft.TransDate,
ft.Amount,
d1.AccountNumber,
d1.AccountName,
d2.ProjectID,
d2.ProjectName
FROM FactTransaction ft WITH (NOLOCK)
INNER JOIN DimAccount d1 ON ft.AccountKey = d1.AccountKey
INNER JOIN DimProject d2 ON ft.ProjectKey = d2.ProjectKey
WHERE ft.TransDate >= DATEADD(month, -3, GETDATE())
AND ft.IsDeleted = 0
Key changes: Specify exact columns needed, use NOLOCK hint for reporting queries, add IsDeleted filter to reduce row scans, limit date range to recent data only.
2. Cloud Indexing Strategy
Implement targeted indexes for your query patterns:
- Create non-clustered index on FactTransaction(TransDate, AccountKey, ProjectKey) INCLUDE (Amount)
- Index DimAccount(AccountKey) if not already clustered
- Index DimProject(ProjectKey) if not already clustered
- Enable automatic index tuning in Azure SQL: ALTER DATABASE SCOPED CONFIGURATION SET AUTOMATIC_TUNING = AUTO
- Monitor index recommendations in Azure Portal under your SQL database > Performance recommendations
3. Incremental Refresh Strategy
Configure Power BI incremental refresh properly:
- Set refresh range to last 2-3 months (not 12 months)
- Archive older data: Keep 24 months historical, refresh only recent
- Use RangeStart and RangeEnd parameters in your Power Query
- Enable “Detect data changes” using a reliable change tracking column (ModifiedDate)
- Schedule refreshes during off-peak hours (2-4 AM) when database load is lower
In Power BI Desktop:
- Create parameters: RangeStart (DateTime), RangeEnd (DateTime)
- Filter your query: Table.SelectRows(Source, each [TransDate] >= RangeStart and [TransDate] < RangeEnd)
- Configure incremental refresh policy: Refresh last 3 months, Store 24 months
4. Aggregation Tables
Create pre-aggregated tables to reduce computation:
CREATE VIEW vw_MonthlyTransactionSummary AS
SELECT
DATEADD(month, DATEDIFF(month, 0, ft.TransDate), 0) AS MonthDate,
ft.AccountKey,
ft.ProjectKey,
SUM(ft.Amount) AS TotalAmount,
COUNT(*) AS TransactionCount
FROM FactTransaction ft
GROUP BY
DATEADD(month, DATEDIFF(month, 0, ft.TransDate), 0),
ft.AccountKey,
ft.ProjectKey
In Power BI, configure aggregation management:
- Create a separate aggregation table in your model
- Set up aggregation mappings: Detail table → Aggregation table
- Power BI will automatically use aggregations when query grain matches
- Test with Performance Analyzer to verify aggregation usage
5. Resource Monitoring and Scaling
Optimize Azure SQL database resources:
- Upgrade from S3 (100 DTUs) to S6 (400 DTUs) or S7 (800 DTUs) for analytics workload
- Consider elastic pool if you have multiple databases
- Enable Query Performance Insight in Azure Portal
- Set up alerts for DTU usage > 80% sustained
- Monitor query execution using Azure SQL Analytics workspace
Key metrics to track:
- DTU percentage (should stay below 80% during refresh)
- Query duration (identify queries > 30 seconds)
- Wait statistics (high PAGEIOLATCH indicates I/O bottleneck)
- Deadlocks and timeouts (should be zero)
6. Power BI Optimization
Additional Power BI dataset improvements:
- Use Import mode instead of DirectQuery where possible for better performance
- Remove unused columns and tables from your model
- Disable auto date/time in Power BI Desktop options
- Optimize DAX measures to use CALCULATE instead of FILTER
- Enable query folding in Power Query transformations
- Split large datasets into multiple smaller datasets if needed
7. Implementation Roadmap
Phased approach:
- Week 1: Implement query optimization and add critical indexes
- Week 2: Configure incremental refresh with 3-month window
- Week 3: Create and deploy aggregation tables
- Week 4: Scale up Azure SQL tier and monitor performance
- Week 5: Fine-tune based on Query Performance Insight recommendations
8. Validation and Testing
Measure improvements:
- Baseline current refresh time (45+ minutes)
- After query optimization: Target 25-30 minutes
- After incremental refresh: Target 15-20 minutes
- After aggregations: Target 8-12 minutes
- After resource scaling: Target 5-8 minutes
Use Power BI Performance Analyzer to identify remaining bottlenecks and continue optimizing. This comprehensive approach should restore and even improve upon your pre-migration performance levels.
This draft is based on general Microsoft Dynamics 365 knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.