none
[SSAS - MDX] Evaluate a measure over a unique combination of different dimension members RRS feed

  • Question

  • I wish to evaluate a measure over a unique combination of different dimension members.

    For example, I wish to look at the measure 'Costs' over three dimensions: 'Product', 'Category', 'Region'.

    I need to evaluate if the costs for each unique combination of product, category and region (based on a dimension attribute) is larger than a specific value (e.g., 1000).

    If it is (e.g., 1500), I want to keep the difference (500) in a calculated measure.

    Is this possible with MDX? Or should I look to prepare this kind of calculations beforehand (in ETL).

    Thanks,

    Thursday, January 26, 2012 4:29 PM

Answers

  • It is possible, but you have to test the performance on you cube. Perharps Aggregations can help but relational it would be the fastest way.

    Try this:

    with member [Measures].[Cost2] as [Measures].[Cost]-1000;
    
    create member currentcube.[Measures].[Diff over 1000] as
      sum(
        filter(
            existing ([Dim Product].[Product].[Product]*
            [Dim Category].[Category].[Category]*
            [Dim Region].[Region].[Region])
            ,[Measures].[Cost] > 1000
        )
        , [Measures].[Cost2]
      )

    At least you should try an nonempty over the whole tuple. If it is too slow, try to nonempty every single dimension.

    Thursday, January 26, 2012 8:50 PM

All replies

  • It is possible, but you have to test the performance on you cube. Perharps Aggregations can help but relational it would be the fastest way.

    Try this:

    with member [Measures].[Cost2] as [Measures].[Cost]-1000;
    
    create member currentcube.[Measures].[Diff over 1000] as
      sum(
        filter(
            existing ([Dim Product].[Product].[Product]*
            [Dim Category].[Category].[Category]*
            [Dim Region].[Region].[Region])
            ,[Measures].[Cost] > 1000
        )
        , [Measures].[Cost2]
      )

    At least you should try an nonempty over the whole tuple. If it is too slow, try to nonempty every single dimension.

    Thursday, January 26, 2012 8:50 PM
  • Thank you for your example. Performance is indeed a serious issue when this calculation is performed. The product of those three dimensions is too much for my server to handle.

    I worked around it by storing in a concatenate of those three keys in my fact table and creating a cube dimension with my fact table as source and that concatenate as the only attribute hierarchy.

    Same results with acceptable performance.

     

    Kind regards,

     

    Jeroen

    Thursday, February 2, 2012 2:42 PM