Executive Summary
This paper provides best practice recommendations that you can apply when designing a physical data model to support the competing workloads that exist in a typical 24×7 data warehouse environment.
It also provides a sample scenario with completed logical and physical data models. You can download a script file that contains the DDL statements to create the physical database model for the sample scenario.
This paper targets experienced users who are involved in the design and development of the physical data model of a data warehouse in DB2 Database for Linux, UNIX, and Windows or IBM® InfoSphere® Warehouse Version 9.7 environments. For details about physical data warehouse design in Version 10.1 data warehouse environments, see “DB2 Version 10.1 features for data warehouse designs” on page 31.
For information about database servers in OLTP environments, see “Best practices: Physical Database Design for Online Transaction Processing (OLTP) environments” at the following URL:
http://www.ibm.com/developerworks/data/bestpractices/databasedesign/.
Introduction to data warehouse design
A good data warehouse design is the key to maximizing and speeding the return on investment from your data warehouse implementation. A good data warehouse design leads to a data warehouse that is scalable, balanced, and flexible enough to meet existing and future needs. Following the best practice recommendations in this paper, you set your data warehouse up for long-term success through efficient query performance, easier maintenance, and robust recovery options.
Designing a data warehouse is divided into two stages: designing the logical data model and designing the physical data model.
The first stage in data warehouse design is creating the logical data model that defines various logical entities and their relationships between each entity.
The second stage in data warehouse design is creating the physical data model. A good physical data model has the following properties:
- The model helps to speed up the performance of various database activities.
- The model balances data across multiple database partitions in a clustered warehouse environment.
- The model provides for fast data recovery.
The database design should take advantage of DB2 capabilities like database partitioning, table partitioning, multidimensional clustering, and materialized query tables.
The recommendations in this paper follow some of the guidelines for the IBM Smart Analytics System product to help you develop a physical data warehouse design that is scalable, balanced, and flexible enough to meet existing and future needs. The IBM Smart Analytics System product incorporates the best practices for the implementation and configuration of hardware, firmware, and software for a data warehouse database. It also incorporates guidelines in building a stable and scalable data warehouse environment.
Download the full report for more on physical database design for data warehouse environments.
Download the report to get started!#Db2