failed refreshes sometimes not shown

[zenzeinet]

TE3: data refresh sometimes does not show the error when refreshes fail

[otykier]

We display whatever error message is returned when the XMLA command fails. Unfortunately, sometimes AS only sends information such as "A previous operation in the transaction failed", or something along those lines, making it difficult to know exactly why the processing failed. But this is unfortunately all the information we have available. You would typically see the same thing by running the TMSL refresh command through SSMS.

It may be possible to obtain additional error information by attaching an AS trace, but it may take a few seconds to attach and start the trace, which is why we probably should not do that by default. Still, if such a trace does provide more informational error messages, it may make sense to at least provide that as an option (imagine a "Trace refresh" checkbox in the "Data refresh" window, or something like that).

In any case, it would be helpful if you could confirm:

A) That you don't see any added information when performing the refresh through SSMS, as opposed to through TE3.
B) That you can obtain more information by attaching SQL profiler and looking at the AS trace.

[zenzeinet]

Hi Daniel,
Interesting points those that you comment - they go beyond what I initially found, as there are two different issues:

Issue A: in SSMS, sometimes the XMLA query returns sucessfully, but the Results pane shows a warning that is actually an error, (Example 1 below, there could be other cases). I cannot see that message (maybe in Outputs? I cannot see it there) and there is no warning, the process seems ok, but it is not, and should be flagged as failing. Maybe my original message did not explain this properly ;) .

Issue B: What you mention in your message. Sometimes the best error message is in the AS trace. I have had to use it quite a few times in the past. Would be nice to have, but at least the error is a clear error, and I can debug it with a trace. Having it optionally integrated in TE3 would surely be nice (but don't you already do it? else how do you recover the ongoing rows when processing partitions?)

Thanks!

Example 1:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
    <Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
      <Warning WarningCode="1094320140" Description="Column '' in Table '' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table." Source="Microsoft Analysis Services" HelpFile="">
        <Location xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100="http://schemas.microsoft.com/analysisservices/2008/engine/100" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl410="http://schemas.microsoft.com/analysisservices/2012/engine/410" xmlns:ddl410_410="http://schemas.microsoft.com/analysisservices/2012/engine/410/410" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xmlns:ddl600="http://schemas.microsoft.com/analysisservices/2013/engine/600" xmlns:ddl600_600="http://schemas.microsoft.com/analysisservices/2013/engine/600/600" xmlns:ddl700="http://schemas.microsoft.com/analysisservices/2018/engine/700" xmlns:ddl700_700="http://schemas.microsoft.com/analysisservices/2018/engine/700/700" xmlns:ddl800="http://schemas.microsoft.com/analysisservices/2018/engine/800" xmlns:ddl800_800="http://schemas.microsoft.com/analysisservices/2018/engine/800/800" xmlns:ddl900="http://schemas.microsoft.com/analysisservices/2019/engine/900" xmlns:ddl900_900="http://schemas.microsoft.com/analysisservices/2019/engine/900/900" xmlns:ddl910="http://schemas.microsoft.com/analysisservices/2020/engine/910" xmlns:ddl910_910="http://schemas.microsoft.com/analysisservices/2020/engine/910/910" xmlns:ddl920="http://schemas.microsoft.com/analysisservices/2020/engine/920" xmlns:ddl920_920="http://schemas.microsoft.com/analysisservices/2020/engine/920/920" xmlns:ddl921="http://schemas.microsoft.com/analysisservices/2021/engine/921" xmlns:ddl921_921="http://schemas.microsoft.com/analysisservices/2021/engine/921/921">
          <Start>
            <Line>0</Line>
            <Column>0</Column>
          </Start>
          <End>
            <Line>0</Line>
            <Column>0</Column>
          </End>
          <LineOffset>0</LineOffset>
          <TextLength>0</TextLength>
          <ddl200:SourceObject>
            <ddl500_500:TableName>Mytable</ddl500_500:TableName>
          </ddl200:SourceObject>
        </Location>
      </Warning>
    </Messages>
  </root>
</return>

[otykier]

TE3 does indeed attach an AS trace when performing a refresh, to track progress and collect any error/warning messages sent from AS during processing. The problem is, that sometimes events containing error/warning messages are not sent from AS at all - likely because the transaction finishes before the trace sampling has done its job. In any case, if anyone knows of a sure fire way to obtain these messages from AS, I would certainly love to know!

[zenzeinet]

Interesting - haven't had it happening to me, but I'm sure you've found it around. My experience is centered around SSAS, debugging with a local Server Profiler so that might help too.

Regarding having the "Warning" results, I would suggest it is interesting to surface that to the TE3 client for the cases when there is a Calculated Table or a Calculated Column [see below] involved. These messages also come when there is a wrong measure, but those are usually less important to know (and wrongly defined measure are already shown by TE3 anyways). Maybe the result of the process could be sent to the Output window, and some link / warning be put when the result is not a clean xml ?

another example (this one of a bad calc column , above one was of a bad calc table, both leave the model in a potential inconsistent state despite the Refresh completing)

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
    <Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
      <Warning WarningCode="1094320129" Description="Function 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values." Source="Microsoft Analysis Services" HelpFile="">
        <Location xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100="http://schemas.microsoft.com/analysisservices/2008/engine/100" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl410="http://schemas.microsoft.com/analysisservices/2012/engine/410" xmlns:ddl410_410="http://schemas.microsoft.com/analysisservices/2012/engine/410/410" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xmlns:ddl600="http://schemas.microsoft.com/analysisservices/2013/engine/600" xmlns:ddl600_600="http://schemas.microsoft.com/analysisservices/2013/engine/600/600" xmlns:ddl700="http://schemas.microsoft.com/analysisservices/2018/engine/700" xmlns:ddl700_700="http://schemas.microsoft.com/analysisservices/2018/engine/700/700" xmlns:ddl800="http://schemas.microsoft.com/analysisservices/2018/engine/800" xmlns:ddl800_800="http://schemas.microsoft.com/analysisservices/2018/engine/800/800" xmlns:ddl900="http://schemas.microsoft.com/analysisservices/2019/engine/900" xmlns:ddl900_900="http://schemas.microsoft.com/analysisservices/2019/engine/900/900" xmlns:ddl910="http://schemas.microsoft.com/analysisservices/2020/engine/910" xmlns:ddl910_910="http://schemas.microsoft.com/analysisservices/2020/engine/910/910" xmlns:ddl920="http://schemas.microsoft.com/analysisservices/2020/engine/920" xmlns:ddl920_920="http://schemas.microsoft.com/analysisservices/2020/engine/920/920" xmlns:ddl921="http://schemas.microsoft.com/analysisservices/2021/engine/921" xmlns:ddl921_921="http://schemas.microsoft.com/analysisservices/2021/engine/921/921">
          <Start>
            <Line>5</Line>
            <Column>7</Column>
          </Start>
          <End>
            <Line>5</Line>
            <Column>45</Column>
          </End>
          <LineOffset>189</LineOffset>
          <TextLength>39</TextLength>
          <ddl200:SourceObject>
            <ddl500_500:ColumnName>SomeColumn</ddl500_500:ColumnName>
            <ddl500_500:TableName>Actos</ddl500_500:TableName>
          </ddl200:SourceObject>
        </Location>
      </Warning>
    </Messages>
  </root>
</return>

[zenzeinet]

Hi Daniel,
Any update on this? On TE2, any Warning after a refresh would clearly be shown (full error message), but it is not in TE3. It is one of the few features I'm missing in TE3 (maybe I'm not searching well, but the above examples I could not find any error messages)
Thanks!

[otykier]

Yes, we are going to surface the Warning messages received through the trace, as you're absolutely right that those may contain crucial information. Hoping to squeeze it into this month's release.

[otykier]

Hi @zenzeinet
Just letting you know that this was fixed in our latest release, where we are now picking up AS trace messages that are received after the refresh operation finishes (it is quite common for them to arrive a few seconds late). This way, all error/warning/progress information should now be visible in the Data Refresh view.