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.

Dimensions

• Student
• Address
• Qualification
• Score
• Work
• Financials
• Program
• Hostel

Measures

• % of accepted offers
• Average work experience of each batch
• Major industry type of each batch
• % of students availing hostel facility’

Secondary Information

• No 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 Number
• Temporary Address
• Hostel facility
• No of beds (Single room/Double room)
• Type of room (A/C, Non A/C)
• Food Preference
• Bank Name
• Bank Account Number
• No of electives

Junk 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.