sql-serverssassql-agent-jobssas-tabular

Error processing tabular model - From SQL Server Agent


We have a tabular cube, processing database (full) in SSMS works fine, but when processing from SQL server agent, throws following error.

 <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="1092550744" Description="Cannot order ''[] by [] because at least one value in [] has multiple distinct values in []. For example, you can sort [City] by [Region] because there is only one region for each city, but you cannot sort [Region] by [City] because there are multiple cities for each region." Source="Microsoft SQL Server 2016 Analysis Services Managed Code Module" HelpFile="" />
    </Messages>
  </root>
</return>

Here is the script is used from SQL server agent.

{
  "refresh": {
     "type": "full",
     "objects": [
       {
         "database": "DBName"
       }
      ]
   }
}

Can anyone suggest how to eliminate this error or ignore this error/warning?

Thanks,


Solution

  • I had the same issue, tabular model in VS 2015, cube in SSAS. Builds fine when I process the database but the SQL Server Agent was bringing up this error. A couple of forums had some mention of the error but no steps for deeper investigation & resolution. Particularly difficult when the 'Cannot Order' is blank. I opened the model in VS, select every column in turn and looked for any sorting operation in either the filter or the 'Sort by Column' button which is easy to miss. Removed all the sorts and it built fine. Take a note of the ones removed as you may have a data issue. enter image description here