Answered by:
[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,
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.- Proposed as answer by Jerry NeeModerator Tuesday, January 31, 2012 8:43 AM
- Marked as answer by Jeroen Bolle Thursday, February 2, 2012 2:42 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.- Proposed as answer by Jerry NeeModerator Tuesday, January 31, 2012 8:43 AM
- Marked as answer by Jeroen Bolle Thursday, February 2, 2012 2:42 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