Hello,
I've a problem writing a MDX statement. The statement should sum up the
turnover for a customer only if the order contains at least two items
with different promotion types.

In SQL, this statement is rather easy. Using the Adventure Works DW
database the SQL statement would be something like

select f1.customerkey, sum(extendedamount)
from factinternetsales f1
where exists (
select *
from factinternetsales f2
where f1.salesordernumber = f2.salesordernumber
and f1.promotionkey <> f2.promotionkey
)
group by f1.customerkey

But how to tanslate the exists statement in MDX using the Adventure
Works cube? Please let me know any suggestions.
thanks

Here's an equivalent MDX query, based on filtering the [Internet Sales
Order Details] fact dimension. Unfortunately, performance is poor (3+
hours on my server), since the benefits of OLAP pre-aggregation are
lost.

If you knew ahead of time that you needed to slice data by number of
promotions per sales order, a dimension or attribute could be created
and performance would be better.

Non Empty [Customer].[Customer].[Customer].Members on 1
from [Adventure Works]
where Filter([Internet Sales Order Details].[Internet Sales
Orders].[Order Number].Members,
Exists([Promotion].[Promotion].[Promotion].Members,, "Internet
Sales").Count > 1)


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hello Deepak,
that's the statement I was looking for. I'll consider introducing a new
dimension.
Thanks,
Christian