Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

how to handle large data size

Hi, I have a data size of 50 million records (only required columns considered) approx in sql server. what should be the best way (import or direct query) to build the report on this data size witho...

General Comment

Tips and Tricks

stretcharm
7 years ago
I tend to avoid direct query if performance is important as unless you are filtering down to small data sets on well indexed sources it can be slow.

Take a look at aggregations as here you can get the best of both worlds by importing aggregations and direct query for individual records

https://docs.microsoft.com/en-us/power-bi/desktop-aggregations
https://www.sqlbi.com/tv/aggregations-in-power-bi/
https://powerbi.microsoft.com/en-us/blog/aggregations-for-petabyte-scale-bi-available-in-the-power-bi-service/
https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table

Anonymous

Not applicable

7 years ago

Hi there. I have a model with about 80 000 000 rows in the fact table and would never even consider the DirectQuery mode if I can use Import. The Import mode is THE BEST OUT THERE you can have. You cannot beat it using the DirectQuery or Dual mode because PQ compresses data with a factor of between 10 and 100x and the data is all stored in memory. YOU JUST CANNOT BEAT IT...

It's as easy as that.

Best
Darek