# Performance Issue: Calculated Measure

• ### Question

• Hopefully there is somebody that can help me out with this one.

I have to do a calculation where I have to exclude two dimension (read: take the all member) in a calculation.

If I'm doing it with one dimension, it's quit fast, but when I'm doing it with two dimensions in SQL Management Studio it talkes around 1 minute for the simplest query and in excel It takes forever.

Maybe there is a magic trick for this one?

Here is a description of the formula (the two dimensions I'm talking about are called dimension1 & dimension2)

Sum(( [Dimension1].[Dimension1].[ALL].Children, [Dimension2].[Dimension2].[All].Children, [DimensionA].[DimensionA].&[1], [Measures].[x])
/Sum(( [Dimension1].[Dimension1].[ALL].Children, [Dimension2].[Dimension2].[All].Children, [DimensionA].[DimensionA Hierarchy].[All], [Measures].[y])

Bram

Monday, March 25, 2013 1:25 PM

• What you really need to do is to include the All Members from every attribute in these dimensions then. There is a function, Root(), that does this but unfortunately it returns errors in certain scenarios so I don't recommend using it.

Incidentally, you don't need to use the sum() function here either, you just need to write a tuple. Something like

([Dimension1].[Attribute1].[All], [Dimension1].[Attribute2].[All], [Dimension2].[Attribute1].[All], [Dimension2].[Attribute2].[All], measures.x)
/
([Dimension1].[Attribute1].[All], [Dimension1].[Attribute2].[All], [Dimension2].[Attribute1].[All], [Dimension2].[Attribute2].[All], measures.y)

Chris

• Marked as answer by Wednesday, April 3, 2013 7:21 AM
Wednesday, March 27, 2013 1:41 PM

### All replies

• Why don't you create a measure with the sum of the dimensions in the bussiness studio for each dimension ?

After that, a calculation from measurea/measureb should be faster.

MVP MCT MCTS Daniel Calbimonte

Monday, March 25, 2013 3:55 PM
• Depends what logic is in measure x and measure y. Do you need to specify the .Children? If the measures are aggregates with no logic at the .Children level then you shouldn't need it.

Query should run faster if you omit .Children and just sum the 'All' member, e.g

[Dimension1].[Dimension1].[All]

Monday, March 25, 2013 4:07 PM
• Hi Dani,

Your solution looks interesting. But wont I have the same performance issues? Basically it's when I'm combining these two dimensions with a measure.

If I leave one of the two out, the performance is perfect.

Tuesday, March 26, 2013 9:50 AM
• Thx, but when using only the .[All] member, it will be filtered when using other attributes of that dimension, actually I simply want to exclude these dimension in the calculation but it looks not that easy :)

Tuesday, March 26, 2013 9:52 AM
• Create a calculated measure using EXCEPT function to exclude the intersection of your base measure with the dimensions you want to exclude.

Check-out the MDX EXCEPT function http://technet.microsoft.com/en-us/library/ms144900.aspx

Tuesday, March 26, 2013 11:04 PM
• What you really need to do is to include the All Members from every attribute in these dimensions then. There is a function, Root(), that does this but unfortunately it returns errors in certain scenarios so I don't recommend using it.

Incidentally, you don't need to use the sum() function here either, you just need to write a tuple. Something like

([Dimension1].[Attribute1].[All], [Dimension1].[Attribute2].[All], [Dimension2].[Attribute1].[All], [Dimension2].[Attribute2].[All], measures.x)
/
([Dimension1].[Attribute1].[All], [Dimension1].[Attribute2].[All], [Dimension2].[Attribute1].[All], [Dimension2].[Attribute2].[All], measures.y)

Chris

• Marked as answer by Wednesday, April 3, 2013 7:21 AM
Wednesday, March 27, 2013 1:41 PM
• Thanks Chris!

Works nice

Thursday, April 4, 2013 9:30 AM