About this Course

This course teaches students practical methods and techniques for designing and constructing a data warehouse. Students will learn how to apply the techniques in the context of an incremental data warehouse development process which is suitable for constructing a data warehouse from a departmental perspective. In addition, students will also learn how this process can further be extended for the construction of consistent, corporate-wide data warehouses. As such, the course is suitable for students who wish to learn how to construct a data mart as well as a corporate-wide, integrated data warehouse.
During the course, students will apply the techniques they learn doing several exercises, which are part of a case study which is consistently developed throughout the course. This approach greatly enhances the practical value of the course, by allowing students to apply the acquired knowledge in practice, under the guidance of the instructor.

Audience Profile

This course is designed for students who have a reasonable knowledge of, and some experience with, data modeling and with database design and construction techniques in general:

• Data modelers
• Database designers
• Database administrators
• Data architects
• Data warehouse project leaders with a technical interest in the matter
• All other technical professionals with an interest in designing and constructing a data warehouse environment

At Course Completion

After completing this course, students will be able to:

• Have a thorough understanding of what a data warehouse is and how it can be enabled
• Be able to apply data warehouse enablement techniques in practice, in projects of non-trivial scope

Prerequisites

Before attending this course, students must have:

• Working knowledge of Transact-SQL.
• Working knowledge of Relational databases.
• Database design skills.

Course Outline

• What Is a Data Warehouse?
• Data Warehousing Today
• Future Trends in Data Warehousing

• Data Flow Architecture
• System Architecture
• Case Study

• Designing the Dimensional Data Store
• Dimension Tables
• Date Dimension
• Slowly Changing Dimension
• Product, Customer, and Store Dimensions
• Subscription Sales Data Mart
• Supplier Performance Data Mart
• CRM Data Marts
• Data Hierarchy
• Source System Mapping
• Designing the Normalized Data Store Waterfall Methodology
• Iterative Methodology

• Hardware Platform
• Storage Considerations
• Configuring Databases
• Creating DDS Database Structure
• Creating the Normalized Data Store
• Using Views
• Summary Tables
• Partitioning
• Indexes

• Introduction to ETL
• ETL Approaches and Architecture
• General Considerations
• Extracting Relational Databases
• Extracting File Systems
• Extracting Other Source Types
• Extracting Data Using SSIS
• Memorizing the Last Extraction Timestamp
• Extracting from Files

• Stage Loading
• Data Firewall
• Populating NDS
• Using SSIS to Populate NDS
• Upsert Using SQL and Lookup
• Normalization
• Practical Tips on SSIS
• Populating DDS Dimension Tables
• Populating DDS Fact Tables
• Batches, Mini-batches, and Near Real-Time ETL
• Pushing the Data In

• Data Quality Process
• Data Cleansing and Matching
• Cross-checking with External Sources
• Data Quality Rules
• Action: Reject, Allow, Fix
• Logging and Auditing
• Data Quality Reports and Notifications

• Metadata in Data Warehousing
• Data Definition and Mapping Metadata
• Data Structure Metadata
• Source System Metadata
• ETL Process Metadata
• Data Quality Metadata
• Audit Metadata
• Usage Metadata
• Maintaining Metadata

• Data Warehouse Reports
• When to Use Reports and When Not to Use Them
• Report Wizard
• Report Layout
• Report Parameters
• Grouping, Sorting, and Filtering
• Simplicity
• Spreadsheets
• Multidimensional Database Reports
• Deploying Reports
• Managing Reports

• What a Multidimensional Database Is
• Online Analytical Processing
• Creating a Multidimensional Database
• Processing a Multidimensional Database
• Querying a Multidimensional Database
• Administering a Multidimensional Database