Dealing with reading a massive table (120 GB and 1.2 billion rows)

[Yazdanifard]

Hi Everyone,

I want to load a 120 GB table in OLTP DB to power bi. I used Direct Query mode to load this data. However, the problem is that when I create different visualize with this table, they always show refreshing! In other words, I cannot use this table in my report anymore due to the fact of large data! 

I also select just 4 necessary columns in SQL query to improve my performance (you may know, smaller width of a table, better performance in reading data in power bi). One point is that the simple query in my OLTP DB may take 5 minutes to show the results!
My question is how can I use this large data in my report? Should I use Data Warehouses or I should just split data for recent months or ...?

I searched the whole forum but nobody has pointed out this issue!

Thanks in advance for your help guys!

Mostafa

[v-lili6-msft]

hi, @Yazdanifard 

For a massive table, i think the best way is filter the query.

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance#directquery-best-practices

Best Regards,

Lin