none
How do I set a CommandTimeout for an AnalysisServices.Database query? RRS feed

  • Question

  • Hi,

    I have Power Query version: 2.26.4128.242 within Excel 2010. I have created two Analysis Services queries both built via MDX queries against our cube. When attempting to merge these queries (each contains Group By functions if that makes any difference) the Power Query preview window loads data but when I load to worksheet I receive the below error:

    "[DataSource.Error] AnalysisServices: The connection either timed out or was lost"

    I ran an sql trace and it says the query was ended by the user, is Excel timing out the query?

    The below code is an example of the format of one of my queries:

    let Source = AnalysisServices.Database("ServerName", "Database", [Query="MDX Code"]), #"Removed Columns" = Table.RemoveColumns(Source,{"Col A", "Col B}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Col C"}, {"Col D"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Col A"},#"Step 2",{"Col B"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Col C"}) in #"Merged Queries"

    I have tried to add "CommandTimeout=#duration(0,3,0,0)" at the end of the Query block, i.e. [Query="MDX Code", CommandTimeout=#duration(o,3,0,0)] but I receive the error "'CommandTimeout' isn't a valid AnalysisServices.Database option"

    Any assistance appreciated.

    Thanks.

    
    Thursday, March 30, 2017 10:40 AM

Answers

  • Unfortunately, we only have a single set of published documentation and it tracks the most recent release -- which is what's available in Power BI. Excel 2016 and the Power Query add-in lag two or more months behind Power BI. In this case, the option was just added for Power BI and so isn't available quite yet in Excel or Power Query. We'll need to find a way to manage this a little better in the documentation.

    Meanwhile, the best authority for your particular version of Power Query is always the product itself. If you enter "=AnalysisServices.Database" into the query editor, you'll get help for the function. In your version, you'll see that there's no option for CommandTimeout.

    Monday, April 10, 2017 1:41 PM

All replies

  • Unfortunately, we only have a single set of published documentation and it tracks the most recent release -- which is what's available in Power BI. Excel 2016 and the Power Query add-in lag two or more months behind Power BI. In this case, the option was just added for Power BI and so isn't available quite yet in Excel or Power Query. We'll need to find a way to manage this a little better in the documentation.

    Meanwhile, the best authority for your particular version of Power Query is always the product itself. If you enter "=AnalysisServices.Database" into the query editor, you'll get help for the function. In your version, you'll see that there's no option for CommandTimeout.

    Monday, April 10, 2017 1:41 PM
  • In my research, I've found that this does exist now:

    docs.microsoft.com/en-us/powerquery-m/analysisservices-database

    Look for "CommandTimeout" in the options field.

    Monday, September 30, 2019 6:23 PM