locked
Process full fails in SSAS 2017 RRS feed

  • Question

  • Hi,

    We have a tabular project in SSAS 2017.

    When we process the data from SSMS > Right click on any table> Selected All Tables >Process Full , It works fine

    When we process the data from SSMS>Right Click on Tabular Project >Process Full, it gives error related to Sorting.

    Message

    Executed as user: XXXXXXX. <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 2017 Analysis Services Managed Code Module" HelpFile="" /></Messages></root></return>.  The step failed.

    What could be the reason behind this ?

    Regards,

    Friday, March 9, 2018 5:15 AM

Answers

  • Hi akj2784,

    Thanks for your question.

    The problem is exactly what the error status. The issue is the error doesn't give any detail.

    What is happening is within the tabular module you have attributes that are sorted by another column. The problem is that SSAS Tabular needs a one to one relationship between the attribute column being displayed and the column being sorted. 

    For example,I had a field called Year_Month which had the values 201201, 201202....201212, 201301, 201302 etc. I was using this field to sort my description field that  had Jan, Feb, Mar, Apr etc. The problem is for description Jan there were multiple sort values, 201201 and 201301. What i should have done is either change my sort values to be 1,2,3,4 etc. or change the display field to be Jan 2012, Feb 2012 etc. Either of these would ensure the one-to-one relationship.Fixing the field values stopped the error.

    If you know all the fields that have sorts on them, you could just manually create a basic query like this:
    SELECT SORT_FIELD, COUNT(DISTINCT DISPLAY_FIELD)
    FROM YOUR_TABLE
    GROUP BY SORT_FIELD
    HAVING COUNT(DISTINCT DISPLAY_FIELD) > 1

    If it will return any rows, then you might encounter this problem.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Tuesday, March 13, 2018 6:20 AM
    • Marked as answer by akj2784 Wednesday, March 14, 2018 5:51 AM
    Monday, March 12, 2018 6:56 AM

All replies

  • Hi akj2784,

    Thanks for your question.

    The problem is exactly what the error status. The issue is the error doesn't give any detail.

    What is happening is within the tabular module you have attributes that are sorted by another column. The problem is that SSAS Tabular needs a one to one relationship between the attribute column being displayed and the column being sorted. 

    For example,I had a field called Year_Month which had the values 201201, 201202....201212, 201301, 201302 etc. I was using this field to sort my description field that  had Jan, Feb, Mar, Apr etc. The problem is for description Jan there were multiple sort values, 201201 and 201301. What i should have done is either change my sort values to be 1,2,3,4 etc. or change the display field to be Jan 2012, Feb 2012 etc. Either of these would ensure the one-to-one relationship.Fixing the field values stopped the error.

    If you know all the fields that have sorts on them, you could just manually create a basic query like this:
    SELECT SORT_FIELD, COUNT(DISTINCT DISPLAY_FIELD)
    FROM YOUR_TABLE
    GROUP BY SORT_FIELD
    HAVING COUNT(DISTINCT DISPLAY_FIELD) > 1

    If it will return any rows, then you might encounter this problem.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Tuesday, March 13, 2018 6:20 AM
    • Marked as answer by akj2784 Wednesday, March 14, 2018 5:51 AM
    Monday, March 12, 2018 6:56 AM
  • Hi Willson,

    I got the root cause and thats for sure related to Sort order. But just want to understand how it works fine when we process the data from SSMS > Right click on any table> Selected All Tables >Process Full 

    Regards,

    Akash

    Monday, March 12, 2018 2:05 PM
  • Hi Akash,

    Thanks for your question.

    >>>I got the root cause and thats for sure related to Sort order. But just want to understand how it works fine when we process the data from SSMS > Right click on any table> Selected All Tables >Process Full
    I thought you just wanted to know the root cause or resolution(workaround).

    For this issue,I guess this is an issue of SSMS or SSAS. I would recommend you to submit it to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server. This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, March 13, 2018 6:02 AM
  • Actually the root cause if the sort order. 

    Run the following queries against the SSAS cube to locate any tables containing a different sort order.  

    select * from $SYSTEM.TMSCHEMA_COLUMNS 
    where SortByColumnID>0


    select * from $SYSTEM.TMSCHEMA_TABLES

    Ron 


    Micatio Software Free IIS Azure Web Log App

    Sunday, February 23, 2020 11:07 AM