Loading Large Tables

Question

I'm connecting successfully to our postgresql database. I then select the tables I want and attempt to Load them. Power BI fails with the error message about running out of memory - one of the tables has over 100,000,000 rows.

Is there any way I can get around this? Can I somehow configure Power BI to only download a subset of the rows somehow? What am I missing?

alanhodgson · Answer

Hey Budfudder,&nbsp;I think the best practice for loading large datasets&nbsp;is using the Direct Query method instead of Import.Also, are you running on a 32-bit or 64-bit machine? And do you have atleast 8GB of RAM?&nbsp;I would also recommend looking at&nbsp;your current&nbsp;Connection Timeout configuration.&nbsp;Cheers,&nbsp;Alan

budfudder · Answer

I'm running Windows 10 64-bit, with 8GB of RAM.

~~I'm using the Direct Query method, not Import.~~ My mistake - we are using Import, we have no choice - Direct Query for postgresql is not supported.

I'm unable to find the Connection Timeout configuration - where is it?

tringuyenminh92 · Answer

Hi Budfudder,Does your postgre database store in this computer? Is this error from database side? With DirectQuery, each chart just loads maximum 1 mil rows when you interactive( https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery) so I don't think it's problem from PBI.

budfudder · Answer

Unfortunately (from what I can see) postgresql isn't one of the databases supported by DirectQuery - so I can ONLY import. Does importing actually create a copy of the whole table locally?&nbsp;I've had similar problems connecting to our CRM database (not timeouts or failures, but just HUGE load times) - again, it's not supported by DirectQuery.

v-caliao-msft · Answer

Hi Budfudder,
&nbsp;
To improve query performance, here are two tips for you.
&nbsp;

Tall, narrow tables are faster. Reduce the unused columns in order to improve performance.
Integers are faster than strings. Strings are stored in a hash table, they are effectively referenced twice, once for the hash value and once to fetch the string associated with that value.

Referencehttp://blog.pragmaticworks.com/power-bi-performance-tips-and-techniqueshttp://promx.net/en/2016/09/optimizing-power-bi-query-performance-with-crm-2016-online-odata-v4-service/
&nbsp;
Regards,
Charlie Liao

Forum Discussion

Loading Large Tables

5 Replies

Related content

Hard delete on a large table with incremental load in Fabric

Onelake security with large tables

Loading data too large

Load of large CSV file to Delta-Lake table

Load Large data from business central