After migrating our D365 Finance environment to cloud, we’re experiencing severe performance degradation with Power BI analytics reports. Reports that previously refreshed in 8-10 minutes now take 45+ minutes, causing significant delays in management reporting.
Our largest report pulls data from multiple fact tables:
SELECT ft.TransDate, ft.Amount, d1.*, d2.*
FROM FactTransaction ft
JOIN DimAccount d1 ON ft.AccountKey = d1.AccountKey
JOIN DimProject d2 ON ft.ProjectKey = d2.ProjectKey
WHERE ft.TransDate >= '2024-01-01'
We’ve noticed that query optimization hasn’t been applied to cloud indexing, and our incremental refresh strategy may not be configured correctly for the cloud environment. The aggregation tables we used on-premises don’t seem to provide the same performance benefits in cloud. Resource monitoring shows high CPU utilization during refresh operations, but we’re not sure how to interpret the cloud-specific metrics. This is impacting our ability to deliver timely financial analytics to executives.
Thanks for the suggestions. I’ve checked our Azure SQL tier and we’re on S3 (100 DTUs). Should we be considering a higher tier for our analytics workload? Regarding incremental refresh, we have it enabled but set to refresh the last 12 months. Would reducing this range improve performance? Also, we’re not currently using any aggregation tables in the cloud environment - should we be rebuilding those?
Your query needs serious optimization. You’re selecting all columns from dimension tables (d1., d2.) which is a performance killer. Specify only the columns you actually need. Also, add proper WHERE clause filters at the dimension level, not just on the fact table. For cloud indexing, you need to create non-clustered indexes on the join columns (AccountKey, ProjectKey) and the filter column (TransDate). Check your Azure SQL database’s Query Performance Insight to identify which indexes are missing.
Don’t forget about Power BI’s built-in aggregation feature. You can create aggregation tables in your dataset that Power BI automatically uses when appropriate. For example, if you have daily transaction data but most reports show monthly summaries, create a monthly aggregation table. Power BI will use the aggregation for summary queries and only hit the detailed table when drill-through is needed. This is a game-changer for cloud performance.
S3 with 100 DTUs is definitely undersized for your workload if you’re processing large fact tables with multiple dimension joins. Consider moving to at least S6 (400 DTUs) or exploring Premium tier options. For incremental refresh, 12 months is quite aggressive - most implementations refresh only the last 1-3 months and archive historical data. Aggregation tables are absolutely critical in cloud environments. Create pre-aggregated views or tables at the grain level you need for your reports. This reduces the computation burden during report refresh significantly.