600 Second Timeout Setting?

[etuckeriv]

Hello,

I am trying to connect to a very large dataset, but as Power BI begins to load the rows I get this error:

I think I've read that there is a way to change the time out value, but I can't seem to find a way to do it. Could anyone point me in the right direction?

[Daryl-Lynch-Bzy]

Hi @etuckeriv , could you please provide more details about the Power Query connector you are using.  The message suggests that you are trying to Import an Analysis Service data.  Is this Power BI data?

[etuckeriv]

Hello!

Thank you for your reply! I'll do my best 🙂

When I go to "Get Data" I chose "SQL Services Analysis Database" to access the data cube where my data is located. I don't know if this matters, but I am also using the "Import" functionality instead of "Connect Live".

I don't know how to write queries yet, so to get around that I have figured out that I can build a table of the data I need from the ENORMOUS dataset I'm connecting to, then run the Performance Analyzer tool and "Copy Query Code" from there to write the query I need to pull that specific data. So I built a table of the data I want, copied the query code, and I've connected to the database URL and pasted that copied query code into the "MDX or DAX query (optional)" section.

I hope all that makes sense. Let me know what other info you might need, and thank you again!

[Daryl-Lynch-Bzy · Accepted solution]

@etuckeriv - Are you really sure that you need to download Analysis Service cube data into Power BI model?  You could be better off using SSMS or DAX Studio to integrate the data, or importing the data from Source.   If from Power BI dataset, extract too much data could exceed CPU and memory limits leading to poor performance.

The following will increase the timeout from 600 seconds to 900 seconds.  Note #duration( day, hour, minute, seconds) 

Source = AnalysisServices.Database( #"Server", #"Database", [Query="MDX or DAX String", ConnectionTimeout=#duration( 0, 0, 15, 0 ) ] )

[AlexisOlson]

The top recommended post to the right has the solution:
Function Odbc.DataSource error with Timeout settings

As mentioned in the documentation, you can use "ConnectionTimeout":

Source = Odbc.DataSource("dsn=Production", [HierarchicalNavigation=true, ConnectionTimeout=#duration(0, 0, 15, 0)])

[etuckeriv]

Thank you so much, I didn't realize those were over there! I will definitely pay more attention to that area in the future.

Also, thank you for the suggestion! I am having trouble figuring out where to put that line. I looked through the post you linked (as well as several others that seemed related from the side bar), as well as the docmuentation you linked, but I don't fully understand what I'm seeing in the other solutions and it looks different from mine. I also tried adding the line or pieces of it in various places within the query code, but everywhere I tried I got an error that it wasn't valid. I realize you can't see what I'm seeing, I'm nervous about sharing the query code or a screenshot because I'm not sure what information might be sensitive. Would you be willing to suggest where I should put the line of code? Or what info you might need to be able to tell me what to do?

[AlexisOlson]

Usually, pasting the M code from the Advanced Editor works well provided you change or redact any names or other sensitive bits that might be within filters or column names since the query just says how to get and transform the data rather than what the data actually is.