SSAS Dimension Order By

  • Ahoi,

    i have the following problem.

    The users of a certain of my cubes use the SORT function alot.

    The sort in SSAS Multidimension is kinda slow when adjusted manually within Excel/the addon used to create the reports.

    My first solution was precalculating a measure for a certain dimension and order the attribute by the dimension itself.

    Most importantly purchases: i preordererd the dimension purchase IDS by the value desc because it was the most common use case.

    The problem is, the order by value is always ordered by the total value of each purchase, but within the reports different filters are used.

    Which leads to many cases where the highest purchase ID in the report is not the highest ID in the preordered order.

    Because if only partial parts of a purchase are considdered, this subtotal of each purchase might be higher than the subtotal of a purchase that has a higher total purchas. But the purchase ID with the higher total purchase is still the one ordered higher because the total is higher.

    The problem is manually ordering is slow and preordering only works by 1 certain order which is not dynamic.

    Any ideas how to improve the order by option multidimensional?

    Heres an example of what i mean with total and subtotal purchase values:

    P1    X1   500

    P1   X2   200

    P2  X1   600

    P1 Total: 700

    P2 Total: 600

    --> Order by Total Value --> P1, P2

     

    If the user filters only "X1":

    Its P2,P1

    But the order by is still P1,P2 because the total is higher

     

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply