INFO-H-419: Data Warehouses 2012

• Toon Calders

• Theory 24 h - Exercises 24h - Project
• 5 ECTS credits

• Master in Computer Science and Engineering [MA-IRIF]
• Master in Computer Sciences [INFO]
• Erasmus Mundus Master in Information Technologies for Business Intelligence (IT4BI)

Old exams of my Advanced Databases course at the TU/e; please note that only the questions about data warehousing are relevant.

Solution to the January 2013 exam

Relational and object-oriented databases are mainly suited for operational settings in which there are many small transactions querying and writing to the database. Consistency of the database (in the presence of potentially conflicting transactions) is of utmost importance. Much different is the situation in analytical processing where historical data is analyzed and aggregated in many different ways. Such queries differ significantly from the typical transactional queries in the relational model:

• Typically analytical queries touch a larger part of the database and last longer than the transactional queries;
• Analytical queries involve aggregations (min, max, avg, …) over large subgroups of the data;
• When analyzing data it is convenient to see it as multi-dimensional.

For these reasons, data to be analyzed is typically collected into a data warehouse with Online Analytical Processing support. Online here refers to the fact that the answers to the queries should not take too long to be computed. Collecting the data is often referred to as Extract-Transform-Load (ELT). The data in the data warehouse needs to be organized in a way to enable the analytical queries to be executed efficiently. For the relational model star and snowflake schemes are popular designs. Next to OLAP on top of a relational database (ROLAP), also native OLAP solutions based on multidimensional structures (MOLAP) exist. In order to further improve query answering efficiency, some query results can already be materialized in the database, and new indexing techniques have been developped.

In the course, the main concepts of multidimensional databases will be covered and illustrated using the SQL Server tools. Complimentary to the course, IBM offers a “proof of technology” session to illustrate a business perspective and alternative tools.

• Multidimensional Databases and Data Warehousing by Cristian S. Jensen, Torben Bach Pedersen, and Christian Thomsen. Morgan & Claypool Publishers.
• Kimball, Ralph; Margy Ross, Warren Thornthwaite, Joy Mundy, Bob Becker (2008). The Data Warehouse Lifecycle Toolkit (2nd ed.). Wiley.
• White and Research Papers
  • Two survey papers: paper 1, paper 2
• Slides (via detailed schedule)

The following books have been used to construct the course material, but are not required reading for the course:

• Data Warehouse Design: Modern Principles and Methodologies, Golfarelli and Rizzi, McGraw-Hill, 2009
• Advanced Data Warehouse Design: From Conventional to Spatial and Temporal Applications, Elzbieta Malinowski, Esteban Zimányi, Springer, 2008
• The Data Warehouse Toolkit, 2nd Ed., Kimball and Ross, Wiley, 2002
• Building the Data Warehouse. 4th edition. Inmon, Wiley, 2005
• Data Warehousing Fundamentals For IT Professionals. 2nd edition. Paulraj Ponniah, Wiley, 2010

• Teradata software http://www.teradatauniversitynetwork.com/tun/

The 2012-2013 academic year student password is 'Analytics'.

• “there is a Netezza simulator available for all on the IBM developers website. It really behaves like a real Netezza in its commands and functions but obviously it only has a fraction of the performance of the real thing.” (Alex Degrave)

This schedule is as detailed as possible at this moment; it may be subject to change. Changes will clearly be communicated to the students through this page, and during the lectures.

The lectures take place on Thursday afternoon: * Thu. 2-4: Theory in S.C4.219 * Thu. 4-6: Exercises in S.C4.219

Lectures are on Wednesday:

• Wed. 10-12: Theory in S.R42.5.107 (W6) and S.P1.3.206 (W8 and following)
• Wed 13-15: Exercises in S.J.1.104

• W1: 20/9 T: Introduction to the course INFOH419 Data Warehousing - Week 1
• W2: 27/9 No lecture; university is closed (Fête de la Communauté Française)
• W3: 4/10 T+E: SQL extensions (group by cube, roll-up; ranking), the MDX query language INFOH419 Data Warehousing - Week 3
• W4: 11/10 T+E: Theory: ROLAP (Star & Snowflake scheme), MOLAP; Materialized views INFOH419 Data Warehousing - Week 4
• W5: 18/10 T+E: Materialized views continued, indexing techniques for data warehousing. Pen and paper exercises. INFOH419 Data Warehousing - Week 5
• W6: WED 24/10: T+E: Extract-Transform-Load; ETL workflow INFOH419 Data Warehousing - Week 6
• W7: No lectures; recuperation week
• W8: WED 7/11: T+E: 2nd part ETL; Maintaining and refreshing the database INFOH419 Data Warehousing - Week 8
• W9: 15/11: 9 to 5 IBM Proof of Technology: Information Server; in Evere. Replaces normal DW lectures that week.
• W10: WED 21/11: No lecture due to Dutch-Belgian Database Day
• W11:
  • 27/11: 10 to 12 (S.UA4.218) IBM lecture on Netezza by Alex Degrave Slides of the presentation
  • 29/11: 13 to 15 Data Mining; Managing massive data: Stream data processing INFOH419 Data Warehousing - Week 11
• W12: WED 5/12 T+E: Data Stream Processing continued; application of stream processing INFOH419 Data Warehousing - Week 12
• W13:
  • WED 12/12 T+E: Concluding session; summary; Q&A INFOH419 Data Warehousing - Week 13
  • THU 13/12 13 to 15: Presentation by Teradata Slides Teradata
• W14: WED 19/12 T+E: (Extra)