Soryr if this has been posted before but I have tried searching (my
Google searching skills aren't the best) with no joy...

Anyway - Analysis Server 2000 problem.

I have a cube with multiple dimensions, two of those dimensions are
incoming site and outgoing site (Source and Dest)....both source and
destination are based on the same dimenstion table...

Now I have a problem in that the users want to be able to query where
the [Source] OR the [Destination] are a specific place.

The MDX that I have at the moment is :-

select
{ [Time_W].[All Time_W].[2006].[Week 13] } on rows,
{ [Measures].[TotCallsAllTypes] }
on
columns from Call
where ([Destination].[All Destination].[High Street], [Source].[All
Source].[High Street])

which returns the INTERSECTION of the Source and Destination
(correct??)

what I want is the UNION of the Source and the Destination (i.e. OR in
SQL parlance)

Now, without messing with the cube structure, is there an easy way to
do this without writing 3 queries (source + dest - intersection)

Thanks in advance

Bob

This would be a little easier in AS2005 as you can put sets directly
into the WHERE clause, but its still possible in AS2000.

What you want is the set of 2 tuples
1) all "sources" for a specific destination:

([Destination].[All Destination].[High Street], [Source].[All
Source])

and 2) all "destinations" for a specific 'source"

([Destination].[All Destination], [Source].[All
Source].[High Street])

And then you can get the sum of your measure across the set of those two
tuples:

Member Measures.[TotCallsAllTypesForLocation] as 'SUM({([Destination].
[All Destination].[High Street], [Source].[All
Source]),([Destination].[All Destination], [Source].[All
Source].[High Street])}, [Measures].[TotCallsAllTypes])'
SELECT
{ [Time_W].[All Time_W].[2006].[Week 13] } on rows,
{ [Measures].[TotCallsAllTypesForLocation] }
on columns
FROM Call
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1144050922.835160.50180 (AT) e56g2000cwe (DOT) googlegroups.com>,
bobjbain (AT) gmail (DOT) com says...

Darren,

Thanks for that but it is still giving me the 'wrong' answer, prehaps I
got my terminology wrong, in fact I probably did.

What we have is the following scenarion

Incoming site ID Outgoing Site ID Num Calls
1 1 1
1 2 1
3 1 1

What your MDX would return for the number of calls is 4 (is that the
UNION??)

What I would like to see is for the number of calls is 3 (this is the
OR function in SQL parlance)

Any further help would be greatly appreciated.

Bob Bain

Sorry about that Bob.

Not quite a union - I think it will be double counting the tuples where
the incoming and outgoing sites both meet the filter.

I am not able to test the following query and it depends a bit on what
the structure of your source and destination dimensions are as to
whether it will work. I have pretty much assumed that the source and
destination dimensions only have a single level.

I have then created two sets:

Set 1) is the selected destination and all sources.

Set 2) is the selected source and all destinations.

I think the summing of the union 2 sets will give the result you are
after. By using the UNION MDX function, duplicated tuples will be
removed so that only the unique tuples remain.

Member Measures.[TotCallsAllTypesForLocation] as '
SUM( UNION(
Crossjoin({[Destination].[All Destination].[High Street]}
, {[Source].[All Source].Children})
,
Crossjoin([Destination].[All Destination].Children}
, {[Source].[All Source].[High Street]})
)
, Member Measures.[TotCallsAllTypesForLocation])'
SELECT
{ [Time_W].[All Time_W].[2006].[Week 13] } on rows,
{ [Measures].[TotCallsAllTypesForLocation] }
on columns
FROM Call
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1144136574.255582.319980 (AT) e56g2000cwe (DOT) googlegroups.com>,
bobjbain (AT) gmail (DOT) com says...

Darren,

Thanks for that, I thought it would be use of Crossjoins and others but
just couldn't get my damn Syntax correct..

It's all great now

Cheers

Bob