Large Dataset performance issues and system getting freeze

[ISAYYED]

Greetings to all the experts in Power BI!

I hope this message finds you well. Currently, I am working on a Power BI project with a large dataset that exceeds 4GB. Two of the tables, after cleansing, contain approximately 100 million rows each.

Although we have reduced the data from 10GB to 4GB, this dataset is still required for various reasons. However, due to the dataset's size, we are experiencing poor performance, with even simple DAX queries taking a long time, and the system sometimes freezing.

Despite having high-performance desktop systems, we are still encountering performance issues. Can anyone provide guidance on how to handle such a large dataset effectively?

In addition, it's worth noting that we are currently using an on-premises Power BI Desktop application for this project.

[ppm1]

A few things to consider:

- make sure there is no more you can to reduce the size of the model. remove any columns you don't need and avoid any high cardinality columns (e.g., datetime, ID on fact tables).

- make sure auto datetime is turned off in the model

- use a star schema (you mentioned two fact tables, so want to confirm)

- see this video on how to troubleshoot the performance of a Power BI visual (and the video related to that one on how to fix it)

https://youtu.be/ScJVQoOWSqc

Pat

[lbendlin]

How much memory does the PC have where you are running this?  If you ran it on Power BI Service you could use incremental refresh for the load, and the Power BI service's memory for the display.

Independently you want to familiarize yourself with DAX Studio and want to study the query plans of your worst performing queries.