There was a question recently in the Power BI forums on how to extend the timeout for a connection to Analysis Services used to import data into Power BI. In other tools you can add a setting like “Timeout=600” to the connection string to extend this timeout, but the Analysis Services connector in Power BI does not expose a way to set the raw connection string.
However if you look at the Source step in PowerQuery you’ll see that it calls the AnalysisServices.Database() function and if you check the documentation for this function you will see the following
CommandTimeout: A duration which controls how long the server-side query is allowed to run before it is canceled. The default value is driver-dependent.
So based on the above information my first attempt was to change the call to this function as follows, however this just resulted in an error:
This caused a fair bit of head scratching, but if you re-read the documentation carefully you’ll notice the following “CommandTimeout : A duration …” and a duration is a specific data type in the M language which you can instantiate using the #duration( <days>, <hours>, <minutes>, <seconds>) constructor.
So changing the CommandTimeout option to the following fixes this issue and has the added benefit of being clearer that the timeout is now 5 minutes (when using the connection string parameter its never completely clear what the units of the timeout are)
= AnalysisServices.Databases(“localhost\tab17″, [TypedMeasureColumns=true, Implementation=”2.0”, CommandTimeout=#duration(0,0,5,0)])
Hello Darren, how does the SSAS server then react to such timeout specification as SSAS has it’s own set of server’s attributes related to timeout?
Specifically attribute “ServerTimeout”, doesn’t this attribute anyway overwrite whatever time period we set in CommandTimeout?
I tested that based on your article and it anyway takes the value from ServerTimeout instead of CommandTimeout, is there any way how to deal with that to not be adjusting the timeout literally for whole SSAS server due to one or few more PBI reports?
I believe that these two settings work together. The Timeout= on the connection string sets how long the client libraries waits for a command to complete and the ServerTimeout sets the maximum time the server will allow a command to run. So the effective timeout will be the lowest of these two settings. Therefore you have to set the server setting at the maximum level you would accept. There is no way that a client can override the server side setting.