ODBC connection - Large table

[Chrisp22o]

Hi,

I am trying to import a large table from a database that I connect to using an ODBC connection.  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.

Is there a way that I can use a direct SQL query on the table in order to import the data I require?  If there is any advice anyone can provide to refine the import of the data that would be appreciated.

Kind Regards,

Chris

[trek5000]

Is there a way to set a row limit for the data preview window in the get data wizard/workflow for ODBC sources? Every time a table is selected the data preview puts a query to the the underlying data source essentially doing a select *.  If using direct query the SQL it passes to underlying source limits to 1000 rows. Why doesn't ODBC do the same thing? Unfortunately not all data sources can be connected via direct query so this limitation is tough to overcome on large tables

[blacksnow]

I'm having what may be similar difficulties. I'm receiving the error below when trying to load a table through an ODBC connection. I tried limiting the range of the row without any impact. Been searching around online some and haven't found much to address this specific issue. I'll keep searching. In the meantime, any insight would be appreciated.

DataSource.Error: ERROR [HY000] [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC 20101 driver][20101]MM- Row too big (7711)
ERROR [HY000] [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC 20101 driver][20101]MM- Row too big (7711)
ERROR [HY000] [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC 20101 driver][20101]MM- Row too big (7711)
ERROR [HY000] [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC 20101 driver][20101]MM- Row too big (7711)
ERROR [HY000] [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC 20101 driver][20101]MM- Row too big (7711)
ERROR [HY000] [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC 20101 driver][20101]MM- Row too big (7711)

[Anonymous]

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]

Hi Scott,

Thanks for your reply.  I could not see the option to add a Direct Query connection from an ODBC source, you can do from SQL Server. 

Im selecting the table to import but then select Edit in the wizard and 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.  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.

Regards,

Chris

[v-qiuyu-msft]

Hi @Chrisp22o,

In Power BI Desktop, DirectQuery mode is not available in ODBC data source. You can refer to this article to see supported data source in DirectQuery mode: Use DirectQuery in Power BI Desktop.

As Progress database is not supported in Power BI Desktop directly, you can use ODBC to connect to Progress database instead. It means we can only use Power BI desktop to get Progress database data in Import mode.

After you publish the report to Power BI Service, you need to create a ODBC use the same connection as in desktop under data gateway. Then set the dataset of this report to use this ODBC data source. See:

On-premises data gateway

Manage your data source - SQL Server

In addition, please note the maximum file size you can import into Power BI Service is 1 gigabyte.

Best Regards,
Qiuyun Yu

[Anonymous]

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

[Chrisp22o]

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

[Anonymous]

Got it. So when you publish your report to the PowerBI service (powerbi.com), the data connection using ODBC will all happen in the background, and you will not have to really worry too much about the timeliness of it (though I do believe there are upper limits to the data size you can connect to, etc, so you might need to see if you surpass those limits). There are often some delays when connecting your power bi desktop file to your data source. It also happens with oData feeds. But that is only really a pain during development. Once you publish your report as intended, then the speed and latency issues should go away.

And btw, you are not the only one clamoring for better connections to Progress. Here is an idea over that you can put your votes into the help raise the awareness of the need for the development team - https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7444396-we-need-to-be-able-to-con...

Btw, if this answers your question, go ahead and please mark this as the solution. That helps keep the forums clean.

Thanks, Scott