Random Procrastination

Darren Gosbell [MVP]

Author: Darren Gosbell

Extending the Analysis Services Command Timeout in Power BI

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.

image

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:

image

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)])

A New Begining

After many happy years on my previous blog platform I decided that it was time for a move. All my previous posts are still available at http://geekswithblogs.net/darrengosbell but from now on all my new content will be posted on this site. Although geekswithblogs.net had a bright start many years ago it is now suffering from neglect, the rss feed had unresolved issues, the comment engine and contact pages were completely broken and they don’t support https.

So I decided that now was the time to move my blog and give it a facelift in the process.