Please help me to write CM on FoodMart 2000, Sales cube, to find
Customer who buy the current product latter.

Could you explain further, perhaps with an example, what you mean by
"who buy the current product latter" - is it that they buy at a Time
later than the selected Time?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I want to find a customer who made the latest purchase (bought) of the
current product in selected Time period. For example, I want that CM
return Sergey, if the current Time member is 2004, and the Milk
(current product) was purchased in 2004, November, 25 by Ivan and
another purchase was made in 2004, December, 13 by Sergey. Or another
question is if I find Time dimension member when the latest purchase
was made (for this example is 2004, December, 13) how I can get the
Customer dimension member from the tuple, constructed of members: Time
- 2004, December, 13 and Measure - Unit sales (as a criteria of
purchase) and what another parts of this tuple must be to find this?

Keeping in mind that a CM can't return the tuple itself, here's a
Foodmart example that returns the Customer Name:

'Extract(Tail(NonEmptyCrossJoin(Descendants(Time.C urrentMember,,LEAVES),
Descendants(Customers.CurrentMember,,LEAVES),
{Product.CurrentMember}, {[Measures].[Unit Sales]}, 2)),
Customers).Item(0).Item(0).Name'

Select {[Measures].[Unit Sales],
[Measures].[LastCustomer]} on columns,
{[Time].[1997].[Q3]} on rows
from Sales
where ([Customers].[All Customers].[USA].[OR].[Salem],
[Product].[All Products].[Drink].[Dairy].[Dairy].[Milk])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Ok, this a one of possible implementations, but this is very slowly on
a large dimensions. What about another decisions?

Why do you say that this is slow - NonEmptyCrossJoin() normally works
faster than Filter(), since every member value doesn't have to be
explicitly tested:

http://www.windowsitpro.com/Windows/...00/pg/1/1.html
I was recently involved in a customer deployment in which the
NONEMPTYCROSSJOIN() function changed a query's performance from 15
minutes to about 3 seconds.
...
Because cubes are generally sparse, Analysis Services stores only the
non-null values; and when the client-side driver, PivotTable Service,
requests data from the server, it receives only non-null blocks of cube
data. NONEMPTYCROSSJOIN() takes advantage of this method of storing and
communicating data in Analysis Services.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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