We have a problem in drilling into an OLAP cube using Excel that I think it
related to the way that Excel either (a) creates the query, or (b) handles
the data returned. Any help would be appreciated.

The cube looks something like this: We have certain products that have
SALES figures (i.e., a SALES measure). The SALES data is in the SALES cube.
We want to compare them to a POPULATION measure that doesn't relate to the
products; this data is in the POPULATION cube.. The two cubes are combined
in a virtual cube. In the virtual cube, when the PRODUCT dimensionis used
in a pivot table, the POPULATION would disappear, since PRODUCT is not part
of the POPULATION cube.

To cure this, we created a new POPULATION measure that's defined as
[measures].[population],ancestor ([product].currentmember,[product].all]).
This forces the population to be the same for all products.

The problem occurs when we set up a pivot table to filter only certain
products, by selecting one or more of them from the pivot table dropdown box
for this dimension. If we select all products, we get a list of all
products, with the population the same for each product (which is correct).
However, if we select any combination of products that's less than the full
list, the POPULATION measure disappears from the pivot table.

The Pivot Table Service appears to code any selected dimension measures
using an EXCEPT MDX function, "excepting" the non-selected measures.
Copying the MDX created by this query into the MDX Sample Application works
fine.

Similarly, using the Cube Analysis add-in also works, but generates entirely
different MDX code; it uses a UNION function between the PRODUCTS that were
included, rather than an EXCEPT function between those products that were
not.

This appears to be a problem in how Excel handles the data returned by OLAP
services, since the same MDX query works correctly in the MDX Sample
Application. Is there any work-around? We would like to make this work.

Thanks

Jon Pearce, jpearce at the domain below
Director, Business Intelligence
DGA Partners, Inc dgapartners.com

Will 'ValidMeasure([measures].[population])' work?

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agmdxfunctions_76cl.asp

Returns a valid measure in a virtual cube by forcing inapplicable
dimensions to their top level.

Syntax

ValidMeasure(«Tuple»)

Returns a valid measure from a tuple in a virtual cube specified in
«Tuple».

Remarks

When computing values in a virtual cube, measure cells only contain
values at the (All) level for dimensions that are not common between the
underlying cubes. The ValidMeasure function returns the measure value
from the cell at the (All) level coordinates for the dimensions that are
not common.
...

- Deepak

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

Yes, that solved the problem. Thanks a lot.

Jon

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