Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bmms
Helper I
Helper I

How to avoid poor performance when the data base has millions of records

Hello Everyone,

 

very soon i will build a PowerBi report based on data base in SQL Server with million of records.
But, I am concerned with performance!

 

In your opinion:

  • What is the best way for built this report?
  • What is the best way to get data from SQL Server?
  • Which things i need be carefull for avoid poor performance?

 

Best Regards.

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

You should not use Direct Query if you can avoid it. Import mode is more performant. Here are some key things to consider:

- Use import mode

- Create a star schema data model

- Only bring in the data you need (remove any unneeded columns/rows)

- Avoid DateTime and Decimal columns (split into Date and Time, used Fixed Decimal)

- Leverage incremental refresh to minimize refresh times

 

Pat

 

Microsoft Employee

View solution in original post

7 REPLIES 7
miTutorials
Super User
Super User

I just made a tutorial today talking about dealing with million records and things to do to speeden up your report. Hope this helps.

 

Reasons why your Power BI report is slow & how to optimize | Performance Tuning | MiTutorials  

ppm1
Solution Sage
Solution Sage

You should not use Direct Query if you can avoid it. Import mode is more performant. Here are some key things to consider:

- Use import mode

- Create a star schema data model

- Only bring in the data you need (remove any unneeded columns/rows)

- Avoid DateTime and Decimal columns (split into Date and Time, used Fixed Decimal)

- Leverage incremental refresh to minimize refresh times

 

Pat

 

Microsoft Employee

Hello,

 

Thanks for your feedback.

 

I will need refresh data two or three times during the work hours! 

 

Using import mode is a good solution? Or the best solution it is create several views in SQL Server and import them?

 

Best regards.

Hi @bmms 

 

You can create a single view using joins and then call in a single table or connect to multiple tables would require you to build the relationship in PBI. So that depends on your requirement, both of them won't have any effect on your model performance. 

 

 

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
AnkitKukreja
Super User
Super User

Hi @bmms 

 

It's best to do most of the transformation on the source (create a view for that) and connect directly to the source using Direct Query. You can refer few of the posts below for more clarity.

 

https://www.youtube.com/watch?v=_zYvybVMk7k

https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-large-models

https://zebrabi.com/power-bi-performance-optimization/#:~:text=3%20simple%20steps%20to%20Power%20BI%....

https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

 

I hope this helps.

 

 

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Hello,

 

thanks for your feedback!


I am little bit confused.

You said that i should use "Direct Query", but on one of the sites you shared (zeabra bi) says that using direct query can be very slow!

 

img.JPG

 

Should I or shouldn't I use direct query?

 

Something is escaping me!!

 

Best regards.

Hi @bmms 

 

Sorry for the confusion. You can fall back on the DQ approach if you need the most recent data, if you can wait for a few hours then Import mode would be best. I agree with the points shared by ppm1.

 

 

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.