I am a new to MDX so forgive me if I am asking a simple thing. I am
converting a query from existing application that used SQL to do reporting
to use Analysis Services 2005.

I have a database of various information about a customers. Let's say I have
multiple fact tables about those customers: Fact1, Fact2 and etc. Each fact
table has a different set of dimensions. I need to create a report based on
the particular fact table, but need to be able to filter the customers that
appear on that report based on the data from other table. So in SQL I would
do something like

select F1.c1,F1.c2,count(distinct F1.customer_id)
from F1 join (select distinct F2.customer_id from F2 where F2.c3=768 and
F2.c4= 235 ) CL1 on F1.customer_id=CL1.customer_id
join (select distinct F2.customer_id from F2 where F2.c4=434
and F2.c7= 343 ) CL2 on F1.customer_id=CL2.customer_id
where F1.c5=123
group by F1.c1,F1.c2

1. Is it possible to do something like that with MDX?
2. If it is possible, and I think it should be how that could be done?

My own thoughts:

In MDX I can't do that join so I have to create a cube that has all that
data from fact tables. I created a measure group for each fact table and put
a DistinctCount measure in them for customer_id. I also created a measure
group for Customers and put a count of rows measure. I also established many
to many relationships between all dimensions through the appropriate measure
groups. What is not clear to me is how to provide the filtering of
customers based on the other fact table ?

I tried running following statement but results are not what I would expect.


select non empty [D1].[Name].MEMBERS on 0, non empty [D2].[Name].MEMBERS on
1, Measures.Customers.Cnt on 2
from [MyCube]
where ([D1].[c5].&[123],
INTERSECT(EXISTS([Customer].MEMBERS,([P3].MEMBERS.&[768],[P4].MEMBERS.&[235])
,'F2'),
EXISTS([Customer].MEMBERS,([P4].MEMBERS.&[434],[P7].MEMBERS.&[343]),'F2'))


Thanks.

Hi Boris,

the cube structure might be worth a closer look. I would need more detailed
information on this.

But based on what I understood from it so far, you should try to use the
dimensionality of your cube for filtering instead of the the WHERE clause.

In general you use the axes fo filter what you want to see and use the WHERE
clause only to set single members other than the default members in those
dimensions that you don't want to put on the axes, but want to keep in the
background. There are more options in AS2005, but this is still the basic
concept.

This means: In your MDX statement try to shift the WHERE conditions onto the
Axes, so that you get only the values that you want, instead of getting all
members and then trying to filter them.

Put the D1 members on 0 and crossjoin them with the customers you want to
see.
Put the D2 members on 1.
If you want to see just 1 measure, put this in the WHERE clause.
If you want to see the data just for 1 single customer, you put him in the
WHER clause.

But if you want to see values for several customers separately you need to
put them on one of the axes and in this case crossjoin them with the set
that is already there.

HTH,
Helmut


"Boris Gertsberg" <bgertsberg (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:ODG6gD%238GHA.3256 (AT) TK2MSFTNGP04 (DOT) phx.gbl...