Connect large data table with ODBC (CData ODBC connecter)

[JackEnviro]

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

[Anonymous]

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




Regards,
Lydia

[JackEnviro]

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

[Anonymous]

@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

[JackEnviro]

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?

[Anonymous]

@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

[JackEnviro]

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

[Anonymous]

@JackEnviro,

Instead of using *, use the following SQL statement.

Select Stage, CloseDate, LastModifiedTime from yourtable

Regards,
Lydia

[JackEnviro]

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

[Anonymous]

@JackEnviro,

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

Regards,
Lydia