Integrating Survey Data

Guys,

I have a Student Term FACT table which stores by term details on student enrollment such as college, level , degree and major.

I have another FACT for Survey which captures survey which is also by term however the grain is different because the survey table may have 20 questions for each student. however the student term fact table has only one record for each term for each student.

I need to produce a report which integrates the two which means I would like to see the survey results by college, level, degree and major of the student.

Whats the best way to integrate these. Should i modify my SURVEY fact table do have additional keys for campus, level , division etc which are read from the enrollment fact. Thanks for your feedback.

Hi,
all you need to do is query the two fact tables (and their associated dimensions) independently and then combine the two result sets based on their common attributes (student and term, presumably).
In a dimensional model you jon fact tables via the dimensions - which is one of the reasons why it is so important to have conformed dimensions in your design.
You definitely shouldn't be changing the design of fact tables to create these types of reports - in my opinion.

Regards,

Nick Thanks for your reply. Could you please give me a sense of what the joins would look like. I will give you the example below.

Fact Table A (STUDENT TERM) - Keys TERM_KEY AND STUDENT_KEY GRAIN- TERM AND STUDENT

Fact Table B (SURVEY) - Keys TERM_KEY AND STUDENT_KEY GRAIN - TERM, STUDENT AND SURVEY QUESTION

I have the following dimension tables STUDENT and TERM.

How do you think should be joined to get the desired results. Thanks.

Query your student/term fact table and associated dimensions to bring back all the attributes you want to report on - including the student and term keys
Query the survey fact table and associated dimensions for all the survey attributes you want - including the student and term keys.
Join the two recordsets based on the 2 keys.
How you actually do this depends on your BI tool or whether you are writing raw SQL. Lots of different ways to do it in SQL but the following pseudo-code is one possible way:

SELECT * FROM
(SELECT student_attributes FROM STUDENT_FACT) A,
(SELECT survey_attributes FROM SURVEY_FACT) B
WHERE A.STUDENT_KEY = B.STUDENT_KEY
AND A.TERM_KEY = B.TERM_KEY

There are probably much more performant SQL statements that will achieve the same result but you'll have to look at data volumes, indexing, filtering, etc. to decide how best to implement this.

Regards,