My question is on measure expressions that involve two measure groups. I tried to implement them and they are excellent in terms of performance. I currently have about two dozen of them and need to add another hundred or more. Is there a penalty in terms of cube process time, or storage, or RAM requred for cube processing for having so many of them? Are they materialized or they are truly expressions evaluated at request time? (I would like to have some background on their implementation before fully committing to go this route and could not find any details in SSAS documentation or anywhere on the Web.)
Thank you,
Victor
Thanks, Victor.
If the measures involved in the expression are from the same measure group, I'd create a named calculation in the DSV or in a SQL view that wraps the fact table. If they are from different measure groups, measure expressions is the recommended performance-enhancement technique, as mentioned in the SSAS 2005 performance guide (see below). Measure expression do incur some runtime performance because this is what the server has to do to resolve the expression:
1. Create a list of dimensions that are common to the two measure groups.
2. Identify the deepest common granularity.
3. Retrieve the value of the measure.
4. Execute the measure expression for each record within the common granularity.
You can optimize the expression for common cases, such as multiplying by 1 when you do currency conversion and by default reports will show in USD, by setting the DirectSlice property (in the CurrencyConversion measure group in this case). Christian Wade has written a great post about measure expressions which you should check out as well.
"Use measure expressions (from the SSAS 2005 performance guide)
Measure expressions are calculations that the Storage Engine can perform. Using measure expressions, you can multiply or divide data from two different measure groups at the measure group leaves and then aggregate the data as a part of normal cube processing. The classic example of this is when you have a weighting factor stored in one measure group, such as currency rates, and you want to apply that to another measure group such as sales. Instead of creating an MDX calculation that aggregates the multiplication of these two measures at the measure group leaves, you can use measure expressions as an optimized solution. This kind of calculation is perfect for a measure expression since it is somewhat more difficult to accomplish in the data source view given that the measures are from different source fact tables and likely have different granularities. To perform the calculation in the data source view, you can use a named query to join the tables; however, the measure expression typically provides a more efficient solution.
While measure expressions can prove to be very useful, note that when you use a measure expression, the Storage Engine evaluates the expression in isolation of the Query Execution Engine. If any of the measures involved in the measure expression depend on MDX calculations, the Storage Engine evaluates the measure expressions without any regard for the MDX calculations, producing an incorrect result set. In this case, rather than using a measure expression, you can use a calculated member or scope assignment."
Thanks Teo. This is very helpful.