Russ Whitney in his article "Alternative Aggregations",SQL Mag March 2003 (InsDoc # 37707) offers solution to quite a complex problem, i.e. Inventory calculations in MS Analysis Services/OLAP.
He writes: "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) )"
Unfortunately the only reason that this formula works is that there are no gaps in loaded values for every leaf-level Time member. If one adds another level under Month, say Day, which is possible for the database the author uses for the illustration, there will gaps and Russ' formula stop working.
I've tested numerous variations of this approach, none of them does the job, I am inclined to think that MS OLAP can't handle this. Maybe you came across of the solution... Please, help.
He writes: "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) )"
Unfortunately the only reason that this formula works is that there are no gaps in loaded values for every leaf-level Time member. If one adds another level under Month, say Day, which is possible for the database the author uses for the illustration, there will gaps and Russ' formula stop working.
I've tested numerous variations of this approach, none of them does the job, I am inclined to think that MS OLAP can't handle this. Maybe you came across of the solution... Please, help.