Hi all,
could someone tell me what is wrong with the following
MDX? It can be run as is against the sales cube in
Foodmart.

With
member [Measures].[# customers] as
'count(nonemptycrossjoin(Descendants
([Customers].currentmember,[Customers].[Name]),
{[Measures].[Unit Sales]}))'
select
{[measures].[Unit Sales],[Measures].[# customers]} on 0,
Filter([Customers].[City].members,not isnull([Measures].
[Unit Sales]) ) on 1
from Sales

Query is supposed to get the following result:
For each city in the customer dimension which has "Unit
Sales", get the "Unit Sales" and the number of customers
contributing to this value. That is, the number of
nonempty rows the crossjoin of the city's descendants
with Unit Sales generates.

expected result gets me:
City unit sales # customers
Altadena 212 84
....
Berkley 72 18
....

The real result gets me this:

City unit sales # customers
Altadena 212 4
....
Berkley 72 6
....

What went wrong? And what is the Analysis Server really
counting?

Thank you in advance.

Lutz

Lutz:

I ran your query using the MDX Sample and got results
like these:

Altadena 2.574 84
.....
Berkley 136 18

which seem to be correct. What am I missing?

Regards,

Brian
www.geocities.com/brianaltmann/olap.html

Brian, thanks for crosschecking.

Maybe my foodmart database needs to be reinstalled again.



The crossjoin inside the "Count"- Function returns the
correct number of tuples, but for some reason the "count"-
function itself returns "1" for a row with a positive
sale and "-1" for a row with negative unit sale.
That is where the incorrect count originates. It sums up
the positive and the negative values and ends up with a
lower result.

I still don't have a clue why this happens.
Any hints?

Lutz

"Solution":
I renamed "Foodmart 2000" to "Foodmart 2000 old" and
reinstalled Foodmart. The problem is gone in the "new
version".
I get a correct result set from newly installed foodmart.
However the old version still yields wrong results.
I compared the cube structure of "Sales old" and "Sales
new" - there is no difference.
I copied the old cube into the new database and the query
came up with correct results.
So the flaw is not in cube or dimension design, both use
the same connection - it is not in the source data either.

Spooky.

Lutz