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_gradestores the average grade for the given combination.
- Draw a snowflake schema diagram for the data warehouse.
Answer: A snowflake schema is shown in Figure below
- 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 Big-University student.
- Roll-up on course from course_id to department.
- Roll-up on student from student_id to university.
- Dice on course, student with department =”CS” and university = “big-university”
- Drill-down on student from university to student_name.
- 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)?
Li = 5-1 =4
So, this cube will contain (Li+1)^4 = 625 cuboids