Loading Large Tables

[Budfudder]

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]

Hey @Budfudder,

I think the best practice for loading large datasets 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?

I would also recommend looking at your current Connection Timeout configuration.

Cheers,

Alan

[Budfudder]

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]

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]

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?

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]

Hi @Budfudder,

To improve query performance, here are two tips for you.

• 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.

Reference
http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques
http://promx.net/en/2016/09/optimizing-power-bi-query-performance-with-crm-2016-online-odata-v4-serv...

Regards,

Charlie Liao