Loading postgres and CRM data

[Budfudder]

My apologies if this is a dumb question - I'm a newbie.

From a couple of other questions I've posted and the responses I've gotten, it seems that data from a postgres database as well as data from a CRM instance cannot be loaded into Power BI via Direct Query; it can only be Imported. Is that correct? We have quite large databases of both kinds, and I'm having all sorts of problems because (as far as I can tell) Power BI is attempting to download the entire tables and store them in RAM. When I save, it then writes the whole tables to hard disk.

Am I missing something? Not understanding it correctly? Is there a way to actually use hundred million-row postgress tables without Power BI trying to download the hundred million rows that I don't know? A way to use a CRM table to which we've added a bunch of custom fields (so that the table now has 300 columns and 600,000 rows)  without Power BI downloading it?

Thanks for any help.

[v-qiuyu-msft]

Hi @Budfudder,

From a couple of other questions I've posted and the responses I've gotten, it seems that data from a postgres database as well as data from a CRM instance cannot be loaded into Power BI via Direct Query; it can only be Imported. Is that correct?

Based on my research, postgres database doesn't support DirectQuery. See: Use DirectQuery in Power BI Desktop. If you connect to postgres database and CRM SQL database, only Import mode is available.

Am I missing something? Not understanding it correctly? Is there a way to actually use hundred million-row postgress tables without Power BI trying to download the hundred million rows that I don't know? A way to use a CRM table to which we've added a bunch of custom fields (so that the table now has 300 columns and 600,000 rows)  without Power BI downloading it?

As far as I know, data import to Power BI desktop are stored in .pbix file. Though there is no official document list Power BI desktop data size limitation, as desktop use Power Query to get data, I guess Power Query limitation might affect data import to desktop.

If you connect to CRM SQL database in desktop, it supports DirectQuery. You can try to create report connect to postgres and CRM separately. Also there are Dynamics CRM Online content packs which you can have a look.

Best Regards,
Qiuyun Yu

[Budfudder]

You mention "connect[ing] to CRM SQL database in desktop" - I'm connecting to an online instance of CRM - and there's no option to choose Direct Query at all. How do I enable Direct Query for it?

[v-qiuyu-msft]

Hi @Budfudder,

As you are connecting to online Dynamics CRM, you can only use Import mode both in Dynamics CRM data source or OData feed.

Best Regards,
Qiuyun Yu

[Budfudder]

Thanks for your reply. How, then, do we get along? The size of our databases (both CRM and postgres) mean that importing is impossible - does that mean we simply cannot use Power BI for those databases?