SSAS: Getting the xmlaWarningCollection from a Process operation

There was a question recently on the Analysis Services forum asking how to get a list of the Warnings from a Process() method in AMO.

There is an overload to the Process methods which includes an xmlaWarning collection, so this should be a pretty easy thing to do - right? Wrong! It's not as easy as it first appears. After inserting an invalid record into the fact table of a test cube, I was able to process the cube using the Business Intelligence Development Studio (BIDS) and see the relevant warnings, but no matter what I tried, when processing from AMO the xmlaWarningCollection always came back empty.

So to find out what was going on I "cracked" open the Microsoft.AnalysisServices assembly using reflector and had a look at what was going on. Here is what I found...

  1. cube.Process(...) internally is calling...
  2. server.Process(...) passing the cube as parameter along with the other parameters that were passed in to the original call. This then calls...
  3. analysisServicesClient.Process(...) this is where the problem lies as this method does not accept an xmlaWarningCollection as a parameter, so the warning collection will never get populated!

But I did not give up there. I figured that I could use the CaptureXML property in AMO to generate a script of the processing method which I could the fire off. I would then get back an XMLA result which I could read through to pull out the warnings. So to test this theory I fired off the following XMLA to process my test cube.

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Sandpit</DatabaseID>
    <CubeID>Sandpit 1</CubeID>
  </Object>
  <Type>ProcessFull</Type>
  <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
  <ErrorConfiguration>
    <KeyErrorLimit>100</KeyErrorLimit>
  </ErrorConfiguration>
</Process>

Note: I had set a KeyErrorLimit of 100, without this the process operation throws an exception at the first error it comes across. The exception does give you details of the error, but if you have more than one invalid record in your fact table this will stop at the first one.

This returns the following XMLA response.

<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="1092354050"
        Description="Server: Operation completed with 2 problems logged."
        Source="Microsoft SQL Server 2005 Analysis Services"
       
HelpFile=""
     
/>
    </Messages>
  </root>
</return>

Which is not entirely helpful, I now know I have 2 errors and that they are logged somewhere (WHERE...???). Changing the command to have an explicit error log file allows you to pick up the individual warning/error details.

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Sandpit</DatabaseID>
    <CubeID>Sandpit 1</CubeID>
  </Object>
  <Type>ProcessFull</Type>
  <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
  <ErrorConfiguration>
    <KeyErrorLimit>100</KeyErrorLimit>
    <KeyErrorLogFile>c:\data\procErrLog.log</KeyErrorLogFile>
  </ErrorConfiguration>
</Process>

This would be OK if you had rights to the file system on the SSAS server, but given that processing rights can be assigned out, this may not always be the case.

What I would like to see is an option in the processing command to ask for a verbose list of warnings. There is an issue on the connect about the warning collection not being populated. If you are interested in the status of this issue or want to vote on getting it changed you can find it here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=256428

Of course another option would be to use the tracing facility, this would enable you to get notifications of the warnings in real time in much the same way that the processing window in BIDS does, but it is a fair bit of overhead to go through just to get a list errors.

Print | posted on Sunday, March 4, 2007 7:39 PM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)