SSAS Slow Calculation Response (Median)

Ryan Anderson 1 Reputation point

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])

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,600 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,208 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 27,391 Reputation points

    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.



    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.

    0 comments No comments

  2. Alexei Stoyanovsky 3,411 Reputation points

    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.

    0 comments No comments