Portals eNewsletters Web Seminars dataWarehouse.com DM Review Magazine
DM Review | Information Is Your Business
   Information Is Your Business Advanced Search

Business Intelligence
Corporate Performance Management
Data Integration
Data Quality
Data Warehousing Basics
Master Data Management
View all Portals

Scheduled Events

White Paper Library
Research Papers



DM Review Home
Current Magazine Issue
Magazine Archives
DM Review Extended Edition
Online Columnists
Ask the Experts
Industry News
Search DM Review

Tech Evaluation Center:
Evaluate IT solutions
Buyer's Guide
Industry Events Calendar
Software Demo Lab
Vendor Listings

About Us
Press Releases
Advertising/Media Kit
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

Are dimension tables for a DW the same as those in a OLTP system?

    Ask The Experts published in DMReview.com
January 2, 2007
  By Joe Oates and Chuck Kelley

Q: I have two questions: 1) Can I consider dimension tables similar to the master tables in an OLTP system? 2) Is transactional data used to populate fact tables only?

Joe Oates' Answer:

Your question will be answered with the assumption that you are speaking about a multidimensional design as opposed to a normalized design.

Dimension tables are similar to master tables or reference tables in OLTP systems. These tables represent business concepts such as customer, product, employee, etc. In most OLTP systems, master or reference tables are generally in second or third normal form. In other words, it may take several tables to describe all of the characteristics of a business concept such as product.

In a multidimensional design, dimension tables are typically denormalized or "flat" tables. Many dimension tables, such as product, are generally implemented as flat hierarchies (e.g., all of the parent and child relationships represented by separate tables in the OLTP system are combined into a single hierarchical table) or as a "core" table containing columns of interest to queries across all lines of business and one or more subordinate tables specific to a particular line of business. A database view can be used to make the core and one of the subordinate tables look like a single flat hierarchy. This denormalization increases query performance and also generally makes it easier to write queries. Most BI presentation tools assume a multidimensional design.

An important concept for data warehouse dimension tables is that they replace be OLTP primary keys with integer primary keys that are generated in the ETL process. These integer primary keys are called surrogate keys. Often, OLTP primary keys are composite keys which are made up of more than one column. Integer keys generally provide better query performance then do composite primary keys.

Fact tables represent the data warehouse equivalent of an OLTP transaction table. Typically, fact tables in a data warehouse are "enriched." By that I mean that fact tables contain information in the form of foreign keys that are not found in the OLTP transaction table. For example, fact tables typically contain foreign key references to a Date dimension and a Geography dimension. These are added during the ETL operation.

Additionally, the OLTP transaction table foreign keys that reference Master tables are replaced by the surrogate keys that reference data warehouse dimension table primary keys.


For more information on related topics visit the following related portals...
DW Basics and DW Design, Methodology.

Joe Oates is an internationally known speaker, author and consultant on data warehousing. Oates has more than 30 years of experience in the successful management and technical development of business, real-time and data warehouse applications for industry and government clients. He has designed or helped design and implement more than 30 successful data warehouse projects. He can be reached at joates_48323@yahoo.com.

Chuck Kelley is an internationally known expert in database and data warehousing technology. He has 30 years of experience in designing and implementing operational/production systems and data warehouses. Kelley has worked in some facet of the design and implementation phase of more than 50 data warehouses and data marts. He also teaches seminars, co-authored four books on data warehousing and has been published in many trade magazines on database technology, data warehousing and enterprise data strategies. He can be contacted at chuckkelley@usa.net.

Solutions Marketplace
Provided by IndustryBrains

Free DB Modeling Trial with ER/Studio
Design and Build More Powerful Databases with ER/Studio.

Recover SQL Server or Exchange in minutes
FREE WHITE PAPER. Recover SQL Server, Exchange or NTFS data within minutes with TimeSpring?s continuous data protection (CDP) software. No protection gaps, no scheduling requirements, no backup related slowdowns and no backup windows to manage.

Database Access Tool
Reverse engineers relational schemas and automatically generates data access code that can be used in Service Oriented Architectures.

Validate Data at Entry. Free Trial of Web Tools
Protect against fraud, waste and excess marketing costs by cleaning your customer database of inaccurate, incomplete or undeliverable addresses. Add on phone check, name parsing and geo-coding as needed. FREE trial of Data Quality dev tools here.

DeZign for Databases - Database Design Made Easy
Create, design & reverse engineer databases with DeZign for Databases, a database design tool for developers and DBA's with support for Oracle, MySQL, MS SQL, MS Access, DB2, PostgreSQL, InterBase, Firebird, NexusDB, dBase and Pervasive.

Click here to advertise in this space

E-mail This Ask The Experts E-Mail This Ask The Experts
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
Site Map Terms of Use Privacy Policy
SourceMedia (c) 2007 DM Review and SourceMedia, Inc. All rights reserved.
SourceMedia is an Investcorp company.
Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.