Hi all -
I need to have multiple fact tables in a single cube. Let me give you
an example situation -

Say I am working with data from a school. I have dimensions - grade,
studentRace, studentGender, etc.. and a simple count measures in a
studentFact table. I also have a teacherFact table with dimensions like
teacherMajor, teacherExperience, etc. How do I bridge between the
students and the teachers? I have a 'class' table, with columns like
studentID, school, grade, courseID, teacherID, ...... so on. Problem
is, I cannot use simple aggregates since there are duplicates (same
student would attend multiple classes, same teacher teaches multiple
classes). So I don't think I can join using a ClassFact table.

Then, I would think that I need a studentFact and a teacherFact table
with different dimensions - I have no idea how that would work. I read
somewhere that Analysis Server 2005 supports multiple fact tables in a
single cube.

Question -
1. Is this supported in Sql Server 2005 standard edition (that's what I
just installed).
2. Do you know of any resources where I can find some documentation on
how to do this?
3. Reading the summary of the problem above, do you have any
suggesions? Please let me know if you want more details about the
problem.

Thanks all. Please help!

Partha

with AS2005 you have a new feature called "many to many" which can help you.

you have to create a cube based on your "class" fact table, and you have to
create a measure like "nb of assignations" (a simple count)

now you add 2 more measure group (1 for the students, 1 for the teachers)
and then use the many-many relationship when you link you dimensions in the
cube.
for example, linking the teacher dimension to the studentfact measure group
go through the "assignations" measure group.

what are your measures? do you use distinct measures?
a distinct count will count only 1 time the student (or the teacher).
a distinct count measure works only on integer values (varchar not
supported)
you can only have 1 distinct count by measure group, but you can setup more
then 1 measure group in 1 cube, so there is no issue ;-)

the classfact could answer many questions:
* how many assignations I have? (count)
* how many students are assigned in classes? (distinct count)
* how many teachers are assigned to classes? (distinct count)
* what is the total length of my assignations? (sum assignation duration in
days)
etc...


"Partha" <alaaade (AT) gmail (DOT) com> wrote

If you are looking for some more information on Many-to-Many dimensions,
there is a tutorial in the documentation and tutorials at:

Analysis Services Tutorials
- SQL Server 2005 Analysis Services Tutorial
- Lesson 5: Defining Relationships Between Dimensions and Measure Grps

There are also examples of multiple fact tables and many-to-many
dimensions in the Adventure Works sample database. I don't think this is
installed by default so you may need to stick your install disk back in
and find the option to add the sample adventure works databases. (Note:
there are 3 sample databases 2 relational ones and an Analysis Services
one.

In the Adventure Works OLAP Database the Internet Sales measure group
contains 2 examples of many-to-many dimension relationships in it
relationship to the "Sales Reason" and "Exchange Rates" dimensions.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <Ok4rob$MGHA.2336 (AT) TK2MSFTNGP12 (DOT) phx.gbl>,
willgart_A_ (AT) hotmail_A_ (DOT) com says...