You can translate the question and the replies:

Power BI to Denodo Connection - Dataset Refresh Timeout (ODBC: ERROR [08001] timeout expired)

Hi, We have a few PowerBI reports that we are trying to change connections for them to use denodo as the connection method instead of connecting straight to the Data Tables. The connection works fine on Desktop using DirectQuery and limiting the dataset to one month for the purpose of dev and testing. It is however a large dataset and thus the initial refresh takes some time to execute once the report is published. However the refresh is unable to complete because of a Query Timeout Error after 900 seconds (15 minutes). Running this query on the main table takes 22min and thus we requere the timout to be extended - to an hour to enable any reports to not encounter this issue. So far, I have used the VDP Admin Tool to change the Server Configuration to have a Thread Expiration Time of 3600000 and the Timeout to 43200000 but this seems to have changed nothing - even after restarting the server once changed. VDP Log gives the following: 4855401 [ODBC(11)-XXX.XXX.XXX.XXX] ERROR 2023-07-27T12:22:50.025 com.denodo.vdb.vdbinterface.server.odbc.ODBCBackend [] - Error: Error executing query. Total time 900.052 seconds. QUERY [VIRTUAL] [QUERY_TIMEOUT] QUERY [JDBC WRAPPER] [PROCESSING] QUERY [JDBC ROUTE] [PROCESSING]
user
27-07-2023 08:27:04 -0400
code

5 Answers

Hi, I’d check the Denodo article [Configuration of ODBC timeout in a DSN](https://community.denodo.com/kb/en/view/document/Configuration%20of%20ODBC%20timeout%20in%20a%20DSN) which explains how to change the query timeout configuration in an ODBC DSN data source. Hope this helps!
Denodo Team
28-07-2023 04:54:18 -0400
code
Thank you for the suggestion but in this particular case we are using a connection string and not the DSN. When using the DSN, the gateway is unable to be used by the report once published - which is a separate issue we still need to solve.
user
28-07-2023 05:25:56 -0400
I got a response from the technical team who advised the following solution which did the trick: "you could execute the below set command in the VQL Shell('Tools'>'VQL Shell') of the Virtual DataPort Administration Tool/Web Design Studio. SET 'com.denodo.vdb.vdbinterface.server.odbc.connection.queryTimeout' = '43200000'." It is worth mentioning that I also ran this for JDBC connections (Replacing "odbc" with "jdbc" in the above script) and restarted the server after running both queries.
user
31-07-2023 04:29:50 -0400
Hi, I am glad to know that the issue is solved. By the other hand, you can use in the connection string, the following string: ` SERVER=<server name>;DATABASE=<database name>?queryTimeout=3600000;PORT=9996;` The query timeout parameter is passed with the database parameter. Hope this helps!
Denodo Team
31-07-2023 04:44:31 -0400
code
Thanks for the suggestion but I supsect that would be only for ODBC connections, not the Denodo connector for Power BI. I did a test for the benefit of the doubt but got the error: " Unable to connect - We encountered an error while trying to connect. Details: "Connection string does not have a valid format. Allowed properties: SERVER, PORT, DATABASE, SSLMODE." "
user
31-07-2023 05:00:13 -0400
You must sign in to add an answer. If you do not have an account, you can register here