ODBC connection - Large table

Question

Hi,&nbsp;I am trying to import a large table from a database that I connect to using an ODBC connection.&nbsp; I am finding that when I try to query the table and also refresh, it is taking a considerable amount of time due to the number of rows that are in the table.&nbsp;Is there a way that I can use a direct SQL query on the table in order to import the data I require?&nbsp; If there is any advice anyone can provide to refine the import of the data that would be appreciated.&nbsp;Kind Regards,Chris

v-qiuyu-msft · Answer

Hi Chrisp22o,
&nbsp;
In Power BI Desktop, DirectQuery mode is not available in ODBC data source. You can refer to&nbsp;this article to see supported data source in DirectQuery mode: Use DirectQuery in Power BI Desktop.
&nbsp;
As&nbsp;Progress database is not supported in Power BI Desktop directly, you can use ODBC to connect to Progress database&nbsp;instead. It means we can only use Power BI desktop to get Progress database&nbsp;data in Import mode.
&nbsp;
After you publish the report to Power BI Service, you need to create a ODBC use the same connection as in&nbsp;desktop under data gateway. Then set the dataset of this report to use this ODBC data source. See:
&nbsp;
On-premises data gateway
Manage your data source - SQL Server
&nbsp;
In addition, please note the maximum file size you can import into Power BI Service is 1 gigabyte.
&nbsp;
Best Regards,Qiuyun Yu

anonymous · Answer

Lots of variables. If the data source provides a Direct Query connection, then you are good. These are pretty well outlined in the data connections in the Power BI data connections. ODBC is the fall back connector for when a more native one is not available.

And are you talking about importing all that data into your Power BI desktop file? In that case, I would say that the primary factor is how long it takes to get the same ODBC data connection flowing into PowerBI.com. That should be much faster and also happening in the background (thank you Gateway!) so that you should not really notice or care (e.g. if the data is updating every two hours or so, the data update will all happen behind the scenes and without you need to be monitoring it.

Hope this helps.

Scott

chrisp22o · Answer

Hi Scott,&nbsp;Thanks for your reply.&nbsp; I could not see the option to add a Direct Query connection from an ODBC source, you can do from SQL Server.&nbsp;&nbsp;Im selecting the table to import but then select Edit in the wizard and&nbsp;use the filters on the necessary columns to try and replicate my SQL statement that I previously used for this type of report in Crystal Reports.&nbsp; I have yet to install the Gateway on one of my servers but when I do as you mention it should run in the background.&nbsp;Regards,Chris

anonymous · Answer

Just to confirm, is this all using Power BI Desktop? And what database are you connecting to? e.g. SAP HANA? SQL Server?&nbsp;

chrisp22o · Answer

I'm using Power BI desktop with a Pro licence. I'm using my odbc connection to connect to a Progress database

Forum Discussion

ODBC connection - Large table

8 Replies

Related content

Onelake security with large tables

Open Mirroring - Replication not restarting for large tables

Large datasets

Can't connect 3 table

2 tables connected (related) by 2 tables