Error processing tabular model - From SQL Server Agent

Question

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,

Could be a bug. Other users also have that problem: https://social.msdn.microsoft.com/Forums/office/en-US/463ab615-a04b-4d6b-bde1-973e2d338613/error-processing-tabular-model?forum=sqlanalysisservices Try to monitor CPU/memory and see the differences — Anton, Jun 09 '17 at 01:31
I am also getting the same error... how you fixed this issue? — Kannan Kandasamy, Oct 04 '17 at 15:55

score 1 · Accepted Answer · answered Jan 24 '18 at 21:59

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.

You can also remove the sorts easily by just editing the the .bim file with a powerful text editor. Especially now that the .bim file is JSON. — Kim, Jul 11 '19 at 05:14

score 0 · Answer 2 · answered Jul 11 '19 at 05:39

Use SQL Server Integration Services (SSIS) for processing. Just create a package with an "Analysis Services Processing Task". This task processes the model like SSMS.

The error message correctly explains the problem but unhelpfully doesn't tell which attribute is the offending one. I was sorting account names by account number but because there were a few accounts with the same name but different number, I got this same error. Setting keepUniqueRows didn't help.

Removing the offending sortBy fixes the problem when processing with an SQL Server Agent. What's interesting is that when the sortBy is in place and I processed the model with SSMS the accounts were sorted as expected. This led me to think this is because SQL Agent Job interprets the warning as an error and does a rollback but SSMS ignores it. The SSIS task probably ignores the warning just like SSMS and processing succeeds.

score 0 · Answer 3 · edited Jul 02 '23 at 00:48

I had a similar error. It was after adding a sort column to a table. Normally when applying the sort column an error will occur telling you that there is more than one sort value for the same value in the column.

However, my model used a related column called Overall Rating that I wanted to sort. When I added a related sort column and then applied the sort column no error occurred.

I looked at the values of the Overall Rating and they included:

Overall Rating	LatestReviewSort
(blank)	0
Unsatisfactory	1
Improvement Needed	2
Achieves	3
Exceeds	4
Exemplary	5

When I went to process the model it failed. I looked at the sort column which should have values 0,1,2,3,4, or 5. Instead it also had blanks.

Overall Rating	LatestReviewSort
(blank)	0
Unsatisfactory	1
Improvement Needed	2
Achieves	3
Exceeds	4
Exemplary	5
(blank)	(blank)

It turns out that my data didn't always have an entry in the related field. This caused real blanks to come over as 0's and missing values as blanks. This caused the failure to occur.

I replaced my related sort column with the following DAX:

VAR LRS = RELATED ( Employee[LatestReviewSort] )
RETURN    SWITCH ( LRS, BLANK (), 0, LRS )

This replaces the blanks with zeros.

The model loaded successfully.

score -1 · Answer 4 · answered Jun 12 '17 at 21:23

-1

Try this,

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

answered Jun 12 '17 at 21:23

Jigar

216
1
8

score -1 · Answer 5 · answered Apr 21 '19 at 04:52

-1

I also faced same problem. I just made type "full" to "automatic" and it starts working.

{
  "refresh": {
    "type": "automatic",
    "objects": [
      {
        "database": "AU MY Model"
      }
    ]
  }
}

answered Apr 21 '19 at 04:52

Mohammad Nazir

1
2

1

I think "automatic" is the same as "Process Default" in SSMS. This is not the correct solution if you want to reload or add new data. – Kim Jul 11 '19 at 05:48

Error processing tabular model - From SQL Server Agent

5 Answers5