May 25, 2010 at 12:06 pm
Ok I have a measure defined in my cube.
It's a sum measure on a value from the table called loadtime.
The measure is [measures].[sum of loadtime]
I want to calculate the Median value of this across dimensions.
I've got a time dimension call Time.
So I created a Calculation called [Median Loadtime]
I've tried the following for the Expression value.
MEDIAN(Descendants([Time].[Calendar].Currentmember,,LEAVES),[Measures].[Sum of Loadtime])
MEDIAN(existing [Time].[Calendar].[Day Name],[Measures].[Sum of Loadtime])
The first one doesn't seem to work (no errors, just no values come back when I try to manipulate the cube in excel. The second doesn't calculate the median, it seems to sum up the values (which I think was because I was using a sum measure as my base).
Ok so I went back to the Cube Structure and defined a new measure off of Loadtime that had no aggregations. I called this Loadtime
I altered my attempts above to use this new measure, neither returns any results. The cube builds fine, deploys with no issue, but it won't give me a median.
I've been spending the better part of the day looking for a step by step tutorial of just what the heck I need to do to use the built in statistical functions to create a calculation that will give me the median.
I understand that I will have to pick a dimensional set to make this work, but I thought using my time dimension and encompassing all of it would work.
This shouldn't be this difficult, but I fear my knowledge of SQL is getting in my way of understanding of MDX and cubes.
Does anybody have any tips? This problem keeps coming back to haunt me when somebody noticesthat the median measure of my cubes are incorrect. Everything I've found seems to say that the right way to do this is what I've done.
May 26, 2010 at 7:22 am
Ok after analyzing a sample cube, I figured out that what I wanted, which is a Median measure of the values underlying the base cells is basically not doable with the median formula, I instead tried to mimick the median formulas I had seen in the sample.
So I adjusted my medians to be
CREATE MEMBER CURRENTCUBE.[Enterprises and Users].[Users].[All].[Median of Enterprise Users]
AS MEDIAN([Enterprises and Users].[Users].[All].Children ),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Agencies].[Agency List].[All].[Median of Agencies]
AS MEDIAN([Agencies].[Agency List].[All].Children ),
VISIBLE = 1;
I would expect that like in a sample cube, I should be able to take either the [Enterprises and Users].[Users] dimension or the [Agencies].[Agency List] dimension and put them on the rows, then drag a normal measure (count of screen uses for instance) to the data cell, and I should see a median value at the bottom of the rows.
Instead, the returned data set seems to leave out the median calculation entirely.
[Measures].[Count of Uses] is defined as a count measure on the Cube Structure page.
Is there still a trick I need to use to get the Median values in there?
My calulcations are as such (if that helps).
CALCULATE;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Ratio of Screens used to number of users actually using the system]
AS [Measures].[Count of Uses]/[Measures].[Distinct Actual User],
FORMAT_STRING = "#,#.00",
NON_EMPTY_BEHAVIOR = { [Distinct Actual User] },
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Ratio of Distinct Screens used to number of distinct users of the system]
AS [Measures].[Unique Screens Viewed]/[Measures].[Distinct Actual User],
FORMAT_STRING = "#,#.00",
NON_EMPTY_BEHAVIOR = { [Distinct Actual User] },
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Average Loadtime discounting Zero Values]
AS (IIF(ISEMPTY([Measures].[Count of Uses with Non Zero Access times]),null,IIF([Measures].[Count of Uses with Non Zero Access times]=0,0,[Measures].[Sum of Loadtime]/[Measures].[Count of Uses with Non Zero Access times]))),
FORMAT_STRING = "#.00",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Average Loadtime]
AS (IIF(ISEMPTY([Measures].[Count of Uses]),null,IIF([Measures].[Count of Uses]=0,0,[Measures].[Sum of Loadtime]/[Measures].[Count of Uses]))),
FORMAT_STRING = "#.00",
NON_EMPTY_BEHAVIOR = { [Count of Uses] },
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Screen Uses per User]
AS [Measures].[Count of Uses]/[Measures].[User Count],
FORMAT_STRING = "#,#.00",
NON_EMPTY_BEHAVIOR = { [User Count] },
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Unique Screen Uses Per User]
AS [Measures].[Unique Screens Viewed]/[Measures].[User Count],
FORMAT_STRING = "#,#.00",
NON_EMPTY_BEHAVIOR = { [User Count] },
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Enterprises and Users].[Users].[All].[Median of Enterprise Users]
AS MEDIAN([Enterprises and Users].[Users].[All].Children ),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Agencies].[Agency List].[All].[Median of Agencies]
AS MEDIAN([Agencies].[Agency List].[All].Children ),
VISIBLE = 1;
May 26, 2010 at 11:48 am
Mark,
As far as I can tell, your last attempt should give you the result you want. As far as making the result visible, try using the ADDCALCULATEDMEMBERS function on the (row?) specifier.
e.g.
WITH MEMBER [GL Accounts].[GL Account].Mark AS MEDIAN([GL Accounts].[GL Account].[All].CHILDREN), VISIBLE = 1
SELECT
ADDCALCULATEDMEMBERS([GL Accounts].[GL Account].MEMBERS) ON 1,
[Measures].[GL Amount] ON 0
FROM
[AcornEPS]
Using SSMS and Excel I confirmed that the result returned is the accurate median.
HTH,
Steve.
May 26, 2010 at 1:05 pm
Thanks Steve,
I'm glad to know I at aleast got the formula right for the calculation.
I'm not writing MDX, just using Excel or the built in browser to look at the cube.
I used the same Median formula on the Sample cube I picked up for Northwind from the SQL Server 2005 Analysis Services Step by Step book, and it works in that cube.
I don't get erorr messages when I build, deploy, or process the cube, but the calculated values that should be at the bottom of the listed out dimension do not show up in the dimensions when dragged into the workspace, or used within Excel as a client to SSAS.
i.e. I drag [Enterprises and Users].[Users] dimension to the row workspace. From my understanding the [Median of Enterprise Users] median value of the other values at that level should show up towards the bottom of the viewed cube surface.
How would I specify ADDCALCULATEDMEMBERS to the scripted Calculations on the third tab of the cube designer?
May 26, 2010 at 2:23 pm
I think it depends on what version of excle you're running but on my 2010, if you right click the pivottable and select pivottable options, on the display tab you can select/deselect the option for showing calc members from the olap server.
hth,
Steve.
May 26, 2010 at 2:29 pm
But the built in browser for BIDS should show it by default right?
Because it doesn't show it either. :/
May 26, 2010 at 2:33 pm
I take it back... It's there now (not sure why), and indeed if I go to pivot table options I can make it show up. 🙂
Awesomeness. At least it works now.
So to make sure I understand.
The statistical functions (STDDEV, MEDIAN, etc) only work when displayed as an extra dimensional value on the dimensions, there isn't a way to make it show up as a measure.
Correct? If so, at least I get it, it sucks because I really need a Median measure for the underlying values, but at least it makes sense.
May 27, 2010 at 8:20 am
You should be able to make a Median measure, the only suckful thing is that you need to i) tie it to a set (e.g. 'Enterprise and Users') and ii) tie it to measure (otherwise you get infinite recursion).
Steve.
May 27, 2010 at 10:25 am
I did that like this.
CREATE MEMBER CURRENTCUBE.[Measures.[Median of Enterprise Users]
AS MEDIAN([Enterprises and Users].[Users].[All].Children,[Measures].[Average Loadtime] ),
VISIBLE = 1;
I found when brought onto the data space, it would sum the loadtimes, not find the median.
So I adjusted the cube by creating a no-aggregate measure of Loadtime called Loadtime and changed the calculation to what I have below.
CREATE MEMBER CURRENTCUBE.[Measures.[Median of Enterprise Users]
AS MEDIAN([Enterprises and Users].[Users].[All].Children,[Measures].[Loadtime] ),
VISIBLE = 1;
Once I did this, the Median measure would not return a value at all.
May 27, 2010 at 2:14 pm
Not sure what to tell you Mark. I couldn't try the first approach as my sample cube didn't have a time dim, so no average over time. I added a measure in (what i think is exactly) the same way as you did, and it returns data nicely.
CREATE MEMBER CURRENTCUBE.[Measures].Mark AS
MEDIAN([GL Accounts].[GL Account].MEMBERS, [Measures].[GL Amount]), VISIBLE = 1;
I guess one difference is that i used 'members' versus the children of the All member.
Steve.
May 27, 2010 at 2:21 pm
Oh it's not an average over time.
It's Two dimensions being used to calculate the average/Mean for each cell.
Sum of Loadtime / Count of records of Loadtime
February 4, 2014 at 3:59 am
I have done it this way. It is working perfect
CREATE MEMBER CURRENTCUBE.[Measures].Electivemedianlos_Test
AS Median
(
{[Date].[Calendar].CurrentMember.Children},
[Measures].[LOS]
),
FORMAT_STRING = "#,##0.00;-#,##0.00"
Regards,
Ranjith
Ranjith Lekamalage
MCITP SQL 2012 (BI Development)
February 4, 2014 at 4:03 am
Can you try this
CREATE MEMBER CURRENTCUBE.[Measures.[Median of Enterprise Users]
AS MEDIAN(
{[Enterprises and Users].[Users].[All].Children}
,[Measures].[Loadtime] ),
VISIBLE = 1;
Ranjith Lekamalage
MCITP SQL 2012 (BI Development)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply