I am trying to find the best way of solving the following problem:

I have inventory balances for a Product A as follows:


Month Qty on Hand
======= ===========
001.2004 120
004.2004 235
006.2004 89
011.2004 42
003.2005 980

======= ===========


Now if the user is trying to report all inventory balances as of
001.2005 even though I do not have a value in the cube for product A I
expect the system to go and pick the last know stock on hand which is
is 42 the known stock on hand qty in 011.2004

Please advice how I can implement these kind of non cumulative
exceptionally aggregated measures. Even if in MDX please explain how
to solve this.

Thanks
Karen

Hi Karen,

This MSDN paper discusses some semi-additive measures:

http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/semiadd2.asp

Amir Netz
Microsoft Corporation

Updated May 18, 2004

Applies to:
Microsoft SQL Server 2000
Microsoft SQL Server 2000 Analysis Services

Summary: Focusing on a classic inventory problem, this article describes
the implementation techniques of semiadditive measures in online
analytical processing.
...
A common real-world problem can arise during implementation of the
closing balance expressions. In many implementations, the Time dimension
is defined with future time period members already contained in the
dimension. The ClosingPeriod([Month]) function does not analyze future
and past time periods. It only traverses the members' hierarchy tree to
find the last leaf descendant under the given member.

For example, take the example of implementing a closing balance
expression with a current date of mid-October 1998. Asking for the
closing balance for 1998 arguably should provide the data from the last
snapshot of the year, or the October snapshot. However, the
ClosingPeriod([Month]) function returns December 1998 as the closing
period of 1998. Because no snapshot exists for December 1998, the
closing balances return NULL.

This more sophisticated expression solves the problem:

Measures.[Last Non Empty Value]:
IIf(IsEmpty((Measures.[Value], Time.CurrentMember) ,
(Measures.[Last Non Empty
Value],Time.CurrentMember.PrevMember),Measures.[Value])
Measures.[Closing Value]: (Measures.[Last Non Empty Value],
ClosingPeriod([Month]))

In this example, the [Last Non Empty Value] measure has a recursive
expression that checks to see whether the value of the current cell is
empty. If it is empty, the expression moves back to the previous period
on the time dimension and checks the [Last Non Empty Value] of the
previous period. The function continues to go back in time until a
nonempty value is found. The [Closing Value] measure can then use the
[Last Non Empty Value]. This recursive behavior helps ensure that, for
the last year, the values of the last snapshot are returned. The less
common CoalesceEmpty() function performs this exercise more efficiently.
This function is equivalent to the expression demonstrated earlier:

Measures.[Last Non Empty Value]:
CoalesceEmpty((Measures.[Value], Time.CurrentMember ) ,
(Measures.[Last Non Empty Value],Time.CurrentMember.PrevMember))
...

Please note that AS 2005 (Yukon) has built-in beginning and end balance
aggregations:

http://www.microsoft.com/technet/pro...uate/dwsqlsy.m
spx#EFAA


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***