Integrating Survey Data

View previous topic View next topic Go down

Integrating Survey Data

Post  hunain on Wed Feb 26, 2014 11:41 pm

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.

hunain

Posts : 19
Join date : 2013-09-15

View user profile

Back to top Go down

Re: Integrating Survey Data

Post  nick_white on Thu Feb 27, 2014 4:31 am

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_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Integrating Survey Data

Post  hunain on Thu Feb 27, 2014 4:53 am

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.

hunain

Posts : 19
Join date : 2013-09-15

View user profile

Back to top Go down

Re: Integrating Survey Data

Post  nick_white on Thu Feb 27, 2014 5:56 am

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,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Integrating Survey Data

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum