Russ Whitney in his article "Alternative Aggregations", SQL Magazine, 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. Can you help, if you come accros a solution for this problem, please

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.

Can you please explain what you mean by "gaps". If there is a Day level -
what do you expect to get for Month level and for Day level.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

Mosha hi,

and many thanks for the reply, I do appreciate it very much.
Under gaps I ment that not all products got sold in every shop every
days.
Therefore a source table for the Inventory cube can have many gaps in
stock activities

I've tried everything (I think). The formulas I am using as follows:
=============LastNonEmptyQty===============
CoalesceEmpty(([Time].CurrentMember,[Measures].[Unit Sales]) ,
([Time].CurrentMember.PrevMember,[Measures].[LastNonEmptyQty]))

=============and ClosingQty===================
([Measures].[LastNonEmptyQty] ,
ClosingPeriod([Time].[Day],[Time].CurrentMember))

(They refer to the [Foodmart 2000].[Sales] with [Day] level added) They
do work well (visually) if we use only Measures on rows and Time on
columns (or vice versa), but as soon as we add, say Store dimension, it
becomes obvious that Stock on Hand at the State level <> Sum of Stock on
hand in all the children branches.

Best regards,
Sergei.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

When there are "gaps" at the leaf time level, then I think that the
native "sum" aggregation can no longer be applied to other dimensions
either, and custom roll-ups or a more complex calculated measure may be
needed.

Take the example you gave of stores in a state. Say Store A has 3 units
of Product X stock on 29th, and Store B has 2 units of Product X stock
on 30th. At the state level, the native "sum" aggregation results in 3
units on 29th and 2 units on 30th, so the last value will be computed as
2, instead of 5 (unless a custom roll-up sums last values).


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks for the reply, but it does not help.
I've tried custom aggregations too. It ignores calculated mesures at
closing date (although they are right).
Do you have any particular custom aggregations in mind?

Regards,
Sergei.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

After experimenting with your Calculated Measures for the
Foodmart Sales cube, and assuming that ClosingQty roll-up
is only required on [Product] and [Store] dimensions,
this version of ClosingQty seems to roll up OK:

'Sum(Descendants(Product.CurrentMember,,LEAVES),
Sum(Descendants(Store.CurrentMember,,LEAVES),
([Measures].[LastNonEmptyQty],
ClosingPeriod([Time].[Day]))))'
- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Deepak,

Thank for the reply. I've tried to use your members as follows:

WITH MEMBER [Measures].[ Tst] AS '
Sum(Descendants(Product.CurrentMember,,LEAVES),Sum (Descendants(Store.
CurrentMember,,LEAVES),([Measures].[LastNonEmptyQty],
ClosingPeriod([Time].[Day]))))', SOLVE_ORDER =99
SELECT { [Time].&[1997] } ON COLUMNS ,
HIERARCHIZE( DISTINCT( { [Product].[All Products], [Product].[All
Products].CHILDREN})) ON ROWS
FROM [Sales]
WHERE ( [Measures].[ Tst] )

This query returns nothing unfortunately.

Regards,
Sergei.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Deepak,

In my previous posting disregard and . I tried to put a piece of
that MDX in bold.

Sergei.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sergei,

I was able to get the following results from the query, though it took
about 30 seconds to return (since all the aggregation up both the
Product and Store hierarchies is done on-the-fly. A single Product
should go faster):

All Products | 65,120
Drink | 6,032
Food | 46,664
Non-Consumable | 12,424


Pl. note that I used ClosingPeriod([Time].[Month]),
since the Sales cube does not have a [Day] level.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Deepak,

It is pointless to continue as I did not explain the problem well enough
I think. If you still have the guts to crack the problem, please, give
me your e-mail and I forward you a detail description with some pictures
(I can't paste them here) that explain the problem.

Thanks,
Sergei.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!