ADVANCED DATA WAREHOUSE AND DATA MINING SECOND LECTURE(1).pptx

vcv

1. CSC612: ADVANCED DATA WAREHOUSE AND DATA MINING SECOND LECTURE DELIVERED BY DR. FASHOTO DEPARTMENT OF COMPUTER SCIENCE

2. OLAP OPERATIONS There are five basic analytical operations that can be performed on an OLAP cube Drill down Roll up Dice Slice Pivot

3. Drill Down In drill-down operation, the less detailed data is converted into highly detailed data.  It can be done by : Moving down in the concept hierarchy Adding a new dimension In the cube given in overview section, the drill down operation is performed by moving down in the concept hierarchy of  Time  dimension (Quarter -> Month).

4. Overview section

5. Roll Up It is just opposite of the drill-down operation. It performs aggregation on the OLAP cube . It can be done by : Climbing up in the concept hierarchy Reducing the dimensions In the cube given in the overview section, the roll-up operation is performed by climbing up in the concept hierarchy of  Location  dimension (City -> Country).

6. Overview section

7. Dice It selects a sub-cube from the OLAP cube by selecting two or more dimensions . In the cube given in the overview section, a sub-cube is selected by selecting following dimensions with criteria : Location = “Delhi” or “Kolkata” Time = “Q1” or “Q2” Item = “Car” or “Bus”

8. Overview section

9. Slice It selects a single dimension from the OLAP cube which results in a new sub-cube creation . In the cube given in the overview section, Slice is performed on the dimension Time = “Q1”.

10. Overview section

11. Pivot It is also known as  rotation  operation as it rotates the current view to get a new view of the representation . In the sub-cube obtained after the slice operation, performing pivot operation gives a new view of it.

12. Overview section

13. How to CREATE OLAP USING MYSQL? -- Create a Student Dimension TableCREATE TABLE Student ( StudentID INT PRIMARY KEY, StudentName VARCHAR(50), DateOfBirth DATE, Gender VARCHAR(10 ) );

14. -- Create Instructor Dimension Table CREATE TABLE Instructor ( InstructorID INT PRIMARY KEY, InstructorName VARCHAR(50), Department VARCHAR(20) );

15. -- Create Course Dimension Table CREATE TABLE Course ( CourseID INT PRIMARY KEY, CourseName VARCHAR(50), Department VARCHAR(20), InstructorID INT, CreditHours INT );

16. -- Create Course Session Dimension Table CREATE TABLE CourseSession ( CourseSessionID INT PRIMARY KEY, CourseID INT, StartDate DATE, EndDate DATE, Location VARCHAR(10) );

17. -- Create Enrollment Fact Table CREATE TABLE EnrollmentFact ( EnrollmentID INT PRIMARY KEY, StudentID INT, CourseID INT, CourseSessionID INT, EnrollmentDate DATE, EnrollmentStatus VARCHAR(20) );

18. -- Create Performance Fact Table CREATE TABLE PerformanceFact ( PerformanceID INT PRIMARY KEY, EnrollmentID INT, Grade VARCHAR(2), CreditHours INT, Attendance DECIMAL(5, 2) );

19. INSERT -- Insert data into Student Dimension Table INSERT INTO Student ( StudentID , StudentName , DateOfBirth , Gender) VALUES (1, 'John Doe', '1990-05-15', 'Male'), (2, 'Jane Smith', '1992-08-20', 'Female'), (3, 'David Johnson', '1991-03-10', 'Male'), (4, 'Emily Brown', '1993-12-05', 'Female'), (5, 'Michael Wilson', '1989-07-25', 'Male');

20. -- Insert data into Instructor Dimension Table INSERT INTO Instructor ( InstructorID , InstructorName , Department) VALUES (1, ' Dr. Smith', 'Mathematics'), (2, 'Prof. Johnson', 'Physics'), (3, ' Dr. Anderson', 'Computer Science'), (4, 'Prof. Davis', 'History'), (5, ' Dr. White', 'Biology');

21. -- Insert data into Course Dimension Table INSERT INTO Course ( CourseID , CourseName , Department, InstructorID , CreditHours ) VALUES (101, 'Calculus I', 'Mathematics', 1, 4), (102, 'Introduction to Physics', 'Physics', 2, 3), (103, 'Database Management', 'Computer Science', 3, 3), (104, 'World History', 'History', 4, 3), (105, 'Biology Basics', 'Biology', 5, 4);

22. -- Insert data into Course Session Dimension Table INSERT INTO CourseSession ( CourseSessionID , CourseID , StartDate , EndDate , Location) VALUES (201, 101, '2023-09-05', '2023-12-15', 'Room 101'), (202, 102, '2023-09-10', '2023-12-20', 'Room 102'), (203, 103, '2023-09-07', '2023-12-18', 'Online'), (204, 104, '2023-09-08', '2023-12-17', 'Room 103'), (205, 105, '2023-09-12', '2023-12-22', 'Room 104');

23. -- Insert data into Enrollment Fact Table INSERT INTO EnrollmentFact ( EnrollmentID , StudentID , CourseID , CourseSessionID , EnrollmentDate , EnrollmentStatus ) VALUES (301, 1, 101, 201, '2023-09-06', 'Active'), (302, 2, 101, 201, '2023-09-06', 'Active'), (303, 3, 102, 202, '2023-09-11', 'Active'), (304, 4, 103, 203, '2023-09-08', 'Active'), (305, 5, 104, 204, '2023-09-10', 'Active');

24. -- Insert data into Performance Fact Table INSERT INTO PerformanceFact ( PerformanceID , EnrollmentID , Grade, CreditHours , Attendance) VALUES (401, 301, 'A', 4, 95.5), (402, 302, 'B', 4, 92.0), (403, 303, 'A-', 3, 96.5), (404, 304, 'B+', 3, 91.0), (405, 305, 'A', 3, 94.5);

25. SELECT -- SELECT * FROM Student; -- SELECT * FROM Instructor; -- SELECT * FROM Course; -- SELECT * FROM CourseSession ; -- SELECT * FROM EnrollmentFact ; -- SELECT * FROM PerformanceFact ;  

26. DEMO ON SLICE -- SELECT CourseName , InstructorID , CreditHours -- FROM Course -- WHERE Department = 'Mathematics';

27. DEMO ON DICE -- SELECT CourseName , CreditHours -- FROM Course -- WHERE Department = 'Mathematics' AND InstructorID = 1;

28. DEMO ON ROLL UP -- SELECT Department, SUM( CreditHours ) AS TotalCreditHours -- FROM Course -- GROUP BY Department;

29. OR Select department, sum( credithours ) as totalcredithours from course group by department with ROLLUP;

30. OR Select department, sum( credithours ) as totalcredithours from course group by ROLLUP(department);

31. DEMO ON ROLL DOWN -- Roll-down -- SELECT CourseName , Department, InstructorID , CreditHours -- FROM Course -- WHERE CourseName = 'Calculus I';

32. DEMO ON PIVOT -- Pivot -- SELECT EnrollmentID , MAX(CASE WHEN Grade = 'A' THEN CreditHours END) AS A_CreditHours , -- MAX(CASE WHEN Grade = 'A' THEN Attendance END) AS A_Attendance , -- MAX(CASE WHEN Grade = 'B' THEN CreditHours END) AS B_CreditHours , -- MAX(CASE WHEN Grade = 'B' THEN Attendance END) AS B_Attendance -- FROM PerformanceFact -- GROUP BY EnrollmentID ;