cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JackEnviro Regular Visitor
Regular Visitor

Connect large data table with ODBC (CData ODBC connecter)

Hi All

 

I'm trying to create somes reports in PowerBI from Zoho using the CData Zoho ODBC driver. I belive a key table I require is the StageHistories table. However when I try to connect directly I get the error shown below.

 

I've tried running a direct SQL query and can return the first 100 rows but not sure how to bring more data through.

 

I've also tried running a query against StageHistories and a table I think we require LinkingModule2 but I'm having trouble querying against the LinkingModule2 table (although I can connect directly)

 

Any help on the best way to connect much appreciated!

 

Thanks

Jack

 

 

 

ODBC Connection.JPG

 

9 REPLIES 9
Moderator v-yuezhe-msft
Moderator

Re: Connect large data table with ODBC (CData ODBC connecter)

@JackEnviro,

Please input your SQL statement in the highlighted textbox to get required columns and rows, then check if you can get data from StageHistories table.
1.PNG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JackEnviro Regular Visitor
Regular Visitor

Re: Connect large data table with ODBC (CData ODBC connecter)

Hi Lydia,

 

The SQL statement I'm running to get the data is as follows:

 

 SELECT TOP 100 * FROM StageHistories

 

This successfully returns the first 100 rows, but the problem is when I try to retrieve every row in this table. It only returns an error message (after some loading time); "429 Too Many Requests".

 

Thanks for the help

Moderator v-yuezhe-msft
Moderator

Re: Connect large data table with ODBC (CData ODBC connecter)

@JackEnviro,

The error could be caused by the large size of the table. Are you able to input the SQL statement into highlighted textbox to get data?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JackEnviro Regular Visitor
Regular Visitor

Re: Connect large data table with ODBC (CData ODBC connecter)

Yes this is the only way I'm able to access any of the data. So when I type in that SQL statement into that Power BI textbox I do get the first 100 rows, but for any practical needs obviously I need all the data. Do you know of any work arounds to import all of the data despite the large table size?

Moderator v-yuezhe-msft
Moderator

Re: Connect large data table with ODBC (CData ODBC connecter)

@JackEnviro,

Do you need to import all columns of the table? How many rows in this table? What is the result when you remove 100 from the SQL statement?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JackEnviro Regular Visitor
Regular Visitor

Re: Connect large data table with ODBC (CData ODBC connecter)

Hi Lydia

 

I only need three of the columns from StageHistories; Stage, CloseDate, and LastModifiedTime. I'm not sure how many rows are in the table, but a fair few I imagine. 

 

When I remove TOP 100 from the statement I get the same error as my first post (the 429 too many requests thing), and just removing 100 does gives the same result.

 

Thanks

Moderator v-yuezhe-msft
Moderator

Re: Connect large data table with ODBC (CData ODBC connecter)

@JackEnviro,

Instead of using *, use the following SQL statement.

Select Stage, CloseDate, LastModifiedTime from yourtable



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
JackEnviro Regular Visitor
Regular Visitor

Re: Connect large data table with ODBC (CData ODBC connecter)

Hi Lydia,

 

I have been trying this, even adding in "WHERE CloseDate >= '20180904'" to try and further limit the number of entries but it keeps throwing back the same error.

 

Thanks

Moderator v-yuezhe-msft
Moderator

Re: Connect large data table with ODBC (CData ODBC connecter)

@JackEnviro,

I would recommend you split the large table into sevaral small tables, then import them into Power BI.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 451 members 4,767 guests
Please welcome our newest community members: