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":
But the order by is still P1,P2 because the total is higher