Hi

just getting back into Analysis Services again, after too long. (have
you any idea how hard it is to get contract work using MS OLAP in this
town (Edinburgh)? Everyone's into Oracle, Business Objects and Cognos.
Last time I used Analysis Services it was called OLAP services, in
1999).

.... anyway to the question in hand.

Here's what I'm imagining:

Two cubes, combined into a virtual cube.
The two cubes share, let's say, 10 dimensions.
Cube1 has 10 more dimensions, which Cube2 doesn't have.
In each of these 10 dimensions, there's a member called "[unknown]" -
in populating the fact table the ID of this member is substituted for
NULLs in the relevant source column.

What I'd like to do, for each of these 10 dimensions, which cube 1 has
but cube 2 doesn't:
a) Create a 1-member dimension on Cube2, containing only the member
"[unknown]"
b) somehow (and this is where the question is) get AS Server to treat
each of the "real" dimensions on Cube1 and its corresponding "1-member"
dimension on Cube 2 as a shared dimension.

Result: in the virtual cube, all the facts in Cube 2 are shunted into
the "[unknown]" member of each of the 10 "not really shared"
dimensions, instead of only appearing in the [ALL] level of the
dimension.
But, cube 2 doesn't have to have the 10 extra dimensions (well, it
does, but they're 1-member dimensions, so effectively don't add
anything to the space).

I'm sure it's possible to get this result with MDX (which I'm still not
very good at - partly due to the lack of a good Query-Analyzer-style
utility. I remember MDXBuilder, but it seems to have been taken over
and no longer be available trialware). I'd like to get this working
"seamlessly" in a virtual cube - partly because, whatever MDX I know,
there'll be no such knowledge here after I finish my contract.

Just realised that this virtual cube I'm talking about is in fact a
UNION cube, so I may be barking (or something) up the wrong lamppost
(don't have Enterprise Edition, so partitions are not possible). More
info:
- This is about answers to a survey
- Database has information about everyone who _might_ have answered the
survey: these are the 10 "really-shared" dimensions.
- Database also has information about the answers to the questions.
(the 10 other dimensions). Obviously, the people who didn't answer the
survey have [unknown] as the answer to every question.
- At present everyone is in one cube, whether or not they answered the
survey. This works fine, but it seems that those 10 dimensions about
the answers to questions are really wasted on the 30% of the total cube
population who didn't answer. So I thought of the possibility of
splitting this into two cubes.

I'd really like to get to know MDX properly this time around: can
anyone recommend a shareware/trialware MDX utility? With
documentation? Wishlist: proper syntax-checking, colour-coding of
code, like QA...
The bundled "MDX sample application" is OK, but not very helpful.

many thanks for any info

cheers


Seb