Data Warehousing and Integration Part 1

Northeastern University via Coursera

Go to class Write review

Overview

Google, IBM & Meta Certificates — All 10,000+ Courses at 40% Off

One annual plan covers every course and certificate on Coursera. 40% off for a limited time.

Get Full Access

This course will cover various topics in data engineering in support of decision support systems, data analytics, data mining, machine learning, and artificial intelligence. You will study on-premises data warehouse architecture, dimensional modeling of data warehouses, Extract-Transform-Load (ETL) integration from source systems to data warehouse, On-line Analytical Processing (OLAP) systems, and the evolving world of data quality and data governance. It offers you an opportunity to design, develop and maintain cloud-based data pipelines. Both on-premises and cloud-based platforms will be used to illustrate and implement data engineering techniques using operational and analytical data warehouses.

Syllabus

Database Concepts 1

This module introduces data warehousing and business intelligence, emphasizing their role in enhancing organizational decision-making. Data warehouses transform raw data into actionable insights using processes like ETL (Extract, Transform, and Load), supported by tools such as OLAP for querying and data mining. While operational databases (OLTP) are suited for daily transactions, OLAP databases are optimized for complex analytics.

Database Concepts 2

This module builds on the foundations of database design from the previous module, focussing on relational database modeling, normalization, and SQL. The readings will guide you in translating a conceptual EER diagram into a relational model, ensuring adherence to normalization principles and aiming for Third Normal Form (3NF). We’ll also emphasize understanding primary keys and foreign keys for maintaining data integrity and establishing table relationships. You will also have the opportunity to create and critique relational models. We’ll then explore SQL basics, covering syntax (SELECT, INSERT, UPDATE, DELETE), querying techniques (WHERE, ORDER BY, JOIN), and operations involving functions and aggregates (COUNT, SUM, AVG, MIN, MAX), which are fundamental in database querying and management.

Data Warehouse Concepts

This module provides an introduction to data warehouse concepts. Data warehouses are based on a multidimensional model. We will look closely into the multidimensional model and its representation as data cubes (also known as hypercubes). We’ll examine how different aspects of data are categorized into facts, measures, and dimensions. Dimensions such as Product, Time, and Customer are organized hierarchically within a cube, allowing data to be analyzed at various levels of detail. Measures such as Quantity and Sales Amount are stored within these cubes, and analysts can navigate through different levels of detail using "rolling up" and "drilling down" techniques. We will also explore key concepts such as granularity, dimension schema, and member hierarchies, which are essential in understanding how data is structured and analyzed in multidimensional models. Finally, we will learn to use techniques such as disjointness, completeness, and correctness to ensure data accuracy and integrity when aggregating information in data cubes, collectively known as summarizability.

Conceptual Design 1

In this module we’ll explore conceptual modeling with multidimensional models, visualized using MultiDim. This approach helps us organize data into facts and dimensions and understand the relationships between them, which is essential for designing data warehouses. We’ll explore topics such as dimensions (e.g., date, customer) and measures (e.g., quantity, total sales) in more detail. We’ll also explore the difference between primary events and secondary events and learn how they are used. Finally, we will look at another categorization of Measures into Flow: Level and Unit Measures.

Conceptual Design 2

In this module, we’ll dive into conceptual modeling of hierarchies within data warehouses, exploring their definitions, characteristics, and significance. Balanced hierarchies have a uniform structure where each child has one parent and all branches are of the same length, making data analysis consistent and efficient. In contrast, unbalanced hierarchies have varying branch lengths and missing aggregation levels, offering flexibility to model real-world scenarios like product categories and geographical hierarchies. You’ll also be introduced to generalized hierarchies, which involve "is-a" relationships between supertypes and subtypes, allowing for detailed data representation but requiring careful management of aggregation and specialization. We’ll also explore alternative hierarchies, showcasing different ways to organize the same dimension, such as calendar vs. fiscal views of time. Finally, we’ll look at parallel hierarchies, both independent and dependent, as tools for analyzing data from multiple perspectives, representing complex organizational structures. Understanding these hierarchy types is crucial for effective data management and analysis in data warehousing.

Logical Modeling

In this module, you’ll explore logical modeling in data warehousing, which is the process of designing a structured, abstract representation of data to be stored, focusing on how data is organized, related, and optimized for efficient querying and analysis. Building on what you learned in the previous modules, you'll take the next step in data warehouse design: translating a conceptual model into a logical model for implementation. The module will focus on the relational representation of data warehouses, including the study of various schema implementations: star, snowflake, starflake, and constellation. You'll also examine the rules for mapping a multidimensional conceptual model to a relational model, highlighting the role and importance of different types of keys in this process. We'll also discuss strategies for maintaining consistency in a data warehouse. Finally, you'll explore how to pre-populate certain dimensions, like time, to streamline operations and improve query performance.

Slowly Changing Dimensions

Designing a data warehouse is a complex process that requires transitioning from high-level conceptual models to detailed logical models. This transition is critical because it bridges the gap between understanding business needs and translating them into a technical framework that effectively supports those needs. In this module, you’ll expand on the logical modeling process covered in the previous module, with a particular focus on dimensional model design and the intricacies of hierarchy modeling. As you delve deeper, you’ll encounter logical modeling for advanced concepts such as many-to-many dimensions, links between facts, and facts with multiple granularities. We’ll also explore the concept of Slowly Changing Dimensions (SCDs), which are essential for managing historical data in your warehouse. You’ll learn how to implement different SCD types to accurately track and manage changes in dimension data over time. Finally, we’ll touch on SQL for OLAP, focusing on advanced concepts like aggregation and window functions, and you’ll learn how to use SQL to query and analyze data warehouses.