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

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,

Answer 1

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.

Answer 2

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