Help Needed: Optimizing a 3.5-Hour Power BI Semantic Model Refresh - What Would You Do?

Hi Koraganti77 ,

Thank you for reaching out to Microsoft Community.

Inorder to reduce the 3.5-hour refresh time of your Power BI semantic model, try migrating to Microsoft Fabric Lakehouse with Delta tables and Direct Lake mode might be effective strategy. Direct Lake leverages the VertiPaq engine, offering import-mode-like performance while avoiding full data duplication. It loads columnar data directly from Parquet-based Delta tables in OneLake, enabling high-speed access with minimal overhead. This approach far outperforms DirectQuery, which federates queries back to Synapse and performs poorly with complex M logic.
Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn
Semantic Model Refresh in Fabric: Beyond the Defaults
Direct Lake overview - Microsoft Fabric | Microsoft Learn

As a first step, you should use Dataflows Gen2 or Fabric pipelines to load your Synapse data into a Lakehouse Delta table. During this process, implement schema optimization by trimming oversized nvarchar(1000) fields, pruning unused columns, and flattening any nested structures. These changes significantly reduce memory usage and improve compression, especially by lowering column cardinality. Fabric tools like Power BI usage analytics can help identify which columns are actually needed.

To preserve your critical M Query logic, replicate it upstream using Dataflows Gen2 or Spark notebooks. This allows complex business rules such as CASE statements and transformations to be precomputed before the data hits the semantic model. If you're using semantic link, notebooks can also be a powerful tool for embedding complex logic into Delta tables while maintaining flexibility.

Once the Delta tables are structured, build your Power BI semantic model in Direct Lake mode, mapping the tables via shortcuts. While Direct Lake does not currently support calculated columns or tables that reference Direct Lake sources, these transformations should be handled upstream or refactored into DAX measures within the model. For complex logic, DAX VAR statements can improve readability and performance.

For incremental refresh, define RangeStart and RangeEnd parameters on a reliable created_on column and enable query folding to push the filter logic back to the Lakehouse. To avoid refreshing full partitions unnecessarily, implement change detection based on metadata columns like ModifiedDateTime. This will help maintain efficiency during weekly refreshes. If you need near real-time freshness, consider using Hybrid Tables for recent data.

To ensure smooth operation, take advantage of Fabric's Semantic Model Refresh Pipelines to manage refresh parallelism and retries. Monitor overall system performance using the Fabric Metrics App, which provides insight into refresh duration, query execution, memory usage, and dataset size. Tools like Analyze My Refresh can help pinpoint bottlenecks and identify optimization opportunities.

By migrating to Delta tables in Fabric Lakehouse, optimizing your schema, pushing business logic upstream, and fully utilizing Direct Lake with incremental refresh and change detection, you can expect to bring your Power BI refresh time well below the 30-minute threshold possibly eliminating refresh time altogether. This architecture not only preserves data accuracy and business rules but also offers scalable, maintainable performance moving forward.

Best Regards,
Chaithra E.