Dimension calculated member is slow

Question

Hi,

(SSAS 2008R2)

I'm trying to create a new calculated member to the ReferencePeriod dimension.

Spec: it must be the latest month where there is some data (Amount) and  DataSource=MACRO in the fact table.

I came up with 2 options (see below) that returns the right result (let's see 2011/09)

Issue: both calculated members are quite SLOW when browsing the cube

Any advice to make them faster is appreciated.

Other info: ReferencePeriod dimension is parent-child with 2 levels (Year, Month)

Thanks in advance.

1)

CREATE MEMBER CURRENTCUBE.[Reference Period].[All].[Latest Macro Month]
 AS {TAIL(FILTER( [Reference Period].[Month].members,
            NOT ISEMPTY( ([Measures].[Amount], [Data Source].[Source].&[MACRO]) )
       ),1)}.item(0),
VISIBLE = 1;

2)
CREATE MEMBER CURRENTCUBE.[Reference Period].[All].[Latest Macro Month]
 AS {TAIL(NONEMPTY (CROSSJOIN(
 {[Reference Period].[Month].members},
  {[Measures].[Amount]},
    {[Data Source].[Source].&[MACRO]}
     )),1)}.item(0).item(0),
VISIBLE = 1;

Answer 1

You could also just try the nonempty with the measure

CREATE MEMBER  CURRENTCUBE.[Reference Period].[All].[Latest Macro Month]
AS TAIL(NONEMPTY ([Reference Period].[Month].members

                             ,([Measures].[Amount], [Data Source].[Source].&[MACRO]))
            ,1).item(0).item(0),
VISIBLE = 1;

Not sure it is going to be that much faster though. Is there a reason you have used a parent child for the dimension? It may be quicker to try and materialise that - see the BIDS Helper for more information on that.

Answer 2

It is still slow as before.

Thanks anyway for the suggestion.

---

thm

Answer 3

Is there a reason that you are using a Parent Child heirarchy. It may be worth trying to resolve the latest month using the actual key rather than the heirarchy, and then getting the monthm though the use of exists.

So:

AS EXISTS([Reference Period].[Month].members

               ,TAIL(NONEMPTY([Reference Period].[Key Attribute].[Key Attribute]

                                        ,([Measures].[Amount], [Data Source].[Source].&[MACRO])), 1)

               ).Item(0).Item(0),

VISIBLE = 1;

Answer 4

Hi Michael,

It returns the right result but the query speed is still the same.

Thanks again for your input.

---

thm

Answer 5

Hi thm

How many members do you have in your dimension?

Also is there a reason this has to be parent child? Can you not materialise this as per the BIDSHelper project?

Alternatively is it something you could set in the source for the dimension, and then have a changing attribute on the dimension which is latest month. You could then simply get the latest member by using the actual dimension member which will be much quicker?

Mike

Answer 6

Hi truc,

I think the following expression provided by Michael is the optimal in your business logic. Per my knowledge, its speed should be acceptable. But you still say the speed is slow. I suspect the performance issue not fully caused by the expression. Could you plese provide more details to describe the dimension or other things related here for further analysis?

CREATE MEMBER  CURRENTCUBE.[Reference Period].[All].[Latest Macro Month]
AS TAIL(NONEMPTY ([Reference Period].[Month].members

                             ,([Measures].[Amount], [Data Source].[Source].&[MACRO]))
            ,1).item(0).item(0),
VISIBLE = 1;

Regards,
Jerry

Answer 7

Hi Mike,

We have around 150 members in the dimension which is not that large. Parent-child is not a must, it is just the standard for all our dimensions.    For my own testing purposes, I have added a new attribute (LatestMacro) to the dimension and set it to 'true' for the target Month.  Now the calculated member looks like follow:                                      

CREATE MEMBER CURRENTCUBE.[Reference Period].[All].[Latest Macro Month]
 AS FILTER( [Reference Period].[Month].members,
            [Reference Period].currentmember.Properties('LatestMacro')='true' ,
       VISIBLE = 1;                                                                                                                                                                                      Guess what, it's still slow ! As Jerry has  mentionned below, may be it's not fully caused by the MDX. I will have to look in other areas of SSAS. Thanks all for your valuable inputs and your time.

---

thm

Answer 8

If you have the LatestMacro member in the dimension, you should be able to simply create a calculated member as

CREATE MEMBER CURRENTCUBE.[Reference Period].[All].[Latest Macro Month]
AS EXISTS( [Reference Period].[Month].members,
            [Reference Period].[LatestMacro].&[true])
       VISIBLE = 1;

This should be much quicker than your previous method. However with only 150 members in the dimension, i wouldn't expect it to take very long, and I think your issue may be more with other MDX, rather than the current calculation

Hope this helps.