Data Warehouse Concepts, Design, and Data Integration

About this course: This is the second course in the Data Warehousing for Business Intelligence specialization. Ideally, the courses should be taken in sequence. In this course, you will learn exciting concepts and skills for designing data warehouses and creating data integration workflows. These are fundamental skills for data warehouse developers and administrators. You will have hands-on experience for data warehouse design and use open source products for manipulating pivot tables and creating data integration workflows.You will also gain conceptual background about maturity models, architectures, multidimensional models, and management practices, providing an organizational perspective about data warehouse development. If you are currently a business or information technology professional and want to become a data warehouse designer or administrator, this course will give you the knowledge and skills to do that. By the end of the course, you will have the design experience, software background, and organizational context that prepares you to succeed with data warehouse development projects. In this course, you will create data warehouse designs and data integration workflows that satisfy the business intelligence needs of organizations. When you’re done with this course, you’ll be able to: * Evaluate an organization for data warehouse maturity and business architecture alignment; * Create a data warehouse design and reflect on alternative design methodologies and design goals; * Create data integration workflows using prominent open source software; * Reflect on the role of change data, refresh constraints, refresh frequency trade-offs, and data quality goals in data integration process design; and * Perform operations on pivot tables to satisfy typical business analysis requests using prominent open source software

Created by:  University of Colorado System

• Taught by:  Michael Mannino, Associate Professor

  Business School, University of Colorado Denver

Basic Info Course 2 of 5 in the Data Warehousing for Business Intelligence Specialization Language English How To Pass Pass all graded assignments to complete the course. User Ratings 4.5 stars Average User Rating 4.5See what learners said Coursework

Each course is like an interactive textbook, featuring pre-recorded videos, quizzes and projects.

Help from your peers

Connect with thousands of other learners and debate ideas, discuss course material, and get help mastering concepts.

Certificates

Earn official recognition for your work, and share your success with friends, colleagues, and employers.

University of Colorado System The University of Colorado is a recognized leader in higher education on the national and global stage. We collaborate to meet the diverse needs of our students and communities. We promote innovation, encourage discovery and support the extension of knowledge in ways unique to the state of Colorado and beyond.

Syllabus


WEEK 1


Data Warehouse Concepts and Architectures



Module 1 introduces the course and covers concepts that provide a context for the remainder of this course. In the first two lessons, you’ll understand the objectives for the course and know what topics and assignments to expect. In the remaining lessons, you will learn about historical reasons for development of data warehouse technology, learning effects, business architectures, maturity models, project management issues, market trends, and employment opportunities. This informational module will ensure that you have the background for success in later modules that emphasize details and hands-on skills.You should also read about the software requirements in the lesson at the end of module 1. I recommend that you try to install the software this week before assignments begin in week 2.


8 videos, 15 readings expand

1. Video: Course introduction video lecture
2. Video: Course objectives video lecture
3. Reading: Powerpoint lecture notes for lesson 1
4. Video: Course topics and assignments video lecture
5. Reading: Optional textbook
6. Reading: Powerpoint lecture notes for lesson 2
7. Video: Motivation and characteristics video lecture
8. Reading: Powerpoint lecture notes for lesson 3
9. Video: Learning effects for data warehouse development video lecture
10. Reading: Powerpoint lecture notes for lesson 4
11. Video: Data warehouse architectures and maturity video lecture
12. Reading: Powerpoint lecture notes for lesson 5
13. Video: Applications and market trends video lecture
14. Reading: Powerpoint lecture notes for lesson 6
15. Video: Employment opportunities video lecture
16. Reading: Powerpoint lecture notes for lesson 7
17. Reading: Overview of software requirements
18. Reading: Pivot4J installation
19. Reading: Pentaho Data Integration installation
20. Reading: Overview of database software installation
21. Reading: Oracle installation notes
22. Reading: Making connections to a local Oracle database
23. Reading: Optional textbook reading material

Graded: Module 1 quiz

WEEK 2


Multidimensional Data Representation and Manipulation



Now that you have the informational context for data warehouse development, you’ll start using data warehouse tools! In module 2, you will learn about the multidimensional representation of a data warehouse used by business analysts. You’ll apply what you’ve learned in practice and graded problems using Pivot4J, an open source tool for manipulating pivot tables. At the end of this module, you will have solid background to communicate and assist business analysts who use a multidimensional representation of a data warehouse.


7 videos, 8 readings expand

1. Video: Data cube representation video lecture
2. Reading: Powerpoint lecture notes for lesson 1
3. Video: Data cube operators video lecture
4. Reading: Powerpoint lecture notes for lesson 2
5. Video: Overview of Microsoft MDX video lecture
6. Reading: Powerpoint lecture notes for lesson 3
7. Video: Microsoft MDX statements video lecture
8. Reading: Powerpoint lecture notes for lesson 4
9. Video: Overview of Pivot4J video lecture
10. Reading: Powerpoint lecture notes for lesson 5
11. Video: Overview of WebPivotTable video lecture
12. Reading: Powerpoint lecture notes for lesson 6
13. Video: Pivot4J software demonstration video lecture
14. Reading: Optional textbook reading material
15. Reading: Pentaho Pivot4J tutorial

Graded: Module 2 quiz
Graded: Assignment for module 2
Graded: Quiz for module 2 assignment

WEEK 3


Data Warehouse Design Practices and Methodologies



This module emphasizes data warehouse design skills. Now that you understand the multidimensional representation used by business analysts, you are ready to learn about data warehouse design using a relational database. In practice, the multidimensional representation used by business analysts must be derived from a data warehouse design using a relational DBMS.You will learn about design patterns, summarizability problems, and design methodologies. You will apply these concepts to mini case studies about data warehouse design. At the end of the module, you will have created data warehouse designs based on data sources and business needs of hypothetical organizations.


6 videos, 8 readings expand

1. Video: Relational database concepts for multidimensional data video lecture
2. Reading: Powerpoint lecture notes for lesson 1
3. Video: Table design patterns video lecture
4. Reading: Powerpoint lecture notes for lesson 2
5. Video: Summarizability patterns for dimension tables video lecture
6. Reading: Powerpoint lecture notes for lesson 3
7. Video: Summarizability patterns for dimension-fact relationships video lecture
8. Reading: Powerpoint lecture notes for lesson 4
9. Video: Mini case for data warehouse design video lecture
10. Reading: Powerpoint lecture notes for lesson 5
11. Video: Data warehouse design methodologies video lecture
12. Reading: Powerpoint lecture notes for lesson 6
13. Reading: Practice problems for module 3
14. Reading: Optional textbook reading material

Graded: Module 3 quiz
Graded: Assignment for module 3

WEEK 4


Data Integration Concepts, Processes,and Techniques



Module 4 extends your background about data warehouse development. After learning about schema design concepts and practices, you are ready to learn about data integration processing to populate and refresh a data warehouse. The informational background in module 4 covers concepts about data sources, data integration processes, and techniques for pattern matching and inexact matching of text. Module 4 provides a context for the software skills that you will learn in module 5.


6 videos, 7 readings expand

1. Video: Concepts of data integration processes video lecture
2. Reading: Powerpoint lecture notes for lesson 1
3. Video: Change data concepts video lecture
4. Reading: Powerpoint lecture notes for lesson 2
5. Video: Data cleaning tasks video lecture
6. Reading: Powerpoint lecture notes for lesson 3
7. Video: Pattern matching with regular expressions video lecture
8. Reading: Powerpoint lecture notes for lesson 4
9. Video: Matching and consolidation video lecture
10. Reading: Powerpoint lecture notes for lesson 5
11. Video: Quasi identifiers and distance functions for entity matching video lecture
12. Reading: Powerpoint lecture notes for lesson 6
13. Reading: Optional reading material

Graded: Module 4 quiz

WEEK 5


Architectures, Features, and Details of Data Integration Tools



Module 5 extends your background about data integration from module 4. Module 5 covers architectures, features, and details about data integration tools to complement the conceptual background in module 4. You will learn about the features of two open source data integration tools, Talend Open Studio and Pentaho Data Integration. You will use Pentaho Data Integration in guided tutorial in preparation for a graded assignment involving Pentaho Data Integration.


6 videos, 7 readings expand

1. Video: Architectures and marketplace video lecture
2. Reading: Powerpoint lecture notes for lesson 1
3. Video: Common features of data Integration tools video lecture
4. Reading: Powerpoint lecture notes for lesson 2
5. Video: Talend Open Studio video lecture
6. Reading: Powerpoint lecture notes for lesson 3
7. Video: Pentaho Data Integration video lecture
8. Reading: Powerpoint lecture notes for lesson 4
9. Video: Software video demonstration for Pentaho Data Integration
10. Reading: Optional reading material
11. Reading: Guided tutorial for Pentaho Data Integration
12. Reading: Documents for the module 5 assignment
13. Video: Course conclusion video lecture

Graded: Module 5 quiz
Graded: Assignment for module 5
Graded: Quiz for module 5 assignment