SSAS Slow Calculation Response (Median)

Question

I am calculating median across a very large set and it can take a long time to give me my results. Is there any type of processing method that I can choose which will calculate the calculated members when I am processing the cubes instead of at run-time? Any suggestions appreciated.

The calculation:
MEDIAN(Filter(SET_NAME,[Measures].[Median_NUM] > 0) ,[Measures].[Median_NUM])

Answer 1

Hi @Ryan Anderson ,

The median value is the middle value in a set of ordered numbers. The median value is determined by choosing the smallest value such that at least half of the values in the set are no greater than the chosen value. If the number of values within the set is odd, the median value corresponds to a single value. If the number of values within the set is even, the median value corresponds to the sum of the two middle values divided by two.

Based on above median value logic, the set need to be ordered, then the median value can be determined. If the set is very large, then the performance could be bad as expected.

So you may consider to make the set as small as possible.

The other thing that i can think of is to make sure [Measures].[Median_NUM] is a physical measure instead of a calculated measure.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Also, see whether you could lose the Filter call. Median ignores NULLs, this could help. And, make sure the engine calculates Median_NUM for the set as fast as possible. Finally, check how you define your set and whether you can speed this up.