DATA WAREHOUSE DATA MODELING AND DESIGN

BACKGROUND: The data warehouse enables users to access vast stores of integrated, operational data to track business trends, facilitate forecasting and planning efforts, and make strategic decisions. Many organizations are building data warehouses, unfortunately, not all are using data models.

Data models represent the core of what the business knows about itself -- that is, the most basic part of its business knowledge. Operational activities depend directly on such data models, and are constrained by the assumptions they make. These assumptions are called business rules. Business rules represent the terms, facts, and rules held in common across all activities. A data model unifies and formalizes these business rules so that they can be applied consistently and modified with less impact. Building a high quality, integrated data model is essential, not only for database design (which requires it), but for planning, designing, and/or acquiring any information system—even smaller ones. The development of a data model is best accomplished in stepwise fashion, following a top down, business oriented approach. Entity relationship (ER) modeling is typically used to get started.

DESCRIPTION: This seminar is a combination lecture and workshop, wherein the attendee will the essential strategies and techniques necessary to design a data warehouse or data mart to meet the goals of usability and performance. This course will discuss the atomic level physical data warehouse model level. The foundation for the data warehouse is the atomic, or organizationally structured, level of data and serves as the single, integrated foundation that addresses all of the informational processing requirements of the organization. Also, discussed in detail is the creating of the star schema based on a sample set of business requirements for information.

Upon completing this seminar the student will:

• Review standard data modeling processes, tasks, and techniques
• Understand the importance of data modeling in the data warehousing
• Know how to do dimensional data modeling
• Know how to transform operational data models into data warehouse data models
• Understand common problems and issues with data warehouse data models

WHO SHOULD ATTEND: Data warehouse project managers, system developers, system and data architects, database administrators, and those interested in how data can be turned into information and how to compete in the marketplace with information.

INSTRUCTOR: Don Meyer is president of Don Meyer & Associates. He advises clients on all aspects of database administration, Unix/network administration, client/server and data warehouse implementations, including conducting training courses and, conducting database performance audits. Don is Oracle 7 DBA Certified and has published articles on client/server downsizing migrations.

• Why use data models
• Overview of the different types of data warehouse data models
• High level subject area data models
• Logical data models
• Physical data models
• Dimensional data models

• Overview of a data warehouse
• OLTP –vs- OLAP
• Data vs process vs object models

• Entity, Attributes, Relationships, Cardinality, Domain Integrity, Primary keys, E/R diagrams
• Normalization – attribution, first, second, & third normal form
• Denormalization
• Complex data model patterns
• Entity dependency, domain entity types, associative relationships, rule-based relationships, transactional relationships.
• Recursive hierarchies, Super and Sub type relationships, time dependent data

• Gathering Requirements
• Identifying data sources
• When an operational data model doesn’t exist
• Data Warehouse modeling issues
• Metadata

• Tables, columns and rows
• RI
• Model denormalization
• System generated keys
• Derived attributes
• Logical and physical model synchronization

• Definition
• Star
• Dimensions
• Facts
• How to solve common problems
• Too many dimensions

Workshop

Duration: 1 Day

Prerequisites: Data Warehouse Technical Overview Course & OLTP data modeling familiarity

Special requirements: None