One-Day (BI-DIMMODD-101-EN)

This one day dimensional modeling workshop focuses on the basic design patterns for building relational database structures for data mart or data warehouse applications on SQL Server.

The workshop begins with an overview of dimension modeling theory and builds on the justification of dimension modeling by reviewing the core structures involved in data marts and data warehouses: conformed dimension tables and fact tables.  The workshop then applies these principles to implementation practices for SQL Server including storage and indexing best practices.

The workshop concludes with a review of meta data management for dimensional modeling including ETL batch and lineage tracking, auditing, and reporting.

This workshop is intended for IT professionals (DBAs and Developers) who are responsible for designing the relational database structures for a Business Intelligence solution.  Data architects who have background in transactional normalization will also benefit from this workshop to help transition their skills from OLTP (on-line transaction processing) to OLAP (on-line analytical processing) design principles.

Before attending this workshop, it is recommended that students have the following skills:

• Relational database experience on any RDBMS platform
• Conceptual understanding of Business Intelligence, DSS, or Data Warehousing objectives

Course Objectives 

Upon completion of this workshop, the student will be able to:

• Understand dimension table concepts such as attributes, hierarchies and surrogate key usage
• Understand fact tact concepts such as measures and aggregation types
• Work with user community and data sources to plan out dimension and fact structures that meet business requirements
• Apply the conceptual knowledge of dimensional design to an implementation on SQL Server

Course Outline

Module 01: Introduction: Concepts and Background

• Definitions
• Business Intelligence Background
• Business Intelligence Architecture
• Dimensional Modeling (Introduction; Concepts)

Module 02: Designing Dimension

• Dimension Overview
• Understanding Hierarchies
• Dimension Keys
• Types of Dimensions
• Tracking History

Module 03: Designing Fact Tables

• Fact Table Overview
• Measures (Additive Nature; Types of Measure Aggregations)
• Types of Fact Table
• Volume Considerations
• Getting Started on Fact Table Design

Module 04: Physical Database Considerations

• Naming Conventions
• Indexing and Keys
• Physical Storage

Module 05: Capturing and Using Meta Data

• Meta Data Background
• Including the Basics