Best solution to improve performance of enterprise scale data

[sivarajan21]

Hi Team,

Calling Microsoft Fabric Experts! Need Your Guidance!

Before the introduction of Microsoft Fabric, we relied on Dataflows Gen2 to connect to SQL sources, pull data, and transform it. For context, Dataflows sit inside workspaces and not within any Lakehouse/Data Warehouse. We then used these Dataflows as the source for our Power BI reports. However, with enterprise-scale data, this approach sometimes slowed down report performance.

Enter Fabric's OneLake 
With OneLake storing data in Parquet and Delta table formats, performance can be significantly improved. I’m exploring the best way to leverage this, and I’d love your insights!

Here are my two thoughts:

1️⃣ Option 1:

• Create a Lakehouse.
• Bring in the existing Dataflows to create Delta tables.
• Use these Delta tables as the source for Power BI reports.
  👉 Will this improve Power BI report performance?

2️⃣ Option 2:

• Create a Lakehouse.
• Build a Pipeline within the Lakehouse to pull data directly from SQL.
• Use this transformed data as the source for BI reports.

What’s your take?

Your suggestions aren’t just helpful for me but could benefit many in the community. I truly appreciate the time and effort from those who love sharing their knowledge.

Looking forward to hearing from the amazing experts out there!

Thank you! 🙏
@Greg_Deckler @marcorusso @v-linyulu-msft @v-yaningy-msft @amitchandak 

[tharunkumarRTK · Accepted solution]

@sivarajan21 

Your question is regarding the report performance.

👉 Will this improve Power BI report performance?

Both the approaches that you have described will create delta tables in a Lakehouse, the only difference is the datasource that you would like to extract the data from. You should be able to use direct lake storage mode with both approaches and can expect better performance. 

To improve the performance of your reports, you can follow these 

1. While creating the delta tables make sure to assign appropriate data types for all the columns.

2. Perform Delta table maintainance commands on schedule basis 

https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-table-maintenance

3. Make sure your delta tables do not fall into the limitations documented here, which can lead to fall back behavior.

https://fabric.guru/controlling-direct-lake-fallback-behavior

4. write optimized DAX expressions. 

5. Try to avoid adding more than 15 visuals in one page. 

6. Report performance will also depend on the number of concurrent users accessing the reports. Perform realistic load test. 

https://github.com/microsoft/PowerBI-Tools-For-Capacities/blob/master/RealisticLoadTestTool/Readme.m... 

Depending on the results, leverage query caching and query scale out features 

https://powerbi.microsoft.com/en-us/blog/announcing-the-public-preview-of-power-bi-dataset-scale-out...

https://learn.microsoft.com/en-us/power-bi/connect-data/power-bi-query-caching

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[sivarajan21]

Thank you so much for this detailed and comprehensive response! 🙏

It’s reassuring to know that both approaches can leverage Direct Lake storage mode for improved performance. Your suggestions for enhancing report performance, especially around Delta table maintenance, optimizing DAX expressions, and managing visual complexity, are incredibly helpful.

The links you’ve shared about Delta table maintenance, fallback behavior, and load testing tools are particularly valuable. I’ll dive into these resources to ensure we’re following best practices while implementing the solution.

If I encounter any challenges while setting up the Delta tables or performing load tests, I hope I can reach out to you for further insights. Thanks again for your guidance—it’s highly appreciated! 😊

I will also discuss with my team about your Power BI Consultation😊

Thanks @tharunkumarRTK