Some cubes don't contain loaded values for every leaf-level Time member. In those cubes, you can't use a formula as simple as the one above to determine the most recent value for a measure. I ran into such a situation while building a cube to track software defects. The cube was built from a fact table that contained defect state transitions. The software quality-assurance process tracks defects through states, which have names like found but not assigned to a developer, assigned to a developer, fixed but awaiting the next product build, fixed but not verified, or closed. Defect state transitions are the occurrences of a defect moving from one state to another. For example, each entry would contain a defect ID, description, date the change occurred, the original state, and the new state. The fact table I used contained a date that the defect changed state, an origination state, and a destination state. To create in the cube a measure that returns the defect state for any time period, I had to include a formula that looks back to the most recent date that the defect changed state:
IIF( Time.CURRENTMEMBER.LEVEL
.NAME "Month",
Time.CURRENTMEMBER.LASTCHILD,
TAIL( FILTER( [Time].[1997]
.[Q1].[1] : Time
.CURRENTMEMBER,
NOT ISEMPTY( [Unit Sales] ) ) *
{ [Unit Sales] }, 1 ).ITEM(0) )
This MDX expression is significantly more complex than the previous one. The principle behind this expression is the same as for the previous expression, but instead of just using (Time.CURRENTMEMBER, [Unit Sales]) as the last parameter to the IIF() function, you have an expression that identifies the most recent non-empty month. First, take the set that includes all months from the first month ( [Time].[1997].[Q1].[1] ) to the current month—the colon operator (:) identifies a range of members—and filter out the empty months. The expression then uses the asterisk operator (*) to cross-join Unit Sales with the set of non-empty months and uses the TAIL() function to reduce the set to its last item. The ITEM(0) operator converts the result of the TAIL() function (a set) to its first item.
In the two MDX examples above, I used Unit Sales from the FoodMart Sales cube so that you could experiment with the MDX. In a real-world situation, you'd replace Unit Sales with a loaded measure. Because you have to use one of the native Analysis Services aggregation types to create the loaded measure, the measure won't return valid information when you use it with nonleaf-level Time dimension members. Because of the possibility of returning invalid data, you need to set the visibility of the loaded measure to false and allow your application's end users to see and use only the calculated measure. After you've selected the measure, you can change the visibility of a loaded measure in the Properties pane of Analysis Manager's Cube Editor.
Many other aggregation types exist, and some are quite simple. For example, a common application of OLAP is to model a business income statement. At the highest level of the income statement, you want to subtract expenses from income to create net income. At all other levels in this dimension, you want to aggregate account categories by using a summation. In this situation, a custom rollup function might be the most appropriate choice.
Calculated measures, calculated cells, and custom rollups are all tools that can help you customize aggregation formulas. Analysis Services is flexible when you know how to use its advanced features. I hope reading through these examples and explanations will help you think more creatively about how to create your OLAP cubes so that they aggregate measure values in a way that's appropriate for the problem you're trying to solve.