none
SSAS - Calculation at Row Level

    Question

  • I have a problem with an agregated calculation that doesn’t make sense.

    I need to apply a calculation at row/cell level in SSAS! Normally, if I need to do row level calculations, I do in SQL source view or ETL, but in this case, the quantity (denominator) is also a Accumulated calculation as you can see the MDX Script below.

    Do you know any alternative to apply the calculation at row level is SSAS? L

    CREATE MEMBER CURRENTCUBE.[Measures].[Qt Inventory]

    AS SUM({NULL:[Dim Date].[Calendar].CurrentMember}

    , [Measures].[Qt Inventory Day]),

    VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Inventory';

     

    CREATE MEMBER CURRENTCUBE.[Measures].[Unit Price EUR]

    AS IIF([Measures].[Qt Inventory]=0,0,

        ([Measures].[Cost Amount Actual]+[Measures].[Cost Amount Expected])/[Measures].[Qt Inventory]),

    FORMAT_STRING = "Currency",

    VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Inventory';     

    Thank you!!

    Saturday, March 15, 2014 3:50 PM

Answers

  • One solution to this issue is to simply move those two calculated measures before the CALCULTATE; statement in your cube script. Before the CALCULATE is executed only the leaf level cells have data, it is the calculate which triggers the evaluation of the "parent" level values.

    However any leaf level calculations like this can be very expensive to perform as they will not make use of any aggregations and will probably not cache well (and aggregations and caching are the two primary mechanisms that SSAS MD achieves it's performance over large data volumes)

    If you are going to try this you will want to change the IIF statement in the [Unit Price EUR] measure to return a NULL in the second parameter

    IIF([Measures].[Qt Inventory]=0,NULL,

    The problem with it at the moment is that SSAS automatically coalesces empty cells to 0 so the check for =0 will actually return true for 0 and empty values. This will lead to a data explosion where every possible leaf combination will have a non-empty value of 0 which will kill the performance.


    http://darren.gosbell.com - please mark correct answers

    Sunday, March 16, 2014 7:55 PM

All replies

  • I just want to have the calculation SUM( ( A + B ) / C) in spite of (SUM(A) + SUM(B) / SUM (C))

    i cannot add the C to DSV because is another calculation (see above)

    any help?

    THANK YOU


    • Edited by PM_SSAS Saturday, March 15, 2014 5:47 PM
    Saturday, March 15, 2014 5:46 PM
  • Hi,

    you need to use function Descendants for navigation to leaf level, then perform calculation on leaf level and aggregate it back again. Here you got sample calculation dealing with similar problem. Recalculation to different currency on date level and aggregating the result

    Jiri

    SUM
    	(
    	DESCENDANTS(-- navigation to leaf level in time
    		[Dim Date].[YQMD].currentmember
    		,[Dim Date].[YQMD].[Date]
    		)
    	,
    	[Measures].[End Of Day Rate]
    	*[Measures].[Sales Amount]
    	)


    Jiri Neoral

    Saturday, March 15, 2014 10:06 PM
  • Dear Neoral,

    Thanks for your reply!

    I'm trying using this MDX:

    SUM(DESCENDANTS([Dim Product].[Product Code],,LEAVES), [Measures].[COST]/[Measures].[QT])

    But If I drag an atribute from this dimension (example Business Area) it will repear the result for every Business areas...

    Any help?

    Sunday, March 16, 2014 12:16 AM
  • I'm using this one, but just work with BA hierarchy:

    SUM(DESCENDANTS([Dim Product].[BA].CurrentMember,,LEAVES), 

    IIF([Measures].[Qt Inventory]=0,0,
        ([Measures].[Cost Amount Actual]+[Measures].[Cost Amount Expected])/[Measures].[Qt Inventory])


    )

    Sunday, March 16, 2014 1:16 AM
  • One solution to this issue is to simply move those two calculated measures before the CALCULTATE; statement in your cube script. Before the CALCULATE is executed only the leaf level cells have data, it is the calculate which triggers the evaluation of the "parent" level values.

    However any leaf level calculations like this can be very expensive to perform as they will not make use of any aggregations and will probably not cache well (and aggregations and caching are the two primary mechanisms that SSAS MD achieves it's performance over large data volumes)

    If you are going to try this you will want to change the IIF statement in the [Unit Price EUR] measure to return a NULL in the second parameter

    IIF([Measures].[Qt Inventory]=0,NULL,

    The problem with it at the moment is that SSAS automatically coalesces empty cells to 0 so the check for =0 will actually return true for 0 and empty values. This will lead to a data explosion where every possible leaf combination will have a non-empty value of 0 which will kill the performance.


    http://darren.gosbell.com - please mark correct answers

    Sunday, March 16, 2014 7:55 PM
  • Thank you Daren!

    I used 

    SUM(DESCENDANTS([Dim Product].[BA].CurrentMember,,LEAVES), 

    IIF([Measures].[Qt Inventory]=0,NULL,
        ([Measures].[Cost Amount Actual]+[Measures].[Cost Amount Expected])/[Measures].[Qt Inventory])


    )

    But maybe I'll try your suggestion later!THANK YOU!

    Tuesday, March 18, 2014 10:41 AM