locked
Extremely slow median measures RRS feed

  • Question

  • Hi

     

    I'm having a problem with extremely slow median measures.

     

    I've created a named set of all record IDs and wrote the measure as Median( [All Records], [Measures].[Age] ). When I drop a dimension into one of the axes, it takes a very long time to calculate the median even at the top level of the hierarchy, and I suspect it's computing the median for all the members of that dimension, even before I've drilled down into them.

     

    Anyone know a better method for this?

     

    Saturday, March 10, 2007 2:16 AM

All replies

  • Your formula computes Median for all records always, regardless of the selection in Records dimension.
    Sunday, March 11, 2007 2:29 AM
  • Sorry Mosha, I don't quite follow. I need this to be a generic measure that will return the median of any cell in the client browser. Since median cannot be preaggregated, I thought the only way to do this was to take the median of the set of all records in the current cell. The other option I had explored went something like this:

    Median (

                  {  ( Axis(0)(0)( Axis(0)(0).Count - 1 ).Dimension.CurrentMember.All, [All Records].[ ID ].[ ID ] ) },

                   [Measures].[Age]

                )

    But this didn't work at all.

     

    Sunday, March 11, 2007 10:40 AM
  • Sorry, this time I don't quite follow. What exactly do you mean by the following: "I need this to be a generic measure that will return the median of any cell in the client browser". Median of what ? Perha[s you could illustrate with couple of examples.
    Sunday, March 11, 2007 4:47 PM
  • My mistake... Should have said median of a measure (e.g. age) within any cell in the spreadsheet. For example, if I have a 2 x 2 table with Male and Female as columns and marital status Single and Married as rows, I'd be showing the median age in each of the 4 cells. Other times, users would be interested in the median age for other combinations of factors, say Gender and Cancer, or Cancer and Socioeconomic Status, but they shouldn't have to select a different median measure for each combination. Sort of like a percentage/proportion against any dimension selected on the row/column axis, as discussed in the following post:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720160&SiteID=1

     

    Sunday, March 11, 2007 11:02 PM
  • If you are guaranteed to always have two axes, then something like that may work

    Median(CrossJoin(Axis(0), Axis(1)), Measures.[Age])

     

    Monday, March 12, 2007 1:51 AM
  • Thanks Mosha, but no luck with that one either. There's probably no other way around this; that is, other than using the Fact Table primary key (Record ID) to select the set of all individual age values.

     

    Tuesday, March 13, 2007 6:10 AM
  • Then I again don't understand your requirements :( Based on what you wrote before:

    > For example, if I have a 2 x 2 table with Male and Female as columns and marital status Single and Married as rows, I'd be showing the median age in each of the 4 cells.

    The formula that I wrote computes then median of these 4 cells and places it into each one of these 4 cells. I have verified it with AdventureWorks which has Gender and Marital Status attributes...

    Tuesday, March 13, 2007 6:32 AM