none
Performance Issue: Calculated Measure RRS feed

  • 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])

    Thanks in advance,

    Bram

    Monday, March 25, 2013 1:25 PM

Answers

  • 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


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

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

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

    http://elpaladintecnologico.blogspot.com

    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


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

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

    Works nice

    Thursday, April 4, 2013 9:30 AM