Data Warehouse 1 Course Syllabus

This course introduces the fundamentals of data warehousing, covering key concepts, architectures, and practical implementations using open-source tools. The course is designed for master’s students with diverse backgrounds and prepares them for an advanced Data Warehouse course.

Course Structure

• 7 weeks of classes (3 hours each)
• 1 final exam session (3 hours)
• Combination of lectures, in-class exercises, quizzes, and computer labs (starting from Week 3)

Weekly Schedule

Week 1: Introduction to DBMS and Data Warehouses

• Challenges for DBs
• Brief history of DBs
• Founding principles of DBMS
• Data Warehouse motivations and definitions
• OLTP vs OLAP
• DW industrial landscape
• In-class exercises: Identifying OLTP vs OLAP scenarios

Materials:

• slides
• PDF
• DBMS principles
• OLTP vs OLAP

Week 2: Data Warehouse Concepts and Relational Schemas

• Multi-dimensional model
• DW architecture and components
• Data model: facts, measures, dimensions, cube
• Multi-dimensional queries: OLAP operations
• Relational schemas for DWHs
• Dimensional modeling
• Fact tables and dimension hierarchies

Materials:

• slides
• PDF

Week 3: SQL 101

• Quiz on Week 1 and Week 2 material (first 30 minutes)
• SQL review with focus on DW-specific queries
• Advanced SQL techniques for data warehousing

Materials:

• slides
• PDF

Week 4: SQL for Data Warehouses

• Avanced SQL course
• Lab: Advanced SQL

Materials:

• slides
• PDF
• lab
• db
• SQL correction, Snowflake correction

Week 5: SQL for Data Warehouses (continued)

• lab

Week 6: Indexing

• Quiz on Week 3 to Week 5 material (first 30 minutes)
• Indexing techniques: B-trees, Bitmap Indexes, Bitmap Join Indexes (course and lab)
  • slides
  • PDF
  • lab
  • glossary

Week 7: Graded Lab

• Graded Lab
  • m1isd-oct24-nom-prenom-reponses.sql
  • Original database file

Week 8: Final Exam

• Comprehensive exam covering all course topics (3 hours)

Assessment

• Graded quizzes (2 or 3 quizzes, total 20%)
• Lab reports (30%)
• Final exam (50%)

Required Tools (Open-source and Free)

• PostgreSQL for relational database examples
• Python with NumPy and Pandas (a recent version)
• MongoDB for NoSQL examples
• Additional open-source tools to be announced for specific labs