Home Dashboard Directory Help
Search

Allow calculated members to return sets by Christopher Webb


Status: 

Active


18
0
Sign in
to vote
Type: Suggestion
ID: 426719
Opened: 3/25/2009 7:27:05 AM
Access Restriction: Public
0
Workaround(s)
view

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.
Details
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
Sign in to post a workaround.