Calculated Member slow response time (median)

Question

Post reply

Calculated Member slow response time (median)

Freefall99

Mr or Mrs. 500

Points: 533
More actions
March 29, 2022 at 6:19 pm

#4007794

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

ReluctantPhilosopher Valued Member Points: 70 More actions · Answer 1

MEDIAN is notoriously slow. Here's an article that suggests a workaround:

Median calculation routine

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

With the understanding that I don't use Analysis Services, can you use T-SQL in SSAS? There are some very fast methods for calculating MEDIAN in T-SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

Jeff Moden wrote:

With the understanding that I don't use Analysis Services, can you use T-SQL in SSAS? There are some very fast methods for calculating MEDIAN in T-SQL.

You can query SSAS with DMX, MDX, XMLA or DAX but not SQL. DMX and MDX are bit like SQL but they are not SQL.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

Jonathan AC Roberts wrote:

Jeff Moden wrote:
With the understanding that I don't use Analysis Services, can you use T-SQL in SSAS? There are some very fast methods for calculating MEDIAN in T-SQL.
You can query SSAS with DMX, MDX, XMLA or DAX but not SQL. DMX and MDX are bit like SQL but they are not SQL.

Thanks for the info, Jonathan. Can SSAS read from a table (I pretty sure it can but have to ask because I've never used SSAS)? If so, then a little separate pre-processing in T-SQL might do the trick.

As they say in Maine... "Ahyup... ya canna git der from here... ys gotta go ova der first, eh". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)