PostgreSQL Timeout Error

[Alex_Ooi]

Hi,

I need your expert advice and possible workarounds that are not too manual for the timeout error I encounter every time I query my dB in PostgreSQL. This is the same type of error appearing in Excel's Power Query and Power BI.

I am not a developer, so the concept of "duplicating" tables/queries into another dB is foreign to me. Another solution that I am currently using is of course to download my query on specific intervals - currently, every end of month - into a folder where I could use Folder.Files ( ) function.

The issue arises when sometimes my users require views that fall out of the specific intervals. For example, I have not downloaded the query for December today, but my users require information from 1 - 22 December. I find my current way of working is still manual.

Any idea how I can workaround the timeout error, so I can do a schedule refresh in Power BI to always get the latest insights?

Your advices are much appreciated.

[mwegener]

Hi @Alex_Ooi ,

did you try to increase the timeout?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Alex_Ooi]

Hi @mwegener there are issues with connecting directly with PostgreSQL (query folding). Discussion thread here: https://community.powerbi.com/t5/Desktop/Query-Folding-disable/m-p/873976#M419092

As such, I connected using ODBC which does not allow me to change timeout.

I am afraid the only way to do this is to query by batch and merge query later. But I am concerned about the optimisation since I don't have Premium to do incremental refresh.

[mwegener]

Hi @Alex_Ooi ,

what are your issues with query folding?

[v-xuding-msft · Accepted solution]

Hi @Alex_Ooi ,

Maybe you could reference the ways to change timeout parameters to check if you could connect the data source without an error. 

1. Add timeout parameter to connection string:

• Connect Timeout=xx  (xx = seconds)

2. Otherwise, you could add  timeout parameter to Query argument within M Code / Advanced Editor:

• let
  Source =
  Odbc.DataSource(
  xxxxxxxxxxxxxxxxx,
  [Query = "xxxxxxxxx",CommandTimeout =#duration(0,1,0,0)]
  )
  in
  Source 

Reference:

https://docs.microsoft.com/en-us/powerquery-m/odbc-datasource

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alex_Ooi]

Hi @v-xuding-msft the default command timeout using this function seems fine to me, 10 mins. It is just that my server defaults its command timeout by 2 minutes. After reading this document, I think there is nothing that Power BI (or any other IDEs) can do much besides having my tech team to change the command timeout for me.