Modeling the Data Warehouse
Chapter 7
Data Warehouse Database Design Phases
Defining the business model (conceptual model)
Creating the dimensional model (logical model)
Modeling summaries
Creating the physical model
Performing Strategic Analysis
Creating the Business Model
Phase 1: Defining the Business Model
Performing strategic analysis
Creating the business (conceptual)model
– Defining business requirements
– Identifying the business measures
– Identifying the dimensions
– Identifying the grain
– Identifying the business definitions
and rules
– Verifying data sources
Creating the Business Model
Performing strategic analysis
Creating the business (conceptual) model
– Defining business requirements
– Identifying the business measures
– Identifying the dimensions
– Identifying the grain
– Identifying the business definitions
and rules
– Verifying data sources
Business Requirements Drive the Design Process
Identifying Measures and Dimensions
Determining Granularity
Identifying Business Rules
Creating the Dimensional Model
Identify fact tables
– Translate business measures into fact tables
– Analyze source system information for
additional measures
– Identify base and derived measures
– Document additivity of measures
Identify dimension tables
Link fact tables to the dimension tables
Create views for users
Dimension Tables
Dimension tables have the following characteristics:
Contain textual information that represents the attributes of the business
Contain relatively static data
Are joined to a fact table through a foreign key reference
Fact Tables
Fact tables have the following characteristics:
Contain numeric measures (metric) of the business
May contain summarized (aggregated) data
May contain date-stamped data
Are typically additive
Have key value that is typically a concatenated key composed of the primary keys of the dimensions
Joined to dimension tables through foreign keys that reference primary keys in the dimension tables
Star Schema Model
Central fact table
Radiating dimensions
Denormalized model
Star Schema Model
Easy for users to understand
Fast response to queries
Simple metadata
Supported by many front end tools
Less robust to change
Slower to build
Does not support history
Snowflake Schema Model
Snowflake Schema Model
Direct use by some tools
More flexible to change
Provides for speedier data loading
May become large and unmanageable
Degrades query performance
More complex metadata
Using Summary Data
Phase 3: Modeling summaries
Provides fast access to precomputed data
Reduces use of I/O, CPU, and memory
Is distilled from source systems and precalculated summaries
Usually exists in summary fact tables
Designing Summary Tables
Average
Maximum
Summary Tables Example
Summary Management in Oracle8i
Using Time in the Data Warehouse
The Time Dimension
Time is critical to the data warehouse
A consistent representation of time is required for extensibility
Creating the Physical Model
Phase 4: Creating the Physical Model
Translate the dimensional design to a physical model for implementation
Define storage strategy for tables and indexes
Perform database sizing
Define initial indexing strategy
Define partitioning strategy
Update metadata document with physical information
Physical Model Design Tasks
Define naming and database standards
Perform database sizing
Design tablespaces
Develop initial indexing strategy
Develop data partition strategy
Define storage parameters
Set initialization parameters
Use parallel processing
Using Data Modeling Tools
Tools with a GUI enable definition, modeling, and reporting
Avoid a mix of modeling techniques caused by:
– Development pressure
– Developers with lack of knowledge
– No strategy
Determine a strategy
Write and publish formally
Make available electronically
Summary
This lesson discussed the following topics:
Creating a business model
Creating a dimensional model
Modeling the summaries
Creating a physical model