Working a large data set is an issue for Power BI

[nadyukhat]

Hi,
I need a help of those who faced the same issue or MS experts. I wonder how to import a large data entity through OData feed.
I filtered columns, left only 3 of them, but number of lines is close to a million (around 1Gb). I set a timeout for 8 hours [0,8,0,0].
I tried different ways: through merge, disabled load, FilterToggle etc. No luck.
How to optimize the query to minimize the import and make it quicker?

DirectQuery does not work for me. Company access is locked to SQL server.
I do appreciate your contribution.

P.S. Just some clarifications. When database was smaller, the report worked properly and was refreshed automatically.

[GilbertQ · Accepted solution]

Hi @nadyukhat

Due to you mentioning that the data set refreshed fine when it was smaller, it would appear that due to the volume being so large, that the timeout is indeed the issue.

The only way I can currently think of working around this, is to store the persisted data in files, so that you do not have to re-query it all via OData (Which as I understand can be quite slow).

You could possibly persist the data using Excel files with the baked in Query Editor?

Or use something like SSIS and potentially store that in a database of some sort?

[nadyukhat]

Thank you for your valuable comment. It works for me (I mean storing old data in txt files)!!!!So grateful!