none
Prevent errors downstream RRS feed

  • Question

  • Hi guys, having trouble trying to catch errors created by timeouts in Power BI Desktop (Apr 2017 )

    I have a table with measure name per row and am running a Analsis service query for each measure to get a table. I deliberatly want to set a short 15 sec timeout  to prevent forever running. However even though the value is showing as an error (instead of the returned table from the query), if I try to use try, the HasError flag shows as false even though the value clearly has an Error

    Query is

    let
        Source = try AnalysisServices.Database("cube.mycompany.com", "BI", 
    [Query="evaluate longrunningqueryhere)"
    ,CommandTimeout=#duration(0,0,0,15)
    ,ConnectionTimeout=#duration(0,0,0,30)
    ])
    in
        Source

    what am I doing wrong?

    The problem is that I need to detect an error and replace with a benign table to allow all the queries that did work to still come out of the other end of the pipeline, so I really hope there is a solution here..

    Thanks for any suggestions

    Brett

    Tuesday, May 23, 2017 5:35 PM

Answers

  • I now realise how to do this and have done it. I have added an addcolumn to count the number of rows in the sub-table (that is created from the analysisservices query) and this forces the execution and allows me to catch the error and replace with a dummy table to prevent further errors downstream.

    This is better than the way I was doing it before, so thanks for the pointer!

    Brett

    Tuesday, May 30, 2017 5:31 PM

All replies

  • Hi Brett,

    The reason it seems to "work" is due to the lazy nature of M. For the preview you pasted above, we don't fully evaluate your AnalysisServices.Database call, so it seems to work...until you click on the value, at which point we do the AS evaluation, and you get an error.

    It would be better to put the try around a number of steps (i.e. the steps that do the actual work against the AS data), not just the AnalysisServices.Database call. What does your M code look like that calls AS for each measure defined in your source table?

    Ehren


    Wednesday, May 24, 2017 6:13 PM
    Owner
  • Thanks Ehren.. interesting although this shows that this is not a very developer friendly implementation of try/catch which is usually there to help developers make robust error handling!

    I would have thought that the very presence of a try/catch would cause instantiation at that point (i.e. we are now doing work on the function inside). If I put the catch "further back" I am in danger of catching more, unintended to be handled, issues.

    So, I'd suggest this should be considered further if possible.

    But ok lets see how I can do this with the current

    The problem is, I think that to help troubleshooting and provide modularisation I have a bunch of functions. So I have a function that will build a dax query and run against a server/database with a specific timeout

    Calc_ValuesPerColumns_Table

    (Server, Database, Measure, DateTable, Columns, CommandTimeout, FromDateKey, ToDateKey) as table =>
    let 
    /*  // testing parameters
        Server = Databases[Server A]
    ,   Database = Databases[Database A]
    ,   Measure = "Yield"
    ,   DateTable = "Dates"
    ,   CommandTimeout = #"Default Command Timeout"
    ,    Columns = WeeklyColumns , 
    //,  Columns - VillageDailyColumns ,  
    */
        ColumnList = Text.Split(Columns, ","),
        TrimmedColumnList = List.Transform(ColumnList, each Text.Trim(_))
    ,    DaxQuery = Calc_ValuesPerColumns_Dax(Measure, DateTable, Columns, FromDateKey, ToDateKey)
    ,    DaxResult = try AnalysisServices.Database
           (   #"Server"
           ,   #"Database"
           ,  [
                Query=DaxQuery
               ,CommandTimeout=CommandTimeout
               ,ConnectionTimeout=#duration(0,0,0,30)
              ]
           )
    //, TryAgain = Table.AddColumn(#"Renamed Columns", "Tryagain", each DaxResult[Value] = "Error") // doesn't work as Failed AnalysisServices.Database doesn't produce a proper error 
    , EscapedDaxQuery = Text.Replace(DaxQuery, """", "\""")
    , TraceInformation = "{""Function"":""Calc_ValuePerDayVillage_Table"",""Server"":"""& Server &""",""Database"":"""& Database & """,""Measure"":"""& Measure &""",""Query"":""" & Text.Start(EscapedDaxQuery,3800) & (if Text.Length(EscapedDaxQuery) > 3800 then "..." else "") &"""}"
    , Trace = if Databases[Do Trace] 
              then Diagnostics.Trace
              (  TraceLevel.Information
              ,  TraceInformation , ()=>DaxResult, true)
              else DaxResult,
        #"Converted to Table" = Record.ToTable(Trace),
        #"Transposed Table" = Table.Transpose(#"Converted to Table"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    need to make this table creation generic to cope with the column parameter, or pass it in (if really necessary)
        #"Expanded Value" = Table.ExpandTableColumn(#"Promoted Headers", "Value", 
     TrimmedColumnList & {"[Measure]", "[Value]"}, 
     TrimmedColumnList & {"[Measure]", "[Value]"})
    ,    Renamed = RenameOutSquareBrackets(#"Expanded Value")
    in
        Renamed

    I then have a function that, given a table with a bunch of the values to pass to this function, will add a table column to the table

    Calc_ValuesPerColumnsByMeasure_Table

    (Measures as table, Server as text, Database as text, Columns as text) =>
    let
    /*  // Test function
        Measures = MeasuresForWeeklyTest,
        Server = Databases[Server B],
        Database = Databases[Database A],
        Columns = WeeklyColumns,
    */
        Source = Measures,
        AddColumn = Table.AddColumn(Source, "ResultsTable", each Calc_ValuesPerColumns_Table(Server, Database, [Measure], [Date Table], Columns, [Command Timeout],[FromDateKey],[ToDateKey])),
        #"Removed Other Columns" = Table.SelectColumns(AddColumn,{"Measure", "ResultsTable"})
    in
        #"Removed Other Columns"

    Then finally I will try to split this table column to reveal the details for each table. This depends on the columns that I've passed in (although I know how to make that generic now)

    Calc_ValuesPerMeasureWeekly_Table

    (Measures as table, Server as text, Database as text) =>
    let
    /*  // Test function
        Server = Databases[Server B],
        Database = Databases[Database B],
    */
        Source = Calc_ValuesPerColumnsByMeasure_Table(Measures, Server,Database, WeeklyColumns)
    ,   #"Expanded DetailResults" = Table.ExpandTableColumn(Source, "ResultsTable", 
            {"HasError", "Error", "Week Year", "Week Year No", "Week Year Week", "Week Year Week No", "Value"}
          , {"HasError", "Error", "Week Year", "Week Year No", "Week Year Week", "Week Year Week No", "Value"})
    in
        #"Expanded DetailResults"

    This Calc_valuesPerMeasuresWeekly_Table function is simply called by a few different data sources with different parameters

    I guess it will error at the Table.ExpandTableColumn so is that the point that I need the try/catch?

    Is there a way to trick the powerquery into erroring earlier (e.g. + 0 to the expected value column)?

    Brett

    Tuesday, May 30, 2017 3:47 PM
  • Hi Brett. Can you put a try/otherwise around the call to Calc_ValuesPerColumns_Table?
    Tuesday, May 30, 2017 5:28 PM
    Owner
  • I now realise how to do this and have done it. I have added an addcolumn to count the number of rows in the sub-table (that is created from the analysisservices query) and this forces the execution and allows me to catch the error and replace with a dummy table to prevent further errors downstream.

    This is better than the way I was doing it before, so thanks for the pointer!

    Brett

    Tuesday, May 30, 2017 5:31 PM