How to Add Dimensions in Fact table in case there is no matching column to sort and use merge join,what to do in this scenario? like i have table student_history_fact(registrationNo,semestre,gpa) amd department_dim(department)...i have to make student_fact(registrationNo,semestre,course,deprtment,cgpa) etcmhassanshahbaz

You got to have information that which student belongs to which Department. If you don't have Relationships there is no way one can create a fact dimension schema. see if you have any other table Or say intermediate table by which you can link this two. Gaurav Gupta http://sqlservermsbiblog.blogspot.com/

If you have assumption that all the students belongs to all the departments. you can do a cross join, but in this cse result case will be (Fact_No_Of_Rows * Dimnesion_No_Of_Rows)Gaurav Gupta http://sqlservermsbiblog.blogspot.com/

this is my schema ,i have created all tables,student_fact remainning mhassanshahbaz

this is my schema ,i have created all tables,student_fact remainning mhassanshahbaz

i created student_history_fact from student_course_fact,now i have to create student_fact from student_history_fact to compute cgpa etc,but also i have to load foreign keys for course from course_dim,department from department_dim ,campus from campus_dim hope it make you clearmhassanshahbaz

I'm not clear about what do you mean, do you want to fill data into student_history_fact table based on joining dimension tables like course_dim, department_dim ,... with another fact table student_fact ? if yes, you can do this with merge join transform or with lookup, with merge join you should sort inputs before connecting to merge join, let us know what you want to do exactly and where you have problem?http://www.rad.pasfu.com

I want to make student_fact from student_history_fact ,so Cgpa will be calculated by gpa(sum)/count(semester).Look at the pic. CGPA done...now comes the point how to load campus_dim,course_dim,department_dim and semester_dim into student_fact tablemhassanshahbaz

As I see there is no Course field in student_history_fact, how you want to fetch them ? you need to fetch data from a table which has course inside, something like student_course_fact tablehttp://www.rad.pasfu.com

ahaan it means for i have to use student_course_fact table(semester,course,registration), student_history_fact(for gpa) & then join with student_dim (for department and campus)....am i right?mhassanshahbaz

yes , I mean that exactly note that you should SORT inputs before merge join and each merge join only accept two input, so you need 3 merge join transform http://www.rad.pasfu.com

also note that It is better if you don't use SORT Transformation because performance issue, use this way to SORT your inputs: http://www.ssistalk.com/2009/09/17/ssis-avoiding-the-sort-components/http://www.rad.pasfu.com