Allow calculated members to return sets - by Christopher Webb

Status : 

 


19
0
Sign in
to vote
ID 426719 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 3/25/2009 7:27:05 AM
Access Restriction Public

Description

I know from the title this sounds a stupid idea, but bear with me...

Very often, the worst-performing calculations on a cube are those that do operations over large sets. Imagine you have a customer dimension with several million customers on; you might want to create calculated measures that tell you the following:
1) The number of customers who bought more than $10 worth of goods this month
2) The number of customers who bought more than $10 worth of goods last month but did not do so this month
... and so on. This is all very doable in MDX at the moment but can be slow - you're taking the set of all customers and then doing several filter operations, then counting the set you get back from the filter.

The thing is, a lot of the logic is repeated across these calculated measures. So, for calculation (1) above you would write MDX something like:
count(
filter(
customer.customer.customer.members
, measures.sales>10
))

But for (2) you'd have to say
count(
except(
filter(
customer.customer.customer.members
, (measures.sales,date.calendar.currentmember.prevmember)>10
),
filter(
customer.customer.customer.members
, measures.sales>10
)
)

In both cases, we need to work out which customers had sales of over $10 this month, but there's no way of sharing that set safely between calculated measures. Using named sets declared inline in (2) might improve its performance, I know, and in some circumstances you can reference a named set declared in one calculated member in another one but it's not a reliable technique. 
Sign in to post a comment.
Posted by Yacoobk on 4/23/2011 at 5:21 PM
Did anything come of this? possibly for SQL11?
Posted by Adam Tappis on 7/10/2009 at 6:13 AM
Couldn't this be implemented via dynamic named sets? An extension to the syntax would allow you to say that you would like to it to re-evalueated every time and internally it would be cached based on context
Posted by Microsoft on 3/30/2009 at 1:57 PM
Thanks Chris.
This is a great idea.

We will look into this.
Edward Melomed