Hi,
I have create a olap cube base on sales with dimensions Time (invoice data),
State, Product code and Customer, Quantity and Price as measures.
Now I need to get last invoice Price for the current set of the analisys
Ex.

Row Date State Product Quantity Price
1 01/01/2005 IL P1 1000 10.3
2 01/15/2005 NY P2 1500 10.2
3 01/31/2004 CA P1 1100 9.5
4 02/01/2005 IL P2 1000 11.2
5 02/15/2005 NY P1 1500 11.4
6 02/28/2004 CA P1 1100 10
7 03/01/2005 IL P1 1000 9.8
8 03/15/2005 NY P1 1500 10
9 03/31/2004 CA P2 1100 9.9

If the current set of analisys is set on Year, State & Product I need this
result:

Year State Product Price Row
2005 IL P1 9.8 7
2005 NY P1 10 8
2005 CA P1 10 6
2005 IL P2 11.2 4
2005 NY P2 10.2 2
2005 CA P2 9.9 9

If the current set of analisys is set on Year, State I need this result:

Year State Price Row
2005 IL 9.8 7
2005 NY 10 8
2005 CA 9.9 9

If the current set of analisys is set on Month, Product I need this result:

Month Product Price Row
1 P1 9.5 3
1 P2 10.2 2
2 P1 10 6
2 P2 11.2 4
3 P1 10 8
3 P2 9.9 9

If someone have an idea to make this please replay to this post.

Thanks in advance

Simon.

Based on the examples, this looks like a "Closing Balance" scenario, ie.
the latest record for a given combination of dimensions. This MSDN paper
discusses some techniques:

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))
...
This approach can be applied along your [Date] dimension, but if there
are multiple rows at the latest date for a given combination of
dimension members, then a measure like Price might need to be averaged.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote