SSAS Tabular 2019 non-descript processing error

Question

hi folks,
I am doing a full processing on a Tabular SSAS 2019 model and often I get non-descript error messages, such as the one below, when processing, which force me to do another full build while running an ssas trace. Typically, the errors stem from column name changes in the datasource, either for sort columns or the base/sorce columns themselves which do not seem to error out even during simple deployments. Is there any way to force SSAS to throw valid error descriptions on both deployments and builds. (I eventually get "The Column 'xxx' does not exist in the rowset" but only when I trace anew the entire build):

Typical non-descript error message:

Executed as user: xxx. Microsoft.AnalysisServices.Xmla.XmlaException: The current operation was cancelled because another operation in the transaction failed. at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.ExecuteStatement(String statement, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.ExecuteStatement(String stmt, StatementType stmtType, Boolean withResults, String properties, String parameters, Boolean restrictionListElement, String discoverType, String catalog) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.SendCommand(String command, Boolean withResults, String properties) at OlapEvent(SCH_STEP* pStep, SUBSYSTEM* pSubSystem, SUBSYSTEMPARAMS* pSubSystemParams, Boolean fQueryFlag). The step failed.

thank you kindly for any thoughts,
Cos

Answer 1

Interesting, I'm not sure what would cause the switch between the TMSL (json) format and the XMLA command. It could be the version of SSMS or SQL Server or it could be the version or compatibility mode of the SSAS model. But this sounds like an issue with the way the agent job is catching and reporting the errors.

I actually stopped using the built-in processing step in SQL Agent many years ago because of problems getting meaningful errors. One of my colleagues built a custom set of scripts and SSIS packages that we are using at the moment which logs everything into SQL tables. But I've also used a CmdExec step in the past to launch a powershell script to do processing so that I had better control over the reporting of errors.

Answer 2

Hi,

How did you do the trace? Did you trace the SSAS Server ?

Since you have found the issue roots that is the changing of the column name , did this error still exists? What is you currenct problem?

Answer 3

1 . I was able to do deployment from DEV to PROD without any warnings/errors, even though there were columns missing in the source.

If by deployment you are talking about something like scripting out a create/alter script from DEV and then running it on PROD then this would be expected as SSAS will not query the source data until you trigger a processing operation. So it will be unaware of any differences in the source until this time.

2 . During processing in Prod, I did not get any detailed error message, just the non-descript message above.

How are you triggering the processing operation? It is normal for the SSAS engine to process multiple operations in parallel so there is often an initial error followed by a series of cancellation errors (multi-dim behaves like this also). All the error messages should be returned in the messages collection of the XMLA response, but it looks like what you have there is some sort of .net call stack not the XMLA response. So it depends on what sort of script / tool you are using as to how you might go about getting the full error details.

Answer 4

hi Darren,
on #1, yes, I was able to get the same confirmation from Daniel Otykier. thank you kindly for the confirmation. He suggested doing a model refresh, to confirm all columns are in place.

on #2, I am invoking the full build through a SQL Agent job, with the script below, when I am getting the non-descript error message at the top of the thread:

{"refresh": {
"type": "full",
"objects".....}
}

Interestingly enough I see that the old processing, below, is also available, but not sure why on one machine the top script is generated (the one that causes the non-descript error) while on another, the bottom one, the <Process xmlns...> is being generated -- this could be the key to this issue. Not sure how to control the processing language:

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Type>ProcessFull</Type>
<Object>
<DatabaseID>ContosoTab</DatabaseID>
</Object>
</Process>

many thanks for any thoughts,
Cos