locked
Semi-additive measure requires a time dimension?! RRS feed

  • Question

  • Hello Friends,

    I have a simple table like below

    And in my cude, not having ay other dimensions, I use "avgForChildren" on yhe measure "score" other than sum. However, it reports to me "Semi-additive measure requires a time dimension" when I build the cube.

    My question , why in this case, it needs the time dimension?

    Secondly, max and min are also smi-additive ones but if changing to MAX or MIN, i t can pass the build, why?


    Derek

    Wednesday, March 21, 2012 9:02 PM

All replies

  • Does anyone know this?


    Derek

    Friday, March 23, 2012 10:55 PM
  • Hi Derek,

    In short,

    • All semi additive use the selected function for the aggregation with time dimension and sum with all others.
    • The Min and Max are particular in the sense that they are used both for time dimension and other dimensions.

    For detailed discussion on this topic, read http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/1a404c20-1a4c-4881-95f0-150e91f6e1b8/ .

    However, if you just want the score average with so simple cube structure, you can get around this issue with additive functions, sum and count. Like this solution: http://blog.stevienova.com/2007/06/20/ssas-averageofchildern-and-semi-additive-measure-requires-a-time-dimension/

    Regards,
    Jerry

    • Edited by Jerry Nee Monday, March 26, 2012 8:29 AM
    Monday, March 26, 2012 8:18 AM
  • If you have created  Dimdate dimension table in your project just go to edit dimentsion select it and in properties make it's type to time.

    and if you do not have time dim the use readymade templates while creating new dimension select generate time table and remaining the wizard itself will let you know.


    p.m.bhoi

    Sunday, April 1, 2012 9:33 AM
  • Hello Pankaj,

    The question is why tiem dimension is necessary.


    Derek

    Sunday, April 1, 2012 4:08 PM
  • Thanks Pankaj Bhoi !! That was the solution.
    Saturday, September 22, 2012 1:56 AM
  • Thanks Pankaj, your solution rocks!
    Thursday, January 31, 2013 8:13 AM
  • Derek,

    I don't think you should be using a semi-additive measure for average score.  Surely, you want to return the average score for any combination of time, course, student etc?

    If so, change your cube to have two physical measures, [course score total] and [course count], where [course count] is the count of scores that you have to be averaged.  the [course score total] is a SUM of the course score.  This measure should probably be invisible, as its raw value means little.  Then create a calculated measure [Avg course score] as [course score total]/[course count].  Now, wherever you are in the cube, you can get an average score.

    This is the classic way of managing averages.  

    Hope that helps,


    Richard

    • Proposed as answer by RichardLees Monday, February 4, 2013 2:02 AM
    Friday, February 1, 2013 3:45 AM
  • Thank you, this just helped me with the same error!
    Tuesday, February 28, 2017 6:53 PM
  • Hi,

    I had different issue but your suggestion helped me.

    Thank you,

    Parag Gajjar

    Tuesday, July 25, 2017 7:36 AM
  • How do it?
    Tuesday, March 6, 2018 8:32 PM