Suppose that a data warehouse for Big-University consists of the following four dimensions: student, course, semester, and instructor, and two measures count and avg_grade. When at the lowest conceptual level (e.g., for a given student, course, semester, and instructor combination), the avg_grade measure stores the actual course grade of the student. At higher conceptual levels, avg_grade stores the average grade for the given combination. a) Draw a snowflake schema diagram for the data warehouse. b) Starting with the base cuboid [student, course, semester, instructor], what specific OLAP operations (e.g., roll-up from semester to year) should one perform in order to list the average grade of CS courses for each BigUniversity student. c) If each dimension has five levels (including all), such as “student < major < status < university < all”, how many cuboids will this cube contain (including the base and apex cuboids)?
a) A Snowflake Shema is shown in figure below:
b) The specific OLAP operations to be performed:
1. Roll-up on course from course_id to department.
2. Roll-up on a student from student_id to university.
3. Dice on course, student with department ="CS" and university = "biguniversity"
4. Drill-down on a student from the university to student_name.
c) N = 4 dimensions
The cube will contain 54 = 625 cuboids.