CY and LY comparison

Hopefully an easy one:

I would like to get a CY comparison vs. a LY comparison of sales between offices. The problem I am running into is the performance of the query. When I try below, it works, but it is painfully slow (I believe the SUM is what is killing it):

With Member [Measures].[CY Sales] as
Sum (( [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220] )
, [Measures].[Sales])

Member [Measures].[LY Sales] as
Sum (( [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7) )
, [Measures].[Sales]
)

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube]

The comparison is important to get the day of week to day of week comparison (hence the 52 weeks * 7 days vs. using parallelPeriod to do a day of the year comparison).

If I try the following, it is *very* quick, but the problem is if I run it for CY and separately for LY, some offices may not have been open last year, or may have closed this year, so the number of rows returned may be (will be) different. Also, I don't know how to "union" them together into one cellset returned.

Select {
[Measures].[Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube Name]
Where { [Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220] }

Does anyone know of a way I can do something like the following, either with Temporary sets, etc.? Please forgive the simplicity of the problem, as I am still cutting my teeth with this powerful, yet complex product. :)

With Member [Measures].[CY Sales] as
({ [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220] } ,
[Measures].[Sales] )

Member [Measures].[LY Sales] as
({ [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7) } ,
[Measures].[Sales] )

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1

From [Cube]

Any suggestions are greatly appreciated!

Thank you in advance,
John Hennesey

It seems to me that the last MDX that you provided won't work, since you are using:

{ [Org].[Reg - Pctr - Office].CurrentMember, [Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7) }

It mixes different dimensions in the enumeration set - you will get an error for that.

I also beleive that the following query should give you very good performance in AS2005 even without SP2, but especially with SP2:

With Member [Measures].[CY Sales] as
Sum ( [Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220], [Measures].[Sales])

Member [Measures].[LY Sales] as
Sum ( [Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7), [Measures].[Sales]
)

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube]

But probably, if you really need to compare at the Day Of Year level, the best idea would be to add [Day Of Year] attribute to your Time dimension, and then you will be able to do the following:

Select { [Calendar].[Year].[2006], [Calendar].[Year].[2007] } on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube Name]
Where { [Calendar].[Day Of Year].&[Day1] : [Calendar].[Day Of Year].&[Day2] } * Measures.Sales

|||

Mosha -

Thank you very much for your quick response. I was afraid that was the only way to do it... When I run the first example, it takes ~9 seconds with just the CY Sales. When I add LY Sales, it goes to 18 seconds. When I add another measure (say, CY Net sales, for the same time period as CY Sales), it balloons to 36 seconds. By the time I get all the measures in (Sales, Return Count, Net sales, etc..) it takes ~1 minute to run. We have a relatively small cube (we do a fully reprocess 4 times a day, which takes < 5 minutes to complete). As for the 2nd example, it is a year to date comparison, so we definitely have to restrict the LY dates to 364 days ago. Sounds like either a redesign of the cube, or service pack 2 is the answer. Does SP2 address this kind of query? Is there any way (for lack of better terms) to design this similar to SQL where you can stash the results from one MDX in a temporary cube, run another MDX then do a join between the two?

Thank you so much,
John Hennesey

|||

> When I run the first example, it takes ~9 seconds with just the CY Sales

Have you tried my variant of the MDX ? I beleive it should perform as fast as set in the WHERE clause even before SP2, but certainly with SP2 as well.

|||

Once again, thank you for the timely response!

Here is what I ran:

With Member [Measures].[CY Sales] as
Sum ( [Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220], [Measures].[Sales])
Member [Measures].[LY Sales] as
Sum ( [Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7), [Measures].[Sales]
)

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[RG].[ZZZ123].Children
} on 1
From [Cube]

I did, and unfortunately it took ~20 seconds.

When I run the following it takes less than a second, whether for CY or LY.

Select { [Measures].[LY Sales] } on 0,
{ [Org].[Region - Pctr - Office].[RG].[ZZZ123].Children } on 1
From [Cube]
Where [Preparer Calendar].[Date].&[20061101].Lag(364) : [Preparer Calendar].[Date].&[20070220].Lag(364)

Our environment is c# querying the cube, so I think the answer in this case (until we apply SP2) is to:

Get a list of all offices