Hi all,
I am having a problem with Analysis Services. Currently we are developing an analysis application that enables a user to view up to 5 year survival for patients.
I have written the following MDX to perform the calculated measure:
Code Snippet
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
//**************************************************************************************
//**************************************************************************************
//**************************************************************************************
// SURVIVAL
// Censor flag = 1 if censored, 0 if noncensored (or true failure),
// NULL if there is data error (death date <= diagnosis date)
CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]
AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],
FORMAT_STRING = "#",
VISIBLE = 0 ;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]
AS 0,
VISIBLE = 1;
SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =
(
Case
/*
At the first survival time (defined as Null, i.e. 1st row with "Blank" time,
when there is no previous month; PrevMember is empty):
Number at risk is simply total number of valid death dates (both censored and uncensored)
in the dataset, which is the total number of patients with censor flag values of
either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).
NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died
by this date are censored and assigned a death date of June 1, 2006.
The censor flag is set to null only when death date is same as or EARLIER than
date of diagnosis, i.e. excludes patients diagnosed during autopsy.
*/
When IsEmpty( [Survival Time].CurrentMember.PrevMember )
Then ( Root( [Survival Time] ), [N Total Deaths] )
/*
This is the survival censor count at the top ("root") level of survival time,
*/
Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:
(
// Number at risk from previous month,
// minus total number of deaths (both censored and uncensored)
// during current month
( [Survival Time].CurrentMember.PrevMember, [Number at risk] )
- [N Total Deaths]
)
End
);
END SCOPE;
//--
CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]
AS 100,
FORMAT_STRING = '0.0',
VISIBLE = 1;
SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =
(
Case
// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):
When IsEmpty( [Survival Time].CurrentMember.PrevMember )
Then (
(
(
( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])
)
)*100
)
Else // At subsequent survival times ( t > 0, censor <> null ):
(
Case
// If everyone's dead (at right end of time(x) axis)
When [Number at risk] = 0
// Then use survival from previous month (PrevMember)
Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )
Else (
(
// Number at risk from previous month, minus true failures (noncensored deaths),
// divided by Number at risk from previous month ...
(
( ([Survival Time].CurrentMember.PrevMember, [Number at risk] ) - ( [Survival Time].CurrentMember,[N Noncensored Deaths]) ) /
([Survival Time].CurrentMember.PrevMember, [Number at risk]
)
)
// then multiplied by survival from previous month
* ([Survival Time].CurrentMember.PrevMember, [Survival])
)
)
End
)
End
); END SCOPE;
When I put the Calculated Measure then the survival time dimension in the x axis in the pivot table, it works fine. But when I put a dimension in the filter and choose multiple items (not all and not only one) it tries to build and run the query but seems to hang.
Has anyone else experienced this sort of thing before?
Would it be something to do with the MDX?
I have been struggling with this for a while and not been able to work it out. ANY suggestions would be fantastic.
If you need further information, let me know.
Steve
I have not gone through your code in too much detail, but you main issue is going to be the .CurrentMember function.
When you have mulitple members on the WHERE clause (which is what happens when you filter by more than one member), you do not have a single current member, you actually have a set of current members. SSAS 2005 just does not handle this well. See this blog post for more details: http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx it might give you some hints on how to alter your code.
|||
Darren,
Thanks for your reply. I tried going to that blog post but couldn't open the page. Are you able to provide another location for the blog?
Cheers,
Stephen
|||
Yeah, I found out that the whole of sqljunkies.com appears to have gone off the air, no word on when or if it will return.
You can find an archived copy of this post in the web archive at http://web.archive.org/web/20070515145719/http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
|||
Thanks Darren. I found the blog but am not sure how to fix my code.
I actually tried a couple of things with no success. I removed the .CurrentMember from my code (see code snippet) and it worked for single select queries but it still hangs for multiselect.
Code Snippet
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
//**************************************************************************************
//**************************************************************************************
//**************************************************************************************
// SURVIVAL
// Censor flag = 1 if censored, 0 if noncensored (or true failure),
// NULL if there is data error (death date <= diagnosis date)
CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]
AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],
FORMAT_STRING = "#",
VISIBLE = 0 ;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]
AS 0,
VISIBLE = 1;
SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =
(
Case
/*
At the first survival time (defined as Null, i.e. 1st row with "Blank" time,
when there is no previous month; PrevMember is empty):
Number at risk is simply total number of valid death dates (both censored and uncensored)
in the dataset, which is the total number of patients with censor flag values of
either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).
NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died
by this date are censored and assigned a death date of June 1, 2006.
The censor flag is set to null only when death date is same as or EARLIER than
date of diagnosis, i.e. excludes patients diagnosed during autopsy.
*/
When IsEmpty([Survival Time].PrevMember )
Then ( Root( [Survival Time] ), [N Total Deaths] )
/*
This is the survival censor count at the top ("root") level of survival time,
*/
Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:
(
// Number at risk from previous month,
// minus total number of deaths (both censored and uncensored)
// during current month
([Survival Time].PrevMember, [Number at risk] )
- [N Total Deaths]
)
End
);
END SCOPE;
//--
CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]
AS 100,
FORMAT_STRING = '0.0',
VISIBLE = 1;
SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =
(
Case
// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):
When IsEmpty([Survival Time].PrevMember )
Then (
(
(
( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])
)
)*100
)
Else // At subsequent survival times ( t > 0, censor <> null ):
(
Case
// If everyone's dead (at right end of time(x) axis)
When [Number at risk] = 0
// Then use survival from previous month (PrevMember)
Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )
Else (
(
// Number at risk from previous month, minus true failures (noncensored deaths),
// divided by Number at risk from previous month ...
(
( ([Survival Time].PrevMember, [Number at risk] ) - ([Survival Time],[N Noncensored Deaths]) ) /
([Survival Time].PrevMember, [Number at risk]
)
)
// then multiplied by survival from previous month
* ([Survival Time].PrevMember, [Survival])
)
)
End
)
End
); END SCOPE;
I appreciate you helping me on this.
Cheers,
Steve
|||
Sorry, I was not entirely clear. While removing the explicit CurrentMember calls is good, there is still an implied one for the .PrevMember calls. The query engine still needs a single member context to calculate a PrevMember. There is a way to do this, but it has the unforutnate side effect of slowing down your calc for single member select. What you would need to do is to wrap your case statement (the bit after the equals inside your scope statement) as follows:
GENERATE( EXISTING [Survival Time].Members, <case statement> )
The Existing operator returns the set of one or more survival time members that are in context for the current query and the generate function essentially loops over the set and evaluates the case statement for each one. The results are then aggregated together and returned.
|||
Hi Darren,
My guess is that multi-select is not occurring on the [Survival Time] dimension, otherwise there should have been an error like: "The MDX function CURRENTMEMBER failed because the coordinate for .. attribute contains a set", as mentioned in Mosha's blog. The scenario described by Steve suggests that [Survival Time] is on columns, and some other dimension(s) (which aren't mentioned in the script) are being multi-selected in the filter field - but Steve could confirm this? It might be useful to know the client tool (presumably some flavor of Excel).
|||
Hi Deepak,
You are right. We are selecting dimensions such as age, location etc.
We are using Dundas for our client tool.
Cheers,
Steve
|||
Steve,
Thanks for the clarification - to confirm the exact Dundas product, is it Dundas Chart for .NET - OLAP Services?
|||
Deepak,
It is Dundas Chart for .NET - OLAP Service (5.5).
Thanks,
Steve
|||
Here are some initial ideas for improving script performance - with more information, these could be elaborated:
It might be possible to handle the first [Survival Time] member using static scoping, rather than a run-time case statement, as discussed in this article. What is the structure of [Survival Time] - how many levels does it have?
Not sure which measures are cube vs. calculated, but if [Measures].[N Total Deaths] and [Measures].[N Censored Deaths] are cube measures, [MEASURES].[N Noncensored Deaths] could be implemented as a cube measure as well. Could you describe the fact table/measure group and associated measures?
Using running sum calculations which leverage block computation - it looks like [MEASURES].[Number at risk] could be implemented using a running sum of [N Total Deaths]?
|||
Hi Deepak,
I have run the query when it works and when it hangs. And the following MDX queries are what resulted:
Working:
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( ( [Year of diagnosis].[Year of diagnosis].[All] ), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
Not Working (hangs/uses up all resources):
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( VISUALTOTALS({ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
Thanks for your help so fay.
Steve
|||
Hi Steve,
Just wanted to clarify a couple of things:
- Is [Survival Time] just a single level (month) dimension - and approx. how many members?
- The VisualTotals() seem to be superfluous, so could you check whether the 2nd query still hangs without VisualTotals(), like:
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, { [Measures].[Survival] } ) } ON ROWS FROM [Survival] WHERE ( { [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }, ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
|||
Hi Deepak,
The [Survival Time] dimension is a single level dimension. It is a count of months from a starting date to a death date.
I also ran the query from the browser in SQL Server Management Studio which dropped Visual Totals and it still hung.
Thanks again for your help.
Cheers,
Steve
|||
I'm not sure you need any of the VisualTotals functions. And unless you have overriden the default member settings on some of your dimensions, explicit referencing the default members would be redundant. The following two queries should be equivalent to the last 2 that you posted.
SELECT
NON EMPTY
{
{[Survival Time].[Survival Time].[All]}
, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS
, NON EMPTY
{[Cancer].[Cancers].[All]
, [Cancer].[Cancers].[Stream].members}
ON ROWS
FROM [Survival]
WHERE
( [Measures].[Survival],[Year of diagnosis].[Year of diagnosis].[All] );
--Was not Working
SELECT
NON EMPTY
{
{[Survival Time].[Survival Time].[All]}
, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS
, NON EMPTY
{[Cancer].[Cancers].[All]
, [Cancer].[Cancers].[Stream].members}
ON ROWS
FROM [Survival]
WHERE ( [Measures].[Survival],
{ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001]
, [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] })
