Schema for a Data Warehouse for Admission Analysis At
Schema for a Data Warehouse for Admission Analysis At
Schema for a Data warehouse for admission analysis at The data warehouse for analysis of admissions at GLIM, Gurgaon for the year 2014-15 would be based on a star schema architecture where the fact table would consist of dimensions including primary student details, address, qualification, work experience for each of the programs offered. Certain measures such as percentage of accepted offers, major industry type, and average work experience of every batch would be computed. Secondary information pertaining to certain dimensions will be provided like the number of students from different regions to specify the diversity of the batch. Each of the dimensions will further consist of relevant attributes such as first name, last name, Contact details etc. for Student details dimension.
DimensionsStudent AddressQualificationScoreWorkFinancialsProgram HostelMeasures% of accepted offersAverage work experience of each batchMajor industry type of each batch% of students availing hostel facility’Secondary InformationNo of students by region (States, North, South, East, West Regions)No of students from specific industry (Energy background, IT etc.)Slowly changing variables include:Contact NumberTemporary AddressHostel facilityNo of beds (Single room/Double room)Type of room (A/C, Non A/C)Food PreferenceBank NameBank Account NumberNo of electivesJunk variables include:Full Name ( Combination of first and last name)Roll No (Combination of program id and self-generated id)Official email id (Combination of name, program id and year)The schema is as shown in figure below:[pic 1]The primary key of each dimension will be the foreign key to the fact table. For instance, Student ID, Program ID, Address ID, Qualification ID, Hostel ID, Job ID, Score ID will be foreign keys for Full Name, Program Name, Permanent Address, Qualification, Bank details etc. in the fact table. Other details will be computed based on queries linking data of two or more dimensions.